Limit field choices based on another field

[grep]

I have a PO form with a subform for Items. The subform includes the
following fields:

Make, Model, Qty, UnitPrice

Make pulls data from MakesTable
Model pulls from ModelsTable, and includes a foreign key field
referencing MakesTable.

What I'd like to do is have the user select Make (i.e. Microsoft, Cisco,
etc.) and then select Model, but I'd like Model to only include those
values in ModelsTable where ModelsTable.MakeID = the previous field's
MakeID. In other words, I only want to show models for the make selected
in the previous field.

If no make is selected, Model should be blank.

How do I do that?

grep

 

[Allen Browne]

See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

 

[grep]

Allen,

The examples provided are helpful, but I've found something that makes
no sense to me.

I now have Model's control source as the table's Model field. I have
also created the stored query:

SELECT ProductID, "(" & PartNum & ") " ProductName AS Product, MakeID
FROM ProductsTable WHERE MakeID = Forms!PurchaseOrders!PO_Details!Make;

When I run the query with the form open and the appropriate selection in
the Make field, it shows the appropriate options. When I set this stored
query as the Row Source, however, I see nothing in the Model field, even
after a requery.

I have set the number of columns as 3 with column widths of 0";3";0" and
the bound column of 1. This *should* show me the correct info.
Something's wrong and I'm not sure how to troubleshoot it.

Any suggestions?

grep

 

[Allen Browne]

I'm not making sense of the SQL statement.

The middle field will end up being something like:
(99) ProductName
and I can't see that being a valid field name.

 

[grep]

I see what you're saying, and when I reduce it to just the fieldname
alone, it works properly. Interesting because I routinely use that type
of syntax to produce results like, "Browne, Allen", instead of two
fields: "Browne" and "Allen". To get that display result, I enter the
following in the Field section of one of the Fields in the Query Design
window:

Name: LastName & ", " & FirstName

When I look in the SQL window, the results would be something like:

SELECT Contacts.LastName & ", " & Contacts.FirstName AS Name FROM Contacts;

Then I would use the field Name as the source or otherwise desired
display reference. I didn't know that wouldn't work here also, as this
really is only a display reference. The actual field value being stored
is ProductID.

In my case, what I want to *see* in the field is "(BEFSX41) Broadband
Firewall Router w/VPN Endpoint" (assuming the value showing in Make is
Linksys).

grep