lookup combo box

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

I have a data entry form where I enter customers. I enter
Last and First Names, Address, etc. I would like a
combofield at the top of the form that populates
with "last, first" for each person in my database. I want
to enter that field and start typing last name. The field
will jump down the list as I type to locate the correct
person. Once I find them, I want to exit the field and
have their record display.
I can do this with their ref# which is unique, or I can
make it work with a single field, but when I try using a
compbined last and first, I can't make it find the record.
I tried adding an unbound field in my form detail section
that displays "last, first" and then tried to match up the
record with what was in my combobox, but it does not work.
Can anyone point me to an example of this type of 'find'
command? Is there a form in Northwinds that you could use
as an example?

Obviously, I could use code to pull out the first name and
the last name and then do a complex search but this seems
like a simple task that should be easy to handle.

Thanks for your help.

Rick
 
Rick said:
I have a data entry form where I enter customers. I enter
Last and First Names, Address, etc. I would like a
combofield at the top of the form that populates
with "last, first" for each person in my database. I want
to enter that field and start typing last name. The field
will jump down the list as I type to locate the correct
person. Once I find them, I want to exit the field and
have their record display.
I can do this with their ref# which is unique, or I can
make it work with a single field, but when I try using a
compbined last and first, I can't make it find the record.
I tried adding an unbound field in my form detail section
that displays "last, first" and then tried to match up the
record with what was in my combobox, but it does not work.
Can anyone point me to an example of this type of 'find'
command? Is there a form in Northwinds that you could use
as an example?

Obviously, I could use code to pull out the first name and
the last name and then do a complex search but this seems
like a simple task that should be easy to handle.
Click to expand...

And it is, too. Give your combo box two columns, and base it on a query
that returns two fields: the ref# (or other unique key) and the
combined "last, first" name, a calculated field. For example, something
like this could be the combo box's RowSource:

SELECT RefNo, LastName & ", " & FirstName
FROM Customers
ORDER BY LastName, FirstName;

Set the combo box's properties as follows:

Column Count: 2
Bound Column: 1
Column Widths: 0"; 2"
Limit To List: Yes
After Update: [Event Procedure]

And build an event procedure for the AfterUpdate event that looks up by
RefNo, based on the combo box's value, like this:

Private Sub cboLookup_AfterUpdate()

With Me!cboLookup
If Not IsNull(.Value) Then
Me.Recordset.FindFirst "RefNo=" & .Value
End If
End With

End Sub

The above will work for Access 2000 or later, except in an ADP.
 
Dirk:

Thanks so much for this help. That is exactly waht I was looking for. This
works beautifully!!

Rick


Dirk Goldgar said:
Rick said:
I have a data entry form where I enter customers. I enter
Last and First Names, Address, etc. I would like a
combofield at the top of the form that populates
with "last, first" for each person in my database. I want
to enter that field and start typing last name. The field
will jump down the list as I type to locate the correct
person. Once I find them, I want to exit the field and
have their record display.
I can do this with their ref# which is unique, or I can
make it work with a single field, but when I try using a
compbined last and first, I can't make it find the record.
I tried adding an unbound field in my form detail section
that displays "last, first" and then tried to match up the
record with what was in my combobox, but it does not work.
Can anyone point me to an example of this type of 'find'
command? Is there a form in Northwinds that you could use
as an example?

Obviously, I could use code to pull out the first name and
the last name and then do a complex search but this seems
like a simple task that should be easy to handle.
Click to expand...

And it is, too. Give your combo box two columns, and base it on a query
that returns two fields: the ref# (or other unique key) and the
combined "last, first" name, a calculated field. For example, something
like this could be the combo box's RowSource:

SELECT RefNo, LastName & ", " & FirstName
FROM Customers
ORDER BY LastName, FirstName;

Set the combo box's properties as follows:

Column Count: 2
Bound Column: 1
Column Widths: 0"; 2"
Limit To List: Yes
After Update: [Event Procedure]

And build an event procedure for the AfterUpdate event that looks up by
RefNo, based on the combo box's value, like this:

Private Sub cboLookup_AfterUpdate()

With Me!cboLookup
If Not IsNull(.Value) Then
Me.Recordset.FindFirst "RefNo=" & .Value
End If
End With

End Sub

The above will work for Access 2000 or later, except in an ADP.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top