Dim within a loop

[craigachan]

I have a table of medical procedures that I want to merge with Word. I want to know if its possible to declare the variables within a loop. Each letter that will be merged can have 1 to 40 procedures. So basically I want to declare the variables depending on the particular patient letter to be merged, as needed. So I want to know if I can do something this. I've tried the following code but keep getting a compile error. I'm somewhat new to VB and I can't seem to find the rules for this code. Can someone tell me what's wrong with this?

Table Fields: DOS, Desc, Area
Form Fields: DOS, Desc, Area



Code

Dim Me.RC = DCount("DOS", "MyForm")

docmd.openform "MyForm"

For x = 1 to Me.RC
Docmd.openform "MyForm"

Dim varDOS & x as String
Dim varDesc & x as String
Dim varArea & x as String

varDOS & x = Forms!MyForm!DOS
varDesc & x = Forms!MyForm!Desc
varArea & x = Forms!MyForm!Area
docmd.gotorecord , , acnext

Next x

And then I'll merge the document in a similar way. Can I get advise on my compile problem? Thank you.

 

[boblarson]

And then I'll merge the document in a similar way. Can I get advise on my compile problem?

This is invalid:

Dim varDOS & x as String
Dim varDesc & x as String
Dim varArea & x as String


you can declare

Dim varDOS as String
Dim varDesc as String
Dim varArea as String

within your loop if you use the values before moving to the next x. Otherwise you need to use an Array.

 

[Banana]

Just a FYI:

*All* variables are initialized within the procedure, regardless on which line where they are dim'ed.

If you want to save resources by declaring them when you actually need it, move it into a separate function and call it from the procedure.

 

[ChrisO]

G’day Banana.

I’m not sure what you mean by *All*…what is the intension of the asterisk?
I ask this because Static variables are not reset to their initialization value on reentry and I believe that should be made clear.

Regards,
Chris.

 

[Banana]

Chris-

I now realize that it was a bit ambiguous. I meant all local variables within a procedures, rather all different scope of variables. You are correct that Static variables does not get reinitialized, but any local variables that are declared by Dim statement are initialized at the start of procedure, regardless of where the Dim statement appears within the procedure.

Astrerisk= emphasis. Some people use bold, but I'm old fashioned.

 

[ChrisO]

Yes, but emphasis placed is especially incorrect when the emphasized is placed on the wrong.
(It makes the wrong especially wrong…one of my pet peeves. )

Regards,
Chris.

 

[Banana]

Fair enough.

 

[CyberLynx]

In my opinion, the code is flawed in almost all areas including its intent.

Perhaps try using a Multi Dimensional Array variable (declared as Variant) to hold the desired data and fill this Array by way of a Recordset rather than flipping though each record of a Form and pulling the data.

As an example, let's assume the Table we want to pull data from is named Mailing List and what we want to pull out of this Table is the data from three specific Fields named FirstName, MiddleName, and LastName in each record witin the that Table. Here is code as to how you might want to attack this and fill and data Array:

[COLOR="DarkGreen"]'Declare required Variables......[/COLOR]
Dim myArray() As Variant [COLOR="DarkGreen"]'Array Variable which will hold our data[/COLOR]
Dim RST As Recordset     [COLOR="DarkGreen"]'Variable to hold our Recordset[/COLOR]
Dim Cnt As Long          [COLOR="DarkGreen"]'Counter used to increment the Array prior to procedural declaration.[/COLOR]
   
[COLOR="DarkGreen"]'Create the Rescordset.[/COLOR]
Set RST = CurrentDb.OpenRecordset("SELECT FirstName,MiddleName,LastName FROM [Mailing List];", dbReadOnly)
   
[COLOR="DarkGreen"]'Loop through the Recordset and fill the Array until
'there are no more records within the Recordset.....[/COLOR]
Do Until RST.EOF
   ReDim Preserve myArray(2, Cnt)    [COLOR="DarkGreen"]'Dimension the Array as needed and preserve any previous declarations.[/COLOR]
   myArray(0, Cnt) = RST!FirstName   [COLOR="DarkGreen"]'Fill the first array element with the First Name.[/COLOR]
   myArray(1, Cnt) = RST!MiddleName  [COLOR="DarkGreen"]'Fill the second array element with the Middle Name.[/COLOR]
   myArray(2, Cnt) = RST!LastName    [COLOR="DarkGreen"]'Fill the third array element with the Last Name.[/COLOR]
   Cnt = Cnt + 1                     [COLOR="DarkGreen"]'Increment the counter. This increment will be our next Array declaration.[/COLOR]
   RST.MoveNext                      [COLOR="DarkGreen"]'Move to next record within the Recordset.[/COLOR]
Loop
   
RST.Close                            [COLOR="DarkGreen"]'Close the Recordset[/COLOR]
Set RST = Nothing                    [COLOR="DarkGreen"]'Free memory[/COLOR]
[COLOR="DarkGreen"]'------------------------------------------------------------------------------------------------------------[/COLOR]
       
[COLOR="DarkGreen"]'Here s how you may utilize the data within the Array (myArray)
'==============================================================[/COLOR]
Dim x As Long
For x = LBound(myArray) To UBound(myArray)
   MsgBox myArray(0, x) & vbCr & myArray(1, x) & vbCr & myArray(2, x)
Next x

[COLOR="DarkGreen"]'Kill the Array to Free Memory[/COLOR]
Erase myArray

.

 

[craigachan]

Thank you CyberLynx. I'll study up on what you wrote and hopefully it will make my application work.