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

Automatic updating record from VBA with data not displayed on a form

P: n/a
I have a subform with multiple records tied to a main record and main
form. In the subform I create and update the subform records. However,
there are other pieces of data, statistics, I want to add automatically
to the record when it is saved. I can access the data via VBA code in a
"before update" event. Any way to address the record itself and all
fields - even fields not on the form?

So far I figure I could
1) put all the statistics fields on the subform layout, hidden and
protected, all bunched on top of each other (15 fields). Might make
debugging difficult.

2) Instead use the after update event. Get the record key, an
autonumbered field, after the update and use VBA code to read the
dynaset and update the record.

Other suggestions?

Bob Alston
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Bob Alston wrote:
I have a subform with multiple records tied to a main record and main
form. In the subform I create and update the subform records. However,
there are other pieces of data, statistics, I want to add automatically
to the record when it is saved. I can access the data via VBA code in a
"before update" event. Any way to address the record itself and all
fields - even fields not on the form?

So far I figure I could
1) put all the statistics fields on the subform layout, hidden and
protected, all bunched on top of each other (15 fields). Might make
debugging difficult.

2) Instead use the after update event. Get the record key, an
autonumbered field, after the update and use VBA code to read the
dynaset and update the record.

Other suggestions?

Bob Alston

It appears that using the Recordsetclone capability is what I need. But
how to get the bookmark of the record being updated or just updated?

Bob
Nov 13 '05 #2

P: n/a
Bob Alston wrote:
Bob Alston wrote:
I have a subform with multiple records tied to a main record and main
form. In the subform I create and update the subform records.
However, there are other pieces of data, statistics, I want to add
automatically to the record when it is saved. I can access the data
via VBA code in a "before update" event. Any way to address the
record itself and all fields - even fields not on the form?

So far I figure I could
1) put all the statistics fields on the subform layout, hidden and
protected, all bunched on top of each other (15 fields). Might make
debugging difficult.

2) Instead use the after update event. Get the record key, an
autonumbered field, after the update and use VBA code to read the
dynaset and update the record.

Other suggestions?

Bob Alston


It appears that using the Recordsetclone capability is what I need. But
how to get the bookmark of the record being updated or just updated?

Bob

More - I think I am close. Not sure if the bookmark property for "me"
is valid????

With Me.RecordsetClone

..Bookmark = Me.Bookmark
..Edit
..Fields("stat01") = 111

..Update
End With

Bob Alston
Nov 13 '05 #3

P: n/a
Bob

The data doesn't need to actually be on the form, only in the form's
underlying recordsource.

Neil

Bob Alston <tu****************@cox.net> wrote in message news:<g2Qqd.61547$_g6.16902@okepread03>...
Bob Alston wrote:
I have a subform with multiple records tied to a main record and main
form. In the subform I create and update the subform records. However,
there are other pieces of data, statistics, I want to add automatically
to the record when it is saved. I can access the data via VBA code in a
"before update" event. Any way to address the record itself and all
fields - even fields not on the form?

So far I figure I could
1) put all the statistics fields on the subform layout, hidden and
protected, all bunched on top of each other (15 fields). Might make
debugging difficult.

2) Instead use the after update event. Get the record key, an
autonumbered field, after the update and use VBA code to read the
dynaset and update the record.

Other suggestions?

Bob Alston

It appears that using the Recordsetclone capability is what I need. But
how to get the bookmark of the record being updated or just updated?

Bob

Nov 13 '05 #4

P: n/a
NeilAnderson wrote:
Bob

The data doesn't need to actually be on the form, only in the form's
underlying recordsource.


Which reminds me to add that this is not the case for reports. (or is it?)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.