Making A Find Button

  • Thread starter Thread starter Strow
  • Start date Start date
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
 
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
 
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
 
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.
 
Strow had uiteengezet :
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.
Click to expand...

The form is probably bound to a recordset (rowsource I think it's
called)?
 
Strow plaatste dit op zijn scherm :
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.
Click to expand...

The form is probably bound to a recordset (showing records from the
table/query) and you are searching on the same form ??
If so, you can check the form's recordset (me.recordset.eof).
 
im not 100 percent sure of what you mean. i know that my find form is
finding the results, now i need to know how i can get them to display
or show up. im not 100 percent on how to explain it, but lets say i
have 2 johns in the table, then the record pointer ar the bottom will
go to 3 after i hit john for first name, but nothing changes. i know
shes returning the results, but nothing is coming up. and when i eneter
a first and second name i get an error runtimme error, that i cant
assign a value to an object and it points to the line Me.Filter =
strSearch. help help me guys
 
Strow stelde de volgende uitleg voor :
im not 100 percent sure of what you mean. i know that my find form is
finding the results, now i need to know how i can get them to display
or show up. im not 100 percent on how to explain it, but lets say i
have 2 johns in the table, then the record pointer ar the bottom will
go to 3 after i hit john for first name, but nothing changes. i know
shes returning the results, but nothing is coming up. and when i eneter
a first and second name i get an error runtimme error, that i cant
assign a value to an object and it points to the line Me.Filter =
strSearch. help help me guys
Click to expand...

Copy and paste the code here please
 
Option Compare Database
Private Sub cmdSearch_Click()
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSearch As String
strSearch = ""


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


If strSearch <> "" Then
Me.Filter = strSearch
Me.FilterOn = True
If Me.Recordset.EOF Then
MsgBox ("No Records Found")
End If
Else
End If

End Sub
 
Strow beweerde :
Option Compare Database
Private Sub cmdSearch_Click()
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
Dim strSearch As String
strSearch = ""


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


If strSearch <> "" Then
Me.Filter = strSearch
Me.FilterOn = True
If Me.Recordset.EOF Then
MsgBox ("No Records Found")
End If
Else
End If

End Sub
Click to expand...

You can delete the following two lines, you're not creating a recordset
here.
Dim rs As New ADODB.Recordset
Set rs = New ADODB.Recordset

Now, from what I've read in previous messages, you have two forms, one
showing the results, one with the search-stuff. I call the results form
frmResult in the code below and the searchform frmSearch.

Remove the last lines and replace them with these :
If strSearch <> "" Then
docmd.openform "frmResults",,,strSearch
'give access some time to show the form
doevents
'then check the recordset of the form to see if any records are
there
If forms!frmResults.recordset.eof Then
MsgBox "No Records Found"
'Close the resultsform again
docmd.close acform, "frmResults"
End If
Else
'The search is showing results, close the searchform
docmd.close acform, frmSearch
End If

End Sub

I think this should do the trick... You are now opening the resultsform
with a filtercondition. It will show you only records that correspond
to the filter. You might want to set the allowadditions to false to
prevent the user adding records.
If no records match the filtercondition, the messagebox comes up and
the form (frmResults) is closed.
 
This is what i have now. Whenver I search by one field, it works no
problem. but if i try to erach by fname, and lname then i ge an error,
and its the same error whenever i use any two fields. please help me.
the error reads
Runtime error "3075"
Syntax error (missing operator) in query expression lastname="whatever"
telephoen = 'whatever'



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
strSeach = strSeach & " & "
End If
strSearch = strSearch & "lastname = '" & Me.txtlNameSeach & "'"
End If
If Nz(Me.txtphoneSeach, "") <> "" Then
If strSearch <> "" Then
strSeach = strSeach & " 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
DoCmd.Close acForm, Find
End If
End Sub
 
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, " ' ", " ' '
")
 
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.
 
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.
 
ahhh nevermind, i have it working now, it is all good, thaikd for all
the help guys
 
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?
 
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)
 
didnt work though gijs, i turned the filter off righ tafter the messgae
boox in the code, and then i requied, and nothing changed
 
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
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combo Search Box 3
subreport in columns instead of rows? 1
Search for a duplicate person before adding 3
Make a sort button 1
test text string for a comma (,) 3
need guidance 1
Continuous Form Auto Fill In 1
Need Help in Making a Find Command Button 1

Back
Top