Main Form with 3 Sub Forms.

[NotAnExpert]

Hi, I have a mainform, and the mainform has 3 subforms on it. For the sake of this exercise I will call them Subform1, Subform2 and Subform3.

Part 1
On the mainform, when I type an ordernumber into the search box, a query shows records with items on that order on Subform1. This works perfectly fine.
When I double click on a record on Subform1, details of that record are added to a new record on Subform2. This also works perfectly fine.
Part 2
On the mainform, I have another area where I can search for a materials, a query shows records matching the search in Subform3. This also works perfectly fine.
When I double click on a record on Subform3, the MaterialID value should be passed to a field on the selected record in Subform2. This DOESN'T work perfectly fine.

I am faced with Run-time error '3021': No current record. This leads me to believe there is a problem with the .edit part of the recordset.

What I do find however is that if I close the form and go back in, where the record on Subform2 created in Part1 already exists and is saved, double clicking a record on Subform3 (as per Part2) works as intended.

So my question is...

What route should I take ideally to make this work properly? I know i'm missing something, but i'm not sure what...

 

[Gasman]

I suspect you need to find the record in subform 2 first?
Perhaps store the ID of subform2 record in a Tempvar, to use from subform3.

You are not even showing any code , so hard to guess how you are doing it, other than using a recordset.

 

[NotAnExpert]

Hi, thank you for coming back Gasman. I appreciate the input.
This is one of those cases where 'you don't know what you don't know'. I had considered finding the ID of the new record in Subform2 but in my case, the record to update from Subform3 should always be the last record.

This is the doubleclick event on Subform1

Private Sub Form_DblClick(Cancel As Integer)
    With Me.Parent.SubForm2.Form.Recordset
        .AddNew
        !OrderNo = Me.Parent!OrderNo
        !ItemNumber = Me!ItemNumber
        !ItemQty = Me!ItemQty
        !ItemDetails = Me!ItemDetails
        .Update
    End With
End Sub

This is the doubleclick event on Subform3

Private Sub Form_DblClick(Cancel As Integer)
    With Me.Parent.SubForm2.Form.Recordset
        .Edit
        !BatchID = Me!BatchID
        .Update
    End With
End Sub

 

[MarkK]

Try...

Private Sub Form_DblClick(Cancel As Integer)
    With Me.Parent.SubForm2.Form.Recordset
        .AddNew
        !OrderNo = Me.Parent!OrderNo
        !ItemNumber = Me!ItemNumber
        !ItemQty = Me!ItemQty
        !ItemDetails = Me!ItemDetails
        .Update
        .Bookmark = .LastModified
    End With
End Sub

There is no guarantee after you run Recordset.Update that the row you just added remains current. Force it back to the last modified row using the .Bookmark and see if that makes a difference.

 

[NotAnExpert]

Try...

Private Sub Form_DblClick(Cancel As Integer)
    With Me.Parent.SubForm2.Form.Recordset
        .AddNew
        !OrderNo = Me.Parent!OrderNo
        !ItemNumber = Me!ItemNumber
        !ItemQty = Me!ItemQty
        !ItemDetails = Me!ItemDetails
        .Update
        .Bookmark = .LastModified
    End With
End Sub

There is no guarantee after you run Recordset.Update that the row you just added remains current. Force it back to the last modified row using the .Bookmark and see if that makes a difference.

Thank you for this. I will give it a try now.

 

[Gasman]

Note subform2 (whatever it is called) is the subform control name, NOT the subform name. They might be the same though. I always rename my subform control as ctrlSubFormName

 

[Pat Hartman]

Do you have any idea what record you are updating when you have sub3 update sub2? I question the logic of this process. Each subform has a Current record. The current record defaults to the first record in the recordset when the form is opened. If you move it, the pointer changes to a different record. How can sub3 know with certainty what record it wants to update, let alone what record is actually current?