I have an Access 2003 DB with a main form, used to enter customer renter issues.. The name field is a drop down that has 2 events. After Update event populates address, city, state, zip fields based on the customer/renter information name entered (via a DLOOKUP to the customer table) This works fine.
The second event is a On not in list event, so that if the name they type in the customer/renter name field is not found in the customer table, it asks them if they want to add it. When they say yes, a form pops up, allowing the user to enter this customer/renter info in the customer table. This works good too, but the complaint is, lets say I type Jack Smith in the customer name field, and it comes up and says Jack Smith is not in the list, do you want to add it, and then I say yes and the customer form pops open, it does not take the originally typed Jack Smith and populate the name field on the pop-up form.
Now, even more annoying is that once the new customer/renter info it typed into the pop-up form, when you exit the form, it does not populate the name field on the main form with the name you just typed into the pop-up form.
I hope this makes sense, it was hard to explain. Below is the On not in list code
Dim MsgStr As String
MsgStr = "'" & NewData & "' is not setup as a Customer/Renter. Do you want to add it?"
If vbYes = MsgBox(MsgStr, vbYesNo + vbQuestion, "New Customer/Renter?") Then
DoCmd.OpenForm "Renter", acNormal, , , acFormAdd, acDialog
Response = acDataErrAdded
Response = acDataErrDisplay