Passing Variable from VBA to Report Trouble

[claycalgary]

Hello

I am having difficulty here. I am trying to write code that will run a daily report for 170 producers, referenced by there number

The macro should be running the report 170 times. Here is what I have.

Option Explicit
Sub Run_Daily_Variance_Reports()

Dim I As Integer

Dim Producer As Variant

Producer = Array("100:270")

For I = LBound(Producer) To UBound(Producer)

DoCmd.OpenReport "Variance_by_Producer_Detail", acViewPreview, "",
"[Reports].[Variance_by_Producer_Detail].[Producer_#]='" & Producer(I)&"'"

Next I

End Sub


When I run the code however, the producer_# will not pass through the the report and no data will appear in the report. The report is generated from a query.

Thanks

Clay

 

[BrettM]

Don't try to force the data into the report.
Set a public variable (or a hidden textbox) and get the report to read that instead.

 

[boblarson]

Don't try to force the data into the report.
Set a public variable (or a hidden textbox) and get the report to read that instead.

Actually, what the OP is doing is normally fine. However, I question what the "Array" is returning as it looks like it isn't set up properly.

I don't think an array is necessary, given the circumstances and it could be done like this:

Option Explicit
Sub Run_Daily_Variance_Reports()

Dim I As Integer

For I = 100 To 270

DoCmd.OpenReport "Variance_by_Producer_Detail", acViewPreview, , 
"[Producer_#]=" & I

Next I

End Sub

Now, a couple of things that were wrong with the initial call -

1. You don't put "" in the one argument that you aren't using. Just leave it blank.

2. You don't use more than the FIELD name (no Reports!...blah, blah, blah).

3. You don't encapsulate NUMBERS in quotes.

 

[BrettM]

You don't encapsulate NUMBERS in quotes.

Now that I look closer into his statement I see I missed this little gem.

I agree about the use of the array though if it is desired then the line would be...

DoCmd.OpenReport "Variance_by_Producer_Detail", acViewPreview, , "[Producer_#] = " & Producer(I)

 

[boblarson]

Now that I look closer into his statement I see I missed this little gem.

I agree about the use of the array though if it is desired then the line would be...

DoCmd.OpenReport "Variance_by_Producer_Detail", acViewPreview, , "[Producer_#] = " & Producer(I)

Yeah, if using a well formatted array. But as it stands just declaring the array isn't going to have numbers there (at least my understanding of an array).

So, in reality something like using a record count would more likely be applicable.

 

[claycalgary]

Worked perfectly thanks.

This is my first time ever with access vba. I gets a little bit frustrating