access vba string concatenation with variable within a loop

[jydbman]

I wrote a vba subroutine to concatenate a string with the loop incremental variable to form a new variable. However, it seems like it translate it as a string rather than the variable. I tried to find the solution and found one similar issue one user had asked, but I can not find the answer. I will be very grateful if you can provide the solution. Thank you very much!

The following is the post.

Const Value1 as long = 5
Const Value2 as long = 12
Const Value3 as long = 7

For i = 1 to 3
MsgBox Value & i '<-- How to do this properly?
Next i

I want MsgBox to display 5 then 12 then 7 rather than Value1 then Value2 then Value3
I can't figgure out how to get Value and i to combine properly to do this.

 

[MStef]

Look at "DemoConcatenationA2000.mdb" (attachment, zip).
Open form1 and try. Look at VBA.

 

[plog]

You've stumbled onto arrays. Your methodology is sound, but your syntax is just a little off. Check out this link for more info:

http://patorjk.com/programming/tutorials/vbarrays.htm

What you want is this:

Dim Values(0 to 2) As Long

Values(0) = 5
Values(1) = 12
Values(2) = 7

For i = 0 to 2
MsgBox Values(i)
Next i

 

[jydbman]

Look at "DemoConcatenationA2000.mdb" (attachment, zip).
Open form1 and try. Look at VBA.

Thank you for spending time create this form. Appreciate your help.

My actual issue is more complicated than simply display the value1, value2, value3. I do need a loop and use the loop incremental variable to identify file names to do data processing. The issue is it recognize variable as string rather than the real file name behind the string variable. When I call other function which use the file name as a parameter, it error out due to file name not found. (It pass the string rather than file name)

Anyway, thanks for your effort and help!

 

[jydbman]

Thank you so much for sending me the link to learn array and appreciate your sample code. It is very helpful for me to learn. It seems to be very promising to my problem. I will try to use array to modify the code tomorrow and let you know how it works.

Thanks again. Very nice of you!

You've stumbled onto arrays. Your methodology is sound, but your syntax is just a little off. Check out this link for more info:

http://patorjk.com/programming/tutorials/vbarrays.htm

What you want is this:

Dim Values(0 to 2) As Long

Values(0) = 5
Values(1) = 12
Values(2) = 7

For i = 0 to 2
MsgBox Values(i)
Next i

 

[jydbman]

Thaks so much, plog!

I change all the variable declarations to the array and use the loop incremental variable as the array index. It works like a charm.

Thanks again!

You've stumbled onto arrays. Your methodology is sound, but your syntax is just a little off. Check out this link for more info:

http://patorjk.com/programming/tutorials/vbarrays.htm

What you want is this:

Dim Values(0 to 2) As Long

Values(0) = 5
Values(1) = 12
Values(2) = 7

For i = 0 to 2
MsgBox Values(i)
Next i

 

[sandyarela]

Hi,
I too have a question on concatenation.
Some code background info: In the form frmMainMenu, there is a text box that gets the computer name 'txtUserID'. I'm trying to create unique table names according to individual that runs the code. qryMaker is just a query.

Here's the code:

Dim tblName As String
Dim strSQL As String

tblName = "tblQueryMaker" & [Forms]![frmMainMenu]![txtUserID]
strSQL = "SELECT * INTO " & tblName & " FROM qryMaker;"

DoCmd.RunSQL (strSQL)

I've tried DoCmd.RunSQL and .execute, but both get the same error message of " must have at least one destination field" . I can't figure out why " FROM qryMaker;" does not get concatenated to strSQL.

Thanks!