By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,574 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Editing records from recordsetclone

P: n/a

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_AfterUpdate()

Me.Subfrm_ProductionData!txtMarks.DefaultValue = Me.txtMarks

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

Me.Subfrm_ProductionData.Requery

End Sub
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
re******@pcdatasheet.com
www.pcdatasheet.com
"Anthony Kroes" <tk****@baytowel.com> wrote in message
news:40*********************@news.frii.net...

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_AfterUpdate()

Me.Subfrm_ProductionData!txtMarks.DefaultValue = Me.txtMarks

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

Me.Subfrm_ProductionData.Requery

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

Nov 12 '05 #2

P: n/a
Hi Anthony,

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

Try it like this:

Private Sub txtMarks_AfterUpdate()

' Me.Subfrm_ProductionData!txtMarks.DefaultValue = 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_ProductionData].Form.RecordsetClone

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_ProductionData.Requery
Set rst = Nothing
End Sub
--
HTH,
Don
=============================
Use My*****@Telus.Net 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" <tk****@baytowel.com> wrote in message
news:40*********************@news.frii.net...

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_AfterUpdate()

Me.Subfrm_ProductionData!txtMarks.DefaultValue = Me.txtMarks

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

Me.Subfrm_ProductionData.Requery

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

Nov 12 '05 #3

P: n/a

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!
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.