Variable as part of field name

[LanWanMan]

Hi All!

I need to put a command into a for next loop and I do not know the correct
syntax to make my for next counter part of the field name. Example:

Dim count as integer
For count = 1 to 8
Forms!frmclaims!EmpInvID1 = EmpInvID
Next count

I need the "1" in EmpInvID1 to be replaced by the count
variable.....EmpInvID[count].

Please reply to the group.

Thanks,

Michael
LanWanMan

 

[Allen Browne]

Dim strName As String
Dim i As Integer
For i = 1 to 8
strName = EmpInvID & i
Me(strName) = 99
Next

That's equivalent to:
Me.Controls(strName) = 99
which substitutes the variable for the literal in this kind of construct:
Me.Controls("EmpInvID1") = 99

 

[LanWanMan]

Thanks Allen,

I will give this a try. But I have a question. What is the purpose of the
strName? Also, I have never understood the "Me". When you use "Me" does
that refer to the current form?

Thanks,

Michael

Dim strName As String
Dim i As Integer
For i = 1 to 8
strName = EmpInvID & i
Me(strName) = 99
Next

That's equivalent to:
Me.Controls(strName) = 99
which substitutes the variable for the literal in this kind of construct:
Me.Controls("EmpInvID1") = 99

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi All!

I need to put a command into a for next loop and I do not know the
correct syntax to make my for next counter part of the field name.
Example:

Dim count as integer
For count = 1 to 8
Forms!frmclaims!EmpInvID1 = EmpInvID
Next count

I need the "1" in EmpInvID1 to be replaced by the count
variable.....EmpInvID[count].

Please reply to the group.

Thanks,

Michael
LanWanMan

 

[LanWanMan]

Allen,

THis is what I used. I will be testing shortly. Let me know, if you could,
if this looks right.

Thanks

Dim strName As String
Dim count As Integer
For count = 1 To 8
If empnum = count Then strName = EmpInvID & count
Else
End If
Next count
Forms!frmclaims!strName = EmpInvID

Michael

Dim strName As String
Dim i As Integer
For i = 1 to 8
strName = EmpInvID & i
Me(strName) = 99
Next

That's equivalent to:
Me.Controls(strName) = 99
which substitutes the variable for the literal in this kind of construct:
Me.Controls("EmpInvID1") = 99

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi All!

I need to put a command into a for next loop and I do not know the
correct syntax to make my for next counter part of the field name.
Example:

Dim count as integer
For count = 1 to 8
Forms!frmclaims!EmpInvID1 = EmpInvID
Next count

I need the "1" in EmpInvID1 to be replaced by the count
variable.....EmpInvID[count].

Please reply to the group.

Thanks,

Michael
LanWanMan

 

[Allen Browne]

It's a bit hard to tell if your code is right, since we don't know what
empnum is. I'm not sure it's right though: since you are comparing it to
count, it seems to hold a number? If so, each time through the loop you are
concatenating another digit onto the number. Did you mean:
strName = "EmpInvID" & count

Count is a property of a form (also a reserved word in JET), so not a good
name for a variable.

The last line would be:
Forms!frmclaims(strName) = EmpInvID

I understand what the loop is for, since the assignment of the variable to
EmpInvIDXX occurs only after the loop is complete.

Re the questions in your parallel reply, Me is a shortcut to refer to the
form that this code is in. While it is optional, using it in your code helps
narrow the possibilitite of that you are referring to (when something else
could have the same name.) As a bonus, when you type the dot after Me,
Access autocompletes the names for you, so you don't misspell things. And
Me.xxx also causes the compiler to complain if you get it wrong, where the
unqualified (without the Me) reference or use of the bang does not.

The strName is a string variable to hole the name of the control. You could
have got away with coding:
Me("EmpInvID" & count)
instead of
Me(strName)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

THis is what I used. I will be testing shortly. Let me know, if you
could, if this looks right.

Thanks

