requerying a field in a subform

[BobSullivan]

I have a subform with companies listed in it. The subform is called frmVendorLocations. There is a Vendor field, and a VendorLocation field. Both are combo boxes. When someone selects a vendor in the vendor field, I would like the subform to requery the vendorLocation field, using the vendor field in the subform as a filter. I can get the requery to work when the form is not a subform. but when it's a subform, I'm getting errors. (method or data member not found, etc). I'm guessing there's a simple fix to get Access to understand that the requery is supposed to take place within the subform, but I don't know the specifics.

Thanks for any help.

[HansV]

What is the Row Source of the VenndorLocation combo box?

[BobSullivan]

Sorry, the table is called TBLVendorLocation. The Field that I mistakenly called Vendor Location is actually called [Production Facility]. The Row source field is this:

SELECT TBLVendorLocation.[Production Facility], TBLVendorLocation.Vendor FROM TBLVendorLocation WHERE (((TBLVendorLocation.Vendor)=[Forms]![FRMProjectVendors]![Vendor])) ORDER BY TBLVendorLocation.[Production Facility];

[HansV]

Thanks. The problem is that Forms]![FRMProjectVendors]![Vendor] is only valid when FRMProjectVendors is open by itself, not when it is a subform on another form. The workaround is to leave the WHERE-condition out of the Row Source:

SELECT [Production Facility] FROM TBLVendorLocation ORDER BY [Production Facility];

or even to leave the Row Source blank in design view, and to change it dynamically in the After Update event of the Vendor combo box:

Code: Select all

Private Sub Vendor_AfterUpdate()
    Me.[Production Facility].RowSource = "SELECT [Production Facility] FROM TBLVendorLocation " & _
        "WHERE Vendor=" & Me.Vendor & " ORDER BY [Production Facility]"
End Sub

The above version assumes that Vendor is a number field. If it is a text field, use

Code: Select all

Private Sub Vendor_AfterUpdate()
    Me.[Production Facility].RowSource = "SELECT [Production Facility] FROM TBLVendorLocation " & _
        "WHERE Vendor=" & Chr(34) & Me.Vendor & Chr(34) & " ORDER BY [Production Facility]"
End Sub

[BobSullivan]

I'm running this on the On change event in the Vendor field. I'm getting an error that reads that Access can't find the field '|1' referred to in your expression.

[HansV]

There is no need to do this in the On Change event. You won't see the dropdown list of the Production Facility combo box until you activate it, so after you have left the Vendor combo box.

[BobSullivan]

I moved the code to the after update event of the vendor field. I'm still getting the same error message: Access can't find the field '|1' referred to in your expression.

Not sure where the |1 is coming from.

[HansV]

The most probable explanation is that I misspelled one of the names in the code. Please check carefully.

[BobSullivan]

Nope. it was my fault. the field in the form is VendorLocation.

Thanks!

[HansV]

Does it work now?

[BobSullivan]

Yep. Again, thanks!

[HansV]

Good to hear that!