Results 1 to 14 of 14
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    For next

    ALL,


    I am using an imported pre-made excel spreadsheet. I have six columns named

    There are 2 spaces between SINCGARS and Number
    SINCGARS 1
    SINCGARS 2
    SINCGARS 3
    SINCGARS 4
    SINCGARS 5
    SINCGARS 6

    In VBA I referencing the table as a DAO.Recordset.

    Set rU = CurrentDb.OpenRecordset("NBOI")

    Thus in the code I have to use rU![SINCGARS 1] etc.

    Since all of them are the same Column Name I could use a for next and cut down the codes by 5/6ths using the for next

    For I = 1 to 6
    SINCNNum = "rU![SINCGARS " & I & "]"
    Next I

    If SINCNum Like "*Cmd" then

    Normally I'd put 6 times with just the number changing
    If rU![SINCGARS 1] Like "*Cmd" then

    How would I go about using the For Next code? The example I have above doesn't work.

    Thanks

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You can't use the DATA in place of the FIELD name.

    Thus in the code I have to use rU![SINCGARS 1] etc.
    This is DATA... you need the field name


    I would think you would have something like
    Code:
    Set rU = CurrentDb.OpenRecordset("NBOI")
    
    Do while not rU.EOF
    
        SINCNNum = rU!FieldName 
                    ' or rU.Fields("FieldName")
                    ' or rU.Fields(1)  '<-- this syntax is from memory - could be wrong
        
        If SINCNum Like "*Cmd" then
    
           msgbox SINCNum
    
        End if
    
      rU.MoveNext
    Loop

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks Steve,
    I understand the point on it being the fieldname.
    The SINCGARS 1..2...3...4..5..6 are the fieldnames. It looks like you set up for a single field name. I was looking for using the SINCNum to match all the fieldnames with the For Next function.

    Code:
    SINCNum1 = rU.Fields![SINCGARS  1]
    SINCNum2 = rU.Fields![SINCGARS  2]
    SINCNum3 = rU.Fields![SINCGARS  3]
    SINCNum4 = rU.Fields![SINCGARS  4]
    SINCNum5 = rU.Fields![SINCGARS  5]
    SINCNum6 = rU.Fields![SINCGARS  6]
    
    For I = 1 To 6
      SINCNum = SINCNum & I
        SINC = "SINC" & I
        If SINCNum = "METT" Then
    For M = 1 To 6
                SINC = "METT-T" & V
    Next M
         End If
    Next I
    How would I cycle through the fieldnames?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Code:
        Dim rst As DAO.Recordset, x As Integer
        Set rst = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
        
        rst.MoveFirst
        Do Until rst.EOF
            For x = 1 To 3
                Debug.Print rst("Field" & x)
            Next
            rst.MoveNext
        Loop

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks aytee111,
    I should have phrased that better.

    How would I cycle through these fields?
    SINCNum1 = rU.Fields![SINCGARS 1]
    SINCNum2 = rU.Fields![SINCGARS 2]
    SINCNum3 = rU.Fields![SINCGARS 3]
    SINCNum4 = rU.Fields![SINCGARS 4]
    SINCNum5 = rU.Fields![SINCGARS 5]
    SINCNum6 = rU.Fields![SINCGARS 6]

    Where I could use the SINCNum1 - 6 as representing the field name?

    I want to take the following:

    Code:
    If rU![SINCGARS 1] = "METT" then
         SINC1 = "METT-T"
    ElseIf rU![SINCGARS 2] = "METT" then
         SINC2 = "METT-T"
    ElseIf rU![SINCGARS 4] = "METT" then
         SINC3 = "METT-T"
    ElseIf rU![SINCGARS 4] = "METT" then
         SINC4 = "METT-T"
    ElseIf rU![SINCGARS 5] = "METT" then
         SINC5 = "METT-T"
    ElseIf rU![SINCGARS 6] = "METT" then
         SINC6 = "METT-T"
    End If
    To Something like this:

    Code:
    For I = 1 to 6
    SINCNum = "??????????????????????????? Where it references [SINGARS  1] to  [SINGARS  6]
    If SINCNum = "METT" then
    SINC = "SINC" & M
      For M= 1 to 6
         SINC = "METT-T"
    End If
      Next M
    Next I

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ah, I understand now. I misread and thought the "SINCGARS 1" was data.

    I am not sure what you are doing (even in Post #3 code), but maybe the demo will help.
    Attached Files Attached Files

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What part of my code don't you understand? Change the table name to be your table name and change the field name to be your field name.

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I think I'm getting it now

    So SINCNum = rU("SINCGARS " & i) replaces rU![SINCGARS 1] through rU![SINCGARS 6]?

    Code:
    For I = 1 to 6
          SINCNum = rU!("SINCGARS  " & I)
          SINC = "SINC" & I
    If SINCNum = "METT" then
         SINC = "METT-T"
    End If
    Next
    I'll give it a shot. Much appreciated!

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is "SINCx"? Are they 6 variables you have defined? If so, that isn't going to work. Set it up as an array: Dim SINC(6) AS String, then SINC(I)="METT-T"

    If rU("SINCGARS " & I)="METT" Then

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Gotcha SINC(6) = SINC1 through SINC6

    How do you relate the output to the table?

    Code:
    For I = 1 To 6
        If rU("SINCGARS  " & I) = "METT" Then
                SINC(6) = "METT-T" & V
         End If
    Next I
                rU.Edit
                rU.SINC1 = SINC(1)
                rU.Update
                rU.MoveNext
        Loop
    Method or data member not found

    I've tried
    rU.SINC1 = SINC(1)
    rU.SINC(1) = SINC(1)
    rU.("SINCGARS " & I) = SINC(1)

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    I have to admit to some confusion here - there are 6 "SINCGARS" fields on your table NBOI and there are also 6 "SINC" fields on the same table which you are updating? Could you please tell us what you are trying to accomplish and we can take it from there.

  12. #12
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Correct, There are 6 SINCGARS Fields and I added 6 SINC fields so I don't destroy the data until I am ready to overwrite, then after all the code correcting and self screw ups I will remove the 6 SINC fields.

    I pull the data from the SINCGARS fields and write the transformed data to the corresponding SINC fields

    I do have the data backed up elsewhere in addition.

    Data Read Fields = SINCGARS
    Data Write Fields = SINC

  13. #13
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Maybe this is what you are trying to do?

    Code:
        rU.MoveFirst
        Do Until rU.EOF
            rU.Edit
                For I = 1 To 6
                    If rU("SINCGARS  " & I) = "METT" Then rU("SINC" & I) = "METT-T"
                Next
            rU.Update
            rU.MoveNext
        Loop

  14. #14
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks aytee111,
    It worked like a charm. After correcting some boneheaded actions on my part.

    Thanks again.


    Quote Originally Posted by aytee111 View Post
    Maybe this is what you are trying to do?

    Code:
        rU.MoveFirst
        Do Until rU.EOF
            rU.Edit
                For I = 1 To 6
                    If rU("SINCGARS  " & I) = "METT" Then rU("SINC" & I) = "METT-T"
                Next
            rU.Update
            rU.MoveNext
        Loop

Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums