Running a Recordset loop function on a subform that cant go to the next line.

[Sodslaw]

Hi Chaps,
i Hope you can help im running into problems with this code....

Private Sub OrderType_AfterUpdate()
Dim TmpTxtHeading As String
Dim NewTxtHeading As String
Dim rst As Recordset
Set rst = Me.FRmQ_OrderItemSUB.Form.Recordset
Me.OrderMainText = Me.MainQtext
Me.DocumentsInc = Me.DocsIncTxt

    If Me.OrderType = "Specification" Then
        Me.PONo.Visible = True
        Me.SpecNo.Visible = True
        Me.OrderPrintDate.Visible = True
      
        If Me.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
            Do While Not rst.EOF
                TmpTxtHeading = Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading
                NewTxtHeading = Replace(TmpTxtHeading, "OPTIONS AVAILABLE:", "OPTIONS DECLINED:")
                Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading = NewTxtHeading
            rst.MoveNext
            Loop
        End If
    End If

    If Me.OrderType = "Quotation" Then
        Me.PONo.Visible = False
        Me.PONo.Value = Null
        Me.SpecNo.Visible = False
        Me.SpecNo.Value = ""
        Me.OrderPrintDate.Visible = False
        Me.OrderPrintDate.Value = ""
            If Me.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
                Do While Not rst.EOF
                    TmpTxtHeading = Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading
                    NewTxtHeading = Replace(TmpTxtHeading, "OPTIONS DECLINED:", "OPTIONS AVAILABLE:")
                    Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading = NewTxtHeading
                rst.MoveNext
                Loop
            End If
    
    End If

End Sub

It's getting stuck on the "rst.MoveNext" lines (both) and im not sure why. the error that it throws up is "invalid object or no longer set".
Its doing what its suppoost to do on the 1st line of the subform and then stops, any help would be appreciated.

 

[Sodslaw]

Maybe its the After update function??

to get it working i tested the code on a button and it worked so i dont know what the issue is...

Private Sub Command185_Click()
Dim TmpTxtHeading As String
Dim NewTxtHeading As String
Dim rst As Recordset

        If Me.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
            Set rst = Me.FRmQ_OrderItemSUB.Form.Recordset
            Do While Not rst.EOF
                TmpTxtHeading = Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading
                NewTxtHeading = Replace(TmpTxtHeading, "OPTIONS AVAILABLE:", "OPTIONS DECLINED:")
                Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading = NewTxtHeading
            rst.MoveNext
            Loop

        End If
End Sub

Via button click event its working any clues?

 

[theDBguy]

Only way to be sure is to step through your code and examine all the values to make sure they are what you expect.

 

[SHANEMAC51]

Maybe its the After update function??

to get it working i tested the code on a button and it worked so i dont know what the issue is...

without an example of the base, I didn't really understand what you want to do

Private Sub OrderType_AfterUpdate()
Dim TmpTxtHeading As String
Dim NewTxtHeading As String
dim s1                      ''''''''''''''''correct
Dim rst As dao.Recordset    ''''''''''''''''
Set rst = Me.FRmQ_OrderItemSUB.Form.Recordset

 If Me.OrderType = "Specification" Then
  Me.PONo.Visible = True
  Me.SpecNo.Visible = True
  Me.OrderPrintDate.Visible = True
    s1=Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading
  '''If Me.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
   Do While Not rst.EOF
    TmpTxtHeading = s1 ''''''''''''''''''''
    NewTxtHeading = Replace(TmpTxtHeading, "OPTIONS AVAILABLE:", "OPTIONS DECLINED:")
    rst.edit                     '''''''''''''''''''
    rst!OrderItem_Spec_Heading = NewTxtHeading ''''''''''''
    rst.update                   ''''''''''''''''''
    rst.MoveNext
   Loop
  '''End If
 End If

 If Me.OrderType = "Quotation" Then
  Me.PONo.Visible = False
  Me.PONo.Value = Null
  Me.SpecNo.Visible = False
  Me.SpecNo.Value = ""
  Me.OrderPrintDate.Visible = False
  Me.OrderPrintDate.Value = ""
  s1=Me.FRmQ_OrderItemSUB.Form!OrderItem_Spec_Heading
   ''''If Me.[FRmQ_OrderItemSUB].Form.RecordsetClone.RecordCount > 0 Then
    Do While Not rst.EOF
     TmpTxtHeading = s1                ''''''''''''''''''
     NewTxtHeading = Replace(TmpTxtHeading, "OPTIONS DECLINED:", "OPTIONS AVAILABLE:")
     rst.edit        ''''''''''''''''''
     rst!OrderItem_Spec_Heading = NewTxtHeading    ''''''''''''''''''
     rst.update   ''''''''''''''''''
     rst.MoveNext
    Loop
   ''''End If
 
 End If

End Sub

 

[Sodslaw]

Thanks for the Replys. I discovered that the OrderType_control as its a combo box, refreshes all the tables in the subform during afterupdate event. (i cant figure why its doing it, but it does no code is doing this) so when it tries to...

Replace(TmpTxtHeading, "OPTIONS DECLINED:", "OPTIONS AVAILABLE:")

... it bombs out half way through.
So rather than triggering the code via the afterupdate event, it works on a lost focus event.

 

[Pat Hartman]

You can't run this type of code in an event where Access is in the middle of updating the record. That is why you were having problems. You have a much larger problem though. Having to update all records in a recordset because you changed one of them screams "DESIGN FLAW". You should probably rethink your table schema and move the data you are updating into the parent table.

 

[Sodslaw]

Hi Pat, thanks for pointing that out.
I had the Ordertype table in the query (hense tables refreshing on this OrderType_AfterUpdate() ) so i removed it and used the Dlookup function to replace it.

Its all fine now thanks for the tip

 

[Pat Hartman]

Getting the query to work is great but doesn't resolve your design flaw which is the reason you need to run the bulk update to begin with. But if you're happy, I'm happy.

 

[Sodslaw]

Bulk update is a find replace function for rich text field, around 200 charachters long, of which formating is critical. in the text there is one insrtance where there is text = OPTIONS AVAILABLE: which needs to change to OPTIONS DECLINED: before the record becomes locked, the rest of the text remains the same. But i know what you mean, eventually this long text will be spit into individual components.

 

[Pat Hartman]

If the text occurs in every record, it is in the wrong table.