Using text box to filter

[Senna_Rettop]

Hello,
I know exactly what I want but don't know the process or codes to do
it. Please help. I want a form that has two text boxes, one for names
and one for SSN. When I type in either of the two, I want to be able
to hit a filter button that shows me all the records that match. I
relatively know how to do this with a combo/drop down box, but I can't
use those because I can't have the names or SSN availible for everyone
to see. That's why I want to use a text box. If anyone knows how to
do this, please help me. Thank you.
-Senna

[DFS]

Re: Using text box to filter

Senna_Rettop wrote:

Hello,
I know exactly what I want but don't know the process or codes to do
it. Please help. I want a form that has two text boxes, one for
names and one for SSN. When I type in either of the two, I want to
be able to hit a filter button that shows me all the records that
match. I relatively know how to do this with a combo/drop down box,
but I can't use those because I can't have the names or SSN availible
for everyone to see. That's why I want to use a text box. If anyone
knows how to do this, please help me. Thank you.
-Senna

Senna,


In the AfterUpdate event of the Name text box, add:

call searchDB("Name" )


In the AfterUpdate event of the SSN text box, add:

call searchDB("SSN")


Then add a sub proc to the Form:

Public Sub searchDB(search Type as String)

me.listBox.RowS ource = ""
if searchType = "Name" then
me.txtBoxSSN = ""
me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE NAME = '" &
ME.txtBoxName & "' ORDER BY..."
ElseIf searchType = "SSN" then
me.txtBoxName = ""
me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE SSN = '" &
ME.txtBoxSSN & "' ORDER BY..."
endif


End Sub

[Senna_Rettop]

Re: Using text box to filter

I tried implimenting the code. I think everything worked until i tried
to make the sub proc.
The two parts of the code listed below turned red when I pasted them
into the form(code). I''m not very familiar with code. Did I insert
the code into the wrong place?
Sorry for the confusion.

me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE NAME = '" &
ME.txtBoxName & "' ORDER BY..."
me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE SSN = '" &

ME.txtBoxSSN & "' ORDER BY..."

[pietlinden@hotmail.com]

Re: Using text box to filter

you need to complete the SQL statement. I would create a query in the
query builder that returned one of the two results I wanted and then
modify from there. Open up a query in NWind.mdb and look at it in SQL
view. Then you should understand what's going on.

the "SELECT FIELDS FROM TABLE WHERE ..."

Might look like
"SELECT FirstName, LastName FROM Employees WHERE SSN=..."

What you're doing is building the SQL statement on the fly, and then
assigning that result to the controlsource of your listbox.

[DFS]

Re: Using text box to filter

Senna_Rettop wrote:

I tried implimenting the code. I think everything worked until i
tried to make the sub proc.
The two parts of the code listed below turned red when I pasted them
into the form(code). I''m not very familiar with code. Did I insert
the code into the wrong place?
Sorry for the confusion.
>
me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE NAME = '" &
ME.txtBoxName & "' ORDER BY..."
me.listBox.RowS ource = "SELECT FIELDS FROM TABLE WHERE SSN = '" &

>ME.txtBoxSSN & "' ORDER BY..."

You probably put it in the right place, but I assumed more familiarity with
SQL.

Where it says 'FIELDS' substitute the names of the fields you want to show,
separating them each with a comma.

Where it says 'TABLE' substitute the name of the database table you're
querying.

Where it says ORDER BY... put ORDER BY Field1, Field2, ... whatever fields
you want to sort on.

If any of the fields or tables has a space in the name, surround it with
brackets []. I noticed I put NAME as one of the fields - bad advice. I
recommend you don't use NAME as a field. Use LAST_NAME, FIRST_NAME,
FULL_NAME, etc. If you use 'Name' in code or in queries, surround it with
brackets [NAME] just in case; name is used by various Access objects and
properties.



Examples:

SELECT PatientID, [Name], SSN, Birthday, [First Visit] FROM Patients WHERE
[Name] = '" & me.txtBoxName & "' ORDER BY Birthday; (doubtful you'll have
2+ patients with the same name, but maybe)

SELECT PatientID, [Name], SSN, Birthday, [First Visit] FROM Patients WHERE
SSN = '" & me.txtBoxSSN & "'; (this one doesn't have a sort order because
SSN is unique - or should be)

