Drop down combo box to open Report...tried tutorial no luck 1

[abradford]

I have this query: qryBSMRDb

SELECT *
FROM qryBSMRDGSa
WHERE (((qryBSMRDGSa.Response)=3) AND ((qryBSMRDGSa.RD)=[Enter RD]));


Which calls another query: qryBSMRDa

SELECT RD, BSMNum, BSMName,'BSMMedSup' AS Instance, BSMMedSupCom AS InsatnceB, BSMMedSup AS Response FROM [BSMRDGS]
UNION ALL SELECT RD, BSMNum, BSMName, 'BSMMMA', BSMMMACOM, BSMMMA FROM [BSMRDGS]
UNION ALL SELECT RD, BSMNum, BSMName, 'BSMPDP', BSMPDPCOM, BSMPDP FROM [BSMRDGS]
...
...

I am trying to make the "[Enter RD]" parameter from the first qry mentioned above, a drop down selection combo box parameter.

I have tried the following link, by creating a separate table with the criteria selections:

http://www.fontstuff.com/access/acctut08.htm

but I am having trouble when it comes to section 2.2 - 2.3. I think because when I use the Build function the criteria, I am calling it from a Query, not a table, like their "staff" table??? Is there another way to accomplish the dropdown?

Here is the new qryBSMRDb which does not work:

SELECT *
FROM qryBSMRDGSa
WHERE (((qryBSMRDGSa.Response)=3) AND ((qryBSMRDGSa.RD)=[Forms]![frmRD]![ComboRD]));


Thanks

 

[dhookom]

which does not work" isn't very descriptive. Is the form open when you run the query? The query will not open the form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Remou]

Please say in what way my responses to your post:
Drop Down Combo Box on Report - tried using form..
thread702-1296514
Failed to resolve the problem.

 

[abradford]

Sorry for the double post, I am new. I was able to get the query to open using the drop down criteria form, now, the question and final destination of mine, is how do I have the Dropdown criteria open a report based on the query?

 

[dhookom]

Add a command button on the form with the drop down. The command button wizard can walk you through creating a button to open a report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[abradford]

ok, am I missing somthing here? I have an event procedure in the after update in the dropdown box on my form...the only thing on the form. It displays the results in Datasheet format. I would like those results to be made into a report, as soon as someone picks certain criteria from the dropdown.

adding the button and using the wizard bypasses my dropdown criteria. sorry if it's right in front of my face...just can't get around it.

 

[dhookom]

First, I wouldn't subject my users to running a report in the after update event of a control. I much prefer to do this with a command button after selecting the criteria from the combo box.

What is the current code in After Update of your combo box? What is the name of your report? What is the name of your query? Is your query the Record Source of your report? What is the SQL view of your report's record source query?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[abradford]

The event procedure in the after update runs a query: qryBSMRDGSb (long name I know) the code for that is at the top. However the code is when I had a parameter in the query simply asking the user to maually enter in the RD name.

I am trying this form/dropdown thing so here is my code in the Event Procedure from my combo box.

Private Sub ComboRD_AfterUpdate()
DoCmd.OpenQuery "qryBSMRDGSb", acViewNormal, acEdit
DoCmd.Close acForm, "frmRD"
End Sub

My Report name is BSM Needs Assesment (bad name i know again)

I have 2 queries, see top, one to normalize my data, and the second to generate everyone who annswered 3 to a certian question:
WHERE (((qryBSMRDGSa.Response)=3)

the record source for my report is:

SELECT qryBSMRDGSa.RD, *
FROM qryBSMRDGSa
WHERE (((qryBSMRDGSa.RD)=[Forms]![frmRD]![ComboRD]) AND ((qryBSMRDGSa.Response)=3));

 

[dhookom]

Change your code to

Private Sub ComboRD_AfterUpdate()
    DoCmd.OpenReport "BSM Needs Assesment", acPreview
    Me.Visible = False
End Sub

The record source of your report should be

SELECT qryBSMRDGSa.*
FROM qryBSMRDGSa
WHERE (((qryBSMRDGSa.RD)=[Forms]![frmRD]![ComboRD]) AND ((qryBSMRDGSa.Response)=3));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[abradford]

Cheers dhookom. works!

 

[phanbui]

If your report based on the query then you may want to try this

Private Sub ComboRD_AfterUpdate()
if isnull(ComboRD) = False then
DoCmd.OpenReport "Your Report Name", acViewPreview
endif
End Sub

or the alternative is to create a command button for the PRINT event, without creating it through the wizard. From the wizard dialog window you should select "cancel".

For example you create a command button called CmdPrint then:

Private Sub CmdPrint_OnClick()
if isnull(ComboRD) = False then
DoCmd.OpenReport "Your Report Name", acViewPreview
else
msgbox "Select something... something from the " & _
"pick list to print/view the report", vbExclamation, ""
Exit Sub
endif
End Sub

NOTE: If the form (frmRD) blocks the report screen, you, then, need to make it invisible. Open your report module and from the Report on Load make the form frmRD invisible (Form_frmRD.visible = False). When the user closes the report, you will need to program Report Form_Unload to make the frmRD visible again (Form_frmRD.visible = True).

Hope this help. Good luck!

 

[abradford]

dhookom-

I took your advise and code and it worked great. Now I am trying to do the same thing for another report/dropdown but am getting this syntax error when I select an item in the new dropdown form:

Syntax Error (missing operator)in query or expression ((BSMRDGS APC.Question 5: Territory Vice President)=[Forms]![frmTVP]![ComboTVP]);

which is in my report record source. I have tried taking out parens, added them...Is it something else? only have one parameter for this one, TVP, unlike two in the first example, RD and if they responded with a 3...

thanks for the help.

 

[dhookom]

[red]BSMRDGS APC.Question 5: Territory Vice President[/red] . Flush all the spaces out of your object names. Your early examples of table, query, and field names didn't seem to have space and other junk in object names. If you use non-standard characters in an object name, you must enclose the name in []s.

[BSMRDGS APC].[Question 5: Territory Vice President]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[abradford]

ya the tables and data are made up of an excel import. So the names are wacked, I will change them to make this easier. Thanks it worked.