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

    Updating combobox on one form after changes made on another

    Set Up: I have three different departments that use respective forms I've built to track their new business. These forms are not the same, only some parts are, and so i have 3 separate forms in my database to accommodate the differences (Yes I have normalized my database). To add a new customer as they're doing their data entry, I have an Add Customer button that opens an "Add New Customer" form. My question is - is there a way, on the on-click event of the Add New Customer save/close button, to then update the combobox on the form they also have open at that time so that they do not need to refresh the New Business form to update the combo box and continue on working? I tried the code below - which worked - but only for one form. So I'm not sure if there is a way to update the code to apply to all forms or if I should have an individual Add New Customer form for EACH new business log? Thoughts?



    Private Sub btnAddCustomerSaveandClose_Click()
    Me.Refresh
    Forms!F_CLNBLog.Form.cboCLNBCustomerName = Me.CustomerID
    Forms!F_CLNBLog.Form.cboCLNBCustomerName.Requery
    DoCmd.Close
    End Sub

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Depending on what the rowsource is for the combo, you could do something along this approach:

    -add the new Customer info to the Customer table
    -if the rowsource of the combo is something like
    SELECT CustomerID, CustomerFName, CustomerLName from Customer
    -since you have added the new Customer to the Customer table,
    you can use Me.Requery to repopulate the combo's rowsource.

    Good luck.

    Here is a reference to an Allen Browne article re comboboxes.

  3. #3
    Kiley is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jun 2016
    Posts
    28
    Thank you! I ended up pulling together a few different solutions that I found worked best. For anybody coming behind me with the same problem, my solution is below.

    For each form that had a combobox for the CustomerID field and needed the capability to add a new customer when it was not in the list. On the comboboxes 'On Not In List' event I added the code:

    ************************
    Begin Code Example
    ************************
    Private Sub cboCLNBCustomerName_NotInList(NewData As String, Response As Integer)
    Dim MsgBoxAnswer As Variant

    Response = acDataErrContinue

    MsgBoxAnswer = MsgBox("Do you want to add this new customer?", vbYesNo, "Add New Customer?")

    If MsgBoxAnswer = vbNo Then
    Me.CustomerID = Null
    DoCmd.GoToControl "YOURCOMBOBOXNAME"
    Else
    DoCmd.OpenForm ("YOURADDCUSTOMERFORM")
    DoCmd.GoToRecord , , acNewRec
    Forms![YOURADDCUSTOMERFORM]![YOURFIELDNAME] = NewData 'This will pull what you've already written in the previous form onto your Add New Customer form so you do not need to re-type anything.
    End If
    End Sub
    ************************
    End Code Example
    ************************

    On the Add New Customer Form, I needed to be able to save the customer name to my customer table, but I also wanted that name to appear back in the form I just left so that I didn't need to additionaly refresh anything, I could just move onto my next field entry. I also needed this to conditionally apply to the applicable form that was open, as there are 5 different forms you can add a new customer from. To accomplish this, I added the following code to my Add New Customer forms' Save and Close button OnClick Event:

    ************************
    Begin Code Example
    ************************

    Private Sub btnAddCustomerSaveandClose_Click()

    On Error GoTo exitline

    If CurrentProject.AllForms("F_CLNBLog").IsLoaded Then
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_CLNBlog.Undo
    Forms!F_CLNBlog.Refresh
    Forms!F_CLNBlog!cboCLNBCustomerName.Requery
    Forms!F_CLNBlog!cboCLNBCustomerName = Me.CustomerID
    DoCmd.Close
    ElseIf CurrentProject.AllForms("F_CLCancelLog").IsLoaded Then
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_CLCancellog.Undo
    Forms!F_CLCancellog.Refresh
    Forms!F_CLCancellog!cboCLCancelCustomerName.Requer y
    Forms!F_CLCancellog!cboCLCancelCustomerName = Me.CustomerID
    DoCmd.Close
    ElseIf CurrentProject.AllForms("F_PENBLog").IsLoaded Then
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_PENBlog.Undo
    Forms!F_PENBlog.Refresh
    Forms!F_PENBlog!cboPENBCustomerName.Requery
    Forms!F_PENBlog!cboPENBCustomerName = Me.CustomerID
    DoCmd.Close
    ElseIf CurrentProject.AllForms("F_PECancellationLog").IsL oaded Then
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_PECancellationlog.Undo
    Forms!F_PECancellationlog.Refresh
    Forms!F_PECancellationlog!cboPECancelCustomerName. Requery
    Forms!F_PECancellationlog!cboPECancelCustomerName = Me.CustomerID
    DoCmd.Close
    ElseIf CurrentProject.AllForms("F_NBQULog").IsLoaded Then
    DoCmd.RunCommand acCmdSaveRecord
    Forms!F_NBQULog.Undo
    Forms!F_NBQULog.Refresh
    Forms!F_NBQULog!cboNBQUCustomer.Requery
    Forms!F_NBQULog!cboNBQUCustomer = Me.CustomerID
    DoCmd.Close
    End If
    exitline:
    End Sub
    ************************
    End Code Example
    ************************

    I hope this helps somebody else. It took a lot of head scratching to get here. I am by no means an expert at this, and am still new at VBA, but learning quickly. Good luck!

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

Similar Threads

  1. Combobox selections on Form are updating data
    By b_rye_chan in forum Forms
    Replies: 4
    Last Post: 07-28-2016, 03:37 PM
  2. Replies: 8
    Last Post: 10-26-2014, 10:56 AM
  3. Replies: 2
    Last Post: 09-08-2012, 08:25 PM
  4. Replies: 2
    Last Post: 12-21-2010, 12:03 AM
  5. Combobox not updating
    By lostfan789 in forum Forms
    Replies: 15
    Last Post: 06-16-2010, 09:21 PM

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
  •  
Other Forums: Microsoft Office Forums