Run Report if?

[Sharon Hague]

Hi All

I have a form called FrmPrintReports. This has an option group which lists all my reports. I then have a command button called PrintPreview which runs the report depending on which option button is highlighted.

I have created another form called FrmEmployeeID. This comprises of just one field which is a combo box ran from a query which list all our current employees.

When I have selected which report I want to print and then pressed my command button (Print Preview) I want to be able to then select an Employee from my form FrmEmployeeID and for it then to run the report based on which employee ID I choose. Obviously this will not be the case for all my reports only certain ones.

Is this possible?

 

[dcx693]

Yes, very possible. Why not have both options on a single form?

If not, on the FrmPrintReports form, have two command buttons, one to run the report directly, one to open the FrmEmployeeID form to let you choose the employee. (Or you can just have one button to open the employee form and on the employee form, either let the user choose one, multiple, or all employees.) The code in button 1 would run the report directly. The code in button 2 could either hide the report form, or pass the report name using the OpenArgs property of the DoCmd.OpenForm method. That way, if you've hidden the report form, you can now reference the choice from, or you have access to the report name in the OpenArgs property.

 

[Sharon Hague]

dcx693

Thanks V much for your reply. I think the buttons on the same screen which you suggested would be the best idea.

However, I am not too familiar with the coding. I have used macro's for what I have done so far. Is it not possible to add this in the macro?

If not where do I put the coding? I have attached a rough copy of my db if it helps.

I'd appreciate any help on this

Cheers

 

[dcx693]

No prob. You can look in the Access on line help for some basic information regarding coding. VBA coding is far more flexible than macros, so I never really tried using macros. It might be possible, but if you have the time, take the opportunity to do the coding in VBA. Besides, you cannot implement an If....Then....Else type of condition in a macro, and you'll need to.

Since you have both controls on the same form now...

First, I'd set up code in the After Update event of the report combo box so that it will enable the PrintPreview command button only if a choice was made.

If Not IsNull(Me.cboReports) Then
     Me.cmdPrintPreview.Enabled=True
End If

Now you'll need to have some code in your PrintPreview command button. The code will need to check the employee combo box to see if you've made a choice. What if no choice is made? The simplest thing to do is to assume that if no choice is made that you want to include all the choices. It will go something like this code:

If Not IsNull(Me.cboEmployeeID) Then
     DoCmd.OpenReport "reportname",acViewPreview,,"[ID]=" & Me.cboEmployeeID
Else
     DoCmd.OpenReport "reportname",acViewPreview
End If

 

[Sharon Hague]

dcx693

I have been really struggling attaching the file but here goes again

 

[dcx693]

Ah. I see that your report chooser is an option group (which you wrote above), but I mistakenly thought was a combo box. You can ignore my code about checking for a report value chosen.

First, you need to put the employee ID chooser combo box onto the same form that has the report option group. (Just copy and paste it from one form to the other.)

Next, your option group value will either be 1 or 2, depending on which option you choose. So you'll need some code in the AfterUpdate event of the Command8 button like this:

Dim strReportName as String
If Me.PrintOptions=1 Then
     strReportName="RptAddressDetails"
Else
     strReportName="RptTelNumbers"
End If

If Not IsNull(Me.Combo0) Then
     DoCmd.OpenReport strReportName,acViewPreview,,"[EmployeeID]=" & Me.Combo0
Else
     DoCmd.OpenReport strReportName,acViewPreview
End If

 

[Sharon Hague]

dcx693

Thanks for your reply. I have looked at the above and I don't have an AfterUpdate option in my Command8 button's properties.

Do you mean to put this coding in the Option Group Properties?




I have now tried to enter the coding in the AfterUpdate part in my option group properties and I am struggling.

I have also tried this coding in the on click part of my command8 button and still struggle.

Any ideas

 

[dcx693]

Oh crap, as you suspected, the correct place for that code is in the On Click event of the command button that was called Command8 in your database. You wrote that you tried that and are still having problems What happens when you click the Command8 button?

 

[Sharon Hague]

dcx693

Result! at long last - I knew that there must have been something either not typed correct or something missing in the coding.

And there was - I had End if missed and also a bracket missing.

It now works a treat

Many thanks for your help.

 

[dcx693]

Cool. Glad to have helped.