Saving Date in Access

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.

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.

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.