Solved Query for Datasheet SubForm referring to Form Controls for Search (1 Viewer)

[Anthony.DG]

Ok Im done banging my head against a wall... for now. Here is the low-down: 2

2 Forms
1 query


I have a form frmLandScaper and a subform sfLandScaper.
frmLandScaper has 2 controls. One is a combobox called cboQuarryFilter for QuarryID_FK the other is a textbox called txtSearchFilter to search MaterialName.
I have an AfterUpdate Event for both of them on frmLandScaper form properties "Me.sfLandScaper.Form.Requery".

In the query qryLandScaper (record source for sfLandscaper), column QuarryID_FK has on criteria line 1:
[Forms]![frmLandScaper]![cboQuarryFilter]

In column MaterialName has on criteria line 1:
Like "*" & [txtSearchFilter] & "*"
Criteria line 2:
Like "*" & [txtSearchFilter] & "*"



Hit Run, query brings up records fine. Open the form and txtSearchFilter works fine but cboQuarryFilter does not.
I have this for another Access database and it worked fine. Not now, I don’t know why it isn’t working.
Please help I have no idea what I’m missing or what else to do or if maybe I even accidentally made it work the first time. Is this the way to do it or is there a better way?

 

[theDBguy]

Can you post a sample copy of your db to show the problem?

 

[Anthony.DG]

Can you post a sample copy of your db to show the problem?

Here is a trimmed down copy.

 

[theDBguy]

Here is a trimmed down copy.

Thank you. I am not sure if this is what you want, but I hope it gives you some ideas.

 

[Anthony.DG]

Ok I see what you did in the query, adding the "Is Null". But how did you add that line of code in the columns to the right? I tried adding the same line to mine but it slaps an "Expr1:" on to it everytime I try and wont work the same as yours.

 

[theDBguy]

Ok I see what you did in the query, adding the "Is Null". But how did you add that line of code in the columns to the right? I tried adding the same line to mine but it slaps an "Expr1:" on to it everytime I try and wont work the same as yours.

If my query is the same as yours, you could just import it over your copy. That's Access way of translating SQL to a GUI display.

 

[Anthony.DG]

So you were using the SQL View to edit it? I just want to be able to repeat it when I need to.

 

[theDBguy]

So you were using the SQL View to edit it? I just want to be able to repeat it when I need to.

No, I actually use the GUI too. However, Access tends to rearrange things around once you switch from Design to Datasheet view.

 

[Anthony.DG]

I see what I was doing wrong!!! OH THANK YOU!! I was being lazy and just Typing "Is Null" into the criteria when I should have been putting the whole thing in for the null criteria.
Column MaterialName
([Forms]![frmLandScapers]![txtSearchFilter]) Is Null

I can duplicate this now you are a lifesaver and the GOAT in my book! THanks again XD!!

 

[theDBguy]

I see what I was doing wrong!!! OH THANK YOU!! I was being lazy and just Typing "Is Null" into the criteria when I should have been putting the whole thing in for the null criteria.
Column MaterialName
([Forms]![frmLandScapers]![txtSearchFilter]) Is Null

I can duplicate this now you are a lifesaver and the GOAT in my book! THanks again XD!!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.