Karen Schaefer
asked on
For I statement - to loop thru code
I need to modify some code to include in the comments field the date from 4 different fields. Each field is name similarly - differing by the number - ie ReqEMO1, ReqEMO2, ReqEMO3, & ReqEMO4, with global variables to match.
NOTE: on after update of each of the fields in question a global variable is set - ie. gEMO1, gEMO2, etc.
I need to first determine if any of these fields contains data and if so include in in the comments field with the following text included:
ReqEMO & field number (ie. 1) & the value of the field., continue if other four fields contain data.
So I thought that a For loop would work in this case. Looping thru the number of the field to return the field name and data.
Need help with loop statement and to include that in the comments field.
Example results:
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
Req_EMO3: = 12234 (value of gEMO3)
Req_EMO3: = 12234 (value of gEMO4)
I only want those fields that contain data and hide the others from the comments field.
if only the first 2 contain data then the comments should read:
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
thanks,
Karen
NOTE: on after update of each of the fields in question a global variable is set - ie. gEMO1, gEMO2, etc.
I need to first determine if any of these fields contains data and if so include in in the comments field with the following text included:
ReqEMO & field number (ie. 1) & the value of the field., continue if other four fields contain data.
So I thought that a For loop would work in this case. Looping thru the number of the field to return the field name and data.
Need help with loop statement and to include that in the comments field.
Example results:
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
Req_EMO3: = 12234 (value of gEMO3)
Req_EMO3: = 12234 (value of gEMO4)
I only want those fields that contain data and hide the others from the comments field.
if only the first 2 contain data then the comments should read:
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
thanks,
Karen
With rs
If Not (.BOF And .EOF) Then
.MoveFirst
Do Until rs.EOF
If recValue <> "" Then recValue = recValue & RSLF
recValue = recValue & SPad(.Fields("Equipment_ID"), MaxWidth(1))
recValue = recValue & SPad(.Fields("MeasNo"), MaxWidth(2))
recValue = recValue & SPad(.Fields("WSNo"), MaxWidth(3))
.MoveNext
Loop
End If
End With
End If
recValue = sFieldName & RSLF & recValue & RSLF & "WORK CODE: " & gWC
If gEMO1 <> "" Then
i = Right(gEMO1, 1)
ElseIf gEMO2 <> "" Then
i = Right(gEMO2, 1)
ElseIf gEMO3 <> "" Then
i = Right(gEMO3, 1)
ElseIf gEMO4 <> "" Then
i = Right(gEMO4, 1)
End If
For i = 0 To 4
recValue = recValue & RSLF & "ReqEmo:" & i & " " & gEMO(i)
Next i
Loop
Before I write some code, are the fields ReqEMO1, 2, 3 & 4 in the current recordset rs?
ASKER
no, wasn't planning on using a recordset - just checking the variable if is null or not.
K
K
You have good idea with:
gEMO(i)
Why you can't use array?
gEMO(i)
Why you can't use array?
ASKER
that is what I am attempting right now without suggest - could you please supply me with example.
K
K
ASKER
ok this needs to be and/or situation:
If gEMO1 <> "" Then
i = 1
ElseIf gEMO2 <> "" Then
i = 2
ElseIf gEMO3 <> "" Then
i = 3
ElseIf gEMO4 <> "" Then
i = 4
End If
For i = 0 To 4
recValue = recValue & RSLF & "ReqEmo:" & i & " " & gemo(i)
Next i
Loop
as is will not return multiple results. Please Help
If gEMO1 <> "" Then
i = 1
ElseIf gEMO2 <> "" Then
i = 2
ElseIf gEMO3 <> "" Then
i = 3
ElseIf gEMO4 <> "" Then
i = 4
End If
For i = 0 To 4
recValue = recValue & RSLF & "ReqEmo:" & i & " " & gemo(i)
Next i
Loop
as is will not return multiple results. Please Help
How are you assigning values for this variables?
"NOTE: on after update of each of the fields in question a global variable is set - ie. gEMO1, gEMO2, etc."
Why not to assign qEMO(1) = ...
"NOTE: on after update of each of the fields in question a global variable is set - ie. gEMO1, gEMO2, etc."
Why not to assign qEMO(1) = ...
ASKER
There are four fields each sets its own variable on afterupate of the individual fields
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
Req_EMO3: = 12234 (value of gEMO3)
Req_EMO3: = 12234 (value of gEMO4)
Req_EMO1: = 12234 (value of gEMO1)
Req_EMO2: = 12234 (value of gEMO2)
Req_EMO3: = 12234 (value of gEMO3)
Req_EMO3: = 12234 (value of gEMO4)
ASKER
and note that there that 1 or more can contain data.
If you can set up an array:
dim gEMO(4)
and set the contents to be whatever you want:
gEMO(1)=1234
gEMO(2)=12345
gEMO(3)=33333
gEMO(4)=4444
(etc)
then you can loop through the values like this:
For i = 1 to 4
if gEMO(i) > 0 then
recValue = recValue & "ReqEmo:" & i & " " & gEMO(i) & vbCrLf
end if
Next
dim gEMO(4)
and set the contents to be whatever you want:
gEMO(1)=1234
gEMO(2)=12345
gEMO(3)=33333
gEMO(4)=4444
(etc)
then you can loop through the values like this:
For i = 1 to 4
if gEMO(i) > 0 then
recValue = recValue & "ReqEmo:" & i & " " & gEMO(i) & vbCrLf
end if
Next
You can declare gEMO as array and assign it to different members of array in every afterupdate event.
for Req_EMO1 field:
gEMO(1) = me.Req_EMO1
etc.
for Req_EMO1 field:
gEMO(1) = me.Req_EMO1
etc.
That loop should actually read:
For i = 1 to 4
if gEMO(i) > 0 then
recValue = recValue & "ReqEmo" & i & ": " & gEMO(i) & vbCrLf
end if
Next
For i = 1 to 4
if gEMO(i) > 0 then
recValue = recValue & "ReqEmo" & i & ": " & gEMO(i) & vbCrLf
end if
Next
ASKER
Having brain freeze - for the life of me not sure of the syntax needed for the array. Please get me example.
K
K
There is no big difference in syntax. Use gEMO(N) where now is qEMON.
What syntax do you want - it's all in the examples above. Except that you might should dim the arrayto be a particular type:
dim gEMO(4) as integer
or
dim gEMO(4) as long
etc.
It's a good idea to use a prefix to show what the type is, so:
dim iEMO(4) as integer
or
dim lEMO(4) as long
or
dim sEMO(4) as string
The rest can be as per my examples above, I think!
dim gEMO(4) as integer
or
dim gEMO(4) as long
etc.
It's a good idea to use a prefix to show what the type is, so:
dim iEMO(4) as integer
or
dim lEMO(4) as long
or
dim sEMO(4) as string
The rest can be as per my examples above, I think!
ASKER
ok I am now confused - where am I setting the value of "i"?
do I still need the first part of my code that sets the value of "i"?
If gEMO1 <> "" Then
i = 1
ElseIf gEMO2 <> "" Then
i = 2
ElseIf gEMO3 <> "" Then
i = 3
ElseIf gEMO4 <> "" Then
i = 4
End If
and if so how do I handle the 1 or more instances needed?
K
do I still need the first part of my code that sets the value of "i"?
If gEMO1 <> "" Then
i = 1
ElseIf gEMO2 <> "" Then
i = 2
ElseIf gEMO3 <> "" Then
i = 3
ElseIf gEMO4 <> "" Then
i = 4
End If
and if so how do I handle the 1 or more instances needed?
K
ASKER
I believe I want integer - since the variable names are gEMO1, 2, etc.
ASKER
or are you talking about the value of those variables - they are currently set as strings.
k
k
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the great assist.
Karen
Karen