Button to FIND and Move to a Specific Record

[Robert T]

Hi:

On a typical form where there are hundreds of customers stored in
tblCustomers, I would like to know how to find a specific customer and go to
his or her record.

I use another database program called Alpha Five where you click a button on
the form and an alphabetical drop down list of customers pops up in
alphabetical order. You select a customer and it uses the value in CustID to
find and move to that customer’s record. Can we do the same thing in Access
2003?

If we can’t use a drop down list, how can I get a box to pop up so the user
can enter a customer’s last name or CustID number and then find and move to
that customer’s record?

Thanks,
Robert

 

[Rick Brandt]

Hi:

On a typical form where there are hundreds of customers stored in
tblCustomers, I would like to know how to find a specific customer
and go to his or her record.

I use another database program called Alpha Five where you click a
button on the form and an alphabetical drop down list of customers
pops up in alphabetical order. You select a customer and it uses the
value in CustID to find and move to that customer's record. Can we do
the same thing in Access 2003?

If we can't use a drop down list, how can I get a box to pop up so
the user can enter a customer's last name or CustID number and then
find and move to that customer's record?

Thanks,
Robert

Open your form in design view and make sure that the toolbox wizards are
enabled. Then drop a new ComboBox control onto your form. One of the
choices within the ComboBox wizard does exactly what you are looking for.

It will the the third choice on the first page of the wizard entitled "Find
a record on my form based on the value I selected in my ComboBox".

 

[Robert T]

Hi Rick:

Thanks so much for the prompt response. It was very easy, however, the code
didn't work when I changed the name from combobox25 to cboFindStudent. I had
to go into the code and change the references to the new name and now it
works perfectly. However, the button doesn't say anything that describes the
function of the combo box, so I guess I'll put a label text box above it to
instruct the user to click on the down arrow.

Thanks so much Rick, now if you can help answer my other question on sending
an email confirmation, we'll be in great shape.

Have a terrific day and thanks again,
Robert

 

[Klatuu]

A combo box control is what is usually used for searching for a specific
record. Typically, the combo is an unbound control. It is left unbound
because changes in it will cause changes in the current record and may create
errors due to duplicate keys.

If you set the Auto Expand property of the combo to Yes, it will provide the
"type ahead" feature. You will usually use the After Update and Not In List
events of the combo. The After Update event you will use to locate a record
and make it the current record. The Limit To List event is used when the
value entered is not in the table. To use this event, set the Limit To List
property to Yes.

The combo will need a row source. It is usually a query based on the
primary key or other field you want to search by. If you are using a field
other than the primary key, it is a good idea to create an index for the
field. If you are wanting to look up customers, you probably have a customer
ID and a customer name. You will probably want to show the user the customer
name but do the search with the customer ID. This is accomplished by making
the combo a multi column combo. Here is an example of a row source for the
combo:
SELECT CustID, CustName FROM tblCustomer;

To show only the customer name, you set the column widths property so the ID
column has a width of 0. As to columns, you need to be aware that when
counting columns in a combo, identifying the bound column is 1 based, but
addressing column values is 0 based. That is, if you want the first column
to be the bound column, you set it to 1, but if you want to return the value
of the first column, it is
Me.cboCustomer.Column(0).

Now to the events. Here is the code for the After Update event you will use
to locate a customer and make it the current record:

Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = " & Me.cboCustomer
Me.Bookmark = rst.Bookmark
set rst = Nothing

Now we need to address the Not In List event so that a user can add a new
record or cancel if they entered an incorrect value:

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblCustomer (CustID) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboCustomer.Undo
Response = acDataErrContinue
End If

 

[Robert T]

Hi Klatuu":

Wow! That's great stuff, as soon as I have time later today, I'm going to
try out what you suggested. We are not dealing with customers, we're actually
using tblStudents to register them for our small training courses here at
work and every student of course has a unique StudentID number. So I can
substitute Student_LastName and StudentID for the customer examples in your
code.

Thanks,
Robert

A combo box control is what is usually used for searching for a specific
record. Typically, the combo is an unbound control. It is left unbound
because changes in it will cause changes in the current record and may create
errors due to duplicate keys.

If you set the Auto Expand property of the combo to Yes, it will provide the
"type ahead" feature. You will usually use the After Update and Not In List
events of the combo. The After Update event you will use to locate a record
and make it the current record. The Limit To List event is used when the
value entered is not in the table. To use this event, set the Limit To List
property to Yes.

The combo will need a row source. It is usually a query based on the
primary key or other field you want to search by. If you are using a field
other than the primary key, it is a good idea to create an index for the
field. If you are wanting to look up customers, you probably have a customer
ID and a customer name. You will probably want to show the user the customer
name but do the search with the customer ID. This is accomplished by making
the combo a multi column combo. Here is an example of a row source for the
combo:
SELECT CustID, CustName FROM tblCustomer;

To show only the customer name, you set the column widths property so the ID
column has a width of 0. As to columns, you need to be aware that when
counting columns in a combo, identifying the bound column is 1 based, but
addressing column values is 0 based. That is, if you want the first column
to be the bound column, you set it to 1, but if you want to return the value
of the first column, it is
Me.cboCustomer.Column(0).