Dim strName As String
Dim count As Integer
For count = 1 To 8
If empnum = count Then strName = EmpInvID & count
Else
End If
Next count
Forms!frmclaims!strName = EmpInvID

Michael

Dim strName As String
Dim i As Integer
For i = 1 to 8
strName = EmpInvID & i
Me(strName) = 99
Next

That's equivalent to:
Me.Controls(strName) = 99
which substitutes the variable for the literal in this kind of construct:
Me.Controls("EmpInvID1") = 99

Hi All!

I need to put a command into a for next loop and I do not know the
correct syntax to make my for next counter part of the field name.
Example:

Dim count as integer
For count = 1 to 8
Forms!frmclaims!EmpInvID1 = EmpInvID
Next count

I need the "1" in c1 to be replaced by the count
variable.....EmpInvID[count].

Please reply to the group.

Thanks,

Michael
LanWanMan

 

[LanWanMan]

Allen,

Thank you for your guidance. With your assistance, I was able to write code
that works. It might be dirty but here's what works for the "on close":

Dim strName As String
Dim MyCount As Integer
Dim MyHolder As Integer
For MyCount = 1 To 8
If empnum = MyCount Then strName = "EmpInvID" & MyCount Else
MyHolder = MyCount
Next MyCount
Forms!frmclaims!(strName) = EmpInvID

I defined empnum as a public variable prior to this code and when the user
selects the correct control in "frmclaims", it sets the empnum value, and
opens the second form. That way the loop knows which control they used.
The "MyHolder" is just used to give the "else" something to do as I was
unable to figure out how to tell the "else" to do nothing. This is my way
of passing the ID field from the second table back into the first table.

I am sure there are more efficient ways of doing this, but I am learning VB
on the fly. I will change the name of "count" variable in my DB.

Thanks again for your help.

Michael
LanWanMan

It's a bit hard to tell if your code is right, since we don't know what
empnum is. I'm not sure it's right though: since you are comparing it to
count, it seems to hold a number? If so, each time through the loop you
are concatenating another digit onto the number. Did you mean:
strName = "EmpInvID" & count

Count is a property of a form (also a reserved word in JET), so not a good
name for a variable.

The last line would be:
Forms!frmclaims(strName) = EmpInvID

I understand what the loop is for, since the assignment of the variable to
EmpInvIDXX occurs only after the loop is complete.

Re the questions in your parallel reply, Me is a shortcut to refer to the
form that this code is in. While it is optional, using it in your code
helps narrow the possibilitite of that you are referring to (when
something else could have the same name.) As a bonus, when you type the
dot after Me, Access autocompletes the names for you, so you don't
misspell things. And Me.xxx also causes the compiler to complain if you
get it wrong, where the unqualified (without the Me) reference or use of
the bang does not.

The strName is a string variable to hole the name of the control. You
could have got away with coding:
Me("EmpInvID" & count)
instead of
Me(strName)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

THis is what I used. I will be testing shortly. Let me know, if you
could, if this looks right.

Thanks

Dim strName As String
Dim count As Integer
For count = 1 To 8
If empnum = count Then strName = EmpInvID & count
Else
End If
Next count
Forms!frmclaims!strName = EmpInvID

Michael

Dim strName As String
Dim i As Integer
For i = 1 to 8
strName = EmpInvID & i
Me(strName) = 99
Next

That's equivalent to:
Me.Controls(strName) = 99
which substitutes the variable for the literal in this kind of
construct:
Me.Controls("EmpInvID1") = 99

Hi All!

I need to put a command into a for next loop and I do not know the
correct syntax to make my for next counter part of the field name.
Example:

Dim count as integer
For count = 1 to 8
Forms!frmclaims!EmpInvID1 = EmpInvID
Next count

I need the "1" in c1 to be replaced by the count
variable.....EmpInvID[count].

Please reply to the group.

Thanks,

Michael
LanWanMan