Find and display one record

[Brian]

I am trying to have a search page that will act as a
telephone book. That is, a simple page that states:

Enter Last Name of person to find: ____________

This should return only the one record of 50 fields which
contains the "Last Name" that was entered.

I've read through two books on Access 2000 and haven't
been able to find out a way to do this. I'm sure it's
there someplace.

Thanks for any help.

Brian

 

[Wayne Morgan]

"one records of 50 fields"? Just making sure this is a typo or I'll need a little more
explanation? Is it one of 50 records or are you wanting to find the result in any field of
any record?

Right off hand, have you tried setting a filter on the form? Have the whole telephone book
displayed on the form. Type in the name and click a button. Next, in the OnClick event of
the button try something like.

Me.Filter = "[Last Name] = """ & Me.txtSearch & """"
Me.FilterOn = True

The quotes count, from left to right, is 1, 3, 4. You will want to use double quotes and
not single in case the last name has an apostrophe, such as O'Rielly.

 

[John Nurick]

Hi Brian,

Display your form in Design view. Make sure the form is bound to the
table containing the list of names (or to a query on that table).

Display the Control Toolbox. Make sure that the magic wand button on the
toolbox is pressed.

Now click the Combobox tool and draw a combobox on your form. The
Combobox wizard will appear. Select "Find a record on my form based on
the value I select in my combo box" and follow the instructions.

 

[Roxie Aho]

The straightforward answer to your question is to base
your form on a parameter query. In the Criteria of the
query grid, type [Enter Last Name...]. When you try to
open the form you will get a dialog box asking for the
Last Name. When entered, it will bring up the one record.

However, I'd suggest a combo box on the form that gives
you an alphabetical list of last names. Use the same
query WITHOUT the parameter and with LastName sorted.

In the form Toolbox, with Control Wizards selected, choose
Combo Box. Ask the combo to look up values in a table or
query, select your Id and LastName fields, select Remember
the Value for Later Use.

In the AfterUpdate event of the combo box use the
following code, modified for your field names.
Private Sub cmbFind_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me!
[cmbFind]
Me.Bookmark = Me.RecordsetClone.Bookmark

Me.LastName.Visible = True

End Sub

For esthetics, if you wish, you can make all the fields
invisible in design view, then use the example of
Me.LastName above to make them visible.

The Combo box will also show the ID field. To hide it,
make the first column 0 in the Column Width property.

Roxie Aho

 

[Brian]

Roxie, Wayne and John,

I appreciate your help. I don't think I could have found a
solution on my own.

Brian

-----Original Message-----
Hi Brian,

Display your form in Design view. Make sure the form is bound to the
table containing the list of names (or to a query on that table).

Display the Control Toolbox. Make sure that the magic wand button on the
toolbox is pressed.

Now click the Combobox tool and draw a combobox on your form. The
Combobox wizard will appear. Select "Find a record on my form based on
the value I select in my combo box" and follow the instructions.

I am trying to have a search page that will act as a
telephone book. That is, a simple page that states:

Enter Last Name of person to find: ____________

This should return only the one record of 50 fields which
contains the "Last Name" that was entered.

I've read through two books on Access 2000 and haven't
been able to find out a way to do this. I'm sure it's
there someplace.

Thanks for any help.

Brian

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.