By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Macro Substitution

P: n/a
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
May 8 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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

May 8 '06 #2

P: n/a
>Wondering if MS Access has Macro Substitution?

Look here:

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

May 8 '06 #3

P: n/a
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

May 10 '06 #4

P: n/a
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
May 10 '06 #5

P: n/a
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

May 10 '06 #6

P: n/a
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
May 10 '06 #7

P: n/a
"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

May 10 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.