Need help with combo box lookup in a form

  • Thread starter Thread starter Amit
  • Start date Start date
A

Amit

Hi,

I'm having a tough time with a combo box lookup in a form.

I have a form to enter new employee information, which is
based on a query that gets all the fields from the
employee table. I would like to add a lookup combo box in
the footer of the form to look up specific employee. I
used the combo box wizard to set it up, and have done it
successfully when I have separate combo boxes for 'first
name' and 'last name'. My boss wants me to add a single
combo box combining the first and last names. I tried
doing that using the combo box wizard, but got a run-time
error. I'll appreciate any help with this.

Thanks!

-Amit
 
Use an expression in the query (alternatively you *CAN*
use multiple columns in the same combo box).

I have a case here where I do this, and my primary key is
the [Employee ID] - by setting the first column width to 0
this is NOT VISIBLE to the user, but my code uses it.
Here's the query:

SELECT DISTINCTROW [ID Master].[Employee ID], [ID Master].
[Last Name] & ", " & [ID Master].[First Name] & " " & [ID
Master].[Middle Name] AS [Full Name]
FROM [ID Master]
ORDER BY [ID Master].[Last Name], [ID Master].[First Name];

This is used as the Row Source

The Column Widths are set to 0";1" ... result is the user
sees a list

Abraham, Aaron
Ackerman, Regina
etc.

Meanwhile I still have the Employee ID so I can still tell
the difference between Scott Smith ID 123456 and say,
Scott Smith ID 234567.

Now on the Click event I call code to populate a subform
and only need to pass it the Employee ID, which is in the
hidden column 0:

Private Sub cbTM_Click()
PopulatePositions Me.cbTM.Column(0)
End Sub
 
Do the employee records have a Primary Key? If so, you can use that to do
the lookup (it will be more efficient anyway). Your combo box row source
should look something like:

SELECT EmployeeID, LastName & ", "& FirstName As FullName
FROM Employees
ORDER BY LastName, Firstname

Set the Bound Column of the combo box to 1, Column Count to 2, and Column
Widths to 0";1.5"

Now, change the code in the AfterUpdate event to do the search on
EmployeeID, not name.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
I tried that, and I'm getting a "Data Type mismatch" error
when I select an employee name. Here's the code in
the "After Update" for the combo box:


Set rs = Me.Recordset.Clone
rs.FindFirst "[StaffID] = '" & Me!
[cboStaffFullName_frmDPHStaff] & "'"
Me.Bookmark = rs.Bookmark

I designed the combo box exactly as you mentioned in your
email. Do I need to specify the column of the combo box in
the code above?

Thanks!

-Amit
 
Hi,
Since you are now using the Id, you don't need the single quotes:
rs.FindFirst "[StaffID] = " & Me! [cboStaffFullName_frmDPHStaff]

--
HTH
Dan Artuso, Access MVP


Amit said:
I tried that, and I'm getting a "Data Type mismatch" error
when I select an employee name. Here's the code in
the "After Update" for the combo box:


Set rs = Me.Recordset.Clone
rs.FindFirst "[StaffID] = '" & Me!
[cboStaffFullName_frmDPHStaff] & "'"
Me.Bookmark = rs.Bookmark

I designed the combo box exactly as you mentioned in your
email. Do I need to specify the column of the combo box in
the code above?

Thanks!

-Amit
-----Original Message-----
Do the employee records have a Primary Key? If so, you can use that to do
the lookup (it will be more efficient anyway). Your combo box row source
should look something like:

SELECT EmployeeID, LastName & ", "& FirstName As FullName
FROM Employees
ORDER BY LastName, Firstname

Set the Bound Column of the combo box to 1, Column Count to 2, and Column
Widths to 0";1.5"

Now, change the code in the AfterUpdate event to do the search on
EmployeeID, not name.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411



.
Click to expand...
Click to expand...
 
-----Original Message-----
Hi,
Since you are now using the Id, you don't need the single quotes:
rs.FindFirst "[StaffID] = " & Me!
Click to expand...
[cboStaffFullName_frmDPHStaff]

Thanks Dan! That worked like a charm!! :)

-Amit

--
HTH
Dan Artuso, Access MVP


"Amit" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
I tried that, and I'm getting a "Data Type mismatch" error
when I select an employee name. Here's the code in
the "After Update" for the combo box:


Set rs = Me.Recordset.Clone
rs.FindFirst "[StaffID] = '" & Me!
[cboStaffFullName_frmDPHStaff] & "'"
Me.Bookmark = rs.Bookmark

I designed the combo box exactly as you mentioned in your
email. Do I need to specify the column of the combo box in
the code above?

Thanks!

-Amit
-----Original Message-----
Do the employee records have a Primary Key? If so, you can use that to do
the lookup (it will be more efficient anyway). Your combo box row source
should look something like:

SELECT EmployeeID, LastName & ", "& FirstName As FullName
FROM Employees
ORDER BY LastName, Firstname

Set the Bound Column of the combo box to 1, Column
Click to expand...
Count
to 2, and Column
Widths to 0";1.5"

Now, change the code in the AfterUpdate event to do the search on
EmployeeID, not name.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hi,

I'm having a tough time with a combo box lookup in a form.

I have a form to enter new employee information,
Click to expand...
which
is
based on a query that gets all the fields from the
employee table. I would like to add a lookup combo
Click to expand...
box
in
the footer of the form to look up specific employee. I
used the combo box wizard to set it up, and have done it
successfully when I have separate combo boxes for 'first
name' and 'last name'. My boss wants me to add a single
combo box combining the first and last names. I tried
doing that using the combo box wizard, but got a run- time
error. I'll appreciate any help with this.

Thanks!

-Amit


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top