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
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

Open in new window

Karen SchaeferBI ANALYSTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

plummetCommented:
Before I write some code, are the fields ReqEMO1, 2, 3 & 4 in the current recordset rs?

0
Karen SchaeferBI ANALYSTAuthor Commented:
no, wasn't planning on using a recordset - just checking the variable if is null or not.

K
0
als315Commented:
You have good idea with:
gEMO(i)
Why you can't use array?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Karen SchaeferBI ANALYSTAuthor Commented:
that is what I am attempting right now without suggest - could you please supply me with example.
K
0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
als315Commented:
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) = ...
0
Karen SchaeferBI ANALYSTAuthor Commented:
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)
0
Karen SchaeferBI ANALYSTAuthor Commented:
and note that there that 1 or more can contain data.
0
plummetCommented:
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
   


0
als315Commented:
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.
0
plummetCommented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
Having brain freeze - for the life of me not sure of the syntax needed for the array.  Please get me example.

K
0
als315Commented:
There is no big difference in syntax. Use gEMO(N) where now is qEMON.
0
plummetCommented:
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!

0
Karen SchaeferBI ANALYSTAuthor Commented:
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
0
Karen SchaeferBI ANALYSTAuthor Commented:
I believe I want integer - since the variable names are gEMO1, 2, etc.
0
Karen SchaeferBI ANALYSTAuthor Commented:
or are you talking about the value of those variables - they are currently set as strings.

k
0
plummetCommented:
I think this is all you need to do:

dim iEMO(4) as integer
dim i as integer

iEMO(1)= (some value or variable, or field)
iEMO(2)= (some value or variable, or field)
iEMO(3)= (some value or variable, or field)
iEMO(4)= (some value or variable, or field)

For i = 1 to 4
    if iEMO(i) > 0 then
      recValue = recValue & "ReqEmo:" & i & "  " & iEMO(i) & vbCrLf
    end if
Next 

Open in new window


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the great assist.

Karen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.