[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2003 On not in list event issue

Posted on 2009-04-30
4
Medium Priority
?
370 Views
Last Modified: 2012-06-22
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

0
Comment
Question by:bobrossi56
  • 2
4 Comments
 
LVL 75
ID: 24275519
"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
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 1000 total points
ID: 24275568
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
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 24275746
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
 

Author Closing Comment

by:bobrossi56
ID: 31576719
Works like a charm...thx so much
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question