Making progress with my search form but not quite there, need help with iif 1

[NeilT123]

I have been trying to amend a "search" facility that I have created on one of my forms.

I would like to be able to search by "like" or "begins with". On the main form I have an option group called OptSearch and value 1 is like and value 2 is begins with. The main form is called frmIfTCropping.

The subform is called frmIfCropping and is a single form and is populated using the following code.

SELECT tblTEMPIfCropping.FarmAccountNumber, tblTEMPIfCropping.AccountName, tblTEMPIfCropping.FieldName, tblTEMPIfCropping.FieldCode, tblTEMPIfCropping.[SubFarm/FieldGroup], tblTEMPIfCropping.FieldComments, tblTEMPIfCropping.FieldOrder, tblTEMPIfCropping.NoLongerCropped, RemovePunc([tblTEMPIfCropping].[FieldName]) AS Expr1
FROM tblTEMPIfCropping
WHERE (((RemovePunc([tblTEMPIfCropping].[FieldName])) Like "*" & [Forms]![frmIfTCropping]![Search2] & "*"))
ORDER BY tblTEMPIfCropping.FieldName;

I was hoping to put an iif statement into this code or add to the WHERE statement so that if Optsearch is 1 then the Like "*" & [Forms]![frmIfTCropping]![Search2] & "*" is used whereas if Optsearch is 2 the like statement loses the 1st "*" to become Like [Forms]![frmIfTCropping]![Search2] & "*" but I am stumped.

Can anyone give some suggestions as to how I achieve this?

Thank you in advance for any input

Neil

 

[dhookom]

I would use a little VBA code to build a SQL statement and then set subform's recordsource to the SQL statement.

Dim strSQL as String
Dim strWhere as String
strWhere = " WHERE 1=1 "
strSQL = "SELECT FarmAccountNumber, AccountName, FieldName, FieldCode, [SubFarm/FieldGroup], " & _
   "FieldComments, FieldOrder, NoLongerCropped, RemovePunc([FieldName]) AS Expr1 " & _
   "FROM tblTEMPIfCropping "
If Not IsNull(Me.Search2) Then
   Select Case Me.Optsearch 
      Case 1
          strWhere = strWhere & " AND [FieldName] Like """*" & [Search2] & "*"""
      Case 2
          strWhere = strWhere & " AND [FieldName] Like " & [Search2] & "*"""
End Select
strSQL = strSQL & strWhere & " ORDER BY FieldName; "
Me.frmIfCropping.Form.RecordSource = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Hi Duane, I have copied your code into my main form and on trying to compile I am getting an error:

Invalid outside procedure and the " WHERE 1=1 " is highlighted. Any suggestions please?

Neil

 

[dhookom]

Did you put the code into the On Click event of a button?

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

OK, ignore the earlier error message. I have copied the code into the on open of the main form. There was already some code to populate the temporary table so now the code reads

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False 'switch off warning messages re adding & deleting records
DoCmd.RunSQL ("INSERT INTO tblTEMPIfCropping SELECT * FROM qryfrmIfCropping")
DoCmd.SetWarnings True 'reset warnings
Me.Requery
DoCmd.Maximize
Dim strSQL As String
Dim strWhere As String
strWhere = " WHERE 1=1 "
strSQL = "SELECT FarmAccountNumber, AccountName, FieldName, FieldCode, [SubFarm/FieldGroup], " & _
   "FieldComments, FieldOrder, NoLongerCropped, RemovePunc([FieldName]) AS Expr1 " & _
   "FROM tblTEMPIfCropping "
If Not IsNull(Me.Search2) Then
   Select Case Me.OptSearch
      Case 1
          strWhere = strWhere & " AND [FieldName] Like """ * " & [Search2] & " * """"
      Case 2
          strWhere = strWhere & " AND [FieldName] Like " & [Search2] & "*"""
