Macro to Run Query Based on ComboBox Selection

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form with several queries set to run by macros. The queries will
run based on the criteria chosen from 1 of the various drop down boxes that
are also located on the form. When I go to run the query I want it to check
the drop down boxes 1 at a time, and move on to the next drop down box if
there is nothing selected. Then when it runs I'd like it to use the name it
used to filter as part of the data provided.

My problem at this time is that when the macro goes to run the query it asks
for me to enter the parameter value. Can you tell what I'm doing wrong and
how to revise it by looking at the SQL I included below?

Thanks so much for your help!

SELECT [Forms]![Form_Main]![Report Group] Or [Forms]![Form_Main]![Report
Group2] Or [Forms]![Form_Main]![Group #] Or [Forms]![Form_Main]![Sub Group #]
Or [Forms]![Form_Main]![Sub Group Name] AS [Group],
dbo_EHP_OutpatientVisits.TosDescriptive AS TOS_Descriptive,
dbo_EHP_OutpatientVisits.IncurredMonth, Sum(Val([PaidAmt])) AS PaidAmount,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits
FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200307) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
(([Group])=[Forms]![Form_Main]![Report Group] Or
([Group])=[Forms]![Form_Main]![Report Group2] Or
([Group])=[Forms]![Form_Main]![Group #] Or ([Group])=[Forms]![Form_Main]![Sub
Group #] Or ([Group])=[Forms]![Form_Main]![Sub Group Name])) OR
((([Forms]![Form_Main]![ReportGroup])="ALL"))
GROUP BY dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth
HAVING (((Sum(Val([PaidAmt])))<>0));
 
You may have a parameter problem if you have a typo in the field name, or,
if you use FORMS!formName!ControlName syntax, if you use CurrentDb, rather
than DoCmd or the User Interface, because CurrentDb does NOT resolve the
syntax FORMS syntax, automatically, for you.

So, it can be the query, or the environment from which you run the query.

Hoping it may help,
Vanderghast, Access MVP
 
Mike,
Thanks for your reply. I'm not completely sure I understand what you're
saying, but here is macro code for 1 of my queries that runs from the form.
Does this help to explain my problem?

Private Sub Outpatient_Paid_and_Visits_Click()
On Error GoTo Err_cmd_Outpatient_Paid_and_Visits_Click

Dim stDocName As String

stDocName = "Trend OP Paid & Visits"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Outpatient_Paid_and_Visits_Click:
Exit Sub

Err_cmd_Outpatient_Paid_and_Visits_Click:
MsgBox Err.Description
Resume Exit_Outpatient_Paid_and_Visits_Click

End Sub


Michel Walsh said:
You may have a parameter problem if you have a typo in the field name, or,
if you use FORMS!formName!ControlName syntax, if you use CurrentDb, rather
than DoCmd or the User Interface, because CurrentDb does NOT resolve the
syntax FORMS syntax, automatically, for you.

So, it can be the query, or the environment from which you run the query.

Hoping it may help,
Vanderghast, Access MVP

shorticake said:
I have a form with several queries set to run by macros. The queries will
run based on the criteria chosen from 1 of the various drop down boxes
that
are also located on the form. When I go to run the query I want it to
check
the drop down boxes 1 at a time, and move on to the next drop down box if
there is nothing selected. Then when it runs I'd like it to use the name
it
used to filter as part of the data provided.

My problem at this time is that when the macro goes to run the query it
asks
for me to enter the parameter value. Can you tell what I'm doing wrong
and
how to revise it by looking at the SQL I included below?

Thanks so much for your help!

SELECT [Forms]![Form_Main]![Report Group] Or [Forms]![Form_Main]![Report
Group2] Or [Forms]![Form_Main]![Group #] Or [Forms]![Form_Main]![Sub Group
#]
Or [Forms]![Form_Main]![Sub Group Name] AS [Group],
dbo_EHP_OutpatientVisits.TosDescriptive AS TOS_Descriptive,
dbo_EHP_OutpatientVisits.IncurredMonth, Sum(Val([PaidAmt])) AS PaidAmount,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits
FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200307) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
(([Group])=[Forms]![Form_Main]![Report Group] Or
([Group])=[Forms]![Form_Main]![Report Group2] Or
([Group])=[Forms]![Form_Main]![Group #] Or
([Group])=[Forms]![Form_Main]![Sub
Group #] Or ([Group])=[Forms]![Form_Main]![Sub Group Name])) OR
((([Forms]![Form_Main]![ReportGroup])="ALL"))
GROUP BY dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth
HAVING (((Sum(Val([PaidAmt])))<>0));
Click to expand...
Click to expand...
 
Ok, so your are using DoCmd to get the query, so the syntax
FORMS!formName!controlName should not be of a problem, as long as the
mentioned form is open, and the control, filled with some value.


When Access prompts you for a parameter, does it supply a name for that
parameter? If so, and if it is like

Forms![Form_Main]![Sub Group #]

then, maybe the form name Form_Main is not open (or it is the form Form
Main, no _ between Form and Main, or other weird similar but different name
that is open)? or is it that [Sub Group #] is a control on this form, and
a control that returns a value (not a list box, or a sub form, as example).



Vanderghast, Access MVP


shorticake said:
Mike,
Thanks for your reply. I'm not completely sure I understand what you're
saying, but here is macro code for 1 of my queries that runs from the
form.
Does this help to explain my problem?

Private Sub Outpatient_Paid_and_Visits_Click()
On Error GoTo Err_cmd_Outpatient_Paid_and_Visits_Click

Dim stDocName As String

stDocName = "Trend OP Paid & Visits"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Outpatient_Paid_and_Visits_Click:
Exit Sub

Err_cmd_Outpatient_Paid_and_Visits_Click:
MsgBox Err.Description
Resume Exit_Outpatient_Paid_and_Visits_Click

End Sub


Michel Walsh said:
You may have a parameter problem if you have a typo in the field name,
or,
if you use FORMS!formName!ControlName syntax, if you use CurrentDb,
rather
than DoCmd or the User Interface, because CurrentDb does NOT resolve the
syntax FORMS syntax, automatically, for you.

So, it can be the query, or the environment from which you run the query.

Hoping it may help,
Vanderghast, Access MVP

shorticake said:
I have a form with several queries set to run by macros. The queries
will
run based on the criteria chosen from 1 of the various drop down boxes
that
are also located on the form. When I go to run the query I want it to
check
the drop down boxes 1 at a time, and move on to the next drop down box
if
there is nothing selected. Then when it runs I'd like it to use the
name
it
used to filter as part of the data provided.

My problem at this time is that when the macro goes to run the query it
asks
for me to enter the parameter value. Can you tell what I'm doing wrong
and
how to revise it by looking at the SQL I included below?

Thanks so much for your help!

SELECT [Forms]![Form_Main]![Report Group] Or
[Forms]![Form_Main]![Report
Group2] Or [Forms]![Form_Main]![Group #] Or [Forms]![Form_Main]![Sub
Group
#]
Or [Forms]![Form_Main]![Sub Group Name] AS [Group],
dbo_EHP_OutpatientVisits.TosDescriptive AS TOS_Descriptive,
dbo_EHP_OutpatientVisits.IncurredMonth, Sum(Val([PaidAmt])) AS
PaidAmount,
Sum(Sgn(dbo_EHP_OutpatientVisits!PaidAmt)) AS OPVisits
FROM (Group_Information INNER JOIN Sub_Group_Information ON
Group_Information.GroupID = Sub_Group_Information.GroupID) INNER JOIN
dbo_EHP_OutpatientVisits ON (Sub_Group_Information.SubGroupID =
dbo_EHP_OutpatientVisits.Subgroup) AND (Sub_Group_Information.GroupID =
dbo_EHP_OutpatientVisits.GroupNbr)
WHERE (((dbo_EHP_OutpatientVisits.IncurredMonth)>=200307) AND
((dbo_EHP_OutpatientVisits.PaidMonth)>=200307) AND
(([Group])=[Forms]![Form_Main]![Report Group] Or
([Group])=[Forms]![Form_Main]![Report Group2] Or
([Group])=[Forms]![Form_Main]![Group #] Or
([Group])=[Forms]![Form_Main]![Sub
Group #] Or ([Group])=[Forms]![Form_Main]![Sub Group Name])) OR
((([Forms]![Form_Main]![ReportGroup])="ALL"))
GROUP BY dbo_EHP_OutpatientVisits.TosDescriptive,
dbo_EHP_OutpatientVisits.IncurredMonth
HAVING (((Sum(Val([PaidAmt])))<>0));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab - if no data, then no heading 3
A query based on another queries 1
A query based on another query 2
Query works, query based on it fails. 2
Access Determine what query to run based on a checkbox 0
Jet error running crosstab query based on select query 1
Basing a query on the selection in a Combo box parameter 2
Prompt to Select Query to Run 3

Back
Top