Avatar of Will Schmidt
Will Schmidt
Flag for United States of America asked on

Saving Date in Access

In Access I have created a customer database and an invoice for my customers.  When a new customer comes in I input their info in the customer database and then in the invoice I us a combo box to find the customer and populate the info in the invoice. The problem I am running into is that when I enter a new customer and then go to the invoice it cannot find the customer info..  I have to click on refresh, refresh all, before it will see the new customer even though I have created a saved button and click on "save".   Is there a way to get Access to save the customer record so that I can go directly to the invoice and pull up the customer info without going through so many steps?
DatabasesMicrosoft Access

Avatar of undefined
Last Comment
Will Schmidt

8/22/2022 - Mon

You need add a requery invocation after you add the new customer.
Jim Dettman (EE MVE)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Will Schmidt

Thank You Jim, that worked beautifully!
Jim Dettman (EE MVE)


  Since that did the job, if you used option #1 (the OnClick of the save button), you are going to want to add one more thing and have it look like this:

If CurrentProject.AllForms("myInvoiceFormNameHere").IsLoaded Then


End If

 The reason is that if you leave it the way it is now and use the customer form when the invoice form is not open, you will get an error.

 Note there are other ways to avoid the error, and even other ways to handle the situation you had (refreshing forms, combo's and list boxes when data changes), but this is the most straightforward.


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Will Schmidt

Everything seems to be working except if I type in a name in the invoice that isn't in the customer database, then go to the customer database, enter the customer's new name in I get an error like what you see in the picture that I uploaded
Jim Dettman (EE MVE)


 What are you using to input the customer;  A text control, combo, or list box?

 And how are you moving to the customer form?   Are you bringing up the customer form on your own or using a Not in List event (if you are using a combo or list box)?


Will Schmidt

Ok, I am pretty green at this, but I learn a little each day when I find time to work on it, but I am using a combo box in the invoice to find the customer in the customer database.  I am bringing up the customer form on my own.  I eventually want to use a Not in List event, but for now this is the way I am doing it.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jim Dettman (EE MVE)


   So this is why one typically wants to use the Not In List event.   That allows you to handle inserting a record or putting up a form for entry, and then doing the requery of the control.   You can also cancel the event and just let it come back to the control.   It also allows you to keep the value that was typed.   Overall, it's a far better way of handling lookup's.

 The error you are getting is because you've typed something into the control, but you have not moved out of it yet, so the data is not yet committed.   That value must be saved or un-done, then the control requery'd

  Since typically you limit lookup's to valid values only, if you want to stick with the current setup, then you'd have to un-do what was typed.   That would be done like this:

If CurrentProject.AllForms(" myInvoiceFormNameHere").IsLoaded Then

       On Error Resume Next


       On Error Goto 0


End If

But this is starting to get messy.   A requery alone wasn't going to hurt anything, but the .undo might as the customer form doesn't know what's going on in your invoice form.   You may be on another control currently and just jumped to the customer form to look something up for a reason not related to the current invoice.

With the code above, it simply knows the invoice form is open, so it's going to un-do the value in the customer combo and requery it every time.

 So you need to switch to using the NotInList event now. The docs for the NotInList event are here:


   But in short, using this event, you get the data that was typed in a variable called NewData and you set the variable  Response to one of the predefined constants ( acDataErrDisplay , acDataErrContinue, or  acDataErrAdded).   Based on that, Access knows what to do when the event is exited.

   In the procedure then, you can then do anything you need to to add the new entry; ask the user if they really want to add the record, create it, pop up a form to let them enter a new record filling in the data they typed, or whatever.

  Here's an example of adding a customer record:

Option Compare Database

Option Explicit

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

   Dim strSQL As String

   Dim strMsg As String

   Dim ctl As Control

   On Error Goto Error_Procedure

   Set ctl = Screen.ActiveControl

   strMsg = "The customer " & NewData & " is not in the list" & vbCrLf & "Do you want to add it?"

   If MsgBox(strMsg, vbYesNo, "Not listed") = vbYes Then

        strSQL = "INSERT INTO Customers (CustomerName) VALUES('" & NewData & "');"

         CurrentDb.Execute strSQL, dbFailOnError

         Response = acDataErrAdded



         Response = acDataErrContinue

    End If


   Exit Sub


   Msgbox "Unexpected Error #" & err.number & " - " & err.description

   Response = acDataErrContinue

   Resume Exit_Procedure

 End Sub

   In place of the add of the customer record, you might want to pop up a form that's modal (must be dealt with before anything else) that simply adds a customer record.    Or you can add the record, then open the form to the new record to let them finish filling out the record.

   What you do there depends on your needs and what's required to add something new for the list.

   NotInList is one of the features built into Access that makes life easier for you.   If you continued with your old method, you'd need to add a flag to indicate that yes, I'm currently entering the customer and it needs to be dealt with, or doing the refresh of the customer combo's in another way (and there are more sophisticated methods of dealing with this problem).



Will Schmidt

Thanks so much for helping me with this.  I am going to wait till Saturday to work with this and get back to you.  You have been a big help.