Multiple Comboboxs

[kevygee]

Thanks in advance for any ideas on my issue. I'm new to using VBA with Access so I don't quite grasp the power of what they can do together. But anyway, here's my problem:

I have a form that contains a combobox. The values in the combobox are a result of a query to another table. If the user enters a value not on the list, an addNewEntry form pops up where the user can enter the data for a new entry in the combobox. When the form closes, the combobox updates nicely. Here's the code I use to do it (which I graciously borrowed from http://support.microsoft.com/suppor...N-US&SD=gn&FR=0)

On the main form, I have this code

Private Sub Building_NotInList(NewData As String, Response As Integer)
   CR = Chr$(13)

         ' Exit this subroutine if the combo box was cleared.
         If NewData = "" Then Exit Sub

         ' Ask the user if he or she wishes to add the new customer.
         Msg = "'" & NewData & "' is not in the list." & CR & CR
         Msg = Msg & "Do you want to add it?"
         If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
            ' If the user chose Yes, start the Customers form in data entry
            ' mode as a dialog form, passing the new company name in
            ' NewData to the OpenForm method's OpenArgs argument. The
            ' OpenArgs argument is used in Customer form's Form_Load event
            ' procedure.
            DoCmd.OpenForm "addbldgform", , , , acAdd, acDialog, NewData
         End If
    
    
    Dim Result
    Result = DLookup("[BuildingID]", "Building", _
                  "[BuildingID]='" & NewData & "'")
         If IsNull(Result) Then
            ' If the customer was not created, set the Response argument
            ' to suppress an error message and undo changes.
            Response = acDataErrContinue
            ' Display a customized message.
            MsgBox "Please try again!"
         Else
            ' If the customer was created, set the Response argument to
            ' indicate that new data is being added.
            Response = acDataErrAdded
         End If
End Sub

Where building is the name of the combobox. Also, on the actual form that pops up to enter data (in this case addbldgform), there is this code:

Private Sub Form_Load()
         If Not IsNull(Me.OpenArgs) Then
            ' If form's OpenArgs property has a value, assign the contents
            ' of OpenArgs to the CompanyName field. OpenArgs will contain
            ' a company name if this form is opened using the OpenForm
            ' method with an OpenArgs argument, as done in the Orders
            ' form's CustomerID_NotInList event procedure.
            Me![BuildingID] = Me.OpenArgs
         End If
      End Sub

Okay, now here's my problem. I have a set of three comboboxes, each one querying a list of contacts. So there's a primary contact, secondar contact and tertiary contact, all getting their values from the same place. So if someone wanted to enter a new contact in the primary contact field, they would type in the name and the form would pop up and they could enter their information. But this does not update the other two comboboxes. Is there a way to update the other two boxes when one of others gets updated?

 

[godofhell]

You can try a refresh or requery.
To do a refresh enter docmd.runcommand accmdrefresh
to do a requery enter me.myfield.requery

I have attached a database that uses search fields, the principal is the same, note what happens to the Cascade Combox when you enter a portion of the First Name. To see all the names enter * in the First Name Text box, then enter portions of the name or full name to see the results on the combo box. The combobox runs off a query string with criteria set to run to match the values like the First Name textbox. You will see that the First Name textbox uses an after update event to refresh the data. If you need further assistance let me know.

Godofhell:
www.kavalpewter.com

 

[sudhirhinduja]

Thank you

Thank you,
Your suggestions here worked like a charm!
Suds.