Macro Substitution

[MtiPaulo]

I know FoxPro has excellent macro substitution but I am trying to find
through MS Access. Wondering if MS Access has Macro Substitution? I will
give you my basic example.

In FoxPro Language Code

For i = 1 to 5
strCmd = "Client" + Alltrim(Str(i)) + "=" + Alltrim(Str(i))
&strCmd && Client1 = 1
Next

that make to store the data into variables like Client1, Client2, Client3 and
so on

In MS Access Language Code

For i = 1 to 5
strCmd = "Client" & Alltrim(Str(i)) & "=" & Alltrim(Str(i))
Eval(strCmd) -----> Error
Next

Any idea how to store the data into the variables? Thank you

[Jeremy Wallace]

I would use an array for this. Check into the VBA help files for
arrays.

Jeremy
--
Jeremy Wallace
Fund for the City of New York
metrix.fcny.org

[Gord]

>Wondering if MS Access has Macro Substitution?

Look here:

http://groups.google.ca/group/comp.d...70a4d31ef5ef45

[Allen Browne]

As others explained, VBA works quite differently than Fox/dBase.

If you are working with variables, an array makes sense. If you are actually
working with fields or controls on a form, you can refer to the Fields or
Controls collection using a string.

For example, if you had text boxes named Client1, Client2, ... Client5
(sounds very unnormalized, but lets use it as an example), you can code:

Dim strName as String
Dim i As Integer
For i = 1 to 5
strName = "Client" & i
Debug.Print Me.Controls(strName )
Next

With a recordset, you can use:
rs.Fields(strName)

Since Controls is the default collection for a form, and Fields is the
default collection for a recordset, you can abreviate that to:
Me(strName )
rs(strName)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MtiPaulo" <u21659@uwe> wrote in message news:5ff596582af69@uwe...

I know FoxPro has excellent macro substitution but I am trying to find
through MS Access. Wondering if MS Access has Macro Substitution? I
will
give you my basic example.

In FoxPro Language Code

For i = 1 to 5
strCmd = "Client" + Alltrim(Str(i)) + "=" + Alltrim(Str(i))
&strCmd && Client1 = 1
Next

that make to store the data into variables like Client1, Client2, Client3
and
so on

In MS Access Language Code

For i = 1 to 5
strCmd = "Client" & Alltrim(Str(i)) & "=" & Alltrim(Str(i))
Eval(strCmd) -----> Error
Next

Any idea how to store the data into the variables? Thank you

[MtiPaulo via AccessMonster.com]

This code is kinda of "hardcode" but I want "flexible" code

intArgCount = 1
Do While True
strClientNo = Trim(GetSubString(Me.OpenArgs, intArgCount, ";")) -----
Return "472"
If strClientNo <> "" Then
Select Case intArgCount
Case 1
Me.lblClient1.Caption = strClientNo
Case 2
Me.lblClient2.Caption = strClientNo
Case 3
Me.lblClient3.Caption = strClientNo
Case 4
Me.lblClient4.Caption = strClientNo
Case 5
Me.lblClient5.Caption = strClientNo
End Select
End If
intArgCount = intArgCount + 1
Loop

I just want to have "flexible" code like this:

intArgCount = 1
Do While True
strClientNo = Trim(GetSubString(Me.OpenArgs, intArgCount, ";")) ---
Return "472"
If strClientNo <> "" Then
GetArgString = "lblClient" & Trim(Str(intArgCount )) & ".Caption"
Me.Controls(GetArgString) = strClientNo ----- Error
End If
intArgCount = intArgCount + 1
Loop

I hope it helps!

Allen Browne wrote:

As others explained, VBA works quite differently than Fox/dBase.

If you are working with variables, an array makes sense. If you are actually
working with fields or controls on a form, you can refer to the Fields or
Controls collection using a string.

For example, if you had text boxes named Client1, Client2, ... Client5
(sounds very unnormalized, but lets use it as an example), you can code:

Dim strName as String
Dim i As Integer
For i = 1 to 5
strName = "Client" & i
Debug.Print Me.Controls(strName )
Next

With a recordset, you can use:
rs.Fields(strName)

Since Controls is the default collection for a form, and Fields is the
default collection for a recordset, you can abreviate that to:
Me(strName )
rs(strName)

