Question No current recordset / canceled by an associated object

[whisp]

Hi!

My form respectively subform has a couple of problems related to the recordset as it is available in VBA.

The mainform contains material data, the subform contains the components of that material and a quantity, while the components are materials themselves. The subform's control source is an SQL statement created by the query builder.

Everything worked fine before i replaced the material-selecting combobox in the continuous subform by a textbox and a button. That button leads to another (dialog) form with some filtering options, which in turn returns the number of the selected material. This material gets inserted into the textbox. To this point it works fine.

But when i enter a quantity before i selected a Material, i get an error message after selecting the Material: This Action was Cancelled by an Associated Object. This happens while executing the following VBA Code on click of the material selection button (exact position commented in code):

Private Sub cbuSelectComponent_Click()
    ' Select component
    Dim SQL As String
    Dim rs As Recordset
    
    DoCmd.OpenForm "Material Selector Dialog", , , , , acDialog, "Dialog"
    If GLB_selected_mat = -1 Then 'cancel
        Exit Sub
    End If
    
    If GLB_selected_mat = -100 Then 'delete
        Set rs = Me.Form.Recordset
        rs.Delete
        Exit Sub
    End If
    
    Set rs = Me.Form.Recordset
    
    If Me.Form.NewRecord Then
        rs.AddNew '*** Here the error message pops up, but only when quantity set before material ***
        rs![Parent Component] = Forms![Materials].[Material ID]
        rs![Materials Components.Sub Component] = GLB_selected_mat
        rs.Update
    Else
        rs.Edit
        rs![Materials Components.Sub Component] = GLB_selected_mat
        rs.Update
    End If
    
    Me.Parent.Refresh
End Sub

I've found the following Microsoft KB Article: support.microsoft.com/kb/189851/en-us (sorry, forum doesn't allow me to post links yet). It didn't help, yet i didn't understand everything.

In their example code they use:

' Restore text box value to the original record contents 
' in this case, that is NULL 
datDataCtl.UpdateControls

I assume this is the relevant part, but i have no DataControl (what's that?) and neither found an UpdateControls method in the subform object.

The second error, "no current recordset", occures when i edit an existing component line in the subform that has been added right before (also using the same event and code as mentioned above). If i close the form after adding the component and open it again, it's no problem.

Thanks for any hints!

whisp

 

[MarkK]

Hello there and welcome to the forum.

First, you don't need this construction ...

Me.Form.Recordset

... Me, in this case, is the form, so you don't need the form property. Instead, just use ...

Me.Recordset

Now, looking closely at this code, which I've amended as per the above ...

    Set rs = Me.Recordset
    If Me.NewRecord Then
        rs.AddNew

... NewRecord is true, so form's recordset is already on a new record. Then you call AddNew on the same recordset. You can only have one currently new record.

Maybe this makes sense?

Private Sub cbuSelectComponent_Click()
    DoCmd.OpenForm "Material Selector Dialog", , , , , acDialog, "Dialog"
    Select Case GLB_selected_mat
        Case -1
        Case -100
            Me.Recordset.Delete
        Case Else
            If Me.NewRecord Then Me.[Parent Component] = Forms![Materials].[Material ID]
            Me.[Materials Components.Sub Component] = GLB_selected_mat
    End Select
    Me.Parent.Refresh
End Sub

I think that does all the same work as your code, but avoids duplication and uses the form's recordset directly. Keep in mind that all the fields in the form's recordset are also members of the form. And the form can handle all the addnews and edits and updates on its own recordset too.

 

[whisp]

Hey!

That way it works like a charm and i know what's going on again. Thanks for the quick, awesome, all-problems-solving answer!

whisp