Search Form not performing a search (1 Viewer)

KV_access

New member
Local time
Today, 04:20
Joined
Mar 24, 2020
Messages
12
Hello,

So, I am still really new to access in general and I am not sure how to figure out why my search form isn't working. I am trying to adapt code from Allen Browne's "Microsoft Access Tips for Serious Users" ser-62 which goes over Search criteria and can be found here: http://allenbrowne.com/ser-62.html

However, when I try to "search" or filter my data, nothing happens. I have attached a picture which shows a basic look at what I am hoping to turn my search criteria into. The table in the picture where you can see the headers in is a Form that references a Query table. (I hope I am using the right nomenclature or at least it is somewhat understandable)

Access Search Form.PNG


I have attached the code I have attempted to adapt, but nothing is filtered when any combination of criteria is filled out. My end goal is to have it filter out based on criteria provided, but if no criteria is provided in any particular text box it will show all records that meet any other filled out text box.

I am not sure where to go from here. My best guess is I am doing something wrong in strWhere = strWhere & "([Work Order#] = """ & Me.txtWordOrder & """) AND ".

Any help or guidance is greatly appreciated! Thank you!


Option Compare Database

Private Sub Command20_Click()
' Search button
Call Search
End Sub
Sub Search()
'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'***********************************************************************
'Look at each search box, and build up the criteria string from the non-blank ones.
'***********************************************************************
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.txtWordOrder) Then
strWhere = strWhere & "([Work Order#] = """ & Me.txtWordOrder & """) AND "
End If

If Not IsNull(Me.txtTask) Then
strWhere = strWhere & "([Task#] = """ & Me.txtTask & """) AND "
End If

If Not IsNull(Me.txtInvoice) Then
strWhere = strWhere & "([Invoice#] = """ & Me.txtInvoice & """) AND "
End If

If Not IsNull(Me.txtCrew) Then
strWhere = strWhere & "([Crew ID] = """ & Me.txtCrew & """) AND "
End If

If Not IsNull(Me.txtEquipID) Then
strWhere = strWhere & "([Eqipment ID#] = """ & Me.txtEquipID & """) AND "
End If

If Not IsNull(Me.txtJobEquipClass) Then
strWhere = strWhere & "([Job / Equipment Classification] = """ & Me.txtJobEquipClass & """) AND "
End If

'Another text field example. Use Like to find anywhere in the field.
' If Not IsNull(Me.txtFilterMainName) Then
' strWhere = strWhere & "([MainName] Like ""*" & Me.txtFilterMainName & "*"") AND "
' End If

'Number field example. Do not add the extra quotes.
' If Not IsNull(Me.cboFilterLevel) Then
' strWhere = strWhere & "([LevelID] = " & Me.cboFilterLevel & ") AND "
' End If

'Yes/No field and combo example. If combo is blank or contains "ALL", we do nothing.
' If Me.cboFilterIsCorporate = -1 Then
' strWhere = strWhere & "([IsCorporate] = True) AND "
' ElseIf Me.cboFilterIsCorporate = 0 Then
' strWhere = strWhere & "([IsCorporate] = False) AND "
' End If

'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date] <= " & Format(Me.txtEndDate, conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event instead.
'The problems are explained at http://allenbrowne.com/bug-06.html
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show no records.
'Me.Filter = "(False)"
'Me.FilterOn = True
End Sub

 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
See below, it is there for a reason
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

Un comment the debug.print and show us what your filter looks like.

If interested I have far simpler code to use.
 

KV_access

New member
Local time
Today, 04:20
Joined
Mar 24, 2020
Messages
12
See below, it is there for a reason
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
'Debug.Print strWhere

Un comment the debug.print and show us what your filter looks like.

If interested I have far simpler code to use.

By filter, you just means what the (Ctrl + G) command windows shows right?

If so, I tried a couple different combinations of criteria and the print out as follow

([Task#] = "500") AND ([Invoice#] = "7000") AND ([Crew ID] = "400")
([Task#] = "500") AND ([Crew ID] = "400")
([Task#] = "500") AND ([Date] >= #03/30/2018#) AND ([Date] <= #04/16/2019#)


It seems to be getting the correct information I provide it, however the table still doesn't update, so I guess it isn't making it to my "query referenced form". I would definitely be interested in simpler code though since I don't really know what I am doing.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
It does not look correct to me. Are Task#, Invoice#, CrewID all text fields? If not you need to get rid of the quotes.
 

KV_access

New member
Local time
Today, 04:20
Joined
Mar 24, 2020
Messages
12
It does not look correct to me. Are Task#, Invoice#, CrewID all text fields? If not you need to get rid of the quotes.

They probably should be numerical values instead of text fields, but because of how everything was created I believe everything is treated as text.

Such as how the Task# Query is being treated below: (Unless I am misunderstanding how it works)
1585346411689.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
If they are numeric need to change the code so that you get
([Task#] = 500) AND ([Invoice#] = 7000) AND ([Crew ID] = 400)
And not
([Task#] = "500") AND ([Invoice#] = "7000") AND ([Crew ID] = "400")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
strWhere = strWhere & "([Task#] = """ & Me.txtTask & """) AND "
should be
strWhere = strWhere & "([Task#] = " & Me.txtTask & ") AND "

Also I would use ' instead of " for text fields
strWhere = strWhere & "([Job / Equipment Classification] = '" & Me.txtJobEquipClass & "') AND "
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
Can you post a version of the db?. Strip it down to just the form if needed.
 

KV_access

New member
Local time
Today, 04:20
Joined
Mar 24, 2020
Messages
12
Can you post a version of the db?. Strip it down to just the form if needed.
I have attached the database stripped down to just the forms. Due to how things where linked it won't contain any actual information, but maybe you can see how everything is setup between the Form linked to a Query and the Search Form. Hopefully this will provide enough information to find the problem and I apologize for only being able to provide this much.


I also haven't attempted any of your suggestions yet on the coding changes, but thank you for taking a look at this!
 

Attachments

  • DatabaseFormsOnly.zip
    76.3 KB · Views: 538

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:20
Joined
May 21, 2018
Messages
8,463
The biggest issue is below so the filter was never going to work. You are not applying the filter to the form you are applying it to a subform.
Code:
       strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True

it should be something like
Code:
  dim sfrm as Access.form
  set sfrm = me.controls("Query Form").form
  sfrm.filter = strWhere
  sfrm.filteron = true

You still need to fix the numeric fields so they do not have ".
When you debug it should look like
SomeTextField = 'TextValue' AND someNumericField = 123 AND SomeDateField = #1/1/2020#

If you want me to look at it in more detail I would need the corresponding tables so I can actually test the results. In other words the form would have to be workable. Or at least I need to know the datatypes of all the fields to use in the search.
 
Last edited:

KV_access

New member
Local time
Today, 04:20
Joined
Mar 24, 2020
Messages
12
The biggest issue is below so the filter was never going to work. You are not applying the filter to the form you are applying it to a subform.
Code:
       strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True

it should be something like
Code:
  dim sfrm as Access.form
  set sfrm = me.controls("Query Form").form
  sfrm.filter = strWhere
  sfrm.filteron = true

You still need to fix the numeric fields so they do not have ".
When you debug it should look like
SomeTextField = 'TextValue' AND someNumericField = 123 AND SomeDateField = #1/1/2020#

If you want me to look at it in more detail I would need the corresponding tables so I can actually test the results. In other words the form would have to be workable. Or at least I need to know the datatypes of all the fields to use in the search.

Sorry for just now getting back, but thank you for all the help. I wasn't aware of how to treat a form versus a subform in this case, so you are right that my code would never have worked. Thank you again though, I was able to get the search filters to work like I had hoped.

I am now running into a separate issue regarding #num errors which I am going to post in a different thread, but thank you!
 

Users who are viewing this thread

Top Bottom