Filter Records Based on 4 Combobox and 2 Textbox (1 Viewer)

Mackbear · Apr 19, 2019

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 · Apr 19, 2019

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 · Apr 19, 2019

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

Cronk · Apr 19, 2019

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 · Apr 19, 2019

June7 said:
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 · Apr 19, 2019

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 · Apr 19, 2019

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

June7 · Apr 19, 2019

Provide your code or db.

Mackbear · Apr 20, 2019

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 · Apr 20, 2019

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 · Apr 20, 2019

June7 said:
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 · Apr 20, 2019

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.

Uncle Gizmo · Apr 20, 2019

I provide several options for creating a Search Form. I think you should be able to apply one of them to your problem.

I have done a series of videos on how to create a Search Form:-
Want to build yourself a Search Form?

The code is available for a few dollars here:-
Building a Search Form - Code - Nifty Access

There's also a free option, follow the instructions on this video here:-
Free Search Form Code - Nifty Access

Finally there's an advanced solution whereby you just drop a ready-made component into your database, set it up (I can do it in about 4 minutes!) And there you go you should have a working Search Form - but it's not free..

Nifty Search Form

I've done a video demonstrating how you could use this in your database, however I won't be able to post the video for a couple of days due to the holidays!

Uncle Gizmo · Apr 20, 2019

Uncle Gizmo said:
I've done a video demonstrating how you could use this in your database, however I won't be able to post the video for a couple of days due to the holidays!

Here's The 1st Video....

Demonstration of how to setup the Nifty Access Search Form in a particular user-case - Nifty Access

Mackbear · Apr 22, 2019

June7 said:
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 · Apr 22, 2019

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 · Apr 22, 2019

MajP said:
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 · Apr 22, 2019

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 · Apr 22, 2019

June7 said:
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 · Apr 22, 2019

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

Code:

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

Filter Records Based on 4 Combobox and 2 Textbox (1 Viewer)

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Nifty Access Guy

Nifty Access Guy

Registered User.

Attachments

You've got your good things, and you've got mine.

Registered User.

AWF VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread