Is this even possible? Reports!strVar.Recordsource 1

[GooGoocluster]

Can you use Varribles when using Reports! ?

dim strVar As String

strVar = "Full Sheet Labels"

Reports!strVar.Recordsource

This is the Error message " Cannot Find Report named 'strVar' "

I have also tried

Reports!(strVar).Recordsource

and

Reports!"["&(strVar)&"]".Recordsource


Thanks in advance

 

[maxhugen]

Try:

Reports(strVar).Recordsource

Max Hugen
Australia

 

[MajP]

To clarify why this works. VB has two types of notation: bang and dot. The bang notation uses the "!" and is in the format:
CollectionName!IndexName
ex.
Reports![Full Sheet Labels]
Forms!frmLabels
Me.Controls!txtBoxLabel

The dot notation is of the form:
CollectionName("indexName")
ex
Reports("Full Sheet Labels")
Me.controls("txtBoxLabel")

One advantage of the dot notation is that you can use a variable for the index name as demonstrated by Max. You can not use a variable in the bang notation. There are many discussions about which one is better, but it is mainly preference.

 

[maxhugen]

MajP,
FWIW, MS advises that the bang notation (eg Reports![Full Sheet Labels]) is faster as it's a direct pointer to the object, whereas using Reports("[Full Sheet Labels]") makes Access enumerate all the objects in that collection until it finds the one you have named.

However, as others have pointed out, the speed difference may be neglible with today's faster processors and larger RAM.

Max Hugen
Australia

 

[MajP]

I do not buy that. Please show me a source that says that. Once the code is compiled there is absolutely no difference. According to Litwin and Getz (A2k Desktop developers Handbook) the bang notation is actually converted to the dot notation prior to compiling.

 

[maxhugen]

First one I found:

http://c85.cemi.rssi.ru/Access/Books/A97ExSol/index12.htm

Use the bang when the object name (form name, control name, etc.) is known in advance, as in Forms!frmCust, for two reasons: (a) it is the shortest structure, thus requiring the least keystrokes; and (b) Access VBA compiles the absolute relationship between the objects and executes the code faster than other syntax variations.

Max Hugen
Australia

 

[maxhugen]

Since Getz/Litwin was my bible too (Access 2.0), I looked a bit more on the web, and found this quote from their later A2k bible:

It turns out that, behind the scenes, the former style of [explicit] dot-and-bang reference is translated to the latter style of parentheses and quotes reference when you execute such a statement. This means that, although using the bang operator will save you a bit of typing, you'll pay for it in a speed penalty.

Looks like things changed in A2k. Still, if it all compiles the same way eventually, I guess the only speed penalty is when I compile the project... not at runtime?

Max Hugen
Australia