Need to pull group/sort Report value into VBA 1

[Quintios]

I have a report in which I want to stuff a subreport, however, the subreport is based on a query that is dependent on the value used for grouping the main report.

How can I get this value?

The main report consists of several subereports; unfortunately, one of the subreports is quite complicated, so I'm going to have to create the information for it via VBA code, creating a temporary table and then basing a crosstab report off of that. Anyway...

When the main report runs, it's grouping is based off of one value only, called 'Sequence'. How do I capture the current value of 'Sequence' and then use it in VBA in the report?

Many thanks... I'm going to crosspost this in the Report section as well.

thx!

Q-

 

[jrbarnett]

Quentin

You can set a control on the subreport equal to the value of the parent control:

=Parent!Form.txtSequence

and it will be in this control txtSequence.

John

 

[Quintios]

It's been a while since I've programmed in Access. To test this by having a dialog box pop up while the report is formatting, what would the code be?

Thanks!

thx!

Q-

 

[Quintios]

I tried this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    MsgBox Me.Sequence, vbOKOnly

End Sub

But the box kept coming up over and over and over and over... I just need it to show up once per group.

thx!

Q-

 

[jrbarnett]

In the Detail_Format event, put

MsgBox "test"

to check that it works, then adapt to your needs or get rid of it - as it will drive you batty after a few minutes.

John

 

[Quintios]

heh, indeed it does.

I'm one step closer to solving my subreport problem.

Thanks!

thx!

Q-

 

[jrbarnett]

Quintios

You shouldn't need to use VBA in the report in your case: just create the subreport as you want, then add it as a subreportto the main report and join them on the Sequence field. VBA in report code slows running them down significantly.

You can set it to hidden on the reports if you don't want it to show up.

John

 

[Quintios]

Well, the problem is the query that feeds the subreport needs the 'Sequence' value hard-coded into the query or it won't work. It's a crosstab query, and the number of columns is dependant on the value of 'Sequence', so each time the report runs I'll have a different number of columns.

NOW the problem is that I'm going to have to create that subreport with VBA code if I want the columns to be evenly spaced.

Example, the report runs and the first 'Sequence' is 10. That Sequence results in 3 columns for the crosstab query. The next time through the report (it basically goes from top to bottom for each Sequence) the Sequence value is 18 and there need to be FIVE columns for that particular crosstab query.

I might have to split this out into a new and separate report as much as I don't want to.

Do you know of any resources that will help in explaining how to COMPLETELY create a report with VBA only? I mean the whole thing, adding text fields, grouping options, the whole nine yards...

thx!

Q-