I know FoxPro has excellent macro substitution but I am trying to find
through MS Access. Wondering if MS Access has Macro Substitution? I

[quoted text clipped - 20 lines]

Any idea how to store the data into the variables? Thank you

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200605/1

[Allen Browne]

For i = 1 to 5
Me("lblClient" & i).Caption = strClientNo
Next

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MtiPaulo via AccessMonster.com" <u21659@uwe> wrote in message
news:5ffef69569731@uwe...

This code is kinda of "hardcode" but I want "flexible" code

intArgCount = 1
Do While True
strClientNo = Trim(GetSubString(Me.OpenArgs, intArgCount,
;")) -----
Return "472"
If strClientNo <> "" Then
Select Case intArgCount
Case 1
Me.lblClient1.Caption = strClientNo
Case 2
Me.lblClient2.Caption = strClientNo
Case 3
Me.lblClient3.Caption = strClientNo
Case 4
Me.lblClient4.Caption = strClientNo
Case 5
Me.lblClient5.Caption = strClientNo
End Select
End If
intArgCount = intArgCount + 1
Loop

I just want to have "flexible" code like this:

intArgCount = 1
Do While True
strClientNo = Trim(GetSubString(Me.OpenArgs, intArgCount,
;")) ---
Return "472"
If strClientNo <> "" Then
GetArgString = "lblClient" & Trim(Str(intArgCount )) &
".Caption"
Me.Controls(GetArgString) = strClientNo ----- Error
End If
intArgCount = intArgCount + 1
Loop

I hope it helps!

Allen Browne wrote:

As others explained, VBA works quite differently than Fox/dBase.

If you are working with variables, an array makes sense. If you are
actually
working with fields or controls on a form, you can refer to the Fields or
Controls collection using a string.

For example, if you had text boxes named Client1, Client2, ... Client5
(sounds very unnormalized, but lets use it as an example), you can code:

Dim strName as String
Dim i As Integer
For i = 1 to 5
strName = "Client" & i
Debug.Print Me.Controls(strName )
Next

With a recordset, you can use:
rs.Fields(strName)

Since Controls is the default collection for a form, and Fields is the
default collection for a recordset, you can abreviate that to:
Me(strName )
rs(strName)

I know FoxPro has excellent macro substitution but I am trying to find
through MS Access. Wondering if MS Access has Macro Substitution? I

[quoted text clipped - 20 lines]

Any idea how to store the data into the variables? Thank you

[MtiPaulo via AccessMonster.com]

Thanks. It works. Wonder why it won't work like this....

strCmd = "lblClient" & i
Me(strCmd).Caption = strClientNo

Allen Browne wrote:

For i = 1 to 5
Me("lblClient" & i).Caption = strClientNo
Next

This code is kinda of "hardcode" but I want "flexible" code

[quoted text clipped - 67 lines]

Any idea how to store the data into the variables? Thank you

--
Message posted via http://www.accessmonster.com

[Albert D. Kallal]

"MtiPaulo via AccessMonster.com" <u21659@uwe> wrote in message
news:5fffc67db8585@uwe...

Thanks. It works. Wonder why it won't work like this....

strCmd = "lblClient" & i
Me(strCmd).Caption = strClientNo

That should work. You should get into the habit of using

Option Explicit
in ALL OF your modeules. While in the code editor, go tools-> options and
check

[x] require variable definidation

The above setting will NOT chagne exsitng code..but in the future, any time
you create a new form, or new code module, the compiler directive
"Option Explicit" will be placed in the code for you....

That way, you are foreced to declear viarles..and
compiler will catch many errors beofre you even run the code...

Does your code complile now? Perhaps you have antoher object, or control
called
strCmd?

If it is a short loop, you could post the code....

You might want to note that since everything in ms-access is a collection,
then you can use a string to reference forms, fields etc, and thus will
RARELY ever need (or miss) the old FoxPro concept of substitution.

I wrote FoxPro applications for a number of
years, and when I switched over to ms-access, I did NOT miss macro subs
since virtually everything in ms-access is a collection, and thus you can
generally use a string to reference those things that otherwise would need
macro
subs in Fox.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal