Creating an advanced search from a form

M

Mike Green

Hi All
I have just spent the last three hours trying to get this to work and I just
cant see where I am going wrong, so please help and point me in the right
direction.

I am trying to produce a simple unbound form that the user can enter items
to search for in the main database. The three items are Caravan Make,
Caravan Reg, and Customer Surname.
I was hopeing to search on any item that was populated but I would settle
for just enetring any one item and it working correctly.
The three data fields in the search form all have text fields in the main Db
data tables.
I was trying to get the search to match anything begining with the
characters that the user has entered into the search form and then pass that
to the search result form. The search results form displays the number of
records that match or "no data found" and all the records. I can get the
thing to work on the complete surname or the complete reg but thats it. I
have tried loads of permitations this evening and I am now going screen
blind so appologies if the code posted below is too bad. I hope that
someone gets the idea of what I am trying to acheive and can point me in the
right direction.

***************************************BEGIN************************
Private Function SEARCH_DATABASE()

Dim StrSQL As String

'first iterate the sql string with the initial settings
StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"
If IsNull(Me![SCaravanRegNo]) Or IsEmpty(Me![SCaravanRegNo]) Then
If Not (IsNull(Me![SCaravanMake]) Or IsEmpty(Me![SCaravanMake]))
Then
StrSQL = StrSQL & "AND Str([CaravanMake]) Like " &
[SCaravanMake] & " "
End If
If Not (IsNull(Me![SCustomer]) Or IsEmpty(Me![SCustomer])) Then
StrSQL = StrSQL & "AND [CustomerSName] Like '" & [SCustomer] &
"'"
End If
'if the information is here do this
Forms![Frm_Search_Results].SetFocus
DoCmd.ApplyFilter , StrSQL
'if there was only reg information do this
Else

