Logic statement to select report

[AKQTS]

To simplify my reports, I am planning on making three reports that will all differ to print data from the same form. This is due to some reports needing features that others will not. When the user clicks print on the form, I only want one of the reports to print though.

The report would be selected based on the current record set pulled up in the form. I.E. if that record set contains a number 5 then print report 1, a number 7 then print report 2, or a number 9 then print report 3.

I have the form and report printing fine right now when I reference the report directly in the macro and switch it out for each report.

I tried setting up a Macro with Open, Print, Close commands for each report with conditional statements defining which form to select. It seemed it only looks at the first record in the data set it is printing and makes the call then moved to the next command which dosent work.



Any help to make this work would be great, thanks

[jzwp11]

The report would be selected based on the current record set pulled up in the form. I.E. if that record set contains a number 5 then print report 1, a number 7 then print report 2, or a number 9 then print report 3.

I assume that the number is in a control on the form which is bound to a field in the underlying table/query. How do you reference this control in your macro? How is this value used in the recordset for the report?

I am not very familiar with macros (I use VBA code), so can you post the macro(s) you are using?

[AKQTS]

All info is bound to a field in a underlying table/ query. Here is a graphical representation of what I am trying to accomplish:

Above is my form, when the operator enters a disposition code of "5-MRB" for any of the records currently displayed on the form, I want Version B of the Report to print and when none of the records displayed on the form have a disposition code of "5-MRB", I want Version A of the Report to print.

Report shown above.

Here is the macro I am using:


With the commands above, when the first record on the form is something besides "5-MRB", it prints Version A of the report. When the first record on the form is "5-MRB", it prints Version B of the report. This is correct, to a point, it should look at any of the records being or not being "5-MRB".

When any records besides the first on the list are "5-MRB", the Macro does not pick it up and prints Version A. This is Incorrect.

I would be fine going with VBA or anything with some sound code.

Thanks

[jzwp11]

On what event is the macro designed to execute? How are the individual records related to each other? Is this a subform on a mainform? I am thinking that the macro will have to execute from a button on the main form once all the edits are done to the list of records. I think you are only getting the 1 report because Access sees only the first record, I think you need to loop through the records to see if the particular item was chosen and then execute the code based on what is found.

[AKQTS]

On what event is the macro designed to execute?

Button on the form

How are the individual records related to each other?

All Parts are under one order, basically the list the operator is seeing is a bill of materials.

Is this a subform on a mainform?

Main Continuous Form, only one form in program.

I think you need to loop through the records to see if the particular item was chosen and then execute the code based on what is found

Excatly, I am not very good with VBA or Macros for that matter. What macros could I use or VBA code? I thought I could convert my current macro to VBA, but I cannot find that operation.

[jzwp11]

In order for this to work we need the order number (or equivalent) to which these detail records are associated. Additionally, if the button is on a continous form, you can only use the information from the record for which the button was clicked. Also, what would happen if the user clicks the button before selecting the disposition for all records?

If I were doing this, I would have the form as a subform with the main form based on the order table (or your equivalent). On the main form, I would just have the order number and the button. The code would then loop through the detail records to make sure that a disposition was selected for each and then do the evaluation as to what was selected & what report needs to be printed.

[AKQTS]

I converted it to a form/ subform. How do i code it to loop through using a Macro or VBA?

[jzwp11]

It will be difficult to create actual working code without knowing your table structure and the datatypes of the fields involved, but I'll give it a shot. This code would go in the on click event of the button on the main form. It assumes you have a control that has the order number.


First, test to see if a disposition was selected for each detail record, if not return a message and exit the code. If so, check for the presence of the 5-MRB code and run the reportA otherwise run reportB

IF DCount("*", "Recordsourceofdetailrecords", "orderIDfield=" & me.orderIDcontrolname & " and nz(disposition,"")="")>0 THEN
msgbox "Disposition not completed for all detail records"
exit sub
ELSE
IF DCOUNT("*","RecordsourceofDetailRecords","orderIDf ield=" & me.orderIDcontrolname & " and disposition="5-MRB")>0 THEN
DoCmd.OpenReport "ReportA", acViewPreview, , "orderIDfield=" & Me.orderIDcontrolname
ELSE
DoCmd.OpenReport "ReportB", acViewPreview, , "orderIDfield=" & Me.orderIDcontrolname
END IF
END IF