Obtain Data from 1 Table based on value selected in another Table

[Kijori]

i've got 2 tables;

Table1 contains these fields:
Code
Name
Division
Sector

Table2 contains these fields:
Company
Person
Code (this is a Lookup)
Name
Division
Sector

what i want to do is when the user select a value from the Code field in Table2, i want the Name, Division and Sector fields in Table2 to be populated (auto) by the values set in Table1 based on whatever Code was selected.

can this be done? thanks.

 

[Isadore]

Kijori: Here's one scenario. On your form say user enters (selects) code, say ID0304.

Now, you want to populate the following three textboxes with the respective values:

Me![Name]=DLookUp("[Name]","Table1","

=Forms![frmMyForm]![Code]")

Me![Name]=DLookUp("[Division]","Table1","[Code]=Forms![frmMyForm]![Code]")

Me![Name]=DLookUp("[Sector]","Table1","[Code]=Forms![frmMyForm]![Code]")

...the DLookUp function, look closely, is nothing more than an SQL query statement; e.g., 

mySQL = "SELECT Table1.Name FROM Table1 WHERE Code = Froms![frmMyForm]![Code]"

 

[Isadore]

Kijori: Lost part of the syntax, must be related to the Tek-Tips syntax parameters.

The lines above should have read:

Me![Name]=DLookUp("[Name]","Table1","[ Code ]=Forms![frmMyForm]![ Code ]&quot

Me![Division]=DLookUp("[Division]","Table1","[ Code ]=Forms![frmMyForm]![ Code ]&quot

Me![Sector]=DLookUp("[Sector]","Table1","[ Code ]=Forms![frmMyForm]![ Code ]&quot

I put spaces around code in case that was the problem.

 

[Darrylles]

Hi Isadore,

Have you looked at the 'Process TGML' link (below the 'Your Reply' box)?

It shows how to format your reply.

tt in square brackets before your code and /tt in square brackets after your code - displays the code exactly as you type it.

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Isadore]

Darr: Thanks for the info!