Populate Fields on Form

[muirchez]

My problem is fairly straight forward (at least it should be!)

I have an unbound form used for entering employee's personal details. (Into a table called "tblPersonalDetails"

On this form there are unbound fields "Job Title, Division, Department etc" giving a summary of the job the employee is starting.

The first field entered on the form is "Position ID" which refers to the Job the employee is starting in. What I need is this....

When someone enters the "Position ID" I need the "Job Title", "Division" & "Department" to auto populate. These fields are in a table called "tblOrganisation" which holds "Position ID" field as well.

I have tried to do this using a select query but when I enter the "Position ID" the "Job Title" field populates with the actual SELECT query, not its value.

Any help greatly appreciated.

 

[Kiwiman]

Howzit

Firstly, is there any reason why you can't have the form bound to the table "tblPersonalDetails"?

Secondly I would only have the PositionID stored in the tblPersonalDetails table, and use a query (joining the two tables) returning the details required?

 

[micks55]

Hi steve,
First I would get rid of those spaces in the names - they will get you in the end.
Have you thought about DLookUp()? Without spaces... in the AfterUpdate Event of the PositionID control,

Me!JobTitle = DLookUp("JobTitle", "tblOrganisation", "PositionID = " & Me!PositionID)

Or

Dim myPosID
myPosID = Me!PositionID
Me!JobTitle = DLookUp("JobTitle", "tblOrganisation", "PositionID = " & myPosID)

How about a simple test to make sure that the data will be found.
If myPodID > 1 And myPodID < 5 Then
Me!JobTitle = DLookUp(...etc.)
End If

While your in the code screen, highlight DLookUp and hit the F1 key for VBA help.

Hope it's helpful
Mick

 

[boblarson]

If the data exists in another table, you shouldn't be storing the same information again in another table. You should only be storing the Primary Key of that other record from the other table so you aren't storing redundant data.