Dlookup Help (1 Viewer)

[tejasdave]

Hi Guys,

I am very new to the access scene and m still learning.

I have 2 tables : OJT Tracker & RefData.

OJT Tracker ( Fields):Name,SID,etc.

RefData ( Fields ): Name,SID,etc

I have made a form of OJT Tracker which is named "OJT Tracker".

in the form i have fields Name,SID etc.

Name is a combo box and the source is set to the table Name from RefData so in dropdown it shows all the names.

I want SID column to auto-populate the SID for the individual when a particular name is selected in the dropdown.

for example: if tejas is selected in the dropdown then the SID which is present next to "Tejas" in RefData column should be auto-populated in the SID column.

I am complete newbie so please try to be in detail.

Thnx

 

[jzwp22]

Welcome to the forum!

First, the word "name" is a reserved word in Access, so it should not be used as a field or table name. The words "Date" and "Time" are also reserved words.

It is also best not to have spaces or special characters in your table and field names as these will force you to enclose every table and field name in square brackets when you create queries. I go even further and do not use spaces or special characters in my query, form and report names.

Also, your two tables have similar structure, you should not have the same data in both tables, you would only need 1 field that points to the related data. This is typically done with key fields.

So for your RefData table assuming that is where you store the basic info about each person

RefData
-pkRefDataID primary key, autonumber
-txtName
-SID
other fields related to the person


Then OJTTracker you would just reference the primary key value of the person. When you do this the key is a foreign key

OJTTracker
-pkOJTID primary key, autonumber
-fkRefDataID foreign key relating back to tblRefData, this needs to be a long number datatype field
other fields related to the tracking


Assuming that you can have multiple tracking records for each person (one-to-many relationship), you would make a join between the two tables via the pkRefDataID-->fkRefDataID

In terms of your Tracker form, you would still use a combo box based on the RefData table, but you will use it to populate fkRefDataID. You can have the combo box display the SID one you make the selection. You would adjust the order of the fields and then the column widths of the combo box to display what you want. The first column with a non-zero width is what is displayed in the combo box once the selection is made.