?
Solved

For I statement - to loop thru code

Posted on 2011-10-14
19
Medium Priority
?
231 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:Karen Schaefer
  • 10
  • 5
  • 4
19 Comments
 
LVL 10

Expert Comment

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

0
 

Author Comment

by:Karen Schaefer
ID: 36970072
no, wasn't planning on using a recordset - just checking the variable if is null or not.

K
0
 
LVL 40

Expert Comment

by:als315
ID: 36970101
You have good idea with:
gEMO(i)
Why you can't use array?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:Karen Schaefer
ID: 36970107
that is what I am attempting right now without suggest - could you please supply me with example.
K
0
 

Author Comment

by:Karen Schaefer
ID: 36970121
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
 
LVL 40

Expert Comment

by:als315
ID: 36970132
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
 

Author Comment

by:Karen Schaefer
ID: 36970146
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
 

Author Comment

by:Karen Schaefer
ID: 36970153
and note that there that 1 or more can contain data.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36970172
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
 
LVL 40

Expert Comment

by:als315
ID: 36970177
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
 
LVL 10

Expert Comment

by:plummet
ID: 36970183
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
 

Author Comment

by:Karen Schaefer
ID: 36970197
Having brain freeze - for the life of me not sure of the syntax needed for the array.  Please get me example.

K
0
 
LVL 40

Expert Comment

by:als315
ID: 36970269
There is no big difference in syntax. Use gEMO(N) where now is qEMON.
0
 
LVL 10

Expert Comment

by:plummet
ID: 36970283
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
 

Author Comment

by:Karen Schaefer
ID: 36970293
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
 

Author Comment

by:Karen Schaefer
ID: 36970300
I believe I want integer - since the variable names are gEMO1, 2, etc.
0
 

Author Comment

by:Karen Schaefer
ID: 36970311
or are you talking about the value of those variables - they are currently set as strings.

k
0
 
LVL 10

Accepted Solution

by:
plummet earned 2000 total points
ID: 36970332
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
 

Author Closing Comment

by:Karen Schaefer
ID: 36970822
Thanks for the great assist.

Karen
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question