fCboSearch

[Kenln]

I am so stuck. I use this function all the time to filter combo boxes and it works just great. I am trying to get it to filter the form itself and nadda, nothing.

Okay here is the form's row source

SELECT qryrmt_Job_Descript.CO_NO, 
qryrmt_Job_Descript.DIV_NO, 
qryrmt_Job_Descript.JOB_NO, qryrmt_Job_Descript.DESCRIPT
FROM qryrmt_Job_Descript
WHERE (
((qryrmt_Job_Descript.DIV_NO) Like fCboSearch([Forms]![frm_Job_Test]![cbo_Div_No].[Value]))
);

Here is the combo box

SELECT DISTINCT 
Job_Info.DIV_NO 
FROM qryrmt_Job_Descript as Job_Info 
UNION 
Select "<Select All>" 
FROM qryrmt_Job_Descript as Job_Info 
ORDER BY 1;

here is the function.

Public Function fCboSearch(vCboSearch As Variant)
    If IsNull(vCboSearch) Or vCboSearch = "" Or vCboSearch = "<Select All>" Then
        fCboSearch = "*"
    Else
        fCboSearch = vCboSearch
    End If

End Function

In theory when I select from the combo box it should filter the form.

I have tried Me.Requery and Me.Refresh but nothing I still get the complete record set.

When I put break in the function vCboSearch is ALWAYS null????? Yes I can use this very same module to filter a combo box for giving jobs in a division and bingo the box filters fine. Where am I goofing up???

Please help,

Thank you,

 

[Kenln]

P.S. I can write a manual me.filteron and me.filterby =

But this function has worked so well everywhere else. I can't seem to find the problem.

Thanks

 

[KenHigg]

To start with I would build the forms recordsource sql statement on the fly and assign it to a text string. Then set the forms recordsource to the string. This, among other things, will allow you to break and see the actual sql statement to see what may be wrong.

As an alternative you could display a msg box with the forms resource string in it to see where the problem may be. I'm guessing it may be as simple as a few missing quotes - ? Something like:

fCboSearch = "'*'"

???

 

[Kenln]

No matter what I did it did not work. I tried my best to trace the flow.

I should have mentioned that the query is a pass-through. I don't know if that was the problem or not.

Since the query is already written, I ended up changing the text (SQL) of the query on the fly. It does the same thing just not as easy or cleanly.

I still do not know why a combo box would work with the Where statement but the form would not work with the exact same where. Also the combo is on the form that does not work.


Sigh....

Thanks for you help,