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

Bob Alston

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

Bob Alston

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

Bob Alston

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

NeilAnderson

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

Bas Cost Budde

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