option groups as criteria for a report

[cfljanet]

I was hoping someone could give me some pointers:

I have several option groups which determine which fields from a query will be used for output on a report. The task relates to the final weights and numbers of packages being loaded into shipping containers and determines the basis of calculations for those weights. For example, the query contains fields for: number of packs scanned, number of packs surveyed, net kgs scanned, net kgs surveyed etc. The option groups are to allow the user to determine whether we will report on the scanned results or the surveyed results. In addition to two options for packs and net kgs there are 4 options for determining the basis of the gross kgs. The reports will be used for declaring final outcomes to the Authorities.


I set up the option groups on a report and set the fields in the detail section for packs, net kgs and gross kgs to determine which field in the query would be used depending on the value selected in the option group. This is a simple IF statement. It all works beautifully.
However....whilst I can select the options in report view, the report view is hopeless for determining how the final report will look as it has no page breaks etc. It looks fine in Print Preview but then you can't select the options in the option groups. (Tearing hair out time!).
So, it would seem more sensible to set the option groups on a form rather than on the report itself. But I am not sure how to relate the two. I can't use Query by Form because the option groups determines a field not a group of records. Does anyone have any suggestion what I could do?

[Dal Jeanis]

Overall, there are a dozen different things you can do, depending on how many options you actually have. You can use any combination of these that makes sense for your particular application:

First, you can have different reports, and the option groups on the form could determine which report was called.

Second, you can pass any constant values that you want to the report via a TempVars variable, and use IIF to select which resulting fields go o the report. (Or global variables with public routines to set and read them would create the same effect.)

IIF([TempVars]![MyOpt1]="scan",[MyScanNumber],[MySurveyNumber])

Third, you could use VBA code behind the controls on the form to build the SQL query that will populate the report.

[cfljanet]

Thanks for the reply Dal.
First option is unnecessary as I will only ever need one combination each time I run the report and this seems to be a bit of a clunky option.
Option 3 looks like a possibility, although being a bit of a dunce when it comes to Access it might take me some time to work it out.
Option 2 is intriguing - essentially because I really haven't a clue what you're talking about... Where would I set that code? I shall have to read up about TempVars I think!

[Dal Jeanis]

cfljanet- Don't discount the first option completely - keep it in mind as a possibility while you're building the report architecture. Sometimes four simple reports can be a lot less clunky than one report with four different sets of header verbage, sort requirements and so on.

[cfljanet]

cfljanet- Don't discount the first option completely - keep it in mind as a possibility while you're building the report architecture. Sometimes four simple reports can be a lot less clunky than one report with four different sets of header verbage, sort requirements and so on.

Hi Dal: actually I have been thinking about the options and I had more or less come to the conclusion that Option 1 is the best one: if nothing else because I would know how to do that! I am frustrated that having spent so long getting it to work before - and in most respects it worked perfectly - with only one report but really I just need it to work - that's the main thing! Thanks a ton for your help

[Dal Jeanis]

You're welcome. You still may find TempVars useful in passing information to the report, for instance if only a few heading items are different between reports X and Y.

Here's a link to a thread where I posted demo modules for Tempvars. https://www.accessforums.net/code-re...ars-36353.html

[cfljanet]

You're welcome. You still may find TempVars useful in passing information to the report, for instance if only a few heading items are different between reports X and Y.

Here's a link to a thread where I posted demo modules for Tempvars. https://www.accessforums.net/code-re...ars-36353.html

Okay, I am looking at TempVars again as the option of having a report for each set of conditions is, as I first thought, ridiculous given that there would potentially be about 12 different scenarios. I confess my knowledge of VBA would not fill a teaspoon so am relying on macros. I read up about tempvars and trying to put everything I learnt into making it work. I would be really grateful if you could tell me where I am going wrong. This is what I have done (using the option group relating to Net Kgs as an example):

Option Group (Frame 1) = Scanned Net Kgs (Value 1)
Survey Net Kgs (Value 2)

SetVarmcro: SetTempVar: Name: NetKgs Expression: [Forms]![TEST]![Frame1]

(I understood from what I read that this will set the value of the TempVar to the selection made in the TEST Form. Am I wrong?)

On the Form TEST - where the Option Groups are placed - I added an OK button to load the report. In the Net Weight control I put in the following expression:

=IIf(([TempVars]![NetKgs]=1),[SumOfNet Wt1],[SumOfNet Kgs])

(where SumOfNet Wt1 = Scanned Net Kgs and SumOfNet Kgs = Survey Net Kgs

The report doesn't flip out at me - which is reassuring (!) - but it returns the SumOfNet Wt1 (i.e Scanned Net Kgs) regardless of what option I choose. This is repeated across all 4 controls relating to the 4 Option Groups.

Clearly I don't know what I am doing, but was wondering if you would give me an idea how to do this right. I haven't been able to find a similar use for TempVars on the internet which could help guide me.

Thank you!!

[Dal Jeanis]

Okay, so where are you actually setting the tempvars?

In the afterupdate event for the options group frame that is around the option buttons, you should have code that looks vaguely like the following:

   TempVars.Add "NetKgs",MyFrame.Value

[cfljanet]

Hooray! yes, I was missing a fairly obvious key aspect of the process. I tried adding the expression above to the AfterUpdate Event and it wouldn't work so eventually - after much faddling - I put it in as code. Very poor code as I've put it in literally just as you have put it (with the right names in place) and it appears to work! But don't hold your breath - shall rigorously test first!

[cfljanet]

It works, it works, it works!!!



just please let me know if I should have something slightly more sophisticated in my code as more basic you couldn't have! Thank you so much Dal

[Dal Jeanis]

Yes, I meant you should put that code in the VBA code for that event. Good hunting!