update through vba new record, currently opened in a form

Question

I have a question regarding updating new record through VBA. First - the assumptions. I've made a form called "assortment", that displays a set of records and a sobreport that shows related invntory. I've put the button on it: "Add new record". This opens the second form "inventory_details" that is intended to enter and view the inventory spcific data. That inventory is of that specific assortment type. So - I've passed the assortment_id to the inventory_details form through the DoCmd.OpenForm like:

    DoCmd.OpenForm stFormName, , , , acFormAdd, , Me.assortment_id

The data source of the "inventory_details" form is a query that contains the assortment table joined to the inventory table by the inventory_id. What is the best way to add this id passed by an OpenArgs to the currently opened new record and refresh the form to show related assortment data? I was trying to do this like:

    Private Sub Form_Open(Cancel As Integer)
        Dim assortmentId As Integer
        If (Not IsNull(Me.OpenArgs)) Then
            assortmentId = Me.OpenArgs()
            Set rst = Me.Recordset
            rst.Edit
            rst.assortment_id_assortment = assortmentId
            Me.Requery
        End If
    End Sub

but it gives me an error "3021 " No current record"...

Ravi Yenugu Ravi Yenugu · Accepted Answer · 2014-06-27T15:26:04

Here are some suggestions,

. Make sure you define the variable rst

. Check for EOF Condition to make sure your recordset contains rows

If rst.EOF Then
    MsgBox "The Recordset is empty."
End If

. Why clone an MS-Access recordset?

update through vba new record, currently opened in a form

1 Answers