Returning Data to Populate a Report

[Phil_b]

Ive been pondering on this problem for far to long now so decided it was time to ask here !

I would like to return (based on a query) some records that would populate a report. I can set the source of the report as the query but my issue is that I need to actually have some code prior to report population.

EG if they have two products I want to list them product1/ product2 and as far as I know this isnt possible when you just set the source for the report...

The data is coming from SQL server so I have the option of a stored procedure but from trsting that seems to make things even more confusing.

Any help appreciated as Ive been stuck on this for far too long !!

Phil

 

[dionem]

Hi,
Don't know if this will help but:
Can you have a form with an option group
And then base the query on the form for example in the query have something like (in the SQL):
ORDER BY IIf(Forms!frm_08_reportsort!opt_sort_by=1,field1,field2),
what the above will do is sort by field one if you have selected option 1, if you have not selected option 1 (ie you have selected option 2) it will sort by field2.

This is for sorting but by using IF, THEN maybe you can get this to select specific data..?

So basically what you would have would be a button "run report" you would click this and then another form would open with your options "report selections" for instance, you would make your selections then click a button on this form to "run report" which would then open the report, and the query would run & be referring to your "report selections" form.

Hope this helps and i am not sending you completely in the wrong direction - please be warned i only dabble in access!!

 

[carash77ash]

here is how i did what you are after.

First create an unbound form with the optionbox.

Put a command button on main form to open unbound form

create a command button on unbound form that has this code

Sub printreports(printmode As Integer)
On Error GoTo Err_Preview_Click

const option1 = 1
const option2 = 2
const option3 = 3

selectcase optionbox

case 1
if me!optionbox = 1 then
DoCmd.OpenReport "reportname", printmode, "queryname"
else
goto err_preview_click
end if

case2
if me!optionbox = 2 then
DoCmd.OpenReport "pcr", printmode, "pc query"
else
goto err_preview_click
end if

case3
if me!optionbox = 3 then
DoCmd.OpenReport "pcr", printmode, "pc query"
else
goto err_preview_click
end if

err_preview_click:
goto exit_preview_click

exit_preview_click:
exit sub

end sub

Ash