Access 2003 On not in list event issue

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
'ADD customer/renter...
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
Else
    Response = acDataErrDisplay
End If

Open in new window

bobrossi56Asked:
Who is Participating?
 
Kelvin SparksConnect With a Mentor Commented:
Add an OpenArgs to the end of your OpenForm line using the NewData as the argument.

For the Form that opens

In the on load event

Try

If me.openArgs <> "" then
   me.txtCustomerName = openArgs
end if

After the reponse in your notinList you may have to vall the afterupdate event again


Kelvin
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"it does not take the originally typed Jack Smith and populate the name field on the pop-up form."

How are you passing this name to the pop up form?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Expanding on what Kevin suggested a bit ...

Private Sub cboRenter_NotInList(NewData As String, Response As Integer)

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, NewData   ' add NewData OpenArgs
    Response = acDataErrAdded
    Me.YourCustomerTextBoxOnMainForm = NewData  ' populate name on main form here
Else
    Response = acDataErrDisplay
End If

And I might suggest changing this

If me.openArgs <> "" then
   me.txtCustomerName = openArgs
end if

to

If Nz(me.openArgs , "") >""  then
   me.txtCustomerName = openArgs
end if

mx
0
 
bobrossi56Author Commented:
Works like a charm...thx so much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.