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
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