Problem with relations

[PoorCadaver]

Hi!

I have a number of tables that are related to each other. I e one computer belongs to one user. In the computers table I can choose a user from a drop box that's connected to a table containing all available users.
The column in the computer table that contains information about the user is made so that the id(int) of the user is stored. So far so good and it's not too complicated.

But when I create a form where I list all the computers with their related users, the user is showed in a drop box. When I change it to a text box (or create a new one) it won't show the username, but the user id (primary key).



How do I solve this? Feels like it shouldn't be too difficult...

Thanks!

Jonas

[Bob Fitz]

Hi Jonas

I assume that the form is currently bound to the "Computers" table.
Create a query with the "Computers" table and the "Users" table joined by the UsersID fields in each table and bind the form to this query. The User's Name field will then be available to use as the Control Source of a text box on the form.

[PoorCadaver]

When I create the query (using wizard), do I need to add every column in both tables?

[Bob Fitz]

You can, but you don't need to. You need the fields that you want on the form.

[PoorCadaver]

I have tried now, but then I realized that it can't work. You see, in reality I have more drop boxes in the particular form/table. I'm showing similar information from many tables, so I can't bind the form to just one table, right?

[Bob Fitz]

Create a query that combines all the tables and bind the form to that query.

[PoorCadaver]

I'm sorry, but how do I bind it?

[Bob Fitz]

Use the query as the Record Source for the form.

[PoorCadaver]

Two things didn't work... First of all, the form now only shows one (1) post and not everyone in the computer table. Second, it still displays the Id and not the name =(

[Bob Fitz]

Ok

Can you post a copy of your db?

[PoorCadaver]

This is a stripped example version.
As you can see in the form, the users names are replaced with their Id...
So the big $1.000.000 question is: how do I get the names there instead? =)

Jonas

[Bob Fitz]

Like this?

[PoorCadaver]

What did you do???

[Bob Fitz]

In the last db I changed the Record Source property of the form from the name of a table (Computers) to an SQL statement (an unsaved query)
Take a look at the attached db.
In this I have created a second form (Comp2) using a Saved query (qryComp2) as the Record Source of the form.

[PoorCadaver]

Ok, thanks a lot! I will try this out and see if I get it =)