How am I screwing up my Search Form?

[MarvinM]

Hi Experts,

I created a search form. It has 6 unbound text boxes and 2 combo boxes in the header. Users can select values from the combo boxes and/or enter names in the other text boxes. These values all go into a filter on my Main table and the filtered results show up in the detail section. That used to work fine.

Now, I've been trying to convert the filter into a query and show the query results in the detail section instead.
(Why? Because of the ever-changing business requirements, of course!)

Here's my road block. :banghead:
For some reason, the detail section went blank. All white.
When I change the Data Entry property to No, it fixes that.
However, the text boxes for entering the search criteria will not accept any values anymore. It's like they are disabled.
When I change Data Entry property to Yes, I can enter text into the text boxes again. But the detail section blanks out again.

What am I overlooking? Can someone please give me a run down of what my property settings should be for a search form like this? I'm afraid I've made so many experimental changes, that it's beyond hope.

Thank you so much!
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010

 

[Isskint]

Hi Marvin

If i am reading your post correctly it sounds as though the query is not returning any data. 1st questionoes a record with the criteria you enter, actually exist? The next thing is to check how you created the parameters in the query. Do you use IIF() to check if the search boxes are blank? IIF(NZ(Forms!Search!Criteria1,0)=0,"*",Forms!Search!Criteria1) - without this you could be asking the query to find records where that field is Null.

Can you post the query SQL?

 

[MarvinM]

Thanks, Isskint,

I got it. I went back and rebuilt the whole search form from the beginning. I must have changed a property that I shouldn't have. Anyway, when I created a fresh new form, everything was working again.

I gave up on the query idea and just built a more complex filter. I was trying to get a constraint based on a second table and I figured out how to do that with a subquery and an IN clause. It looks like this:

If lngLer > 0 Then 'There is a constraint on the tmpResp table.
   strWhere = "[Rec_Num] IN (SELECT Rec_Num FROM tmpResp WHERE " & strResp & ";)"

strWhere is my filter condition and strResp is a similar condition built from the 2 controls that go to the second table.
Thanks!
_________________
Regards,
Marvin M
Windows 7 Professional, MS Access 2007/2010