EOF Loop on Subform

[steve21nj]

I am experiencing an issue when running a loop on my subform. The code works on the first record, but doesn't move to the second or additional records of the subform, if there is in fact additional records.

Below is my current code.

Private Sub Process_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
If Me![Process] = -1 Then
   Me![Process Date] = date
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key)
[COLOR=red]With db[/COLOR]
[COLOR=red]rst.MoveFirst[/COLOR]
[COLOR=red]Do While Not rst.EOF[/COLOR]
[COLOR=red]'check to see if bagnum has value first[/COLOR]
[COLOR=red]If IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.BagNum) Then Exit Do[/COLOR]
[COLOR=red]Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.Combo268 = 2[/COLOR]
[COLOR=red]rst.MoveNext[/COLOR]
[COLOR=red]Loop[/COLOR]
 
[COLOR=black]End With[/COLOR]
[COLOR=black]rst.Close[/COLOR]
Set rst = Nothing
MsgBox "Update Information.", vbOKOnly, "Update when applicable"
    Else: Me![Process Date] = ""
If Me![Process] = 0 Then
'check to see if bagnum has value first
If Not IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.BagNum) Then
    Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.Combo268 = 1
 
End If
End If
End If
End Sub

The code below was used to replace and test part of the above to see if the subform would return multiple records, that it will indeed call back each record.

Example if a record has 3 subform entries, 3 msgbox will display.
If a record has 1 subform entry, 1 msgbox will display.

Since the msgbox counts are appearing correctly, I am assumin the code is working but not sure why it will not change the values of the 2nd, 3rd, etc records, only the 1st.

PS. The message box always comes back blank, not returning the bag number. Not sure if thats an issue or not.

[FONT=Arial]With db[/FONT]
[FONT=Arial]Do While Not rst.EOF[/FONT]
[FONT=Arial]MsgBox BagNum[/FONT]
[FONT=Arial]rst.MoveNext[/FONT]
[FONT=Arial]Loop[/FONT]

 

[pr2-eugin]

Not sure if this would solve it, but just try the following code..

Private Sub Process_AfterUpdate()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    If Me[COLOR=Blue][B].[/B][/COLOR][Process] = -1 Then
        Me[COLOR=Blue][B].[/B][/COLOR][Process Date] = Date
        Set db = CurrentDb
        Set rst = db.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & [COLOR=Red][B]Key[/B][/COLOR])
        [B][COLOR=Blue]rst.MoveLast[/COLOR][/B]
        rst.MoveFirst
        Do While Not rst.EOF
            'check to see if bagnum has value first
            If IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form[COLOR=Blue][B]![/B][/COLOR]BagNum) Then Exit Do
            Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form[COLOR=Blue][B]![/B][/COLOR]Combo268 = 2
            rst.MoveNext
        Loop
        rst.Close
        Set rst = Nothing
        MsgBox "Update Information.", vbOKOnly, "Update when applicable"
    Else 
        Me[COLOR=Blue][B].[/B][/COLOR][Process Date] = ""
        If Me[COLOR=Blue][B].[/B][/COLOR][Process] = 0 Then
            'check to see if bagnum has value first
            If Not IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form[COLOR=Blue][B]![/B][/COLOR]BagNum) Then
                Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form[COLOR=Blue][B]![/B][/COLOR]Combo268 = 1
            End If
        End If
    End If
End Sub

So what is Key? I have made changes marked in Blue..

If Me.Process is a Yes/No type.. I also believe that the If inside the Else

        If Me.[Process] = 0 Then

is redundant, as the Else is executed if it is not -1..

 

[steve21nj]

Still the same results.

The [key] is the main PrimaryKey that links the two tables. [DetentionID] is the fk of the [key].

Private Sub Process_AfterUpdate()
Dim db As DAO.Database
Dim rst As DAO.Recordset
 
If Me![Process] = -1 Then
   Me![Process Date] = date
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key)
rst.MoveLast
        rst.MoveFirst
        Do While Not rst.EOF
            'check to see if bagnum has value first
            If IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form!BagNum) Then Exit Do
            Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form!Combo268 = 2
            rst.MoveNext
            Loop
rst.Close
Set rst = Nothing

 

[JHB]

The code works on the first record, but doesn't move to the second or additional records of the subform, if there is in fact additional records.

Why do you think it should update more as the first record?
I can't find in you code that the "pointer" is moved to next record in your sub form.
Maybe I've over looked something.

 

[steve21nj]

I thought the MoveNext command pushed it to the next record in the subform. Any thoughts on what I should add?

rst.MoveNext

 

[JHB]

No rst.MoveNext moves only the pointer in the recordset, it has no influence on the subform in that way you thought.
Use at update query, or use the RecordsetClone from the subform.

 

[steve21nj]

I may need assistance with the recordsetclone. I tried the following below, but it still only changes the first record. I am beginning to think I have to much fluff in my code at this point.

Private Sub Process_AfterUpdate()
If Me![Process] = -1 Then
   Me![Process Date] = date
 
Dim rst As DAO.Recordset
Set rst = Me.SubfrmDetaineeReviewGroupOut.Form.RecordsetClone
 
        With rst
        Do Until rst.EOF
 
            'check to see if bagnum has value first
            If IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form!BagNum) Then Exit Do
            Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form!Combo268 = 2
            rst.MoveNext
            Loop
            rst.Close
            End With
           Set rst = Nothing
 
MsgBox "My Message.", vbOKOnly, "Update when applicable"
    Me![Reason for Detention].SetFocus
    Me![Reason for Detention].Dropdown
Else: Me![Process Date] = ""
'check to see if bagnum has value first
If Not IsNull(Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.BagNum) Then
    Forms![Detention Group Edit1]!SubfrmDetaineeReviewGroupOut.Form.Combo268 = 1
 
End If
End If
End Sub

The code will run fine for the first record of the subform, but the program will freeze if there are 2 or more records in the subform.

 

[JHB]

It'll not work this way, the RecordsetClone from the subform hasn't also no influence on the subform, in that way you think.
You've to put the it the other way round, you update the relevant field, (the field Combo268 is bound to), in the Recordset with the value you wish.
And at finally you make a Refresh for the subform.

But why not use a Update Query, it is easier and quicker?

Post the database with sample data, then I'll fix it.

 

[steve21nj]

I'd like to stick with corrected VBA if possile because I want to apply this to another form based on the clicking and unclicking of a check box.

I cannot upload a copy of the database at this time. Can you assist with modifiying the VBA from above? I'm confused with you say the recordsetclone from the subform is the other way around. From everything I've read it seems to be on par with other solutions, but I guess I am missing something. What should the codes end result be?

 

[steve21nj]

JHB,

Do you have any suggestions in getting the vba for the recordsetclone to work based on the code i have above?

 

[gemma-the-husky]

in simple terms, what is your process trying to do, to the records in the form (subform).

I would have thought if you have to do it in code - eg in response to a button click - you need to process the data on the form - maybe by iterating either the recordsetclone, or by iterating a similar recordset/query returning the same items.

This process will change the records in the underlying table. At the end of which you need a me.refresh, or probably a me.requery to refresh the form(subform)

what this process does not do, is to continually move the active record cursor to each row of the displayed records in turn - which is presumably the sort of behaviour you are expecting.

 

[JHB]

JHB,

Do you have any suggestions in getting the vba for the recordsetclone to work based on the code i have above?

Yes I'll made a example on that later today.

 

[steve21nj]

what this process does not do, is to continually move the active record cursor to each row of the displayed records in turn - which is presumably the sort of behaviour you are expecting.

gemma-the-husky,
I am looking for that type of behavior, just automated. Think I am asking for to much. I want to check mark box to update my main form with todays date, then update my subform that is located on my main page footer. The subform is located on the footer because both the main and subform are continuious forms. So when i click on another record within the main form, my subform shows the new records.

Within the subform, I am attempting to update the combo box from [Pending] to [Completed]. The subform may have 5 records or 0 associated with the main form. After the update, I want to display my message box, set focus, and drop my main forms combo.

When I uncheck the box, I want to remove the text box date, and update the subforms combo box from [Completed] to [Pending].


JHB,
Thank you for your help.

 

[JHB]

I haven't check the code in real, so maybe an error would be in there.

Private Sub Example()
  Dim dbs As Database, rst As Recordset
 
  Set dbs = CurrentDb
[B][COLOR=red]'You have to create a recordset which collect the data equal to the subform[/COLOR][/B]
[B][COLOR=red]'I expect that the below code does that, else you have to change it[/COLOR][/B]
  Set rst = dbs.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key, dbOpenDynaset)
  If Not rst.EOF Then
    If Me.[Process] = -1 Then
      Me.[Process Date] = Date
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
          rst.Edit
          rst!["The FieldName in the Recordset which is equal to Combo268"] = 2
          rst.Update
        End If
        rst.MoveNext
      Loop Until rst.EOF
    Else
      Me.[Process Date] = ""
      If Me.[Process] = 0 Then
        If Not IsNull(rst![BagNum]) Then
          rst.Edit
          rst!["The FieldName in the Recordset which is equal to Combo268"] = 1
          rst.Update
         End If
      End If
    End If
    Me.Refresh
  End If
End Sub

 

[steve21nj]

JHB,

Thank you! I added the DAO.Recordset because it would produce and error and highlight rst.edit

The only issue comes when unchecking the box. The code update only the first record and doesn't cycle through all the records like the first part of the code. Any suggestions?

Private Sub Process_AfterUpdate()
Dim dbs As Database, rst As [COLOR=red]DAO.Recordset
[/COLOR]Set dbs = CurrentDb
If [Out to Mag Court] = -1 Then
Me![Mag Court Date] = date
Me![Date Out] = date
End If
'You have to create a recordset which collect the data equal to the subform
  
  Set rst = dbs.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key, dbOpenDynaset)
  If Not rst.EOF Then
    If Me.[Process] = -1 Then
      Me.[Process Date] = date
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
          rst.Edit
          rst![[COLOR=red]PropertyStatus[/COLOR]] = 2
          rst.Update
        End If
        rst.MoveNext
      Loop Until rst.EOF
    Else
      Me.[Process Date] = ""
      If Me.[Process] = 0 Then
        If Not IsNull(rst![BagNum]) Then
          rst.Edit
          rst![[COLOR=red]PropertyStatus[/COLOR]] = 1
          rst.Update
         End If
      End If
    End If
    Me.Refresh
  End If
End Sub

 

[JHB]

It should go trought them all, (if there are more).
Or it could be because IsNull(rst![BagNum]) is true.
Set in a break point in the start of the code and follow it, step by step.

 

[steve21nj]

I ended up duplicating what you did before the midline else statement and it works. The question is, is this the correct way of handling that? Thank you again.

Private Sub Process_AfterUpdate()
Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb
If [Out to Mag Court] = -1 Then
Me![Mag Court Date] = date
Me![Date Out] = date
End If
'You have to create a recordset which collect the data equal to the subform
  
  Set rst = dbs.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key, dbOpenDynaset)
  If Not rst.EOF Then
    If Me.[Process] = -1 Then
      Me.[Process Date] = date
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
          rst.Edit
          rst![PropertyStatus] = 2
          rst.Update
        End If
        rst.MoveNext
      Loop Until rst.EOF
    Else
      If Me.[Process] = 0 Then
      Me.[Process Date] = ""
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
          rst.Edit
          rst![PropertyStatus] = 1
          rst.Update
        End If
        rst.MoveNext
      Loop Until rst.EOF
      End If
    End If
    Me.Refresh
  End If
End Sub

 

[JHB]

Yes it is ok, if it gives you what you want and you are the only one who can decide it.
But it could have be done more time-saving, with less code and more professional if you have would use an UpdateQuery.

Private Sub Process_AfterUpdate()
  Dim dbs As Database
  
  Set dbs = CurrentDb
  If [Out to Mag Court] = -1 Then
    Me![Mag Court Date] = Date
    Me![Date Out] = Date
  End If
  If Me.[Process] = -1 Then
    Me.[Process Date] = Date
    dbs.Execute ("UPDATE tblPropertyDetails SET PropertyStatus = 2 " _
    & "WHERE BagNum Is Not Null AND DetentionID=" & Key)
  Else
    If Me.[Process] = 0 Then
      Me.[Process Date] = ""
    End If
    dbs.Execute ("UPDATE tblPropertyDetails SET PropertyStatus = 1 " _
    & "WHERE BagNum Is Not Null AND DetentionID=" & Key)
  End If
  Me.Refresh
End Sub