Adding new records in Access without wrecking the form

Question

I'm working on a simple Access 2003 application to keep track of things that need to be done for clients for some colleagues. Each colleague has a set of clients, and each client has a set of actions that need to be taken by a certain date. I've set up a form that consists of a combobox for client ID (indexed), a drop-down for the person who is handling that client's case, and a button for adding new clients (a standard Access-created Add Record button). The actions are listed in a subform below these three elements.

The problem I've run into is that the first person I tested this on clicked the button to add a new record, then didn't fill it out and tried to select another client from the drop-down list. Access interprets this as an attempt to set the selected Client ID as the ID for the new record and rightfully throws an error for duplicate primary keys. I can think of a couple of ways around this problem, but I'd much rather hear your elegant solutions than kludge together some junk in a language I don't know.

Let me know if you have any questions. Thank you.

Answer 1

Do not use the same control for data entry and navigating. it is sure to confuse. Create a textbox for entering new client IDs and change the combo so that it is no longer bound to the client id field (column).

Answer 2

One key user interface principle that many people seem to miss:

Don't allow the user to do something you don't want them to do.

Now, in your case, based on how you've described things, I agree with @Remou that you need separate combo boxes, one for navigation, one for assigning the client.

But if for some reason you really need to do what you describe, the key is to disable the client combo box until the record is saved. So in the code behind your Add New command button, you'd set the combo box's .Enabled property to false. In the form's AfterInsert event, you'd set it back to True.

The idea is simply to make it imposslble for the user to do the wrong thing.

Sounds simple, but it's much harder to do than it sounds, since as the programmer you almost always have a perspective that prevents you from seeing your user interface the same way the user does. This is why I always say that in any of applications there are as many bugs left to be found as there are users who haven't tried it yet.