I want to run query based on option group selection.

[JimJones]

Hi,

I have a report based on a query.
That query makes a calculation.
I have added 3 more queries.

I want to add an option group to my form.

Then, I'd like to be able to run my report based on that particular query.

I believe I would add Select Case to the Open Event in my report. But, then what ?

How do I code the Select Case, given my option group control name is Option2. Also, how do I code the query it's coming from ?

Thanks for your help,
Jim

 

[bjackson]

declare a global variable for the query name
Public MyQueryName as string
use the on click event for the button that opens your report

Select Case me.Option2.value
case 1
MyQueryName="Thenameofyourquery"
case 2
MyQueryName="Thenameofyourqueryno2"

case 3
MyQueryName="Thenameofyourqueryno3"
case else
end select

in the on open event of the report
me.report.recordsource=MyQueryName

 

[JimJones]

BJackson,

It would seem to work, except for one detail which I'm not sure would work, if not directly addressed:

How would the individual fields of my report layout become
populated with <u>any</u>, let alone the correct data from the desired query result ?

It seems in the example you gave me, the proper query would certainly be run, but there's a total field on the report.
That total field would be different if each of the different queries was to be run.

Can you help me further?

Thanks,
Jim

 

[bjackson]

I thought the query that your report is based on makes the calculation?

set common field names to use rather than the table field names
eg:in the query grid for each query the report will be based on
query 1
Mytotal:[price] where [price] is the table field name
Myitem:[ItemDescription] etc
Myaddress:[adress1] etc
query 2
Mytotal:[Amounts]
Myitem:[CarDescription]
Myaddress:[DeliveryAddress]

then in your report set the fields controlsource to
MyTotal
Myitem
Myaddress
etc etc

 

[JimJones]

Thank You, yes, that will do it for me.

I don't know what happened. My brain froze or something.

I wasn't even finished reading the first sentence of your
reply when I said "oh wait a minute . . . ".

I was stuck on the fact that that there are 5 or so different queries - not that I could use the same naming conventions between the queries.

Thanks,
Jim

 

[bjackson]

your welcome
just make sure the data types match in your querys and report

 

[JimJones]

BJackson,

I believe I followed your instructions, but likely I coded something wrong, so I've included my button's code for your review.
My Select routine is only returning the error message:
Microsoft Access Can't find the object 'ChargeEstimate'.


I think I understand that "ChargeEstimate" is a variable and does not actually exist as a query name.

Following is the exact code in my program:

Private Sub cmdEstimatedCustomerCost_Click()

On Error GoTo Err_cmdEstimatedCustomerCost_Click

Dim stDocName As String

stDocName = "Estimated Costs For Repair Work"


Select Case Me![Frame105].Value

Case 1
ChargeEstimate = "qryEstimatedCharge"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 2
ChargeEstimate = "qryEstimatedCharge625"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 3
ChargeEstimate = "qryEstimatedCharge6p5"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 4
ChargeEstimate = "qryEstimatedCharge675"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 5
ChargeEstimate = "qryEstimatedCharge7p"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 6
ChargeEstimate = "qryEstimatedCharge725"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 7
ChargeEstimate = "qryEstimatedCharge7p5"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 8
ChargeEstimate = "qryEstimatedCharge775"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case 9
ChargeEstimate = "qryEstimatedCharge8p"
DoCmd.OpenQuery "ChargeEstimate", , acViewNormal

Case Else
End Select

DoCmd.OpenReport stDocName, acPreview

Exit_cmdEstimatedCustomerCost_Click:
Exit Sub

Err_cmdEstimatedCustomerCost_Click:
MsgBox Err.Description
Resume Exit_cmdEstimatedCustomerCost_Click

End Sub

I have cut and past the entire code above to rule out
errors while making this reply.

I also declared a Global variable for ChargeEstimate and didn't know where to put it, so I put it in declarations, along with the Option Explicit and Option Compare Database statements. Was that correct to do ?

Please help,
Thanks,
Jim

 

[bjackson]

why are you opening the query as well as the report?

create a code module
you do this by selecting the modules tab in the db window
select new
option compare database
option explicit

public ChargeEstimate as string

close and save the module-call it Globals

-------------------------------------
------------------------------------

Select Case Me![Frame105].Value

Case 1
ChargeEstimate = "qryEstimatedCharge"


Case 2
ChargeEstimate = "qryEstimatedCharge625"


Case 3
ChargeEstimate = "qryEstimatedCharge6p5"


Case 4
ChargeEstimate = "qryEstimatedCharge675"


Case 5
ChargeEstimate = "qryEstimatedCharge7p"


Case 6
ChargeEstimate = "qryEstimatedCharge725"


Case 7
ChargeEstimate = "qryEstimatedCharge7p5"


Case 8
ChargeEstimate = "qryEstimatedCharge775"


Case 9
ChargeEstimate = "qryEstimatedCharge8p"


Case Else
exit sub
End Select

DoCmd.OpenReport stDocName, acPreview

Exit_cmdEstimatedCustomerCost_Click:
Exit Sub

Err_cmdEstimatedCustomerCost_Click:
MsgBox Err.Description
Resume Exit_cmdEstimatedCustomerCost_Click

End Sub

 

[JimJones]

BJackson,

I modified my code as you descrobed, but now I get a compile error.

I got the error after I removed the
Public ChargeEstimate as String statement from my declarations, since you said to create a new module called Globals, which I did, an put the statement in there.

Until I got the compile error, though, I was getting:
The Record source 'ChargeEstimate' specified on this form or report does not exist.

Please help,

Thanks,
JIm

 

[bjackson]

have a look at this demo,i have used your exact code except
for the report name and i get no errors

 

[bjackson]

Jim
One thing you may need to check, is to make sure you spell the
query names correctly in your code,otherwise you will get a doesnt exist error

 

[JimJones]

BJackson,

Almost, but not quite. I think I have a clue as to why it still is not working.

Your example (thank you very much by the way) works as I imagine it should.

On mine however, I have a slight difference.

On my report, I just want to return an estimate for service charge report for the current customer on the screen.
In your example, you return all the records in your report.

My query has a Forms![CusomerData] . . . to get the CustomerNumber value from the form. I'm not sure how to make that reflect into the variable in the Select Case.

What I am getting is the report, but with #Name? errors all over EXCEPT for where I explicity tell it to get the value from the form.

I'll wait for you to get back to me, once again.
Thank you for kind support,
Jim

 

[bjackson]

sorry mate you have lost me
your query is what sets the records to return,so to return records based on a control on a form,then you would put that criteria in you query,not in a select case

make use of the access help files

the #name error-this is straight from access help files

#Error? or #Name? appears in a control on a form or report.
#Error? or #Name? may appear in a control for a number of reasons. To correct the problem, do the following:

Make sure that the field specified in the control's ControlSource property hasn't been removed from the underlying table or other record source.


Check the spelling of the field name in the control's ControlSource property.

 

[JimJones]

BJackson,

My database seems to not like something which is not
very apparent.

I've decided to use the Select Case code, but remove the variable,
as I'll keep the 9 queries and the 9 reports and use the Group Option to Run the query and Preview the report in the same
Select statement. This may not be the best way, causing
some overhead, but it's for one, maybe 2 two users.

Thanks for all your kind help,

Most of the database's functions work. Would you like me to zip the database and post it in a pm,
or post it here?

Jim

 

[bjackson]

post it in the forum,as theres a lot smarter people out there than me.As they say two heads are better than one