Bind a query to a subform/datasheet for sorting (1 Viewer)

[EricI]

I have a form that displays a company with a subform (datasheet) that shows the contact records linked to that company. I want to sort the contact records by the last name column rather than the ID. After searching the forum, I believe the way to do this is to bind a query to the subform instead of the table.

Sounds easy enough, but I am not exactly sure how to do this. I can create a query and select the fields I want and indicate which column(s) should be sorted. I can go into the properties of the subform and change the record source from the table to the query.

What I can't figure out is how to use the CompanyID from the company record with the query so that I only get the contacts for that company.

I am using Access 2000.

Can anyone provide some guidance for me?

Thanks.

Eric

 

[SQL_Hell]

you need a form reference in your query.

Say if you had a text box on your form called txtCompanyId


you would do this:


select *
from table
where CompanyId = forms!form1!txtCompanyId

Where is says form1 you need to change that to the name of the main form that the subform resides in.

 

[Rich]

Provided the CompanyID is in the record source then just set the Child/Master link fields on the MainForm property sheet

 

[EricI]

you need a form reference in your query.

I opened the query in design mode, added the CompanyID field from Contact, and then in the criteria field, I used the expression builder to reference the CompanyID from the Company form. This did the trick for me. Thanks for the tip!!

Eric