We help IT Professionals succeed at work.

Saving Date in Access

Medium Priority
61 Views
Last Modified: 2020-02-26
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?
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2014

Commented:
You need add a requery invocation after you add the new customer.
President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:

<< 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..>>


 The way to fix this is to have the combo on the invoice form requery:


   Forms!myInvoiceFormNameHere!myCustomerComboNameHere.Requery


  You can do that either:


1. Off the save button's OnClick event.

2. In the invoice form, do it in the GotFocus event of the combo control.


 The 2nd is not always ideal in that you re-query the control then every time you move to it rather than just once when new data is added.


Jim.



Author

Commented:
Thank You Jim, that worked beautifully!
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

Will,


  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

       Forms!myInvoiceFormNameHere!myCustomerComboNameHere.Requery

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.


Jim.


Author

Commented:
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
Error.PNG
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

Will,


 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)?


Jim.



Author

Commented:
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.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

Will,


   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

        Forms!myInvoiceFormNameHere!myCustomerComboNameHere.Undo

       On Error Goto 0

       Forms!myInvoiceFormNameHere!myCustomerComboNameHere.Requery

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:


https://docs.microsoft.com/en-us/office/vba/api/access.combobox.notinlist


   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

    Else

         ctl.Undo

         Response = acDataErrContinue

    End If


Exit_Procedure:

   Exit Sub


Error_Procedure:

   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).


HTH,

Jim.




Author

Commented:
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.