Good day all! I am hoping somebody can help me find a better way to do this. Currently i have a form with many different comboboxes and fields for each record. I have a CoWentTo combobox and AgWentTo combobox I am having issues updating. If they enter a name that does not already exist for any of those fields, the NotOnList event triggers and opens a second form. One in which, for CoWentTo for example, they can add a new company name to the company table. On the OnClick event for my "Save" button on this form, i have the following code:

Private Sub btnAddCoWentToSaveandClose_Click()
On Error GoTo exitline

If CurrentProject.AllForms("F_Cancellations").IsLoade d Then
DoCmd.RunCommand acCmdSaveRecord
Forms!F_Cancellations.Undo
Forms!F_Cancellations.Refresh
Forms!F_Cancellations!cbocowentto.Requery
Forms!F_Cancellations!cbocowentto = Me.CoWentToID
DoCmd.Close
Else
DoCmd.Close
End If
exitline:
End Sub

This works fine, until they move to the next field: AgWentTo combobox and they have to also add a new agent that doesn't exist in that table, they go through the same process, this time with a second form: "Add Agent", with the same code:

Private Sub btnAgWentToSaveandClose_Click()
On Error GoTo exitline

If CurrentProject.AllForms("F_Cancellations").IsLoade d Then
DoCmd.RunCommand acCmdSaveRecord
Forms!F_Cancellations.Undo
Forms!F_Cancellations.Refresh
Forms!F_Cancellations!cboagwentto.Requery
Forms!F_Cancellations!cboagwentto = Me.AgWentToID
DoCmd.Close
Else
DoCmd.Close
End If
exitline:
End Sub

When they save and close out of that second form, it clears the new company they just added prior to because at that point the record was considered "Dirty" (which makes sense, as my code says if dirty, then undo), and so the user has to start all the way from the beginning now and re-enter information.



To "fix" this, i have added an AfterUpdate event to save the record once they leave the AccountRep field (which sits just before the CoWentTo combobox). I then added an "On Got Focus" event save the record when they tabbed over from "CoWentTo" to the next field "AgWentTo".

This method ends up prompting the user up to three times during their record entry if they have to enter a new Company and Agency each time due to the main form having a BeforeUpdate event to fire off the message box "Do you want to save changes?".

I know there has got to be a better way to do this. Does anybody have any thoughts? In summary:

  • User completes AccountRep field selection and hits tab > Record is saved. They move onto next field, "CoWentTo"
  • User ADDS new Company by way of NotOnList event firing off, opening a 2nd form "Add Company". Once user clicks Save and Close on that form, it closes, and the new company is showing in the CoWentTo combobox now. They move onto next field, "AgWentTo"
  • User ADDS new Agency by way of the NotOnList event firing off, opening a 2nd form "Add Agency". Once user clicks Save and Close on that form, it closes, and the new agency is showing in the AgWentTo combobox now. They move onto next fields and complete record.
  • Record is complete and user is prompted: do you want to save changes? Y/N


Thanks in advance for any assistance!!! It is greatly appreciated!