Detect when bound field is updated on subform

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.

re: Detect when bound field is updated on subform

-----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-----


vulcaned@isp.com wrote:[color=blue]
> 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.
>[/color]

re: Detect when bound field is updated on subform

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

HTH
Michael

re: Detect when bound field is updated on subform

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.

re: Detect when bound field is updated on subform

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.