using combo boxes as criteria in query (1 Viewer)

[aleemaher]

I have two combo boxes in different forms
Combo0 has names of the officers
Combo001 has names of the companies.
I want to use both combo boxes as criteria for the query, the problem i face is that i have to select options in both combo boxes to run the query successfully, i want the query to run with either combo box being empty.

SELECT [Officers Offsite 1].[Officer Name], [PSC EXAM].[Company Name], [PSC EXAM].[Document Examined], [PSC EXAM].[If other Document Selected], [PSC EXAM].[Type of Examination], [PSC EXAM].[Action if others], [PSC EXAM].[Financial Period], [PSC EXAM].[Company Type], [PSC EXAM].Action, [PSC EXAM].Status, [PSC EXAM].[Action Date], [PSC EXAM].Remarks, [PSC EXAM].[Date of Entry]
FROM [PSC EXAM] INNER JOIN [Officers Offsite 1] ON [PSC EXAM].[Officer] = [Officers Offsite 1].[Officer Name]
WHERE ((([Officers Offsite 1].[Officer Name])=[Forms]![Officerwise]![Combo0]) AND (([PSC EXAM].[Company Name])=[Forms]![Form1]![Combo001])) OR ((([PSC EXAM].[Company Name])=[Forms]![Form1]![Combo001]) AND (([Forms]![Officerwise]![Combo0]) Is Null)) OR ((([Officers Offsite 1].[Officer Name])=[Forms]![Officerwise]![Combo0]) AND (([Forms]![Form1]![Combo001]) Is Null)) OR ((([Forms]![Officerwise]![Combo0]) Is Null) AND (([Forms]![Form1]![Combo001]) Is Null));

 

[Ranman256]

then can you use a filter?
Since a query cant use a NULL control in the where, use a continuous form that shows all records,
then user can pick 1 or both combo values,
then click a button to run this code in the buttons CLICK event:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"        'text
if not IsNUll(txtID) then sWhere = sWhere & " and [ID]=" & txtID                 'numeric
if not IsNUll(txtDate) then sWhere = sWhere & " and [EventDate]=#" & txtDate & "#"      'dates

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

 

[Uncle Gizmo]

I have two combo boxes in different forms

Do you mean two stand-alone forms which are popped up on your screen or do you mean two sub forms which are embedded into one main form?

 

[aleemaher]

then can you use a filter?
Since a query cant use a NULL control in the where, use a continuous form that shows all records,
then user can pick 1 or both combo values,
then click a button to run this code in the buttons CLICK event:

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string

sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"        'text
if not IsNUll(txtID) then sWhere = sWhere & " and [ID]=" & txtID                 'numeric
if not IsNUll(txtDate) then sWhere = sWhere & " and [EventDate]=#" & txtDate & "#"      'dates

If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

Both Combo boxes are in different forms. Form 1 is used to search by officer wise and it has the first combo box, the form 2 is used to search company wise and has second combo box.

 

[aleemaher]

Do you mean two stand-alone forms which are popped up on your screen or do you mean two sub forms which are embedded into one main form?

two stand alone popped up forms

 

[Uncle Gizmo]

What runs the query? Is it a command button, is it a form that you open and then the query is behind that form?

 

[aleemaher]

What runs the query? Is it a command button, is it a form that you open and then the query is behind that form?

Button in both forms runs the same query

 

[arnelgp]

SELECT [Officers Offsite 1].[Officer Name], [PSC EXAM].[Company Name], [PSC EXAM].[Document Examined], [PSC EXAM].[If other Document Selected], [PSC EXAM].[Type of Examination], [PSC EXAM].[Action if others], [PSC EXAM].[Financial Period], [PSC EXAM].[Company Type], [PSC EXAM].Action, [PSC EXAM].Status, [PSC EXAM].[Action Date], [PSC EXAM].Remarks, [PSC EXAM].[Date of Entry]
FROM [PSC EXAM] INNER JOIN [Officers Offsite 1] ON [PSC EXAM].[Officer] = [Officers Offsite 1].[Officer Name]
WHERE
[Officers Offsite 1].[Officer Name]=IIF(IsNull([Forms]![Officerwise]![Combo0]), [Officers Offsite 1].[Officer Name],[Forms]![Officerwise]![Combo0])
AND
[PSC EXAM].[Company Name]=IIF(IsNull([Forms]![Form1]![Combo001]), [PSC EXAM].[Company Name], [Forms]![Form1]![Combo001]);

 

[aleemaher]

SELECT [Officers Offsite 1].[Officer Name], [PSC EXAM].[Company Name], [PSC EXAM].[Document Examined], [PSC EXAM].[If other Document Selected], [PSC EXAM].[Type of Examination], [PSC EXAM].[Action if others], [PSC EXAM].[Financial Period], [PSC EXAM].[Company Type], [PSC EXAM].Action, [PSC EXAM].Status, [PSC EXAM].[Action Date], [PSC EXAM].Remarks, [PSC EXAM].[Date of Entry]
FROM [PSC EXAM] INNER JOIN [Officers Offsite 1] ON [PSC EXAM].[Officer] = [Officers Offsite 1].[Officer Name]
WHERE
[Officers Offsite 1].[Officer Name]=IIF(IsNull([Forms]![Officerwise]![Combo0]), [Officers Offsite 1].[Officer Name],[Forms]![Officerwise]![Combo0])
AND
[PSC EXAM].[Company Name]=IIF(IsNull([Forms]![Form1]![Combo001]), [PSC EXAM].[Company Name], [Forms]![Form1]![Combo001]);

I tried implementing this code but combo001 does not return any results

 

[arnelgp]

test with query using Left Join (replace INNER JOIN with LEFT JOIN in your query).

 

[aleemaher]

test with query using Left Join (replace INNER JOIN with LEFT JOIN in your query).

The code is working fine however when i use the first form, a prompt pop up opens "Enter Parameter Value" and it asks for the combo box value from the second form and vice versa

 

[Uncle Gizmo]

From what you said I gather you have two forms, each using the same query. One form provides one parameter for the query, and the other form provides another parameter for the query?

 

[Gasman]

The code is working fine however when i use the first form, a prompt pop up opens "Enter Parameter Value" and it asks for the combo box value from the second form and vice versa

So open both the forms if you are referring to them?
I tended to use Tempvars set from forms or wherever to avoid such problems? However I never considered having a criteria from more than one form? Has to be something wrong with your logic there, surely?

If you do it this way, you can set the whatever the non open form tempvar is, to something that works?
You can only refer to form controls WHEN THE FORM IS OPEN.

 

[aleemaher]

From what you said I gather you have two forms, each using the same query. One form provides one parameter for the query, and the other form provides another parameter for the query?

yes exactly

 

[Uncle Gizmo]

Has to be something wrong with your logic there, surely?

I agree

 

[Uncle Gizmo]

It sounds like you have created two forms, forms which are basically the same structure, but have different uses.

Most people make this mistake, creating one form for displaying records and then make a copy of it, then change its structure so that it can be used for adding data, or some similar scenario.

This mistake leads you to exactly the position you find yourself in.

The solution is to stick to one form and use VBA code to change it to suit the different uses you put it to.

I suggest you choose one of the forms, the one with the most functionality built-in. Add to this form both combo boxes "companies" and "officers" and and any other controls that are absent.

You should find your companies and offices query much easier to create on this dual purpose form.

 

[Pat Hartman]

I'm not sure what you are trying to save by complicating a query in an attempt to reuse it. It is NOT possible to reference a form that is not open. Will both forms always be open? Doesn't sound like it. I agree with Uncle, there is some logic error in your design.