Searchable Form

[Guest]

I have a database that i need to be able to search date of birth, social
security number, first name, or last name one or more at a time. Needs to
able to be sorted at any time.

 

[Rick Brandt]

I have a database that i need to be able to search date of birth,
social security number, first name, or last name one or more at a
time. Needs to able to be sorted at any time.

Have you looked at the built in Filter-By-Form feature? When your form is
opened look in the standard forms toolbar for it.

 

[Guest]

I need it so I don't have to click on Filter by form each time I would like
to search by a date of birth or something else.

 

[Rick Brandt]

I need it so I don't have to click on Filter by form each time I
would like to search by a date of birth or something else.

Well, your original requirements (searching multiple values on multiple fields)
can get fairly complex in a hurry if you want to build something customized.

If I were doing this I might place controls in the header of the form for search
criteria and then either construct a filter in code or a modified SQL statement
for the RecordSource using the entries in those controls.

How familiar are you with VBA code?

 

[Guest]

I don't know VBA code at all.

Well, your original requirements (searching multiple values on multiple fields)
can get fairly complex in a hurry if you want to build something customized.

If I were doing this I might place controls in the header of the form for search
criteria and then either construct a filter in code or a modified SQL statement
for the RecordSource using the entries in those controls.

How familiar are you with VBA code?

 

[Guest]

I know VBA somewhat well.

 

[Garret]

Well this same problem was my biggest dilemma when I first started
Access at the beginnning of the summer. A lot of research, trial &
error, and dedication to tens of hours in front of the computer finally
got my problem solved. (I wish I had discovered Google groups before
=(...).
I think what you want is to locate a specific record based on criteria
that you type in (or any criteria that you want). You can do this with
a list box (lstSearch). Use the listbox wizard to create a listbox "To
find records on the form based on the value I select in the list box".
Make a text box to type into (txtSearch) Then you can make a command
button that you can click when you want to find specific records
(cmdSearch). If you are going to be searching by multiple criteria
(which you are I assume), then create a frame with several option
buttons inside with the labels describing the category to search by
(fmeSearchby). Now under the command button code, enter something like
this:

Private Sub cmdSearch_Click()
'1 if Searching by Field1, 2 if searching by Field2
Select Case [fmeSearchby]
Case 1:
[lstSearch].RowSource = "SELECT [Table1].[Field1],
[Table1].[Field2], [Table1].[Field3] FROM [Table1] WHERE
((([Table1].[Field1]) Like " & "'" & [txtSearch] & "'" & ")) ORDER BY
[Table1].[Field1];"
Case 2:
[lstSearch].RowSource = "SELECT [Table1].[Field2],
[Table1].[Field1], [Table1].[Field3] FROM [Table1] WHERE
((([Table1].[Field2]) Like " & "'" & [txtSearch] & "'" & ")) ORDER BY
[Table1].[Field2];"
End Select
End Sub

Just substitute out the "table1", "field1", etc and insert your field
names.
Hope this helps.

 

[Guest]

I don't understand where you came up with "Field1", "Field2", etc.

Well this same problem was my biggest dilemma when I first started
Access at the beginnning of the summer. A lot of research, trial &
error, and dedication to tens of hours in front of the computer finally
got my problem solved. (I wish I had discovered Google groups before
=(...).
I think what you want is to locate a specific record based on criteria
that you type in (or any criteria that you want). You can do this with
a list box (lstSearch). Use the listbox wizard to create a listbox "To
find records on the form based on the value I select in the list box".
Make a text box to type into (txtSearch) Then you can make a command
button that you can click when you want to find specific records
(cmdSearch). If you are going to be searching by multiple criteria
(which you are I assume), then create a frame with several option
buttons inside with the labels describing the category to search by
(fmeSearchby). Now under the command button code, enter something like
this:

Private Sub cmdSearch_Click()
'1 if Searching by Field1, 2 if searching by Field2
Select Case [fmeSearchby]
Case 1:
[lstSearch].RowSource = "SELECT [Table1].[Field1],
[Table1].[Field2], [Table1].[Field3] FROM [Table1] WHERE
((([Table1].[Field1]) Like " & "'" & [txtSearch] & "'" & ")) ORDER BY
[Table1].[Field1];"
Case 2:
[lstSearch].RowSource = "SELECT [Table1].[Field2],
[Table1].[Field1], [Table1].[Field3] FROM [Table1] WHERE
((([Table1].[Field2]) Like " & "'" & [txtSearch] & "'" & ")) ORDER BY
[Table1].[Field2];"
End Select
End Sub

Just substitute out the "table1", "field1", etc and insert your field
names.
Hope this helps.

I know VBA somewhat well.

 

[Garret]

You said you wanted to search by Date of Birth, First name, Last name,
SSN, etc. So I assumed that these were all fields of the same table
used to describe a person (maybe a customer, employee, etc). So
"Field1" could be "LastName" or "Firstname", or whatever your field
names are that you could be searching by.

 

[Guest]

ok, I thought so but I was just going to check. Thank you.

 

[Garret]

No problem. Let me know if it works out for you, if thats what you
wanted or not.