Filter Records Based on 4 Combobox and 2 Textbox

[Mackbear]

Hi everyone, it's me again... right now I am trying to filter a record using combo boxes and textboxes, it is like searching for a particular record based on the criteria and the criterias are on the combo box and textbox. I followed a tutorial and I don't know what I missed. I have attached a file with the image of the error and the form and the code as well. Thank you very much!:banghead:

 

[June7]

Not getting any values in the parameters. Need to step debug.

Also, need a space before and after "AND" so text won't run together: " AND " and a space after where "

Missing apostrophes for the text parameters. They must be in pairs.

What tutorial did you follow? Here is one http://allenbrowne.com/ser-62.html

 

[Mackbear]

Thanks for this!!! I will try this later, by the way this is the tutorial i followed:
https://youtu.be/HMjQbSxbhYQ

 

[Cronk]

And if you are posting again, it would help if you posted the sql or the string on which you are generating the filter, not just the error message.

 

[Mackbear]

Not getting any values in the parameters. Need to step debug.

Also, need a space before and after "AND" so text won't run together: " AND " and a space after where "

Missing apostrophes for the text parameters. They must be in pairs.

What tutorial did you follow? Here is one http://allenbrowne.com/ser-62.html

Hi, i tried the tutorial here and when i run the code it is asking for a parameter for vendor. Thankss

 

[June7]

Does the query have dynamic parameter?

Have to provide your code for analysis if you really want some help. Or the db. See instructions at bottom of my post.

 

[Mackbear]

Tried everything, does not work. =' c please help = 'c

 

[June7]

Provide your code or db.

 

[Mackbear]

Hi, thank you so much for taking some time to look into it. I have attached the DB. I would like to apply filters on the following forms:

frmEmergencyPaymentAP
APWorkflow1
frmlatefeewaiver1

 

[June7]

The only form with code behind Search button is APWorkflowresolved. The code references a different form name.

Does not use the code provided in the tutorial I referenced. Doesn't even use the search criteria code from the tutorial you referenced. Need to change code in the button Click event.

Follow one of the tutorials and build your code in the button Click event.

I prefer Allen Browne's approach that sets Filter and FilterOn properties instead of RecordSource.

Every module should have Option Explicit in header. DB can be set to do this by default from the VBA Editor: Tools > Options > Editor > check Require Variable Declaration.
Will have to manually add to existing modules.

 

[Mackbear]

The only form with code behind Search button is APWorkflowresolved. The code references a different form name.

Does not use the code provided in the tutorial I referenced. Doesn't even use the search criteria code from the tutorial you referenced. Need to change code in the button Click event.

Follow one of the tutorials and build your code in the button Click event.

I prefer Allen Browne's approach that sets Filter and FilterOn properties instead of RecordSource.

Every module should have Option Explicit in header. DB can be set to do this by default from the VBA Editor: Tools > Options > Editor > check Require Variable Declaration.
Will have to manually add to existing modules.

Yes, I tried it the tutorial you suggested a while ago but does not work and went back to the tutorial in youtube which does not use a button, the filtering is triggered by the after update of the combo box and textboxes. The search criteria is created as functions which are called under each comboboxes' after update. Where do I set the Filter and FilterOn properties instead of RecordSource. I apologize as I don't know much about how Access works, I just copy the codes I find online. = 'c

 

[June7]

Allen Browne's tutorial is often referenced and proven code. Since you did not provide your attempt, can't say why you have issue with it.

Looked at form APWorkflow. If you step debug the code you will see the IsNull() tests are not working. Controls are not null when empty, they have empty string. Null and empty string are not the same. The Clear procedure is setting controls to empty string then Search code tests for null. Set controls to Null in the Clear procedure or test for empty string in the Search.

 

[Mackbear]

Allen Browne's tutorial is often referenced and proven code. Since you did not provide your attempt, can't say why you have issue with it.

Looked at form APWorkflow. If you step debug the code you will see the IsNull() tests are not working. Controls are not null when empty, they have empty string. Null and empty string are not the same. The Clear procedure is setting controls to empty string then Search code tests for null. Set controls to Null in the Clear procedure or test for empty string in the Search.

Hello, I tried the Allen Browne's and everything is working except the filter for Vendor. I can't identify what is wrong. I have attached the DB, I hope you can look into it ='c

 

[MajP]

If Not IsNull(Me.cbvendo) Then
strWhere = strWhere & "([VendorName] Like ""*" & Me.cbvendo & "*"") AND "
Debug.Print strWhere
End If

debug and you will see why.

 

[Mackbear]

debug and you will see why.

Hello, when I go to Debug, it does nothing. What should I be looking for? I'm sorry, I am a beginner on this.

 

[June7]

What do you mean by 'go to Debug'? Did you set a breakpoint in code? Did you use Debug.Print? Refer to link at bottom of my post.

What would be a valid combination of inputs that should return records?

The Vendor combobox value is vendor number not vendor name. If you Debug.Print the strWHERE you will see that.

 

[Mackbear]

What do you mean by 'go to Debug'? Did you set a breakpoint in code? Did you use Debug.Print? Refer to link at bottom of my post.

What would be a valid combination of inputs that should return records?

The Vendor combobox value is vendor number not vendor name. If you Debug.Print the strWHERE you will see that.

What do you mean by 'go to Debug'? Did you set a breakpoint in code? Did you use Debug.Print? Refer to link at bottom of my post.
- I go to the "Debug" tab in the VB window. I don't know how to use the Debug.Print

What would be a valid combination of inputs that should return records?
- I don't know what this means.

The Vendor combobox value is vendor number not vendor name. If you Debug.Print the strWHERE you will see that.[/QUOTE]
- I set this field as short text not a number. The combobox selection is not a number but names. How was this a number?

 

[June7]

Read the tutorial on Debugging in referenced link. Your code has a Debug.Print line with instruction on how to use it. Read that.

        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere

Never mind the inputs question. Code is using wildcards so this shouldn't matter.

Code is referencing VendorName field:

strWhere = strWhere & "([Vendor Name] Like ""*" & Me.cbvendo & "*"") AND "

The combobox provides value like "*4*" which is the vendor ID. Look at the combobox RowSource, ColumnCount, ColumnWidths properties to see why.

 

[Mackbear]

Read the tutorial on Debugging in referenced link. Your code has a Debug.Print line with instruction on how to use it. Read that.

        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere

Never mind the inputs question. Code is using wildcards so this shouldn't matter.

Code is referencing VendorName field:

strWhere = strWhere & "([Vendor Name] Like ""*" & Me.cbvendo & "*"") AND "

The combobox provides value like "*4*" which is the vendor ID. Look at the combobox RowSource, ColumnCount, ColumnWidths properties to see why.

I got it to work now thanks for the explanation! Vendor is now searching, now we fixed one, there is another one that came up! The Notice ID search on the APWorkflow1 form is saying No Criteria even if I know the notice id exist within that table. On the APWorkflowHistory form it works fine, I don't know if I accidentally changed something but I looked into the codes and they are the exact copy.... I attached the DB thank you so much I really appreciate it!

 

[June7]

Missing NOT:

If Not IsNull(Me.txsearchnoticeid) Then

If you had set breakpoint in the procedure and step debugged, that should have jumped out at you.