Return Name not ID in form from query

[Chief]

Hello,
Access 2010. (Screen shots attached)
I have created a query to feed a form. The query is from a single table client Contact. There is a relationship to another table clients via the clientID in the client contact table.
I run the query and receive all the information from the table including the company as the name NOT the ID.
However when I run the form the client ID is visible NOT the actual name.
I think it is just the field that the Company is directed to??
Im sure it something simple. please see screen shots fro all details.

Thank you
OK I know it is the Control source and row source now, but I am not sure where to set these? it is the same for dept and job title and all three are combo boxes. I dont necessarliy need them to be combos as this list/form is mainly for viewing the data, then click on open to open the contacts full details.
thanks

 

[Beetle]

The Control Source of your Company combo box is ClientID. Therefore, the Row Source for this combo box needs to return the ClientID along with the Company name. The Row Source should look more like;

Select ClientID, Company From Contacts;

Then you set the Column Count to 2 and the Column Widths to 0";1" (or whatever width works best for the second column). That way the combo box will display the Company name but will store the ClientID.

You didn't show the property sheets for your other combo boxes but it is likely the same issue.

 

[Chief]

G'day Beetle,
Thanks for the quick reply.
I made some changes to the properties, see attached screen shots.
The company is now blank.
The control source is ClientID from the query 'File As:' if that is an issue?

thanks.

 

[Beetle]

In your first post your combo box Row Source was based on a table/query named Contacts. Now, your Row Source is based on a table/query named ClientContacts. I'm not sure why the change in record source. It might be helpful if you posted some info about your table structure so we can see what we're working with here.

 

[Chief]

Yes you are right mate, sorry for the confusion.
If you look at the screen shots I have shown the table and the query set up.
There is also shots of the form and the form properties.
I know the control source and row source are not correct, I just don't know how to set these up. The confusion is most likely because I have copied the form from another DB.
If I need to change the query in some way? or just point the control and row source to the right place. the rest of the form fields are pointing to the query field File As: in the query.
If I can supply anything further please let me know. I will supply shots of table structure too for you but the form is using the query to collect data.
Thanks again for your help

Jason.

 

[Beetle]

The CompanyName field is in tblClients, so that's where you need to retrieve it from. The query that the form is based on should return ClientID from tblClientContacts (which it appears to be doing). You should not try to return the CompanyName in the query (not sure if you're trying to do that or not). Then, on your form, the combo box should use ClientID (from the query) as it's Control Source and should have something like the following in the Row Source;

Select ClientID, CompanyName From tblClients

Also, make sure that the Column Count and Column Width properties of the combo box are set accordingly (as I mentioned in a previous post).

 

[Chief]

Thank you Sean
All over it now ;-)
I was under the impression that if I was using a query for the form that all information had to come from that query and not the tables themselves.
Got all three issues working now.

Thanks again.
Jason.