Subform Combo Record Source Issue

J

JK

I have a main form called PurchaseOrders and a subform called
PurchaseOrdersSubform. The main form is opened using another form called
frmPOList. On frmPOList there are two cmd buttons, cmdEdit and cmdEditAll.
There is also a list box that displays all of the open Purchase Orders.

The subform is for the parts that are added to the purchase order. The Main
form contains the SupplierID field. The subform contains a combo box for
PartID. The control source for the PartID field has the SupplierID field
selected and the criteria for this field is
[Forms]![PurchaseOrders]![SupplierID].

When a PO is created, and the SupplierID is selected on the main form, only
the parts assigned to the selected supplier should appear as available
options in the combo boxes (subform.)

If I open frmPOList and double-click a PO, all of the information is
displayed correctly. Any parts assigned to the PO appear as intended. If I
select Edit All or if I just open the PurchaseOrders (main form) by itself,
the parts in the combo do not display correctly. The subform always acts like
SupplierID #1 is on the main form even when it's not.

If I navigate to a PO where the supplierid is anything but #1, the part
record is there but the fields are blank.

I've tried requering a bunch of different ways but can't seem to make it work.

I'm not sure if any of this makes any sense - but if you have any idea what
could be causing this I would love it if you'd let me know.

This is the code behind the cmdEdit button that is working correctly.
Private Sub cmdSome_Click()
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do
If Me!lstCName.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstCName.ItemsSelected
' Grab the PurchaseOrderID column for each selected item
strWhere = strWhere & Me!lstCName.Column(0, varItem) & ","
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the companies form filtered on the selected companies
strWhere = "[PurchaseOrderID] IN (" & strWhere & ") And (ckClosed =
False)"
DoCmd.OpenForm FormName:="PurchaseOrders", WhereCondition:=strWhere
DoCmd.Close acForm, Me.Name
End Sub


This is the code behind cmdEditAll which does not work.
Private Sub cmdAll_Click()
' Open companies unfiltered
DoCmd.OpenForm "PurchaseOrders"
DoCmd.Close acForm, Me.Name
Forms![PurchaseOrders].SetFocus
End Sub
 
J

JK

Nevermind - how annoying. All I had to do was requery the two subform
controls in the subform on current event. Erg. Thx anyway.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top