Tables to Tables

[billythedj66]

Hello Access Users,
I am pretty proficient in Excel but now that I have to work in Access I am like a fish out of water. I have a table (Employees) with two fields; Name and Phone Number, I also have another table (Projects) in which there is a field to look up an employee name. That part works fine. What I would like to have is after the employee name is chosen, the next field will automatically show the employee's phone number. I am sure there is a way to do this, I just don't know how. Any help would be greatly appreciated.

Bill

 

[Cbrine]

Billy,

It is possible to do something similair to this, but the user would still need to select the single entry from a drop down the table. You would need to setup the second table's phone number field, in the table setup, to lookup from the orginal table. There should be a lookup tab on the bottom of the screen. Set this to listbox, or combobox, and set the datasource to the orginal table. Set the name criteria to current table name field. I would suggest that a form makes much more sense for something like this, although either way is going to be difficult.

HTH
Cal

 

[billythedj66]

So you are telling me that there isn't a way to make it so if the user selects "John Doe" from the list box, that the next field would not show "1-800-555-1212"?

 

[Cbrine]

Billy,

Yes, kinda. What I mean is that you can have them select Joe Doe in the first list, but you can't default the phone to the matching phone number from the first table. You can setup a SQL statement to limit the list, but I can't find a way to reference the current table entry as criteria. I tried Me!Name, and just Name without any heading, I tried linking the two tables in the SQL Statement, but still no luck.
If someone can figure out if it's possible to reference the current record entry in a table, we should be able to setup the phone field as a listbox that lists only the matching phone number.

Here's the SQL statement I last tried.

SELECT Table1.Phone, Table1.Name, Table2.Name FROM Table1 INNER JOIN Table2 ON Table1.Name = Table2.Name WHERE (((Table1.Name)=[Name]));

If we can get [Name] to reference the current table entries name, we might be able to limit the listbox in the phone field to the correct phone number only from Table1

Cal

 

[Andrew Fergus]

Hi Bill

So you are telling me that there isn't a way to make it so if the user selects "John Doe" from the list box, that the next field would not show "1-800-555-1212"?

From your posts to date I'm not sure if you are entering the data into the table or via a form. If you are trying to do this in the tables then I'm with Cal on this one and can't offer any suggestions.

However, if you are doing this through a form (it is highly recommended that data entry is done through a form) then it can definitely be done and there was a similar problem that was recently answered here.

If you have a look at the previous thread there are a couple of suggested solutions. If you can't adapt them to your database then please advise.

HTH, Andrew.