Easy search form (1 Viewer)

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Hello!

I have been trying to adapt a simple search form I found online but am running into problems. I've attached a small extract of the db if anyone can help?

Two issues, one the combo box on the search form isn't showing the codes needed in order to complete the search, also the date function isn't working.

I'm not the best at VBA and am really struggling, any help will be greatly appreciated!!

Thank you!
 

Attachments

  • Test (2).zip
    80.6 KB · Views: 126

Gasman

Enthusiastic Amateur
Local time
Today, 00:09
Joined
Sep 21, 2011
Messages
9,276
Firstly you do not have a field called EnteredOn?, it is called Date, which is a reserved word in any case and should not be used.?
Your combo has a field width of 0cm, so nothing will show.?

Debug.Print strwhere just before you set the filter and set a breakpoint in the code and step through with F8.
Your Code field is not numeric, so surround the value with single quotes '
See my debugging link.

Do all that and it appears to work.?
 
Last edited:

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Firstly you do not have a field called EnteredOn?, it is called Date, which is a reserved word in any case and should not be used.?
Your combo has a field width of 0cm, so nothing will show.?

Debug.Print strwhere just before you set the filter and set a breakpoint in the code and step through with F8.
Your Code field is not numeric, so surround the value with single quotes '
See my debugging link.

Do all that and it appears to work.?
Hi, thanks very much...getting there!! Combo box now showing values and date search works, got the following problem when trying to search using the combo box

'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.
Debug.Print strWhere
Me.Filter = strWhere - This line highlights yellow when trying to search using combo box
Me.FilterOn = True
End If
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:09
Joined
Sep 21, 2011
Messages
9,276
So show the content of the filter string?
That is what the Debug.Print is for ?

I dare say it will say Data Mismatch as it did for me.?
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Comes up with run time error 3057 and syntax error (missing operator) in query expression
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 19:09
Joined
May 21, 2018
Messages
5,679
debug.jpg

What is in the immediate window?
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Got it working with 2 extra "" either side of Me.cboFilterCode

If Not IsNull(Me.cboFilterCode) Then
strWhere = strWhere & "(
Code:
 = """ & Me.cboFilterCode & """) AND "
    End If


Just another quick one :) I'm also trying to add in a check box search criteria, I have the following code below:

    If Not IsNull(Me.cboFilterCode) Then
        strWhere = strWhere & "([Code] = """ & Me.cboFilterCode & """) AND "
    End If


But it comes up with an error on this line below:

Me.FilterOn = True

I'm assuming it is because the checkbox is either true or false?  Any ideas?

Thank you soooooo much!!
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Got that totally wrong, above

I need the code to say that when the check box for invoices is choosen it will show all results that are invoices
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:09
Joined
Sep 21, 2011
Messages
9,276
That is not a checkbox control? :-(

Rather than I having to go back into the DB all the time and likely that code is out of date, please post with tags the code you are currently trying to use?
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
If Not IsNull(Me.Check56) Then
strWhere = strWhere & "([Invoice] = & Me.Check56 & ) AND "
End If
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
If Not IsNull(Me.Check56) Then
strWhere = strWhere & "([Invoice] = & Me.Check56 & ) 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 + 1, 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
 

mounty76

Registered User.
Local time
Yesterday, 16:09
Joined
Sep 14, 2017
Messages
177
Have solved it

strWhere = strWhere & "([Invoice] = & Me.Check56 & ) AND "

Now reads

strWhere = strWhere & "([Invoice] ) AND "

Works perfectly, thanks again everyone, have a great day!
 

Gasman

Enthusiastic Amateur
Local time
Today, 00:09
Joined
Sep 21, 2011
Messages
9,276
Again :( , Debug.Print the strWhere to see what you have.?

Also inspect what the Invoice field holds for all the states it can be? You have to match that.

And please, please start using code tags. :(
 

Users who are viewing this thread

Top Bottom