Filter lookup data list based on data in another field

[David Hochhauser (DHeshMan]

I have looked through the knowledge base and tried to do a cascade lookup but
I'm having difficulty. For simplicity I have genericized the names of my
items.

Table: Xs; Fields: X_ID, X_Y_ID, X_Z_ID
Table: Ys; Fields: Y_ID
Table: Zs; Fields: Z_ID, Z_Y_ID

Relationships: Ys one-to-many with Xs
Zs one-to-many with Xs
Ys one-to-many with Zs

Xs.X_Y_ID Row Source = SELECT Ys.Y_ID FROM Ys;
Xs.X_Z_ID Row Source = SELECT Zs.Z_ID FROM Zs;
Zs.Z_Y_ID Row Source = SELECT Ys.Y_ID FROM Ys;

I would like to be able to choose my Xs.X_Y_ID and then filter the list of
choices possible for my Xs.X_Z_ID based on the relationship between Ys and
Zs. So I tried the following Row Source for my Xs.X_Z_ID

SELECT Zs.Z_ID, Zs.Z_Y_ID FROM Zs WHERE (Zs.Z_Y_ID=[X_Y_ID]);

But this returned with an empty list instead of a filtered list.

I tried using Me.X_Y_ID instead of [X_Y_ID] but the "Me." was not recognized
when viewing the table.

Any help would be greatly appreciated.
Thanks

 

[scubadiver]

If I may, I would like to back up slightly with your relationships

You say each "Y" has many "X"s and each "Z" also has many "X"s. This tells
many that each "Y" has many "Z"s and each "Z" has many "Y"s since X is being
used as a junction table.

Have you connected Y and Z directly? I am not entirely sure why would have to.

 

[Jamie Collins]

If I may, I would like to back up slightly with your relationships

You say each "Y" has many "X"s and each "Z" also has many "X"s. This tells
many that each "Y" has many "Z"s and each "Z" has many "Y"s since X is being
used as a junction table.

Have you connected Y and Z directly? I am not entirely sure why would have to.

I agree the OP should back up a little but I think your logic is
flawed.

Let 'X' by 'wheel', 'Y' be 'lorry' and 'Z' be 'hand-cart'.

Ys one-to-many with Xs: each lorry has many wheels
Zs one-to-many with Xs: each hand-cart has many wheels
Ys one-to-many with Zs: each lorry has many hand-carts

How did you arrive at each Z has many Ys? Probably from the OP's vague
table descriptions, which look to be flawed anyhow. I think we need
some test data, preferably with the real entity names.

Jamie.

--

 

[David Hochhauser (DHeshMan]

Ok,
Here is a typical use case.

Purchases: Purchase_ID, P_Date, P_Vendor_ID, P_Quantity, P_Item_ID
Vendors: Vendor_ID, V_Name
Items: Item_ID, I_Name, I_Vendor_ID

Vendor 1 sells Item 1 and Item 2
Vendor 2 sells Item 3 and Item 4
Vendors is 1-to-many with Items

Purchase 1 came from Vendor 1
Purchase 2 came from Vendor 1
Purchase 3 came from Vendor 2
Vendors is 1-to-many with Purchases

Purchase 1 was 5 of Item 2
Purchase 2 was 10 of Item 2
Purchase 3 was 5 of Item 3
Items is 1-to-many with Purchases

Data Entry: Within Purchases, enter the Date, select the Vendor, and enter
the quatity. Have the row source for P_Item_ID be a list of available items
based on the selected Vendor to choose from.

 

[George Nicholson]

When Vendor is selected:

"SELECT Items.Item_ID, Items.I_Name FROM Items WHERE Items.I_Vendor_ID = " &
VendorID just Selected

Pretty sure Northwind.mdb has an example, or variation, of this. Very
standard.

HTH,


"David Hochhauser ([email protected])"

 

[David Hochhauser (DHeshMan]

Thanks. I couldn't find where to put that in the table definition, but I was
able to put it into a form. However, instead of putting it on an event of
the Vendor_ID, I put it on the GotFocus event of the Item_ID which worked
like a charm.

Private Sub P_Item_ID_GotFocus()
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items WHERE ([_Items].[I_Vendor_ID] = " & Me.P_Vendor_ID & ") ORDER BY
[_Items].I_Name;"
End Sub

 

[David Hochhauser (DHeshMan]

For the record, after some hand shaking and data checking here is my final
implementation:

Private Sub P_Item_ID_GotFocus()
' If we have a valid Vendor ID
If Me.P_Vendor_ID > 0 Then
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items WHERE ([_Items].[I_Vendor_ID] = " & Me.P_Vendor_ID & ") ORDER BY
[_Items].I_Name;"

Else
' filter the list with a dummy value to create an empty list
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items WHERE ([_Items].[I_Vendor_ID] = 0) ORDER BY [_Items].I_Name;"

End If
End Sub

Private Sub P_Item_ID_LostFocus()
' Remove the Filter - This was needed to support the datasheet view.
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items ORDER BY [_Items].I_Name;"
End Sub

Dim Temp_ID

Private Sub P_Vendor_ID_GotFocus()
Temp_ID = Me.P_Vendor_ID.Value
End Sub

Private Sub P_Vendor_ID_Change()
If Not Temp_ID = Me.P_Vendor_ID.Value Then
' If we truly have a data change event clear the P_Item_ID
P_Item_ID.Value = ""
End If
End Sub

Thanks. I couldn't find where to put that in the table definition, but I was
able to put it into a form. However, instead of putting it on an event of
the Vendor_ID, I put it on the GotFocus event of the Item_ID which worked
like a charm.

Private Sub P_Item_ID_GotFocus()
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items WHERE ([_Items].[I_Vendor_ID] = " & Me.P_Vendor_ID & ") ORDER BY
[_Items].I_Name;"
End Sub

When Vendor is selected:

"SELECT Items.Item_ID, Items.I_Name FROM Items WHERE Items.I_Vendor_ID = " &
VendorID just Selected

Pretty sure Northwind.mdb has an example, or variation, of this. Very
standard.

HTH,


"David Hochhauser ([email protected])"

 

[George Nicholson]

I couldn't find where to put that in the table definition,

...that's because there isn't a place for it in the table definition. You can
only do that kind of thing in a form.

HTH,



"David Hochhauser ([email protected])"

Thanks. I couldn't find where to put that in the table definition, but I
was
able to put it into a form. However, instead of putting it on an event of
the Vendor_ID, I put it on the GotFocus event of the Item_ID which worked
like a charm.

Private Sub P_Item_ID_GotFocus()
P_Item_ID.RowSource = "SELECT [_Items].Item_ID, [_Items].I_Name FROM
_Items WHERE ([_Items].[I_Vendor_ID] = " & Me.P_Vendor_ID & ") ORDER BY
[_Items].I_Name;"
End Sub

When Vendor is selected:

"SELECT Items.Item_ID, Items.I_Name FROM Items WHERE Items.I_Vendor_ID =
" &
VendorID just Selected

Pretty sure Northwind.mdb has an example, or variation, of this. Very
standard.

HTH,


"David Hochhauser ([email protected])"
message