Autofill a field (1 Viewer)

[Gorio]

It must be simple. I need to fill a field on a form based on the contents of the previous field. The table has a collection of records that include the leadEmployeeNum and leadEmployeeName. When entering the employee number I wish the employee name to be entered in the next field. If there is no match either leave the field blank or plave "unknown" in the field. Any help is appreciated.

 

[charityg]

field1 afterupdate
set rst=db.openrecordset("LookupTableName")
rst.findfirst "[field1]='" & me!field1
if rst.nomatch then
me!field2="unknown"
else
me!field2=rst!field2
endif
rst.close

 

[Gorio]

So far I'm getting close but haven't been able to make it work yet.

Here is what I have:

Private Sub txtSecndEmpNum_AfterUpdate()


Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblSimpleCheck")

rst.FindFirst "[SecndEmpNum] = " & "#" & Me.txtSecndEmpNum & "#"
If rst.NoMatch Then
Me.txtSecndEmpName = "UNKNOWN"
Else
Me.txtSecndEmpName = rst!SecndEmpName
End If
rst.Close


End Sub

SecndEmpNum is the name of the field in the table tblSimpleCheck. SecndEmpName is in the smae table. They are fields number 8 and 9 respectively. The "txt" prefix denotes the corresponding field on the form that I am using.

When run I get error # 3251 stating "Operation is not supported for this type of object."

Any more help available out there?

 

[R. Hicks]

If you would include the table with the employee information in the Query you are using for the record source for the form, and you have the relational joins correct, you can enter the employeenumber and the employeename will automatically fill through the relational join. You only need to set the Control Source for the employeename to the correct field in the recordset from the query.

HTH
RDH

 

[Gorio]

I am only using one table, their is no relation set up with another table. I tried to use a combo box to fill it automatically, but it always returned a blank field above the correct answer, resulting in the user having to stop and choose the name. I couldn't find a way to eliminate the blank field.

So instead of using a query I planned on using a code to provide the proper result. So far I have not managed to succeed. BUt with the help of those here, I hope to figure this out.