Populating a form field based on another field value

[cousinconley]

I have a Microsoft Access 2000 database and form. The form writes to a Transaction table. The form has a drop down list that pulls an employee number from an Employee table. I have a field for employee name I would like to populate based on the employee number.

Here is what I am trying to do:

SELECT EMPLOYEE.NAME FROM EMPLOYEE WHERE EMPLOYEE.EMPLID = FORM.EMPLOYEE_NUMBER

I have never done anything this advanced in Access and wondered if someone could point me the right way. I have tried dozens of VBA examples with no luck.

Thanks, Richard

 

[Pampers]

Hi richardconley,
It is not that hard.

STEP1; COMBOX DESIGN
In design mode, dubbelclick your (unbound) combobox (dropdown). The property-tabel opens; put your cursor in the row source property of the combox. Press the three dots ... at the right hand side. The query builder now opens. Drag EmployeeName to the first position, EmployeeNumber on the second. Close the grid and save the changes. In the column widths property of the combo put the first measurement to 0cm (or inch) and the second to 3cm or so. The first column will now be hidden. Set Bound column-propery to 1 (EmployeeName).

STEP2: VB-CODE IN AFTER UPDATE EVENT
Go to the event-tab of the property-tabel of your combox. Put your cursor in the after-update event. Click on the three dots.... Choose Code Builder (OK). Create the following:

Private Sub Combo3_AfterUpdate()
    EmployeeName = Combo3 'name of your combobox
End Sub

Close the VB-screen, and test your code!!





Pampers
Just became father!

 

[cousinconley]

Thanks Pampers for the response. In your instructions are you refering to the Employee ID or the Employee Name at the beginning of your first paragraph or a completely new box?

Thanks,

Richard

 

[a23freak]

I have a similar problem in that I have numerous text boxes that I need to populate based on the initial drop down box selection. The drop down is the Name. I need the other text boxes: Address, City, State, Country, Zip Code populated based on the drop down selection. I have tried adding additional items based on your info in Step 1 and I then modified Step 2 to include the other fields (i.e. Address = Combo3, City = Combo3,etc. But this does not work. Thanks, Dave

 

[PHV]

Have a look at the Column property of the CombobBox object, e.g.:
Address = Combo3.Column(2)
City = Combo3.Column(3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

Hi richardconley and a23freak,
Sorry for the late response. Forgot your posts.

As PHV says, have a look at Columnproperty. You can refer to different columns in your combox (dropdown) by using the column-property.

Say your combox (cboEmployee) has the following columns (fields). EmployeeID, EmployeeName, EmployeeAddress.

In the afterupdate event of the combox, you can set the values of your unbound textcontrols on your form by refering to the different columns of the combox. The first column in the combox is refered to as Column(0), the second as Column(1) etc.. Here is what you can use in the after_update_event:

Private Sub cboEmployee_AfterUpdate()
'populate 3 unbound fields on my form with data from combox
Me.EmployeeID = Me.cboEmployee.Column(0)
Me.EmployeeName = Me.cboEmployee.Column(1)
Me.EmployeeAddress = Me.cbEmployee.Column(2)
End Sub

Hope this helps...

Pampers
Just let it go...