Combo Boxes

[Jack]

Another combo box question.
My data entry form has two combo boxes, one for employee numbers and one for corresponding employee names. I want my data entry people to enter the employee number and have the name automatically fill in when they "Tab" out of the number field. The numbers and names are in the same table.
I am using Access 97 P.E.
Thanks

Jack (a new guy)

 

[josephyeo]

Set your rowsource of the second combo to a the table that contains all their names. They can then select one.

 

[Jack]

Thanks,but this is what we do now. We have 140 names in the name field and I want to have the employee name fill in automatically when the corresponding employee number is entered.

Jack

 

[Neal]

You could use dlookup as the control source. Coding would be something like this:

=DLookup("[NameField]", "YourTable", "[EmployeeNo] = " & Me![YourTextBox])

 

[BarkerD]

You can do this a couple of ways.
You could use a DLookup, include the fields in a query, or use a recordset. I use the recordset.

Put this code in the OnLostFocus Event of the Employee # combobox. Make sure to limit to list.

'First check for a value in combobox.
If Isnull(me.cmbName) = True then
'Do nothing
Else

Dim rst as DAO.Recordset
Dim db as DAO.Database
Dim strSQL as String

strSQL="SELECT * FROM EmployeeTableName WHERE [Employee#FieldName] = " & me.cmbName & ";"
'Use " around me.cmbname if it is a numeric variable. If it is a string, use 3"

Set db = CurrentDB
Set rst=db.OpenRecordset(strSQL,dbOpenSnapShot)

'I won't ckeck for end of file, since limit to list is set to true.
'Now you can assign the controls on your from the values from the recordset.

me.txtEmployeeName=rst![EmployeeName]
me.txtEmployeeAddress=rst![EmployeeAddress]
etc.

'Always close open objects before ending code.
Set rst=Nothing
Set db=Nothing

End If

Hope this helps
Duane Barker



[This message has been edited by BarkerD (edited 01-10-2001).]