Subform/Combo box

[doodle]

Greetings gurus. Windows xp, access 97

I am having trouble with a combo box updating a subform. It doesn't
make sense to me, because I have numerous forms set up this way and
they work fine. I'm missing something. This is what I have:

Main Form = frmOrderEntry
Form Control Source = tblOrders
Combo Box on Main Form = cmbShipperID
Combo Box Row Source = tblShipper
Combo Box Control Source = ShipperID
Bound Column = 1
Column Count = 1

Subform = frmShipper
Control Source = tblShipper
Link Child = ShipperID
Link Master = ShipperID
Fields = txtAddress, txtAddress2,txtPhone,txtFax

tblShipper Fields
ShipperID
Address1
Address2
Phone
Fax

tblOrders Fields
OrderNum
ShipperID

Should work like this:

The user selects a Shipper from the combo box on the main form and the
subform updates with the address, phone and fax for that shipper.

Someone please enlighten me. I don't see anything wrong with what I
have here.

-doodle

 

[Douglas J. Steele]

You could put code in the AfterUpdate of the combo box to set a filter on
your subform:

Private Sub cmbShipperID_AfterUpdate()
Me.subForm.Form.Filter = "ShipperID = " & Me.cmdShipperID
Me.subForm.Form.FilterOn = True
End Sub

Likely, your subForm control name will be the same as the form being used as
a subform, so you'll probably need to use

Private Sub cmbShipperID_AfterUpdate()
Me.frmShipper.Form.Filter = "ShipperID = " & Me.cmdShipperID
Me.frmShipper.Form.FilterOn = True
End Sub

 

[doodle]

Doug,

When I try to comiple this, I get a message that says "Method or data
member not found for cmbShipperID:

Private Sub cmbShipperID_AfterUpdate()
Me.frmShipper.Form.Filter = "ShipperID = " & Me.cmbShipperID
Me.frmShipper.Form.FilterOn = True

End Sub

 

[Douglas J. Steele]

What line's highlighted?

On your main form, take a look at the container that holds the subform. Is
it named frmShipper? If not, change frmShipper to whatever that control is
named.

 

[doodle]

The highlighted portion is .cmbShipperID, which doesn't amke much sense
to me since it obviously exists since the event is for Private Sub
cmbShipperID_AfterUpdate()

 

[Douglas J. Steele]

Where have you got the code: in the form that contains the combo box, or
(mistakenly) in the subform?

 

[doodle]

It is in the form with the combo box. I've made a little progress. Now
I am getting a different error. It says that I can't assign a value to
this object and breaks on the first line. Here is the code:

Private Sub cmbShipperID_AfterUpdate()
Me.frmShipper.Form.Filter = "ShipperID = " & Me.cmbShipperID
Me.frmShipper.Form.FilterOn = True

End Sub