Prefill after info entered

[CMcGrann]

I am working on a form that after I key in info, I want the other fields to
automatically prefill.

I have a table that contains Employee Name, Department & Manager.

In the form, I have the row sourse set to table/query. After I enter the
Employee name into the form, is it possible for the Deparment and Manager to
automatically prefill since it exists in the table I'm referencing?

Thanks!

 

[Damon Heron]

This is an unbound form? The rowsource you are referring to is for a
combobox? If so, then create a query with EmpName, Dept, and Mgr and set
the # of columns to 3. Column widths 1";0";0" - This will show just the
employee name. Then in the afterupdate event of the combo, put :
me.[yourtextboxforDept] = me.combobox.column(1)
me.[yourtextboxforMgr] = me.combobox.column(2)

Damon

 

[Damon Heron]

If it is a bound form (to the table) then in the combobox afterupdate event:

Private Sub cboxName_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[yourTableID] = " & str(Nz(Me![cboxName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Damon

 

[Jeff Boyce]

A bit of clarification ...

Are you asking for a way to have the form (simply) display the related
information, or are you asking for a way to get the related information
STORED in the underlying table?

If the latter, stop now! If the information is already available in one
table, storing it a second (and a third and a fourth and ...) time in
another table puts the integrity of your data at risk.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[CMcGrann]

No, the information already exists in a table. I am simply trying to get the
info to auto fill after the emplyee name is chosen.

For example. I have Tom Smith with a Manager of Bob Hoffman from IT.

I want to be able to choose Tom Smith from a combo box than I want Bob
Hoffman (Manager) and IT (Dept) to fill in.

Thanks!

 

[Jeff Boyce]

If you add a couple unbound textboxes to your form, you can "fill" them via
the AfterUpdate event for that combobox you use to pick the employee.

You would need to make sure the added information is part of the query
underlying your combobox.

Your AfterUpdate event would include something like:

Me!txtManager = Me!cboSelectEmployee.Column(1)
Me!txtDepartment = Me!cboSelectEmployee.Column(2)

NOTE: the .Column() is zero-based. If your query holds the ManagerName in
the second column, you'd use .Column(1) - start counting 0, 1, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP