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

Detect when bound field is updated on subform

P: n/a
Hi All,
Hopefully I explain this well........

In Access97 I have a form which has a tab control on it, each tab has a
sub-form which is bound to its appropriate table(I'll call them
'Detail' tables). I have a 'Header' table and fields from it are
displayed on the form above the tab control.
Header table name is tblClientInfo
Detail tables are tblPolicyInfo, tblBeneInfo, tblRiderInfo

In the tblClientInfo I have a TimeStamp field which I want to append to
when a user updates anything on each of the tabs so, how do I detect
that something was updated on the Policy Info tab and then append a
TimeStamp (ex: UserID updated Policy information on 01/19/05 at 2:14pm)
to the TimeStamp field in the tblClientInfo table. And then if the user
clicks on the Riders tab and updated something do the same except sub
"Rider information" for "Policy information".
I'm not sure how to detect if anything on the subform for the current
active tab has been updated, what's the vba coding to do this?
thanks for any and all help
bobh.

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


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Usually, "time stamps" (I call them audit columns) are placed in the
table that is updated/appended. The table usually has 3 audit columns:
UserID, DateEdited, DateAdded. To update those columns I use the bound
form's Before Update event. E.g. (assumes User Security is operable):

Private Sub Form_BeforeUpdate(Cancel As Integer)

Me.UserID = CurrentUser()
Me.DateEdited = Date()
If Me.NewRecord = True Then Me.DateAdded = Date()

End Sub

Be careful using the word TIMESTAMP as a column identifier. In SQL
Server it refers to a data type that is not a time indicator, but a row
version number, used for internal validation.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQe64OoechKqOuFEgEQJzawCfb5WlAxyyCCb+zFczhvD7Nl PfShEAoMnM
THCyRylYMfiaD3HQfEouKBuC
=S5Bi
-----END PGP SIGNATURE-----
vu******@isp.com wrote:
Hi All,
Hopefully I explain this well........

In Access97 I have a form which has a tab control on it, each tab has a
sub-form which is bound to its appropriate table(I'll call them
'Detail' tables). I have a 'Header' table and fields from it are
displayed on the form above the tab control.
Header table name is tblClientInfo
Detail tables are tblPolicyInfo, tblBeneInfo, tblRiderInfo

In the tblClientInfo I have a TimeStamp field which I want to append to
when a user updates anything on each of the tabs so, how do I detect
that something was updated on the Policy Info tab and then append a
TimeStamp (ex: UserID updated Policy information on 01/19/05 at 2:14pm)
to the TimeStamp field in the tblClientInfo table. And then if the user
clicks on the Riders tab and updated something do the same except sub
"Rider information" for "Policy information".
I'm not sure how to detect if anything on the subform for the current
active tab has been updated, what's the vba coding to do this?
thanks for any and all help
bobh.

Nov 13 '05 #2

P: n/a
Lookup the Me.Dirty feature in Access, it will tell you when a form has
changed!

HTH
Michael
Nov 13 '05 #3

P: n/a
Well, that's what I was trying and could not get it to work hance a
note here.
I tried

If Me!subformname.Forms.Dirty then
'do my coding
end if

in the On Exit event of the subform but it never got kicked-off so I
then tried the After Update event which got kicked off but the 'IF'
statement never evaluated to true. And alll the other events I tried
the 'IF' statement would not evaluate to true even though I did change
data in several text fields on the subform.
bobh.

Nov 13 '05 #4

P: n/a
Ok let me try again to describe it what I'm looking to do.

I have a form called 'Update Accounts' the form has unbound text boxes
which get populated via a combo box from a table called tblClientInfo.
this form has a Tab control which has four(4) tabs
on each tab there is a form on it(which I call a subform which is a
continuos form)
each subform has text boxes that are bound to different tables so,
tab label Policy Info has form on it called sbfrmPolicyInfo which is
bound to table tblPolicyInfo,
tab label Beneficiary Info has a form on it called sbfrmBeneInfo which
is bound to tblBeneInfo,
tab label Rider Info has a form on it called sbfrmRiderInfo which is
bound to tblRiderInfo,
tab label Acct Summary has a form on it called sbfrmAcctSum and is tied
to a union query

tblPolicyInfo, tblBeneInfo, tblRiderInfo are related via a field I call
CID, it is the unique identifier

When a user clicks on the Policy Info tab on the loaded form called
'Update Accounts' they would see the subforms text boxes filled with
information about that clients pollicies
So, the user changes several pieces of information on the Policy
subform and then clicks on the tab named Beneficiary Info
Now, I want to detect that data was changed on the Policy Info
tab/subform and run some vba code that would open the tblClientInfo
which contains the CID as well, and append a line of text to the field
called TmeStmp in tblClientInfo who's datatype is Memo.

To do this I was hoping to use the 'Dirty' property but have not been
able to get it to work so far nor have I been able to find the right
event but,

I was hoping for something like
in the On Exit event of the tab labeled Policy Info have vba code

If me!sbfrmPolicyInfo.Forms.Dirty then
dim strTS as String
and of course all the other dim's
and of course all the other vba statements to open a dynaset
strTS = me!userID & " updated Policy information on " & Date() & "
at " & Time()
strSrch = "[cid]=" & me!bxCidNbr
MyRec.FindFirst strSrch
MyREc.Edit
MyRec!TmeStmp = MyRec!TmeStmp & vbcrlf & strTS
MyRec.Update
end if

What is the correct syntax for using the 'dirty' properity for a
subform in a tab control
any ideas on this?
thanks
bobh.

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.