Results 1 to 12 of 12
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    looping code with letters in specified sequence

    I want to add new records to table using looping code. I hadsimilar problem before and ssanfu helped me to solve it. I was trying to reusethis code but I am stack again.
    I have 4 boxes on a form: cboPJAdd, txthtcAdd, txtPhaseNoand txtPhaseLetCnt.
    Based on values entered in these boxes I want to insert newrecords to the table. The problem I have with is to add to column “Phase”, letter“A”, (only when txtPhaseNo value = 1 and txtPhaseLetCnt value =1) or A,B,C for(for txtPhaseNo = 3 and txtPhaseLetCnt value = to how many times each letterrepeats itself in sequence) . In txtPhaseNo I use only 2 nubmers: “1” & “3”.In txtPhaseLetCnt I specify how many times each letter needs to be repeated (theonly letters I use is A,B&C)
    For example if txtPhaseNo =3 and txtPhaseLetCnt =2 I want toinsert into “Phase” the following for each consecutive record, A,A,B,B,C,C andrepeat it till the loop ends. I have attached small database with table, formand a code I have so far to better explain what I need to accomplish.
    I would really appreciate your help as this is toocomplicated for my programming skills.


    Private Sub btnPjAdd_Click()


    Dim db As DAO.Database
    Dim rec As DAO.Recordset

    Dim ControlPanel As String 'cboPJAdd
    Dim Controller As Integer 'txtHtcAdd
    Dim PhaseNumber As Integer 'txtPhaseNo
    Dim PhaseLetterCount As Integer 'txtPhaseLetCnt
    Dim Phase As String


    Dim i As Integer, k As Integer 'counter

    Set db = CurrentDb
    Set rec = db.OpenRecordset("Select * from tbl_SkidAssignment")

    'get values from form controls
    ControlPanel = Me.cboPJAdd
    Controller = Me.txthtcAdd
    PhaseNumber = Me.txtPhaseNo
    PhaseLetterCount = Me.txtPhaseLetCnt

    'start loop
    For i = 1 To Controller
    rec.AddNew
    rec("SkidNo") = ControlPanel 'string
    rec("Htc") = i
    rec("Phase") = k

    rec.Update

    k = k + 1
    If k > PhaseLetterCount Then
    k = 1

    End If



    Next i

    'clean up
    rec.Close
    Set rec = Nothing
    Set db = Nothing

    MsgBox "Done"
    DoCmd.OpenTable ("tbl_SkidAssignment")

    End Sub


    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Note: If you use the code tags, the code will retain the indenting.. (the "#" button in the quick reply menu)

    Try this - if I did the testing correctly, it should be right.
    Code:
    'Add New Control Panel
    Private Sub btnPjAdd_Click()
        'Code modified by ssanfu
    
        Dim db As DAO.Database
        Dim rec As DAO.Recordset
    
        Dim ControlPanel As String    'cboPJAdd
        Dim Controller As Integer    'txtHtcAdd
        Dim PhaseNumber As Integer   'txtPhaseNo
        Dim PhaseLetterCount As Integer    'txtPhaseLetCnt
        Dim Phase As String
    
        Dim i As Integer, j As Long, k As Long    'counter
    
        Set db = CurrentDb
        Set rec = db.OpenRecordset("SELECT SkidNo, HTC, Phase, Notes FROM tbl_SkidAssignment")
    
        k = 0  'counter
        j = 1  'counter
    
        'get values from form controls
        ControlPanel = Me.cboPJAdd
        Controller = Me.txthtcAdd
        PhaseNumber = Me.txtPhaseNo
        PhaseLetterCount = Me.txtPhaseLetCnt
    
        'start loop
        For i = 1 To Controller
            rec.AddNew
            rec("SkidNo") = ControlPanel    'string
            rec("Htc") = i
            rec("Phase") = Chr(65 + k)
    
            rec.Update
            '        Debug.Print ControlPanel & ", " & i & ", " & Chr(65 + k)
    
            If j >= PhaseNumber Then  ' how many times to loop before inc letter
                j = 1
                k = k + 1
                If k >= PhaseLetterCount Then  ' how many letters
                    k = 0
                End If
            Else
                j = j + 1
            End If
        Next i
    
        'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    
    
        MsgBox "Done"
        DoCmd.OpenTable ("tbl_SkidAssignment")
    
    End Sub

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    txtPhaseNo of 1 should not be allowed to pair with txtPhaseLetCnt greater than 1?

    Don't need to open recordset to existing records, that is unnecessary consumption of memory.

    Set rec = db.OpenRecordset("SELECT * FROM tbl_SkidAssignment WHERE 1=0")

    Steve, I tested your code with a txtPhaseLetCnt of 4 and it only outputs 3 and it doesn't seem to deal with a txtPhaseNo of 1 correctly. I know OP's examples did not use more than 3 but I tried coding for more. Here is my attempt.
    Code:
        'start loops
        i = 1
        Do While i <= Me.txthtcAdd
            j = j + 1
            For k = 1 To IIf(Me.txtPhaseNo = 1, 1, Me.txtPhaseLetCnt)
                If i <= Me.txthtcAdd Then
             '       Debug.Print Choose(j, "A", "B", "C") & " : " & i
                    rec.AddNew
                    rec("SkidNo") = Me.cboPJAdd
                    rec("Htc") = i
                    rec("Phase") = Choose(j, "A", "B", "C")
                    rec.Update
                End If
                i = i + 1
            Next
            If j = IIf(Me.txtPhaseNo = 1, 1, 3) Then j = 0
        Loop
    
    Last edited by June7; 12-14-2019 at 04:17 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    ssanfu, thanks, the code does the job when I enter the values in reverse, phases 1 or 3 in txtPhaseLetCnt and number of letters I want to repeat in string into txtPhaseNo
    I will adjust it to my needs. I would never be able to do it without your help. Thanks one more time

    Quote Originally Posted by ssanfu View Post
    Note: If you use the code tags, the code will retain the indenting.. (the "#" button in the quick reply menu)

    Try this - if I did the testing correctly, it should be right.
    Code:
    'Add New Control Panel
    Private Sub btnPjAdd_Click()
        'Code modified by ssanfu
    
        Dim db As DAO.Database
        Dim rec As DAO.Recordset
    
        Dim ControlPanel As String    'cboPJAdd
        Dim Controller As Integer    'txtHtcAdd
        Dim PhaseNumber As Integer   'txtPhaseNo
        Dim PhaseLetterCount As Integer    'txtPhaseLetCnt
        Dim Phase As String
    
        Dim i As Integer, j As Long, k As Long    'counter
    
        Set db = CurrentDb
        Set rec = db.OpenRecordset("SELECT SkidNo, HTC, Phase, Notes FROM tbl_SkidAssignment")
    
        k = 0  'counter
        j = 1  'counter
    
        'get values from form controls
        ControlPanel = Me.cboPJAdd
        Controller = Me.txthtcAdd
        PhaseNumber = Me.txtPhaseNo
        PhaseLetterCount = Me.txtPhaseLetCnt
    
        'start loop
        For i = 1 To Controller
            rec.AddNew
            rec("SkidNo") = ControlPanel    'string
            rec("Htc") = i
            rec("Phase") = Chr(65 + k)
    
            rec.Update
            '        Debug.Print ControlPanel & ", " & i & ", " & Chr(65 + k)
    
            If j >= PhaseNumber Then  ' how many times to loop before inc letter
                j = 1
                k = k + 1
                If k >= PhaseLetterCount Then  ' how many letters
                    k = 0
                End If
            Else
                j = j + 1
            End If
        Next i
    
        'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    
    
        MsgBox "Done"
        DoCmd.OpenTable ("tbl_SkidAssignment")
    
    End Sub

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you see code in post #3?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, thank you for your reply, I inserted the loop into code and played with the combinations.
    For 3 phases each time the code inserts the right number of letters, however it starts the loop at "B", so the sequence is "B","C","A" instead of "A","B","C"
    Probably I haven't explained the single phase correctly. Single phase has only one combination of letters "A" repeated for each row regardless what number user inserts into txtPhaseLetCnt.
    When I was inserting different numbers to txtPhaseLetCnt keeping txtPhaseNo as "1", each time I was getting only "B" & "C" inserted into row 1 & 2 and the others empty. I was expecting seeing "A" for all the records.

    Quote Originally Posted by June7 View Post
    Did you see code in post #3?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    When I test code, it starts loop with "A". Works perfect for me. Same for Phase of 1, only letter "A" is entered in records. Are you sure you are testing my code?

    Really need to include code that verifies inputs are provided before creating records.

    Why open table for users to view? Users should only work with forms and reports.

    Don't bother with MsgBox if some other action is performed, such a opening table/form/report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, the way I understood your instruction was to replace ssafu looping code with yours, I could be wrong but this is what I did, please see the code I was testing database with.

    Code:
    Private Sub btnPjAdd_Click()    'Code modified by ssanfu
    
    
        Dim db As DAO.Database
        Dim rec As DAO.Recordset
    
    
        Dim ControlPanel As String    'cboPJAdd
        Dim Controller As Integer    'txtHtcAdd
        Dim PhaseNumber As Integer   'txtPhaseNo
        Dim PhaseLetterCount As Integer    'txtPhaseLetCnt
        Dim Phase As String
    
    
        Dim i As Integer, j As Long, k As Long    'counter
    
    
        Set db = CurrentDb
        Set rec = db.OpenRecordset("SELECT SkidNo, HTC, Phase, Notes FROM tbl_SkidAssignment")
    
    
        k = 0  'counter
        j = 1  'counter
    
    
        'get values from form controls
        ControlPanel = Me.cboPJAdd
        Controller = Me.txthtcAdd
        PhaseNumber = Me.txtPhaseNo
        PhaseLetterCount = Me.txtPhaseLetCnt
    
    
        'start loop
         
        i = 1
        Do While i <= Me.txthtcAdd
            j = j + 1
            For k = 1 To IIf(Me.txtPhaseNo = 1, 1, Me.txtPhaseLetCnt)
                If i <= Me.txthtcAdd Then
             '       Debug.Print Choose(j, "A", "B", "C") & " : " & i
                    rec.AddNew
                    rec("SkidNo") = Me.cboPJAdd
                    rec("Htc") = i
                    rec("Phase") = Choose(j, "A", "B", "C")
                    rec.Update
                End If
                i = i + 1
            Next
            If j = IIf(Me.txtPhaseNo = 1, 1, 3) Then j = 0
        Loop
        'clean up
        rec.Close
        Set rec = Nothing
        Set db = Nothing
    
    
    
    
        MsgBox "Done"
        DoCmd.OpenTable ("tbl_SkidAssignment")
    
    
    End Sub
    Quote Originally Posted by June7 View Post
    When I test code, it starts loop with "A". Works perfect for me. Same for Phase of 1, only letter "A" is entered in records. Are you sure you are testing my code?

    Really need to include code that verifies inputs are provided before creating records.

    Why open table for users to view? Users should only work with forms and reports.

    Don't bother with MsgBox if some other action is performed, such a opening table/form/report.

  9. #9
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Thank you for the suggestions you made in the last post, I learned from ssanfu that if the explanation what I am trying to achieve is ambiguous it is better to create simple database and post it at the same time.
    In my main database I don't allow users to create records when all inputs are not entered and recordset was already created for PJ. The open table is just to view the output when testing the code. I also not allow users to have access to tables, everything is done trough form.

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    With my code remove lines - especially the two setting k and j variables:
    Code:
        Dim ControlPanel As String    'cboPJAdd
        Dim Controller As Integer    'txtHtcAdd
        Dim PhaseNumber As Integer   'txtPhaseNo
        Dim PhaseLetterCount As Integer    'txtPhaseLetCnt
        Dim Phase As String
    
        k = 0  'counter
        j = 1  'counter
    
    
        'get values from form controls
        ControlPanel = Me.cboPJAdd
        Controller = Me.txthtcAdd
        PhaseNumber = Me.txtPhaseNo
        PhaseLetterCount = Me.txtPhaseLetCnt
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, thank you, I have removed k=0 & j=1 and the code works perfectly, learned a lot about looping from you guys

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @June
    Thanks for stepping in and straightening out the code. I was up way too long (~20 hrs)..

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

Similar Threads

  1. looping code doesn't loop
    By HS_1 in forum Programming
    Replies: 12
    Last Post: 08-08-2019, 12:10 PM
  2. Code to Generate a sequence number
    By shod90 in forum Programming
    Replies: 1
    Last Post: 07-04-2018, 05:17 PM
  3. looping code, a better way ?
    By trevor40 in forum Programming
    Replies: 8
    Last Post: 01-12-2015, 03:54 PM
  4. Spell out category code letters in a report.
    By IzzyKap in forum Reports
    Replies: 1
    Last Post: 02-26-2013, 02:12 PM
  5. Looping code for printing reports
    By Lockrin in forum Access
    Replies: 2
    Last Post: 02-09-2010, 05:48 AM

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