last name search field ; alphabet row?

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

I'd like to create a last-name search field for a big
database of customers, where the user could put in the
desired last name and immediately go to the correct
record.
For ease of use, I'd like to do this with a control
right on the same form that allows data entry to the
record, Maybe in the header. Is that possible, or do I
have to make a separate form? Please point me to examples
and step-by-step how-to's for the best way to do this,
including any indexing steps.
Also, you know those alphabet rows where you select an
initial letter? I'm thinking those are cute, but usually
just add an extra step. Any design opinions on that?
Thanks. The newsgroups are a great service,
 
Hi,
The easiest way is use the wizard to create a combo box on your form.
Choose the 'find a record based on the value in the combo' option.
The user can then type a name into the combo.
 
The way I do this is by building a SQL string (based on
the lookup criteria entered) and then using it to filter
the form.

Try this:
In the header of your form, add a ComboBox(cboLastNames)
Click to expand...
and a CommandButton (cmdFilter).
Use the wizard to have the combo box look up all the
Click to expand...
distinct last names in the customer table for the ComboBox.
In the OnClick event for the CommandButton, put this
Click to expand...
code:

Private Sub cmdFilter_Click()

' if a last name has not been entered
' display an error message and end sub
If IsNull([cboLastNames]) Or [cboLastNames] = "" Then
MsgBox "You forgot to select a last name. " _
& "Please try again."
cboLastNames.SetFocus
Exit Sub
End If

' otherwise ...

' build filter string
Dim strFilter as String
strFilter = "[LastName]='" & [cboLastNames] & "'"

' filter form
DoCmd.ApplyFilter , strFilter

End Sub

Hope this helps!

Howard Brody
 
Thanks. This looks great for a form. But I can't seem to
get this done in a Data Access Page. Can you tell me how
to do it there?
-----Original Message-----
The way I do this is by building a SQL string (based on
the lookup criteria entered) and then using it to filter
the form.

Try this:
In the header of your form, add a ComboBox
Click to expand...
(cboLastNames)
and a CommandButton (cmdFilter).
Use the wizard to have the combo box look up all the
Click to expand...
distinct last names in the customer table for the ComboBox.
In the OnClick event for the CommandButton, put this
Click to expand...
code:

Private Sub cmdFilter_Click()

' if a last name has not been entered
' display an error message and end sub
If IsNull([cboLastNames]) Or [cboLastNames] = "" Then
MsgBox "You forgot to select a last name. " _
& "Please try again."
cboLastNames.SetFocus
Exit Sub
End If

' otherwise ...

' build filter string
Dim strFilter as String
strFilter = "[LastName]='" & [cboLastNames] & "'"

' filter form
DoCmd.ApplyFilter , strFilter

End Sub

Hope this helps!

Howard Brody

-----Original Message-----
I'd like to create a last-name search field for a big
database of customers, where the user could put in the
desired last name and immediately go to the correct
record.
For ease of use, I'd like to do this with a control
right on the same form that allows data entry to the
record, Maybe in the header. Is that possible, or do I
have to make a separate form? Please point me to examples
and step-by-step how-to's for the best way to do this,
including any indexing steps.
Also, you know those alphabet rows where you select an
initial letter? I'm thinking those are cute, but usually
just add an extra step. Any design opinions on that?
Thanks. The newsgroups are a great service,
.
Click to expand...
.
Click to expand...
 
Back
Top