Recordset Help Needed

[mattc66 via AccessMonster.com]

I have the below code that works some of the times, but not all. Here is my
issue. If there is no records in the table it works the first time. Then when
I go to add another record it says "No Current Record". If I close the form
and then re-open it, it work. I thought by adding the Set rs = Nothing it may
help. It did seem to do anything.


Private Sub TRAN_TYPE_DblClick(Cancel As Integer)
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

'Save any edits
If Me.Dirty Then
Me.Dirty = False
End If

'Check there is an order in the form.
If Me.NewRecord Then
MsgBox "Enter/select the order you wish to add a row to."
Else

'Append new record to the subform.
Set rs = Forms![frmOR_POInfo_C1]![frmMRPPODetail].Form.RecordsetClone
Set rs1 = Forms![frmOR_POInfo_C1]![frmWO_Cut].Form.RecordsetClone

rs1.AddNew
rs1![ASSY_ITEM] = rs![M-PO-PART-NUM]
rs1![Type] = rs![M-PO-TRAN-TYPE]
rs1![JOB_CMP_DATE] = rs![M-PO-DATE-DUE]
rs1![QTY] = rs![SumOfM-PO-QTY]
rs1.Update
Set rs = Nothing

End If

Set rs = Nothing
Set rs1 = Nothing

End Sub

 

[Ken Snell \(MVP\)]

You don't tell the rs recordset which record it's to use? When you set an
object to the RecordsetClone, you need to move it to the correct record so
that you're sure it's on the right record.

As for the error, it sounds to me as if you have a Form_BeforeUpdate event
procedure that may not be letting the form do what you want. This error can
be tricky to track down, but look at your other event procedures' code and
walk through the code in Break mode to see when you "lose" the record.

 

[mattc66 via AccessMonster.com]

Thank you Ken.

I may have not been clear on my failure. The code is working except when I go
to add a second record. It's as if the Record set is blank. So what I have to
do is exit the form and then go back in to work again.

Matt

You don't tell the rs recordset which record it's to use? When you set an
object to the RecordsetClone, you need to move it to the correct record so
that you're sure it's on the right record.

As for the error, it sounds to me as if you have a Form_BeforeUpdate event
procedure that may not be letting the form do what you want. This error can
be tricky to track down, but look at your other event procedures' code and
walk through the code in Break mode to see when you "lose" the record.

I have the below code that works some of the times, but not all. Here is my
issue. If there is no records in the table it works the first time. Then

[quoted text clipped - 37 lines]

End Sub

 

[Ken Snell \(MVP\)]

Do you have the LinkChildFields and LinkMasterFields properties filled in
for the subform control holding the form into which you're inserting the new
record? If not, does your code insert values for the foreign key field(s)
that are needed for linking back to main form?

Have you tried inserting this line at the end of your code inside the If
EndIf block that inserts the code:
Forms![frmOR_POInfo_C1]![frmWO_Cut].Form.Recordset.MoveLast

--

Ken Snell
<MS ACCESS MVP>

Thank you Ken.

I may have not been clear on my failure. The code is working except when I
go
to add a second record. It's as if the Record set is blank. So what I have
to
do is exit the form and then go back in to work again.

Matt

You don't tell the rs recordset which record it's to use? When you set an
object to the RecordsetClone, you need to move it to the correct record so
that you're sure it's on the right record.

As for the error, it sounds to me as if you have a Form_BeforeUpdate event
procedure that may not be letting the form do what you want. This error
can
be tricky to track down, but look at your other event procedures' code and
walk through the code in Break mode to see when you "lose" the record.

I have the below code that works some of the times, but not all. Here is
my
issue. If there is no records in the table it works the first time. Then

[quoted text clipped - 37 lines]

End Sub

 

[mattc66 via AccessMonster.com]

Thanks I got it to work.

Do you have the LinkChildFields and LinkMasterFields properties filled in
for the subform control holding the form into which you're inserting the new
record? If not, does your code insert values for the foreign key field(s)
that are needed for linking back to main form?

Have you tried inserting this line at the end of your code inside the If
EndIf block that inserts the code:
Forms![frmOR_POInfo_C1]![frmWO_Cut].Form.Recordset.MoveLast

Thank you Ken.

[quoted text clipped - 21 lines]