Query By Form - using Text Boxes and Combo Boxes in criteria

[vegemite]

The title is from an old thread that is now closed. I have built a form that has multiple fields, some combo box and some free text unbound. The search button calls a query which I designed from the awesome example from Jon K in the original post. The example opened the records on the query form, I want to open my main form and limit to the records that match the criteria selected.
I know this is so simple it probably shouldnt work but it has worked in other parts of my database (with a simple search)....


DoCmd.OpenForm "frmJobManagement", acNormal, "qrySearchProject"

 

[theDBguy]

Hi. What does your query SQL look like and what happens with your code now?

 

[vegemite]

SELECT [ProjectTitle] Like "*" & [Forms]![frmSearchMenu]![txtDescSearch] & "*" Or [Forms]![frmSearchMenu]![txtDescSearch] Is Null AS Expr1, tblJobManagement.OPMStaff, [Method]=[Forms]![frmSearchMenu]![cmbSearchMethod] Or [Forms]![frmSearchMenu]![cmbSearchMethod] Is Null AS Expr3, [Method]=[Forms]![frmSearchMenu]![cmbSearchMethod] Or [Forms]![frmSearchMenu]![cmbSearchMethod] Is Null AS Expr4, [MainFocus]=[Forms]![frmSearchMenu]![cmbSearchFocus] Or [Forms]![frmSearchMenu]![cmbSearchFocus] Is Null AS Expr5
FROM tblOrganisation INNER JOIN tblJobManagement ON tblOrganisation.OrgNameID = tblJobManagement.ClientOrgID;

 

[vegemite]

It just opens all records, even though I have set the filter. Im actually not sure if thats just because the query doesnt work!

 

[theDBguy]

It just opens all records, even though I have set the filter. Im actually not sure if thats just because the query doesnt work!

Well, I was expecting to see a WHERE clause in your query. What happens if you try to add a WHERE clause to it? Right now, if the query opens to all records, then the form will too, I think.

 

[vegemite]

OF course!!
In the examples I am looking it the AS Expr isnt there , that self generated - should I delete it, it comes up with a syntax error


SELECT tblJobManagement.ProjectNumber
FROM tblJobManagement
WHERE [ProjectTitle] Like "*" & [Forms]![frmSearchMenu]![txtDescSearch] & "*" Or [Forms]![frmSearchMenu]![txtDescSearch] Is Null AS Expr1, tblJobManagement.OPMStaff, [Method]=[Forms]![frmSearchMenu]![cmbSearchMethod] Or [Forms]![frmSearchMenu]![cmbSearchMethod] Is Null AS Expr3, [Method]=[Forms]![frmSearchMenu]![cmbSearchMethod] Or [Forms]![frmSearchMenu]![cmbSearchMethod] Is Null AS Expr4, [MainFocus]=[Forms]![frmSearchMenu]![cmbSearchFocus] Or [Forms]![frmSearchMenu]![cmbSearchFocus] Is Null AS Expr5
FROM tblOrganisation INNER JOIN tblJobManagement ON tblOrganisation.OrgNameID = tblJobManagement.ClientOrgID;

 

[theDBguy]

Hi. I have no idea what you're trying to get out of your query, so I don't know if you should take out anything or not. What I was wondering though was what happens if you add a WHERE clause to it?

 

[vegemite]

@theDBguy you are awesome! I got it to work (kindof) with a few tweaks only it is still displaying a dialog box for the fields I didnt enter. I want the user to be able to add all the search fields or leave some blank.

 

[theDBguy]

@theDBguy you are awesome! I got it to work (kindof) with a few tweaks only it is still displaying a dialog box for the fields I didnt enter. I want the user to be able to add all the search fields or leave some blank.

Hi. Glad to hear you got it sorted out. Keep tweaking it until you get it right. Good luck!