Automatic form field filling from a table data with primary key?

[Cero21]

I don't know if I can explain this properly but here goes.

I have three tables.

Personnel(ServiceNumber[primary key], Name, Initials, etc...)
Operations(OperationName)
DeployedPersonnel(ServiceNumber[foreign key], OperationName[foreign key], start date, end date)

DeployedPersonnel as you can see is the relationship between Personnel and Operations.

What I would like is a form based off of DeployedPersonnel where the user selects a service number and that persons associated data from Personnel is displayed alongside. This is so the user can verify that the employee they selected is the one they want before they enter in the other fields.

Is there a way I could do this?

 

[Cero21]

Sorry about the question title I was thinking about filling the deployedpersonnel table with the Name and Initial fields from the Personnel table and have those field automatically fill when selecting the corresponding service number.

I more appropriate title would be:
Showing related table fields when selecting the primary key

 

[psemianonymous]

Use the DLookup() function to grab the values. Use the DataSource property of a textbox to use DLookup to query the table and pull your ... let's say name field ... for the primary key you just entered. It will automatically update whenever you change the primary key field in the form.

If I could provide sample code I would; unfortunately I don't have any samples right now. It would look something like:

[in form design view, txtName properties:]
DataSource: =DLookup("namefieldname","personnel","ID = " & txtPrimaryKeyTextboxname.Value )

Something like that, good luck.