[Senna_Rettop]

Re: Using text box to filter

Thank you so much for all your help so far, but I must ask it further.
I made the changes you mentioned before and they seem to work. But now
when I enter text into my text boxes and press enter the code window
comes up with an error message.
it says "compile error; methods or data member not found" When I
press ok, it then shows me the code "Public Sub searchdb(search Type As
String)" highlighted in yellow. I think that there is a disconnect
between this command and the form. I've tried tinkering with it but
without success. I please excuse my inexperience. I've pasted below
all the code on the form so far.

Option Compare Database

Private Sub Form_Current()

End Sub

Private Sub TxtBoxName_Afte rUpdate()
Call searchdb("Name" )

End Sub

Private Sub TxtBoxSSN_After Update()
Call searchdb("SSN")

End Sub
Public Sub searchdb(search Type As String)

Me.ListBox.RowS ource = ""
If searchType = "Name" Then
Me.TxtBoxSSN = ""
Me.ListBox.RowS ource = "SELECT [First Name], [Last Name], SSN, [ID
1] FROM [Patient Names] WHERE [Last Name] = '" & Me.TxtBoxName & "'
ORDER BY [First Name]"
ElseIf searchType = "SSN" Then
Me.TxtBoxName = ""
Me.ListBox.RowS ource = "SELECT [First Name], [Last Name], SSN, [ID
1] FROM [Patient Names] WHERE SSN = '" & Me.TxtBoxSSN & "' "

End If

End Sub

Private Sub search_filter_C lick()

On Error GoTo Err_search_filt er_Click

DoCmd.DoMenuIte m acFormBar, acRecordsMenu, 2, , acMenuVer70

Exit_search_fil ter_Click:
Exit Sub

Err_search_filt er_Click:
MsgBox Err.Description
Resume Exit_search_fil ter_Click

End Sub

[DFS]

Re: Using text box to filter

Senna_Rettop wrote:

Thank you so much for all your help so far, but I must ask it further.
I made the changes you mentioned before and they seem to work. But now
when I enter text into my text boxes and press enter the code window
comes up with an error message.
it says "compile error; methods or data member not found" When I
press ok, it then shows me the code "Public Sub searchdb(search Type As
String)" highlighted in yellow. I think that there is a disconnect
between this command and the form. I've tried tinkering with it but
without success. I please excuse my inexperience. I've pasted below
all the code on the form so far.
>
Option Compare Database
>
Private Sub Form_Current()
>
End Sub
>
Private Sub TxtBoxName_Afte rUpdate()
Call searchdb("Name" )
>
End Sub
>
Private Sub TxtBoxSSN_After Update()
Call searchdb("SSN")
>
End Sub
Public Sub searchdb(search Type As String)
>
Me.ListBox.RowS ource = ""
If searchType = "Name" Then
Me.TxtBoxSSN = ""
Me.ListBox.RowS ource = "SELECT [First Name], [Last Name], SSN, [ID
1] FROM [Patient Names] WHERE [Last Name] = '" & Me.TxtBoxName & "'
ORDER BY [First Name]"
ElseIf searchType = "SSN" Then
Me.TxtBoxName = ""
Me.ListBox.RowS ource = "SELECT [First Name], [Last Name], SSN, [ID
1] FROM [Patient Names] WHERE SSN = '" & Me.TxtBoxSSN & "' "
>
End If
>
End Sub
>
Private Sub search_filter_C lick()
>
On Error GoTo Err_search_filt er_Click
>
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, 2, , acMenuVer70
>
Exit_search_fil ter_Click:
Exit Sub
>
Err_search_filt er_Click:
MsgBox Err.Description
Resume Exit_search_fil ter_Click
>
End Sub

I'm pretty sure the code will work if the control names match. My example
code uses controls named 'listBox', 'txtBoxName' and 'txtBoxSSN'. Rename
your listBox control to 'listResults' and update the search code to use that
name. Leave your text boxes 'txtBoxName' and 'txtBoxSSN' alone.

I think that will take care of the compilation issue. If not,
unfortunately, Access doesn't always highlight the problem where it actually
exists. Do you have a command button on your form named 'search_filter' ?
If not, remove that code.

[Senna_Rettop]

Re: Using text box to filter

Thank you so much for you help. The code works wonderfully!!!