Query By Form - using Text Boxes and Combo Boxes in criteria (1 Viewer)

vegemite · Nov 29, 2019

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 · Nov 29, 2019

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

vegemite · Nov 29, 2019

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 · Nov 29, 2019

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 · Nov 29, 2019

vegemite said:
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 · Nov 29, 2019

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 · Nov 29, 2019

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 · Nov 29, 2019

@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 · Nov 29, 2019

vegemite said:
@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!

Query By Form - using Text Boxes and Combo Boxes in criteria (1 Viewer)

vegemite

Registered User.

theDBguy

I’m here to help

vegemite

Registered User.

vegemite

Registered User.

theDBguy

I’m here to help

vegemite

Registered User.

theDBguy

I’m here to help

vegemite

Registered User.

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread