Results 1 to 2 of 2

Record is not saving when adding new record from 2nd form to update combobox

  1. #1
    Kiley is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2016
    Posts
    28

    Record is not saving when adding new record from 2nd form to update combobox

    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!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    5,194
    Are the bulleted points what you have happening or want?
    I'm not sure I follow the process/problem. I don't get why you affect the record in process on the 1st form. If the "new input" form control (e.g. [Company]) is bound, closing that form is one of the triggers that will save a record. A Save/Close button is a nice touch and provides some user intuition, but isn't absolutely necessary. While DoCmd.Close should work, Me.Dirty = False is a common precursor to closing the input form and in your case, requerying the 1st form combo pertaining to the data just entered would be necessary. Note that I said requery the combo - not the form. Maybe that's your issue as you seem to be reloading a form when a record is in the process of being entered.

    Also, if you have 'many' combos on form 1, do you have a separate form for adding to the list for each one of them? That would be overkill.
    Lastly, please use code tags (#) around your code along with indentation. Not only does it make it easier to read, it prevents extra spaces like what you have in line 3 of the first procedure.
    - "doesn't work" is no help. Post err msgs and where.
    - Use code tags for code/sql. Implement changes in copies of your database.
    Code isn't smart. It does what you ask with what you give it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to update record with combobox selection
    By vector39 in forum Programming
    Replies: 3
    Last Post: 09-25-2017, 08:19 AM
  2. Replies: 6
    Last Post: 05-09-2017, 08:13 AM
  3. Replies: 7
    Last Post: 03-05-2016, 02:04 PM
  4. Update ComboBox when clicking next record
    By MrDummy in forum Forms
    Replies: 6
    Last Post: 12-07-2015, 01:58 PM
  5. Replies: 4
    Last Post: 08-14-2012, 06:14 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums