Results 1 to 6 of 6
  1. #1
    jrdnoland is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41

    Loop thru subform recordset

    I have a continuous form that contains a subform. Each form has its own table. I'm trying to loop thru the subforms recordset. Sometime it works other times it doesn't, not sure what I'm missing.

    Here is the code I'm using for this section:


    Code:
    Private Sub cmdUpdateSub_Click()
    Forms!frmDASHBOARD.SetFocus
    DoCmd.Minimize
    Forms!frmTestRequests_Main.SetFocus
    Dim Style
    Dim x As Integer
    Dim rstTestRequests As DAO.Recordset
    
    Forms!frmTestRequests_Main!cboNewProjectStatus.SetFocus
    Set rstTestRequests = Me.frmSubTestRequests_Subform.Form.RecordsetClone
    
    If (Forms!frmTestRequests_Main!cboNewProjectStatus.Text = "") Then
        Style = vbYes + vbCritical
        MsgBox "Fill Project_Status and then Click Update Release Record agiain.", Style, "Missing Information"
        Exit Sub
    End If
    
    If (IsNull(Forms!frmTestRequests_Main![txtRelease_Counter].Value)) Then
        'Must be the first iteration, add 1 to the txtRelease_Counter box
        Forms!frmTestRequests_Main![txtRelease_Counter].Value = 0
    End If
    
    x = Forms!frmTestRequests_Main![txtRelease_Counter].Value
    rstTestRequests.FindFirst x
    
    If rstTestRequests.NoMatch Then
        'Nothing
    Else
        Me.frmSubTestRequests_Subform.Form.Bookmark = rstTestRequests.Bookmark
    End If
    Select Case x
        Case 1
            ContinueReleaseNext (x)
                Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Me.frmSubTestRequests_Subform.Form.Recordset.MoveNext
                    'continue loop
                    x = x + 1                '2
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
                Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Me.frmSubTestRequests_Subform.Form.Recordset.MoveNext
                    x = x + 1           '3
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
                Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Me.frmSubTestRequests_Subform.Form.Recordset.MoveNext
                    x = x + 1           '4
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
        Case 2
            ContinueReleaseNext (x)
            Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Me.frmSubTestRequests_Subform.Form.Recordset.MoveNext
                      x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
        Case 3
            ContinueReleaseNext (x)
            Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Forms.[frmTestRequests_Main].[frmSubTestRequests_Subform].Form.[Recordset].MoveNext
                    x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
        Case 4
            ContinueReleaseNext (x)
            Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not Me.frmSubTestRequests_Subform.Form.Recordset.EOF) Then
                    Forms.[frmTestRequests_Main].[frmSubTestRequests_Subform].Form.[Recordset].MoveNext
                    x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
    End Select
    End Sub
    Here is an image of the forms:
    Click image for larger version. 

Name:	Project Requests.jpg 
Views:	19 
Size:	241.9 KB 
ID:	44348

    Basically, for each of the Main records, there will be four possible Releases. After setting a Release to True the Main table is updated to reflect this as a new record.
    I'm not sure what at times I can't get the recordset to move to the next record?

    Thanks,
    Jeff

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    A number of things spring to mind.

    When you are on a form simply refer to the object on it using Me.YourControlName - Using the long version (Forms!frmTestRequests_Main!frmSubTestRequests_Subf orm.SetFocus) makes it much harder to read, and you get no intellisense.

    Why are you setting focus to the subform all the time? You very rarely need a control or the subform to have focus to refer to controls or their values.

    As to your problem, you appear to be using a recordset clone but then refer to the forms recordset in the tests, I'm puzzled by that, but I'm not sure I get the purpose of the process anyway.
    If any record is set to true in the subform, update the master record to what?

    I would have thought a simple after update event could capture this, but even simpler a DCount() or query would be able to display the result without performing any update, and therefore always be accurate?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    jrdnoland is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Quote Originally Posted by Minty View Post
    A number of things spring to mind.

    When you are on a form simply refer to the object on it using Me.YourControlName - Using the long version (Forms!frmTestRequests_Main!frmSubTestRequests_Subf orm.SetFocus) makes it much harder to read, and you get no intellisense.

    Why are you setting focus to the subform all the time? You very rarely need a control or the subform to have focus to refer to controls or their values.

    As to your problem, you appear to be using a recordset clone but then refer to the forms recordset in the tests, I'm puzzled by that, but I'm not sure I get the purpose of the process anyway.
    If any record is set to true in the subform, update the master record to what?

    I would have thought a simple after update event could capture this, but even simpler a DCount() or query would be able to display the result without performing any update, and therefore always be accurate?


    -The reason I didn't use Me, was that I tried it and the recordset still wasn't moving thru all the records in the subform.
    - I was setting focus all the time because again I was trying to get the recordset to move thru all of the records.
    - The master record gets updated so that each project with a release will be in the main record.

    How would I move the recordset clone to the next record each time?

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Okay - please take a step back.
    What field do you want updated in the main record, and what do you want it updated to?

    Please note that this is generally a very bad way of doing these things, if someone changes that underlying record anywhere you have to ensure you capture that update and adjust the related field in your top level record.
    Hence my suggestion that you should simply look up the related values in your sub table.

    Clarify the table names, the field names and the values and criteria you expect to cause your update.

    At the moment I see no need to loop through any recordset, a simple update query can achieve what you desire, or better still an accurate calculated result.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    jrdnoland is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Ohio
    Posts
    41
    Quote Originally Posted by Minty View Post
    Okay - please take a step back.
    What field do you want updated in the main record, and what do you want it updated to?

    Please note that this is generally a very bad way of doing these things, if someone changes that underlying record anywhere you have to ensure you capture that update and adjust the related field in your top level record.
    Hence my suggestion that you should simply look up the related values in your sub table.

    Clarify the table names, the field names and the values and criteria you expect to cause your update.

    At the moment I see no need to loop through any recordset, a simple update query can achieve what you desire, or better still an accurate calculated result.
    I'm trying to get a current db system to work with new requirements. The new requirements are that now each workbook can have 1 to 4 releases. I have put the necessary releases for each one in a subtable and that subtable is in the subform.

    So at Start - a single record is added to the main table with the Release set at 0, and all four possible Releases are put in the subtable. The main table is being opened with a batchfile and all of the possible releases are in the subtable. At this point an Admin opens the main record and will then loop thru all the possible Releases and add each of them, if the appropriate checkbox is selected. Once selected the suitable release record merges with the main record to create a new record which is saved in the main table.

    This record in the main table is then used to assign resources to. This is then sent to a Trello board and the project is worked on.

    I think the above will work the issue I'm having is to get the subtable/subform records to loop thru all four possible Releases. This doesn't always seem to work and I'm not sure why.

    Click image for larger version. 

Name:	Main Record.png 
Views:	13 
Size:	60.3 KB 
ID:	44349

    This is the code I'm using:
    Code:
    Private Sub cmdUpdateSub_Click()
    Forms!frmDASHBOARD.SetFocus
    DoCmd.Minimize
    Forms!frmTestRequests_Main.SetFocus
    Dim Style
    Dim x As Integer
    Dim rstTestRequests As DAO.Recordset
    
    Forms!frmTestRequests_Main!cboNewProjectStatus.SetFocus
    Set rstTestRequests = Me.frmSubTestRequests_Subform.Form.RecordsetClone
    
    If (Forms!frmTestRequests_Main!cboNewProjectStatus.Text = "") Then
        Style = vbYes + vbCritical
        MsgBox "Fill Project_Status and then Click Update Release Record agiain.", Style, "Missing Information"
        Exit Sub
    End If
    
    If (IsNull(Forms!frmTestRequests_Main![txtRelease_Counter].Value)) Then
        'Must be the first iteration, add 1 to the txtRelease_Counter box
        Forms!frmTestRequests_Main![txtRelease_Counter].Value = 0
    End If
    
    x = Forms!frmTestRequests_Main![txtRelease_Counter].Value
    rstTestRequests.MoveFirst
    
    If rstTestRequests.NoMatch Then
        'Nothing
    Else
        Me.frmSubTestRequests_Subform.Form.Bookmark = rstTestRequests.Bookmark
    End If
    Select Case x
        Case 1
            ContinueReleaseNext (x)
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                    'continue loop
                    x = x + 1                '2
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                    x = x + 1           '3
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
                'Forms!frmTestRequests_Main!frmSubTestRequests_Subform.SetFocus
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                    x = x + 1           '4
                End If
                Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                ContinueReleaseNext (x)
        Case 2
            ContinueReleaseNext (x)
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                      x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
        Case 3
            ContinueReleaseNext (x)
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                    x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
        Case 4
            ContinueReleaseNext (x)
                If (Not rstTestRequests.EOF) Then
                    rstTestRequests.MoveNext
                    x = x + 1
                    Forms.frmTestRequests_Main.[txtRelease_Counter] = x
                    ContinueReleaseNext (x)
                End If
    End Select
    End Sub

  6. #6
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You haven't really answered my questions, just restated yours...

    I'll try again.
    You have five possible releases of an item.
    Do they follow on from each other? e.g.
    Project 1 initial release 0
    Project 1 release 1
    Project 1 release 2
    Project 1 release 3 etc

    So if 1 is already released then then next one will always be 2?

    I'm finding it really hard to understand the use of the prepopulated sub table to drive the revisions.
    Why not just add the revision number to the sub table when you need it.
    In the after update event of the sub form update the main record accordingly. No need for a complicated loop of anything.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Loop Part Of Recordset
    By DMT Dave in forum Access
    Replies: 1
    Last Post: 10-23-2019, 02:07 AM
  2. Need your help in a loop in the recordset
    By Muntasser in forum Programming
    Replies: 3
    Last Post: 03-19-2018, 12:04 PM
  3. Loop Not Progressing Through Recordset
    By nmlinac in forum Programming
    Replies: 1
    Last Post: 02-15-2013, 10:54 AM
  4. Double loop recordset
    By silverspr in forum Programming
    Replies: 7
    Last Post: 03-07-2012, 01:43 PM
  5. Loop through recordset
    By eliedebs in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 12:26 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