Report selection Using an Option Group

[gtobey]

I would like to use an option group to run a query to create a report for a selected individual. I created the option group OK, but all its doing is generating a numeric value depending on the person selected. I need to get the persons name using this value and stuff it into a query to produce a report. I'm not sure how to proceed. Thanks.

 

[Garridon]

Try this FAQ:

http://www.tek-tips.com/faqs.cfm?spid=702&sfid=2028

 

[BakeMan]

Well with the information provided I would say you need to create a form with the option group on it and a button to run the report. Write a procedure for the button that takes the value of the option group and assigns the proper name to a variable.

Button_Click()

Dim strName

Select Case me.optiongroup1

Case 1

strName = "John Doe"

Case 2

strName = "Joe Bloe"

End Select

I would then use this name to create the SQL string I am looking for.

strSQL = "SELECT * FROM [Table1] WHERE [Name] = '" & strName & "';"

I would then open the report in design view, assign the SQL as the record source, save the report and open it in preview mode.

DoCmd.Echo False
DoCmd.OpenReport "Report1", acViewDesign
Reports!Report1.RecordSource = strSQL
DoCmd.close acReport, "Report1", acSaveYes
DoCmd.Echo True
DoCmd.OpenReport "Report1", acViewPreview

The only draw backs to this method are that you have to run the report from the form and it is a little slow.

I hope this points you in the right direction.

BAKEMAN

 

[gtobey]

Bakeman: I'm trying to get your solution to work, however, I'm getting run time error 2580 That Microsoft Access cannot find Table or Query... Looking at the record source propery in the report it appears that the part of the query to the right of the = is >>> '. <<<. I may have the syntax wrong. Is it single quote double quote or double quote single quote... Anyway, the part about not finding the query may be due to syntax. Thanks.