473,322 Members | 1,504 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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.

Nov 13 '05 #1
4 2936
-----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
Lookup the Me.Dirty feature in Access, it will tell you when a form has
changed!

HTH
Michael
Nov 13 '05 #3
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: John Bailo | last post by:
I have a main form that can launch several subforms. Both the main form remains active when the subform is visible. I want to prevent the user from launching multiple copies of the subform. ...
4
by: Doslil | last post by:
I have a form which has a subform.In the main form I have only one field (this is a drop down list and has a query attached to it) which selects empno,Name from the EmployeeInformation table. ...
6
by: P | last post by:
Hi, I have a form with several controls. One of them is initialized through VB in frm_current. I am using the frm_beforeupdate event to prompt users for saving changes. How can I make it so that...
6
by: MLH | last post by:
When the vehicle entry form (frmVehicleEntryForm) first opens, the additional owner SubForm control (frmAddnlOwnrListSubForm) is enabled. You can click on it and it will accept the focus. But after...
6
by: Welie | last post by:
I am having a problem an it's driving me crazy, I hope someone can correct my technique. I can't find a pattern and don't know where to look to debug this. The problem is on a continuous form, the...
4
by: Tom_F | last post by:
To comp.databases.ms.access -- I know that the recommended way to detect an update to a form, is to use the form's "Before Update" event, and putting in VBA code like: Private Sub...
7
by: dscarbor | last post by:
I have a simple form with 4 fields, and a subform that retrieves records that are potential matches based on a query. The user will use the ID from the subform record and enter it into one of the...
3
by: kathnicole | last post by:
Hi, I am using MS Access 2007/Vista I have a field, ProductCost in the subform. i need to find the total of ProductCost and assign it to a field, Total, in the Main Form . I read an article...
1
by: bkberg05 | last post by:
Hi - I have a form called 'Order'. It has a subform called 'Order_Detail'. On the 'Order_Detail', I have many records for each order record. On the footer of the subform (it's a continuous...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.