last name search field ; alphabet row?

[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,

 

[Dan Artuso]

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.

 

[Howard Brody]

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)

and a CommandButton (cmdFilter).

Use the wizard to have the combo box look up all the

distinct last names in the customer table for the ComboBox.

In the OnClick event for the CommandButton, put this

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

 

[jb]

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

(cboLastNames)
and a CommandButton (cmdFilter).

Use the wizard to have the combo box look up all the

distinct last names in the customer table for the ComboBox.

In the OnClick event for the CommandButton, put this

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,
.

.