Copy from 1 subform to another

[amywolfie]

This is an existing topic. A good solution is given, but one line of code doesn't work:

rst2!Field2Update = Me.SF1Field

It has to do with Update.

What is the proper VBA syntax to update a field as shown above?

Thanks!

[NeoPa]

AmyWolfie:
This is an existing topic.

Please provide a link to the topic so that we can make sense of this question.

[amywolfie]

Yes, sorry. The original topic is at:
http://bytes.com/topic/access/answer...ubform-another

[NeoPa]

The syntax seems fine to me. What problem are we trying to make sense of here for you?

I suspect we'll need to see your code, and your error message, with your line number of where the error occurs. For more details of how to post code so that we can work with it more easily see Before Posting (VBA or SQL) Code.

[amywolfie]

Here is my code:

Private Sub PrimaryDataBatchID_AfterUpdate()
On Error GoTo Error_Handler:
 
 'Appends DataBatchID to OK_DataBatchID since usually the same -- AW
 
Dim rst2 As Recordset
Dim rst1 As Recordset
 
Set rst2 = Forms![frmMain]![subfrm_tblOverkeyDataBatchID].Form.RecordsetClone
Set rst1 = Forms![frmMain]![subfrmgd_tblPrimaryDataBatch].Form.RecordsetClone
 
'find the current record
rst1.FindFirst "fk_OK = " & Me.fk_OK
'"SF1ID = " & Me.SF1ID
 
rst2.MoveFirst
Do While Not rst2.EOF
If rst2.AbsolutePosition = rst1.AbsolutePosition Then
rst2.Edit
rst2!fk_OK2Update = Me.fk_OK
 
'rst2!Field2Update = Me.SF1Field
rst2.Update
Exit Do
Else
rst2.MoveNext
End If
Loop
rst1.Close
rst2.Close
Set rst1 = Nothing
Set rst1 = Nothing
 
Exit Sub
 
Error_Handler:
    MsgBox Err.Number & " " & Err.Description
 
End Sub

====

Line #20 : Error 3265 Item not found in this collection

If I remove the 'Update' I get no error, but nothing happens either.

Thanks!

Thanks.

[NeoPa]

rst2!fk_OK2Update = Me.fk_OK

As Me.fk_OK is referred to (presumably) without problems on line #13, I would say this idicates that rst2!fk_OK2Update is somehow an invalid reference. I suspect fk_OK2Update is not the name of a field in that recordset. The confusion may be that it's the name of a control on the form which the recordset drives perhaps?

[hjozinovic]

Amy,
you are having problem with using RecordsetClone property.
This property is Read-only. That is why you can't update your data.
You should use Recordset property instead. Try this:

Set rst2 = Forms![frmMain]![subfrm_tblOverkeyDataBatchID].Form.Recordset
Set rst1 = Forms![frmMain]![subfrmgd_tblPrimaryDataBatch].Form.Recordset
 

[NeoPa]

hjozinovic:
you are having problem with using RecordsetClone property.
This property is Read-only. That is why you can't update your data.

Are you saying that the RecordsetClone recordset is actually non-updatable?
The property being Read-Only would not effect the ability to update data via the dataset of course ;-) I suspect it's just confusion of terminology.

[amywolfie]

The actual field name is fk_Ok2

The original code on this topic read:

SB2Key2Update

So I added "Update" to the field name. When I remove, the code runs, but no updates actually happen.

Thanks.

[NeoPa]

Regardless of the original code, the name in your code must match the name of the field in your project, not the name of someone-else's.

NB. Make sure you understand the difference between the terms Field and Control. It's no good using the name of the control to try to refer to the field.

hjozinovic:
You should use Recordset property instead.

Have you tried the suggestion from post #7? If so, what did you find.

[hjozinovic]

@NeoPa
I had problems simulating this situation because Access kept crashing down. I looked up the explanation in Help and here is what I found:
"You can use the RecordsetClone property to refer to a form's Recordset object specified by the form's RecordSource property. Read-only.

Remarks
The RecordsetClone property setting is a copy of the underlying query or table specified by the form's RecordSource property. If a form is based on a query, for example, referring to the RecordsetClone property is the equivalent of cloning a Recordset object by using the same query. If you then apply a filter to the form, the Recordset object reflects the filtering.

This property is available only by using Visual Basic and is read-only in all views."

I thought the problem might be referring to RecordsetClone, but now I made test database and got the correct results using RecordsetClone. So....I was wrong in my first post.

[NeoPa]

hjozinovic:
So....I was wrong in my first post.

Nevertheless, it was good thinking, and was worth posting and delving into further.