Creatiing a report based on Option groups, queries and parameters

[Canadiangal]

I have created a forum that allows the end user to pick which report they would like to run and also pick the following criteria: employee, class, date, etc. I want to have a button that will generate this report. The choice to pick the report is based of the option radio buttons (option group) with values such a 1 for report 1, 2 for report 2, etc. The choice for employee, class, date, etc are combo boxes. I know how to create a query and run a report from this, but creating a report based on the aspects chose from a form are confusing to me. Any help would be appreciated.

Thanks in advanced!

[June7]

Use code to select the correct report based on the option group value. I use only VBA, so something like:

Select Case OptionGroupControl
Case 1
strRpt = "report1 name"
Case 2
strRpt = report2 name"
etc.
End Select
DoCmd.OpenReport strRpt

[Canadiangal]

How do I get the parameters that the end user has select to choose on those values for the report? I am getting a parameter question being asked? Not sure why.

[June7]

Maybe this will help http://datapigtechnologies.com/flash...mtoreport.html

[Canadiangal]

Thank you June7. This is getting me a lot further. Just a quick question. How would I find out what query was associated with a report. I am revamping an Access95 database. Just trying to see exactly what their query was. Unfortunately this thing is huge and I can't seem to find the original query.

[June7]

Open report in DesignView, look at the RecordSource property. It can reference a table or query object or be an SQL statement.

[Canadiangal]

This is my VBA code, but the button doesn't do anything. Before I was getting errors, but I changed a field name and now I am not getting anything. I went back and changed the field name back but it still fails. I did watch your video which was excellent!!! I could repeat everything n the video, but I can't get my code to work. Do you see anything wrong here?


Private Sub Command5_Click()
Dim res As Recordset
Dim Chz As Integer
Chz=Forms!Edreports!Frame7
If Check63 Then
Chz=Chz*10
End If
Select Case Chz
Case 1 'Attendance Report
IfIsNull(Combo31) Then
DoCmd.OpenReport "EdRep4a", acPreview 'for Class
Else
DoCmd.OpenReport "EdRep4b", acPreview 'for Employee
End If
Case 2 'Non Attend Reminder List
DoCmd.OpenReport "EdRep6", acPreview
Case 3 'Non Attend Reminder List
DoCmd.OpenReport "EdRep5", acPreview
Case 4 'Annual Report
DoCmd.OpenReport "EdRep0", acPreview
Case 5 'Print Names
DoCmd.OpenReport "EdRep7", acPreview
Case 6 'Print Appraisal by date
DoCmd.OpenReport "EdRep6", acPreview
Case 10 'Class by Department-Break by Department
DoCmd.OpenReport "EdRep4", acPreview 'for Class
Case 20 'Non Attendance Reminder List- Break by department
DoCmd.OpenReport "EdRep4b", acPreview
End Select
End Sub

[June7]

No, can't see what is wrong.

Refer to link at bottom of my post for guidelines on debugging techniques.

[Canadiangal]

Finally, I think I have found my issue. On the original form; which I copied from the old database, there are four unbound text fields. When one of the combofields is selected (course number), these fields automatically fill in. How is this completed? The field names do not exist in the database, but they are pulling from the course number field. Example, the end user chooses course number 1426, then the unbound text fields fill in with date, time, instructor, and room number. This data does all exist in the class database, but I don't understand how it is pulling it in. It isn't a lookup fields either.

[June7]

You mean 4 unbound textboxes. Don't see what this has to do with the button code failing.

The 4 textboxes do not have expressions in the ControlSource?

Does the combobox have code in its AfterUpdate event?

What table has the date, time, instructor, room number data?

[Canadiangal]

The combobox does not have anything in its afterupdate event.
The button code is failing because these 4 unbound textboxes are conditions on the report.
The class table has the four related unbound fields.
So I take it I need some code written in the afterupdate event. If so, can you give me an example of what needs to be written here. I am assuming I need the original field = the field on the form. Being new to code, I am not sure how to write this.

[Canadiangal]

If I leave the afterupdate event blank and I do the control source to reflect the combo field, will that work the same?

[June7]

The event property must show [Event Procedure] for VBA code (or [Embedded Macro] for macro or call a function) otherwise the code won't execute.

Yes, can either set the values of textboxes with VBA or by expression in ControlSource.

[Canadiangal]

Thank you!

[Canadiangal]

I am really, and I mean really new to VBA. Right now I am taking an Access 95 database and putting it into a Access 2007 database. I have been managing to limp by up until this latest form. To simplify things I have kept all forms, queries, reports, fields, etc by the same name as they were in the old system. However, some of his queries do not show up in the Queries area. The queries are not hidden.
The following is VBA code and I am wondering what the Query they are calling here is actually called?

If Frame30.Value=1 And Not IsNull(Forms!EdEnroll!Department.Value) Then
DoCmd.RunSQL "UPDATE wrkTable SET Wrktable.Chosen=True "&_
"Where WrkTable.Department=Forms!EdEnroll!Department.Valu e;"
Forms!EdEnroll.Repaint
End If

My question here is do I have to create an update query in access for this code to work? Or is it running the query through the code? If the latter is true, why wouldn't my code be working?

Is there an vba checker online, like there is a HTML5 code checker?

Thank you in advance for any help you can give.