Combo Box (One to Many Relationship)

[Martano0811]

I have a combo Box in a split form and would like the form to filter only on specific critera. For Example, there is one to many relationship to "Region" in my data and would only like to show all records in the "West" region. Currently when I use the combo box on a specific region it just moves to the next region in the form but includes all regions. I would like to show only the specific region (West, East, Central) Any assistance would be nice. (Im only using one table in this example)

 

[Pat Hartman]

There are at least two ways to do this - the Access way and the client/server way. I always choose the client/server way because it works in all cases.

Change the form so that its RecordSource query references the unbound combo where you are choosing the Region.

Select ....
From ...
Where Region = '" & Forms!yourform!cboyourregion & "'";

or, if you want to allow all

Select ....
From ...
Where Region = '" & Forms!yourform!cboyourregion & "'" Or Forms!yourform!cboyourregion Is Null;

Then in the AfterUpdate event of the region combo put:

Me.Requery

 

[Martano0811]

This helps:

Im getting Error on this in the Record Source query...is there something wrong with the syntax?


SELECT [CPC Weekly Data].Region
FROM [CPC Weekly Data]
Where [CPC Weekly Data].Region = ' " & Forms!CPCREMilestoneFormReport!CboRegion & "'"
GROUP BY [CPC Weekly Data].Region
ORDER BY [CPC Weekly Data].Region;

 

[Pat Hartman]

What is the error message?

Is the region a text field or is it numeric? If it is numeric, it should not be enclosed in quotes.

 

[Martano0811]

Yes, it is Text, "error message in string in query expression": Where [CPC Weekly Data].[Region] = ' " & Forms!CPCREMilestoneFormReport!CboRegion & "'"

 

[Pat Hartman]

Where [CPC Weekly Data].Region = Forms!CPCREMilestoneFormReport!CboRegion

You only need the quotes and the ampersands if you are building a SQL string in code. If this SQL is in the RecordSource, replace it with the above syntax.

 

[Martano0811]

Great thank you Pat, one last thing a prompt is coming up ...."Enter Parameter Value" ....this shouldnt happen should it?

 

[Pat Hartman]

Access is prompting for a parameter because whatever it is is misspelled. Or, if it is asking for the form field, it may be that the form is not open. A form must be open for it to be referenced from a query or other form/report/code.