StrSQL = "SELECT Qry_Caravan_Customer_plot.CustomerID,
Qry_Caravan_Customer_plot.Caravan, Qry_Caravan_Customer_plot.Customer,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.Site,
Qry_Caravan_Customer_plot.CaravanRegNo,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.CustomerSName,
Qry_Caravan_Customer_plot.CaravanMake"
StrSQL = StrSQL & "FROM Qry_Caravan_Customer_plot WHERE
((Qry_Caravan_Customer_plot.CaravanRegNo Like '*" & Me.SCaravanRegNo & "
*')) ;"
Forms![Frm_Search_Results].RecordSource = StrSQL

End If

DoCmd.Close A_FORM, "Frm_Search_Input"
Debug.Print strSQLAppend
End Function
*****************************************************************END*************************************************Thanks in advanceMike
 
A

Albert D. Kallal

You don't near that much code:

Private Function SEARCH_DATABASE()

Dim StrSQL As String
'first iterate the sql string with the initial settings

StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"

If IsNull(Me.SCaravanMake) = False Then
StrSQL = StrSQL & "AND CaravanMake Like " & Me.SCaravanMake & "*' "
End If

If IsNull(Me.SCustomer) = False Then
StrSQL = StrSQL & "AND CustomerSName Like '" & Me.SCustomer & "*' "
End If

If IsNull(Me.SCaravanRegNo) = False Then
StrSQL = "CaravanRegNo = '" & Me.SCaravanRegNo & "'"
End If


StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Forms![Frm_Search_Results].RecordSource = StrSQL

End Function

the above is ALL you need. If you don't enter anything, then the sql will
have the 1 =1 as the condition, and that just fine and dandy.

Further, note how I setup the last condition of "RegNo"..if the user enters
a reg number, then THE OTHER TWO conditions are ignored!!! (they are ignored
because the StrQL assigns a new condition, and does NOT concatenate the
previous values. If you need customer + regno + caravamake, then simply
change the sql as:

StrSQL = StrSql & "and CaravanRegNo = '" & Me.SCaravanRegNo & "'"

But, I don't think it makes sense to search by a given customer and van name
when you have a RegNo

I have no idea what the following is for:
DoCmd.Close A_FORM, "Frm_Search_Input"

I don't think you need it, but likely it is un-related to the rest of this
problem anyway...

I would suggest that you make the form Frm_Search_Results model, so then
the user has to close that form to return to the condistiosn form at which
time they can enter, or chagne values.

In addtion, I used the "like" command, so, ONLY the first few chracters of a
customer name, or van name need be entered for seaching. (I assume that is
likey why you used the like command).
 
M

Mike Green

Albert
Thanks again for your assistance that works great! If I could possibly
trouble you with one more part to this solution, how would I display a
simple warning if no data matches the query?

Regards
Mike




Mike Green said:
Hi All
I have just spent the last three hours trying to get this to work and I
just cant see where I am going wrong, so please help and point me in the
right direction.

I am trying to produce a simple unbound form that the user can enter items
to search for in the main database. The three items are Caravan Make,
Caravan Reg, and Customer Surname.
I was hopeing to search on any item that was populated but I would settle
for just enetring any one item and it working correctly.
The three data fields in the search form all have text fields in the main
Db data tables.
I was trying to get the search to match anything begining with the
characters that the user has entered into the search form and then pass
that to the search result form. The search results form displays the
number of records that match or "no data found" and all the records. I
can get the thing to work on the complete surname or the complete reg but
thats it. I have tried loads of permitations this evening and I am now
going screen blind so appologies if the code posted below is too bad. I
hope that someone gets the idea of what I am trying to acheive and can
point me in the right direction.

***************************************BEGIN************************
Private Function SEARCH_DATABASE()

Dim StrSQL As String

'first iterate the sql string with the initial settings
StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"
If IsNull(Me![SCaravanRegNo]) Or IsEmpty(Me![SCaravanRegNo]) Then
If Not (IsNull(Me![SCaravanMake]) Or IsEmpty(Me![SCaravanMake]))
Then
StrSQL = StrSQL & "AND Str([CaravanMake]) Like " &
[SCaravanMake] & " "
End If
If Not (IsNull(Me![SCustomer]) Or IsEmpty(Me![SCustomer])) Then
StrSQL = StrSQL & "AND [CustomerSName] Like '" & [SCustomer] &
"'"
End If
'if the information is here do this
Forms![Frm_Search_Results].SetFocus
DoCmd.ApplyFilter , StrSQL
'if there was only reg information do this
Else

StrSQL = "SELECT Qry_Caravan_Customer_plot.CustomerID,
Qry_Caravan_Customer_plot.Caravan, Qry_Caravan_Customer_plot.Customer,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.Site,
Qry_Caravan_Customer_plot.CaravanRegNo,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.CustomerSName,
Qry_Caravan_Customer_plot.CaravanMake"
StrSQL = StrSQL & "FROM Qry_Caravan_Customer_plot WHERE
((Qry_Caravan_Customer_plot.CaravanRegNo Like '*" & Me.SCaravanRegNo & "
*')) ;"
Forms![Frm_Search_Results].RecordSource = StrSQL

End If

DoCmd.Close A_FORM, "Frm_Search_Input"
Debug.Print strSQLAppend
End Function
*****************************************************************END*************************************************Thanks
in advanceMike
Click to expand...
 
A

Albert D. Kallal

Mike Green said:
Albert
Thanks again for your assistance that works great! If I could possibly
trouble you with one more part to this solution, how would I display a
simple warning if no data matches the query?
Click to expand...

Well, if it was a reprot, I would simply use the reports on-no data event.

For a form?

Hum you could go:

if dcount("*" , "qry_Caravan_Customer_plot", strSql) = 0 then
msgbox "no records, please try again"
else
DoCmd.OpenForm "Frm_Search_Results"
StrSQL = "select * from Qry_Caravan_Customer_plot where " & StrSQL
Forms![Frm_Search_Results].RecordSource = StrSQL
end if

As mentioned, you should consider making the search results form model, as
then the user will be forced to close the form to return back to the prompt
for to change the criteria.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)
Regards
Mike




Mike Green said:
Hi All
I have just spent the last three hours trying to get this to work and I
just cant see where I am going wrong, so please help and point me in the
right direction.

I am trying to produce a simple unbound form that the user can enter
items to search for in the main database. The three items are Caravan
Make, Caravan Reg, and Customer Surname.
I was hopeing to search on any item that was populated but I would settle
for just enetring any one item and it working correctly.
The three data fields in the search form all have text fields in the main
Db data tables.
I was trying to get the search to match anything begining with the
characters that the user has entered into the search form and then pass
that to the search result form. The search results form displays the
number of records that match or "no data found" and all the records. I
can get the thing to work on the complete surname or the complete reg but
thats it. I have tried loads of permitations this evening and I am now
going screen blind so appologies if the code posted below is too bad. I
hope that someone gets the idea of what I am trying to acheive and can
point me in the right direction.

***************************************BEGIN************************
Private Function SEARCH_DATABASE()

Dim StrSQL As String

'first iterate the sql string with the initial settings
StrSQL = "1 = 1 "
'find out what information is there
DoCmd.OpenForm "Frm_Search_Results"
If IsNull(Me![SCaravanRegNo]) Or IsEmpty(Me![SCaravanRegNo]) Then
If Not (IsNull(Me![SCaravanMake]) Or IsEmpty(Me![SCaravanMake]))
Then
StrSQL = StrSQL & "AND Str([CaravanMake]) Like " &
[SCaravanMake] & " "
End If
If Not (IsNull(Me![SCustomer]) Or IsEmpty(Me![SCustomer])) Then
StrSQL = StrSQL & "AND [CustomerSName] Like '" & [SCustomer] &
"'"
End If
'if the information is here do this
Forms![Frm_Search_Results].SetFocus
DoCmd.ApplyFilter , StrSQL
'if there was only reg information do this
Else

StrSQL = "SELECT Qry_Caravan_Customer_plot.CustomerID,
Qry_Caravan_Customer_plot.Caravan, Qry_Caravan_Customer_plot.Customer,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.Site,
Qry_Caravan_Customer_plot.CaravanRegNo,"
StrSQL = StrSQL & "Qry_Caravan_Customer_plot.CustomerSName,
Qry_Caravan_Customer_plot.CaravanMake"
StrSQL = StrSQL & "FROM Qry_Caravan_Customer_plot WHERE
((Qry_Caravan_Customer_plot.CaravanRegNo Like '*" & Me.SCaravanRegNo & "
*')) ;"
Forms![Frm_Search_Results].RecordSource = StrSQL

End If

DoCmd.Close A_FORM, "Frm_Search_Input"
Debug.Print strSQLAppend
End Function
*****************************************************************END*************************************************Thanks
in advanceMike
Click to expand...
Click to expand...
 

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

message if no matching data 2
strSQL in Search Form 4
PLEASE HELP.....List Box Search Form 1
No results on a multiple parameter search 3
search button to report based on multiple combo boxs ( combo criteria based upon queries ) 6
Add Next Number To A Field from another Form 6
Problem with form opening From List Box 2
search cmd on a form for a subform on a different form 2

Top