can you use a table as a data validation list?

[merlin777]

I wan't to limit entries in a field to those that appear in another table. e.g. to only allow people's names that appear in a table of staff members. Names will be added to the table from time to time.

is this possible or is there an alternative? Should I use a form instead?

 

[plog]

You should always use a form if you are going to support users.

Forms will make this task extremely simple. You create a drop down, set the source to your table, then check the property on the drop down that limits the data to just the list.

And just to be clear, you should not store the person's name in this table (let's call this T2). You should have an autonumber primary key in your Staff table. That is the value you should actually store in T2. It would be a foreign key (google that) to your Staff table from T2.

 

[merlin777]

Thanks for that. Let's see if i've understood you correctly.

i create a form
create a drop down
set the source as my staff table and limit the data to this list
my staff table has auto number as its primary key

I don't quite understand T2. Is this the table that I'm trying to populate? I understand the staff name field in T2 will contain a number and not a name but where does it come from?

Is this number the auto number field in the staff list table?

sorry to be so dense.

 

[plog]

Yes, T2 is the table you are adding the people to, not the Staff table. Staff table will have this autonumber primary key (StaffID) and all the other fields related to your staff (FirstName, LastName, DOB, etc.). In T2 you will simply store StaffID. That's how you do it properly.

Now we are talking about your form, which feeds data into T2. On this form you will have a drop down, its record source will be a query from StaffTable. The drop down will show the names of the people, but behind the scenes it will actually be storing the StaffID in T2. That's how you make it work properly.

 

[merlin777]

do i need to create a query to be the record source or does that happen automatically?

 

[merlin777]

and, last dumb question (for now)....

my T2 now has a number in the staff name field. Is there a way to view the data that shows the name rather than the number?

 

[plog]

do i need to create a query to be the record source or does that happen automatically?

Yes, sort of. The query you create will exist just on that form. You set up the drop down on your form, then go into its Properties and click on the elipses (...) in the one that says 'Row Source'. That will bring up the query designer, you select your Staff table, make the first column StaffID and the next column the Lastname. That's the query.

my T2 now has a number in the staff name field. Is there a way to view the data that shows the name rather than the number?

Yes, make an actual query. Bring in T2 and your Staff table, join them appropriately and bring down all the fields you want to see.

 

[merlin777]

What do you mean by 'join them appropriately'?

I already have them joined by a relationship - is that the same thing?

 

[plog]

In your query, you would JOIN them StaffID to StaffID. In design view that means a line would go between the two.

 

[merlin777]

I already have a relationship line drawn between them - is that the same thing?

 

[plog]

I don't know what you mean. Give whatever you're doing a shot and when it doesn't work, post back. No need to extra re-double verify everything before trying.

 

[merlin777]

I don't know what you mean. Give whatever you're doing a shot and when it doesn't work, post back. No need to extra re-double verify everything before trying.

sorry about that - it's a bad habit....