A common scenario is how to handle the way Access responds when a user attempts to select a value not shown in a drop-down "combo box." (Naturally, I'm assuming that the properties of "limit to list" is set to "yes.") Normally, Access generates its own message, which entry-level beginners may not understand. You can always insert a MsgBox code in the "Not in List" event procedure of the box, but Access will still generate its own message regardless. Below is code which allows you to specify your own error message. The line of code which prevents Access from showing its default message is shown in bold:
|
Private Sub Active_Inactive_NotInList(NewData As String, Response As Integer)
MsgBox "You Must Select An Item in the List" Response=acDataErrContinue End Sub |
Naturally, you may wish to give the user the option to add a new entry to the list. One way I do this is to have a very small form based on the table the combo box is deriving its values from. I have this form open up after the user clicks "ok" on the MsgBox . The code looks similar to below:
|
Private Sub Active_Inactive_NotInList(NewData As String, Response As Integer)
MsgBox "You Must Select An Item in the List" Response=acDataErrContinue DoCmd.OpenForm "customer_form", acNormal, "", "", acAdd, acNormal DoCmd.GoToControl "customer_name" End Sub |
In the above code, the "DoCmd.Open" line opens up the "customer_form" and does so in the "add new" mode. Of course, you would substitute your own form name in the place of "customer_form."
The "GoToControl" line makes the cursor go in the field you wish. Normally, when a form is opened in the "add new" mode, the cursor is in the "autoid" field or somewhere like that, and generally you are forced to click the mouse in the proper field. This line of code prevents the need for that.
There is more about this at Microsoft's Knowledge Base, articles Q88148 and Q161007 .