End Select
strSQL = strSQL & strWhere & " ORDER BY FieldName; "
Me.frmIfCropping.Form.RecordSource = strSQL
End If
End Sub

I put the final End If in.

I then deleted the recordsource from frmIfCropping. Does the above code automatically set the record source of frmIfCropping or is there something else I need to do?

There is a further subform sat in frmIfCropping which is on a master/child link of FieldCode.

If I try to open the forms I get several Enter Parameter Value for FieldCode and there is no data in the form frmIfCropping.

Also should the

strWhere = strWhere & " AND [FieldName] Like """ * " & [Search2] & " * """"

not be

strWhere = strWhere & " AND [Expr1] Like """ * " & [Search2] & " * """"

as Expr1 has had the spaces taken out of FieldName.

Thank you for your help with this.

Neil

 

[dhookom]

I wouldn't put the code in the Open of the main form since you haven't had the opportunity to enter anything in the search text box or set any options. I would add a command button that would apply the criteria. Also, the End If needs to be moved and the RemovePunc() applied to FieldName.
[CODE vba]
Dim strSQL As String
Dim strWhere As String
strWhere = " WHERE 1=1 "
strSQL = "SELECT FarmAccountNumber, AccountName, FieldName, FieldCode, [SubFarm/FieldGroup], " & _
"FieldComments, FieldOrder, NoLongerCropped, RemovePunc([FieldName]) AS Expr1 " & _
"FROM tblTEMPIfCropping "
If Not IsNull(Me.Search2) Then
Select Case Me.OptSearch
Case 1
strWhere = strWhere & " AND RemovePunc([FieldName]) Like """ * " & [Search2] & " * """"
Case 2
strWhere = strWhere & " AND RemovePunc([FieldName]) Like " & [Search2] & "*"""
End Select
End If
strSQL = strSQL & strWhere & " ORDER BY FieldName; "
debug.print "strSQL: " & strSQL
Me.frmIfCropping.Form.RecordSource = strSQL
[/code]
If you still get some errors, learn how to set a break point and step through your code.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Thank you for your help Duane. I got lost on your suggestion but it did get me thinking and for those who are interested my solution was to introduce a second unbound search box.

I then coded my main search box on the master form as follows

Private Sub Search_Change()
Dim vSearchString As String
 vSearchString = Search.Text
 
'option 1 is "like"
 If Me.OptSearch = 1 Then
        Search2.Value = vSearchString
        Search3.Value = ""
'option 2 is "begins with"
    ElseIf Me.OptSearch = 2 Then
        Search3.Value = vSearchString
        Search2.Value = ""
        
 End If
Me.frmIfCropping.Requery
Me.frmIfCropping.Form.Recordset.MoveFirst
End Sub

and then introduced amended the code behind the subform as follows

SELECT tblTEMPIfCropping.FarmAccountNumber, tblTEMPIfCropping.AccountName, tblTEMPIfCropping.FieldName, tblTEMPIfCropping.FieldCode, tblTEMPIfCropping.[SubFarm/FieldGroup], tblTEMPIfCropping.FieldComments, tblTEMPIfCropping.FieldOrder, tblTEMPIfCropping.NoLongerCropped, RemovePunc([tblTEMPIfCropping].[FieldName]) AS Expr1
FROM tblTEMPIfCropping
WHERE (((RemovePunc([tblTEMPIfCropping].[FieldName])) Like "*" & [Forms]![frmIfTCropping]![Search2] & "*" And (RemovePunc([tblTEMPIfCropping].[FieldName])) Like [Forms]![frmIfTCropping]![Search3] & "*"))
ORDER BY tblTEMPIfCropping.FieldName;

and all seems to work

 

[dhookom]

How much did you test this? I'm a bit surprised it works as you want.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

I have been using the form for about an hour and every time it has done exactly what was expected. The only issue I have to resolve is if I am using the "begins with" option and I type in too many letters and there is no match then it throws a runtime error 3021 no current record but I will deal with that tomorrow.