Editing records from recordsetclone

[Anthony Kroes]

I have a subform on a form and they are not linked. On the main form is
a text box where the user types in a number. When that number changes,
I have some code to make the corresponding text field in the subform
default its value so that the next record created uses the value from
the box on the main form. So far so good - works fine.

What I also need to do at the same time is change all *existing* records
on the subform to that same value. I am trying to do this via editing
the recordsetclone, but nothing seems to change - how do I write changes
made to that to the real recordset? Existing code listed below.

Thanks,

Private Sub txtMarks_AfterU pdate()

Me.Subfrm_Produ ctionData!txtMa rks.DefaultValu e = Me.txtMarks

Dim db As Database
Dim rst As Recordset
Set rst = Me.RecordsetClo ne
With rst
.MoveFirst
Do While Not .EOF
.Edit
!Marks = Me.txtMarks
.Update
.MoveNext
Loop
End With

Me.Subfrm_Produ ctionData.Reque ry

End Sub


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[PC Datasheet]

Re: Editing records from recordsetclone

Anthony,

The key is to have a way to determine which records are "Existing" records on
the subform. Once you are able to do that, then you can use an Update query to
change the "existing" records to the same value.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Anthony Kroes" <tkroes@baytowe l.com> wrote in message
news:408ebfbf$0 $202$75868355@n ews.frii.net...[color=blue]
>
> I have a subform on a form and they are not linked. On the main form is
> a text box where the user types in a number. When that number changes,
> I have some code to make the corresponding text field in the subform
> default its value so that the next record created uses the value from
> the box on the main form. So far so good - works fine.
>
> What I also need to do at the same time is change all *existing* records
> on the subform to that same value. I am trying to do this via editing
> the recordsetclone, but nothing seems to change - how do I write changes
> made to that to the real recordset? Existing code listed below.
>
> Thanks,
>
> Private Sub txtMarks_AfterU pdate()
>
> Me.Subfrm_Produ ctionData!txtMa rks.DefaultValu e = Me.txtMarks
>
> Dim db As Database
> Dim rst As Recordset
> Set rst = Me.RecordsetClo ne
> With rst
> .MoveFirst
> Do While Not .EOF
> .Edit
> !Marks = Me.txtMarks
> .Update
> .MoveNext
> Loop
> End With
>
> Me.Subfrm_Produ ctionData.Reque ry
>
> End Sub
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Don Leverton]

Re: Editing records from recordsetclone

Hi Anthony,

I think your problem is that you are attempting to edit the wrong
recordset...
Me.RecordsetClo ne is for the main form's recordset, not the subform's...

Try it like this:

Private Sub txtMarks_AfterU pdate()

' Me.Subfrm_Produ ctionData!txtMa rks.DefaultValu e = Me.txtMarks ' You don't
need this

' Dim db As Database ' You don't need this either

Dim rst As DAO.Recordset
Set rst = Me![Subfrm_Producti onData].Form.Recordset Clone

With rst
.MoveLast
.MoveFirst

Do While Not .EOF ' Hmmm.... This code will run until it hits the last
record...
.Edit
!Marks = Me.txtMarks
.Update

If Not .EOF Then 'Without this, won't it respond with a "No
Current Record" error?
.MoveNext
End If
Loop

..Close
End With

Me.Subfrm_Produ ctionData.Reque ry
Set rst = Nothing
End Sub


--
HTH,
Don
=============== ==============
Use My.Name@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

=============== =============== ==




"Anthony Kroes" <tkroes@baytowe l.com> wrote in message
news:408ebfbf$0 $202$75868355@n ews.frii.net...[color=blue]
>
> I have a subform on a form and they are not linked. On the main form is
> a text box where the user types in a number. When that number changes,
> I have some code to make the corresponding text field in the subform
> default its value so that the next record created uses the value from
> the box on the main form. So far so good - works fine.
>
> What I also need to do at the same time is change all *existing* records
> on the subform to that same value. I am trying to do this via editing
> the recordsetclone, but nothing seems to change - how do I write changes
> made to that to the real recordset? Existing code listed below.
>
> Thanks,
>
> Private Sub txtMarks_AfterU pdate()
>
> Me.Subfrm_Produ ctionData!txtMa rks.DefaultValu e = Me.txtMarks
>
> Dim db As Database
> Dim rst As Recordset
> Set rst = Me.RecordsetClo ne
> With rst
> .MoveFirst
> Do While Not .EOF
> .Edit
> !Marks = Me.txtMarks
> .Update
> .MoveNext
> Loop
> End With
>
> Me.Subfrm_Produ ctionData.Reque ry
>
> End Sub
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Anthony Kroes]

Re: Editing records from recordsetclone


Thanks for the replies - as Don pointed out, the problem is (was!) that
I was using the recordsetclone of the main form when I should have been
using the one from the subform. Stupid oversight, but I was focused on
the code, not the bigger picture.

Thanks for the help and all the suggestions. You have a great forum
here!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!