Making A Find Button

S

Strow

What would be the best way to make your own personalized find button. i
want one that only searchs for either fname, lname, phone, or a
combination of the three. what is the bets way to go about this, hard
coding it and not using any sort of wizard. would really appreciate the
help
 
G

Gijs Beukenoot

Strow bracht volgend idée uit :
What would be the best way to make your own personalized find button. i
want one that only searchs for either fname, lname, phone, or a
combination of the three. what is the bets way to go about this, hard
coding it and not using any sort of wizard. would really appreciate the
help
Click to expand...

I would probably setup 3 unbound textboxes and a search-button. Then
perform a filter on the form with these values when the user clicks the
button.
You probably want to build the filter-string in code to check for
null-values (users didn't fill in a field) and perhaps to check for a
'no records found with these values'.

ButtonClickEvent:

Dim strSearch as string
strSearch = ""

if Nz(Me.txtfNameSeach,"") <> "" then
strSearch = "fname = '" & me.txtfNameSeach & "'"
endif
if Nz(Me.txtlNameSeach,"") <> "" then
if strSearch <> "" then
strSeach = strSeach & " AND "
endif
strSearch = strSearch & "flname = '" & me.txtlnamesearch & "'"
endif
if Nz(Me.txtphoneSeach,"") <> "" then
if strSearch <> "" then
strSeach = strSeach & " AND "
endif
strSearch = strSearch & "flname = '" & me.txtphonesearch & "'"
endif

if strSearch <> "" then
me.filter = strSearch
me.filteron = true
'To be honest, I'm not sure if the following will work, haven't tries
this beore...
if me.recordset.eof then
call msgbox("No records found with these criteria...",
vbinformation & vbokonly,"Search")
endif
else
'Nothing to search for, user didn't enter any values
endif
 
S

Strow

you were right about the me.recordset.eof. it doesnt owork.. the rest
of the code executes perfectly, but i think i can find my way around
the rest of it. although if you have any pieces of code yu would like
and try to help me i am all ears!! thanks again
 
S

Strow

i think the problem i have s i have not declared a recordset, and im
not completely sure if what i have here is right. this is on a seperate
form, so i guessi should somehow link it to the client form so it can
search, its giving me a little trouble because its calling for a
redcordset, but i dont know how to make the recordset connect to the
clien form.
 
D

Douglas J Steele

Not sure if it's the total cause, but you need to change

strSeach = strSeach & " & "

to

strSeach = strSeach & " AND "

in the If Nz(Me.txtlNameSeach, "") <> "" Then construct.

As well, be aware that if any of the names have apostrophes in them (like
O'Reilly), your code won't work. You need either

If Nz(Me.txtfNameSeach, "") <> "" Then
strSearch = "firstname = " & Chr$(34) & Me.txtfNameSeach & Chr$(34)
End If
If Nz(Me.txtlNameSeach, "") <> "" Then
If strSearch <> "" Then
strSeach = strSeach & " AND "
End If
strSearch = strSearch & "lastname = " & Chr$(34) & Me.txtlNameSeach &
Chr$(34)
End If

or (assuming you're using Access 2000 or newer)

If Nz(Me.txtfNameSeach, "") <> "" Then
strSearch = "firstname = '" & Replace(Me.txtfNameSeach, "'", "''") & "'"
End If
If Nz(Me.txtlNameSeach, "") <> "" Then
If strSearch <> "" Then
strSeach = strSeach & " AND "
End If
strSearch = strSearch & "lastname = '" & Replace(Me.txtlNameSeach, "'",
"''") & "'"
End If

where, exagerated for clarity, that's Replace(Me.txtfNameSeach, " ' ", " ' '
")
 
S

Strow

still not working i am still gettin the same error
Run-time error '3075':

Syntax error (missing operator) in query expression 'firstname =
'John'lastname = 'Strowbridge".

that is exactly how it is wrote, with the quotes and everything. i
suspect im missing a & or the single or double quotes are in the wrong
space. its like she doesnt speerat ethe two words.
 
D

Douglas J Steele

Gijs Beukenoot said:
Strow bracht volgend idée uit :

Add an r (typo) and modify the & to AND
strSearch = strSearch & " AND "
Click to expand...

Good catch. I missed the typo!

Strow: Do you not have Option Explicit at the top of each module? Errors
like that should be caught at compile.
 
S

Strow

yes i forgot about the option expilcit, they already had this program
started when i came here and i forgot to check.

1 more question, if i want to requery the client form after i find no
matches, what is the code i use???
DoCmd.Requery ......then what?
 
G

Gijs Beukenoot

Strow plaatste dit op zijn scherm :
yes i forgot about the option expilcit, they already had this program
started when i came here and i forgot to check.

1 more question, if i want to requery the client form after i find no
matches, what is the code i use???
DoCmd.Requery ......then what?
Click to expand...

First, set the me.filteron to false (that removes the filter), then you
can execute a me.requery (although I'm not sure you'll need to requery
when you remove a filter)
 
S

Strow

didnt work though gijs, i turned the filter off righ tafter the messgae
boox in the code, and then i requied, and nothing changed
 
G

Gijs Beukenoot

Strow formuleerde op woensdag :
didnt work though gijs, i turned the filter off righ tafter the messgae
boox in the code, and then i requied, and nothing changed
Click to expand...

Having one (searching and showing) or two (one for searching and one
for showing) forms?

Maybe you should post the code again so we can have a look
 
S

Strow

Option Explicit
Option Compare Database
Private Sub cmdSearch_Click()
Dim strSearch As String
Dim strReportName As String
Dim strCriteria As String
strSearch = ""

If Nz(Me.txtfNameSeach, "") <> "" Then
strSearch = "firstname = '" & Me.txtfNameSeach & "'"
End If
If Nz(Me.txtlNameSeach, "") <> "" Then
If strSearch <> "" Then
strSearch = strSearch & " AND "
End If
strSearch = strSearch & "lastname = '" & Me.txtlNameSeach & "'"
End If
If Nz(Me.txtphoneSeach, "") <> "" Then
If strSearch <> "" Then
strSearch = strSearch & " AND "
End If
strSearch = strSearch & "Telephone = '" & Me.txtphoneSeach & "'"
End If

If strSearch <> "" Then
DoCmd.OpenForm "Clients", , , strSearch
DoEvents
If Forms!clients.Recordset.EOF Then
MsgBox "No Records Found"
'DoCmd.Close acForm, "Clients"
End If
Else
Me.FilterOn = False
DoCmd.Close acForm, "Find"
End If
End Sub

this is the code for my find form, and i want it so that after the
serach is done, the find box closes and if there are no records i want
my clients form to requery and have all the records in it instead of it
becoming blank because no records
 
G

Gijs Beukenoot

Het is zò dat Strow formuleerde :

If strSearch <> "" Then
DoCmd.OpenForm "Clients", , , strSearch
DoEvents
If Forms!clients.Recordset.EOF Then
MsgBox "No Records Found"
'DoCmd.Close acForm, "Clients"
End If
Else
Me.FilterOn = False
DoCmd.Close acForm, "Find"
End If
End Sub

this is the code for my find form, and i want it so that after the
serach is done, the find box closes and if there are no records i want
my clients form to requery and have all the records in it instead of it
becoming blank because no records
Click to expand...

OK, well, you're almost there then. But, the Me keyword is always about
the current form. The current form is the form "Find" and you want to
remove the filter from the form "Clients"
So, change that to
Forms![Clients].Filteron = false
 
Top