Help with Combo Box based on query

[neilhoop]

Hello.

I'm a new Access 2007 user.

I have two tables, Landlords and Tenants

To keep things simple at this stage each Landlord has an ID number for each record and each Tenant has an ID number and a corresponding Landlord's ID number for each record.

I have a form (Certs) that has a control on it (ID Client) that shows the Landlord's ID number. I would like to add a Combo Box to the form to list all the Tenants for a particular Landlord. I have built a query using the Query Wizard and modified it to try and do the above but I can not work out how to reference the ID Client control value on my form. Can anybody help?

I hope that I have explained this well enough.

 

[Poppa Smurf]

In your Landlord Combo box you will need to have the combo box bound to the Landlord's Client Number. In your Tenants combo box I assume you have the Client Number as a field in your tenant record. This Client number needs to be in the record source for Tenants combo box. Now in the record source for Tenants combo box, in the criteria of the Client Number field enter Forms!(name of your form)!(name of your landlord combo box)

 

[neilhoop]

I've read your reply several times but unfortunately I don't understand it.

I don't think I've explained what I want to do and what I've already done very well so here goes .....

On my form at the moment I have a text box showing the Landlord's ID (ID Client)

I want to add a combo box so that I can list all Tenants belonging to the Landlord and choose one to populate another text box (ID Job) with with Tenants ID. I will then use the value of ID Job to link to a sub form to display the Tenant record.

I have sort of got this working at the moment with a 'standard' combo box. It lists the Landlord's ID, Tenant ID, Name and first address line. The problem is that it lists all Tenants not just the ones applicable to one Landlord.

I built a query that should pull all the records correctly and created a combo box based on that query.

I figured out before your reply how to reference my text box on my Certs form, in my case, [Forms]![Certs]![ID Client]

I have put this into the criteria for the query but I only ever get one record returned even though there is more than one.

Any help would be appreciated.

------------------------
neilhoop

 

[Poppa Smurf]

Please post a copy of your database, so that I can understand what you are trying to achieve.

 

[neilhoop]

No need, all sorted now.

I'd some how managed to include another table my query that wasn't needed. Got rid of the unwanted table, the query works perfectly as does the combo box, thanks for your help anyway.

---------------
neilhoop