AUTO POPULATING FIELDS FROM A SEPERATE TABLE

[stevembe]

I am an Access novice but have managed to build my first database with much appreciated assistance from this forum. However, one major improvement I would like to make is aut0 populating certain fields from another table. I currently have a form where employee details are manually input, such as unique employee number, age, branch etc but what I would like to do is add another table and when users go to the main form and type in the employee id, which is 8 figures then all the personal data fields are updated, e.g. first name, last name, age, branch etc. Can anbody give me a steer in the right direction or where I can look to get the best solution.

Thank you for YOUR time.

[stevembe]

Managed to do this using a combo box but can anyone advise if there is a way to replicate these fields in another form/table, a bit like using VLOOKUP in Excel?

[BJ_]

Hi Steve,

There is a DLookup built-in function that might do what you want. Where to use it will depend on what those fields on your form are doing - are they read-only (just to let the user confirm the employee ID they entered was correct) or read and write (populating values you want the user to be able to change)? The latter suggests to me a problem in table structure because you would probably want to change the values in the original table; nevertheless it could make perfect sense in some situations. If the former, I would suggest a little bit of VBA in the form called from the form's OnCurrent event and the Employee combobox's AfterUpdate event.

In the form's code module:



Option Compare Database
Option Explicit

'The following assumes
' - your EmployeeID control on this form is a combobox called "cbxEmployeeID"
' - you have another table called EmployeesTableName with a primary key "EmployeeID" and fields "Age" and "Branch"
' - the fields on this form you want to update are called "tbxAge" and "tbxBranch"

Private Sub UpdateLookupFields()
If IsNull(cbxEmployeeID.Value) Then
tbxAge.Value = ""
tbxBranch.Value = ""
Exit Sub
End If
Dim id as Long: id = cbxEmployeeID.Value
tbxAge.Value = DLookup("[Age]", "EmployeesTableName", "[EmployeeID] = " & id)
tbxBranch.Value = DLookup("[Branch]", "EmployeesTableName", "[EmployeeID] = " & id)
End Sub

Private Sub Form_Current()
UpdateLookupFields
End Sub

Private Sub cbxEmployeeID_AfterUpdate()
UpdateLookupFields
End Sub



Best wishes,
Brendan

[stevembe]

Thank you Brendan, after a lit bit of addition it worked! Very much appreciated!