Now to the events. Here is the code for the After Update event you will use
to locate a customer and make it the current record:

Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = " & Me.cboCustomer
Me.Bookmark = rst.Bookmark
set rst = Nothing

Now we need to address the Not In List event so that a user can add a new
record or cancel if they entered an incorrect value:

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblCustomer (CustID) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboCustomer.Undo
Response = acDataErrContinue
End If

Hi:

On a typical form where there are hundreds of customers stored in
tblCustomers, I would like to know how to find a specific customer and go to
his or her record.

I use another database program called Alpha Five where you click a button on
the form and an alphabetical drop down list of customers pops up in
alphabetical order. You select a customer and it uses the value in CustID to
find and move to that customer’s record. Can we do the same thing in Access
2003?

If we can’t use a drop down list, how can I get a box to pop up so the user
can enter a customer’s last name or CustID number and then find and move to
that customer’s record?

Thanks,
Robert

 

[Klatuu]

Sorry, won't work for students, only customers

Good luck with it.

Hi Klatuu":

Wow! That's great stuff, as soon as I have time later today, I'm going to
try out what you suggested. We are not dealing with customers, we're actually
using tblStudents to register them for our small training courses here at
work and every student of course has a unique StudentID number. So I can
substitute Student_LastName and StudentID for the customer examples in your
code.

Thanks,
Robert

A combo box control is what is usually used for searching for a specific
record. Typically, the combo is an unbound control. It is left unbound
because changes in it will cause changes in the current record and may create
errors due to duplicate keys.

If you set the Auto Expand property of the combo to Yes, it will provide the
"type ahead" feature. You will usually use the After Update and Not In List
events of the combo. The After Update event you will use to locate a record
and make it the current record. The Limit To List event is used when the
value entered is not in the table. To use this event, set the Limit To List
property to Yes.

The combo will need a row source. It is usually a query based on the
primary key or other field you want to search by. If you are using a field
other than the primary key, it is a good idea to create an index for the
field. If you are wanting to look up customers, you probably have a customer
ID and a customer name. You will probably want to show the user the customer
name but do the search with the customer ID. This is accomplished by making
the combo a multi column combo. Here is an example of a row source for the
combo:
SELECT CustID, CustName FROM tblCustomer;

To show only the customer name, you set the column widths property so the ID
column has a width of 0. As to columns, you need to be aware that when
counting columns in a combo, identifying the bound column is 1 based, but
addressing column values is 0 based. That is, if you want the first column
to be the bound column, you set it to 1, but if you want to return the value
of the first column, it is
Me.cboCustomer.Column(0).

Now to the events. Here is the code for the After Update event you will use
to locate a customer and make it the current record:

Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = " & Me.cboCustomer
Me.Bookmark = rst.Bookmark
set rst = Nothing

Now we need to address the Not In List event so that a user can add a new
record or cancel if they entered an incorrect value:

If MsgBox(NewData & " Is Not In The Customer Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblCustomer (CustID) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[CustID] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboCustomer.Undo
Response = acDataErrContinue
End If

Hi:

On a typical form where there are hundreds of customers stored in
tblCustomers, I would like to know how to find a specific customer and go to
his or her record.

I use another database program called Alpha Five where you click a button on
the form and an alphabetical drop down list of customers pops up in
alphabetical order. You select a customer and it uses the value in CustID to
find and move to that customer’s record. Can we do the same thing in Access
2003?

If we can’t use a drop down list, how can I get a box to pop up so the user
can enter a customer’s last name or CustID number and then find and move to
that customer’s record?

Thanks,
Robert

 

[Robert T]

Hi Klatuu:

Maybe I'm missing something obvious but I don't know how the "Not In List"
event kicks in. Let's assume I'm looking for a student who isn't in
tblStudents. I click on the drop down list, search the names, and the student
isn't there. But then what?

I temporarily solved the problem by adding a new command button saying,
"Student Not Found". I then added the following code you gave me, modified
ever so slightly, to the Click event.

If MsgBox(NewData & " Is Not In The Student Table " & vbNewLine _
& "Do you want to add it", _
vbInformation + vbYesNo, "Not Found") = vbYes Then
CurrentDb.Execute ("INSERT INTO tblStudents (StudentID) " _
& "VALUES ('" & NewData & "');"), dbFailOnError
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "[StudentID] = '" & NewData & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing
Response = acDataErrAdded
Else
Me.cboFindStudent.Undo
Response = acDataErrContinue
End If

 

[Klatuu]

You have to have the Limit To List property set to Yes for the event to fire.
If you enter a value that is not found in the combo's row source, the Limit
to List event fires, but the After Update does not.

 

[Robert T]

Hi Klatuu:

[You have to have the Limit To List property set to Yes for the event to
fire. If you enter a value that is not found in the combo's row source, the
Limit to List event fires, but the After Update does not.]

That's my question, how does Access know when a student is NOT in the list.
You click the down arrow to search for a student. If you can't find them, how
does Access know you couldn't find him/her because they're not on the list
and therefore, the event fires.

Where does the opportunity arise to enter a new student? Would it be just as
easy to have a separate button wherein the user enters a new student if they
can't find him/her on the list?

And I did check "YES" for "Limit to List".

 

[Klatuu]

If you type in a student's name that is not in the list and press Enter, the
Not In List event fires. You could have a separate button, but why? This
will do it for you.