How to requery a combobox on one form from another form?

[10 Gauge]

Hey y'all I have a bit of a predicament.



I'm working on a PO tracking system and I've got it done for the most part and it's currently in use. I am however having a problem with it and hoping someone can help me out.

On my main form I have 2 subforms (PO's and Return PO's) both with comboboxes which list our vendors - stored in their own table. When someone has the "Add/Edit Vendor" form open and has added or edited vendors I would like to have the main forms subforms update to reflect those additions or changes when the user presses the "close form" button on the "Add/Edit Vendors" form.

I played around with all sorts of different basic strings of code trying to get this to work and got a vb error every time.

Thanks for your help guys!

Basic Form Structure:

*FormA
-SubformA
-SubformB

*FormB

Basically looking to have a control on FormB that will requery the comboboxes on SubformA and SubformB.

I am thinking the string should look something like this....

Forms!POTracker![PurchaseOrders subform].Form!Requery

[10 Gauge]

This looks as if it may have worked... Does this look correct?

Private Sub CloseForm_Click()
If Me.Dirty Then
        Me.Dirty = False
    End If
    Forms!POTracker![PurchaseOrders subform].Form!Vendor_ID.Requery
    DoCmd.Close acForm, Me.Name
End Sub

[randman1]

That looks right to requery one of the subforms.

I prefer a different method by raising custom events. That way, the Add/Edit Vendor form doesn't have to specifically know what to update and won't throw an error if the form is opened from the DB window without the main form open. It requires a little more coding but is much more flexible.

In the declaration section of the Add/Edit Vendor form, declare a custom event:

Public Event AllDoneHere()

The form's OnClose event, raise the custom event:

Private Sub Form_Close()
     RaiseEvent AllDoneHere()
End Sub

In the declaration section of your main form, declare a module level object variable for the Add/Edit Vendor form:

Private WithEvents AddEditForm as Form_[the forms actual name]

Also in the main form, select AddEditForm from the left dropdown list (Object list) and AllDoneHere from the right dropdown list (Procedures). The editor will create a new code block for the custom event. Add the code to requery the subforms:

Private Sub AddEditForm_AllDoneHere()
     Me.PurchaseOrderSubform.Form.Requery
     Me.OtherSubformName.Form.Requery
     '... Anything else you want to do after closing the AddEditForm ...

     Set AddEditForm = Nothing
End Sub

The biggest difference is how you open the Add/Edit Vendor form. Rather than DoCmd.OpenFom "Add/EditVendor" method, you create a new Instance of the form. This is easily accomplished using this method:

Public Sub cmdOpenAddEditForm_Click()
     Set AddEditForm = New Form_[actual form name]
     AddEditForm.Visible = True
End Sub