470,624 Members | 2,476 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,624 developers. It's quick & easy.

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

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
4 1929
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 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
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
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.

Similar topics

11 posts views Thread by Jason | last post: by
3 posts views Thread by Steven Stewart | last post: by
2 posts views Thread by Jon S via DotNetMonster.com | last post: by
1 post views Thread by Spock | last post: by
1 post views Thread by rdemyan via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.