Solved: A really cool and interesting question!

[Eric58132]

Morning all,

I've got a good one this time, promise. I'm trying to develop a system that tracks the amount of time a record is worked on. The record can be worked on from within multiple forms.
My thought was to create a second table that more or less collected timestamps.

There is record navigation on the form, so whenever a new record is visited, the "now()" function would be used to record the initial time. Upon moving to a new record or saving an update to the existing record, a second "now()" would be recorded. These two values, along with record ID and the form name would be appended to the second table.

My problem is that these form objects have a control source that ISN'T table 2.... and I don't think they should be. I merely want to collect the values within these objects upon the "save" or "next record" action, and append them to the end of table 2.

How can I do this? thank you for your help. I will be here all day and can give speedy replies to any questions that you may have.

[OBP]

Eric, you have described what you want to do perfectly. So which part don't you know how to do?
One thing that you didn't account for in your description was someone opening the record before lunch and then just moving on after lunch without actually "Working" on it. I would have thought you would only want added and Changed data to trigger your second time and the saving procedure.

[Eric58132]

I don't physically know how to append these fields into the second table because it says their control source is for the first table.

You raised an excellent point about the "before and after lunch" examples.... I'm hoping those will prove to be outliers in my data and I can filter them out of any queries at a later time.


Conceptually I know how access works, and can visualize what I want to do, but I'm 23 and fresh out of school with a non-IT business degree so I'm still just learning the details of the program and the VBA behind it.

[hansup]

There is record navigation on the form, so whenever a new record is visited, the "now()" function would be used to record the initial time. Upon moving to a new record or saving an update to the existing record, a second "now()" would be recorded. These two values, along with record ID and the form name would be appended to the second table.
You want to record the time a user spends viewing a record, whether or not any changes were made. Correct?

Can more than one person use your application at the same time?


My problem is that these form objects have a control source that ISN'T table 2.... and I don't think they should be. I merely want to collect the values within these objects upon the "save" or "next record" action, and append them to the end of table 2.
Assuming you have sorted out which events to base this on, you can set an object reference to the database, then use its Execute method for a SQL statement to INSERT or UPDATE "table 2" --- you're correct that "table 2" need not be included in your form's record source.

I'm curious about the big picture on this one. Once you've figured out how to collect your audit data, how will you use that information?

[OBP]

OK, I have created an "Audit Trail" set of VBA code that does something very similar to what you want, except it also stores the "Change" to the data in the 2nd table as well. Would you like a copy of the database to see how it works?
It should be fairly easy to adapt it to do what you want, as I can help you to do that.

[Eric58132]

i would absolutely like to see/use it. and thanks for the help!


as for the reasoning behind this procedure, the resulting data could drive a significant change in how the database itself is used. This DB is currently used by about 8 people simultaneously to try to match unpaid accounts to a name/number for billing. Accounts enter the system and are researched to try to attain a phone number for the address. From there, 1,2 or 3 phone calls are made to the number in attempt to get a name. If this fails, or the outstanding balance is too high, the account is locked (which may require multiple attempts). In terms of account locking, the DB serves as a part of dispatching.

Currently the team is extremely inefficient at researching and calling. Given the hours they work on a weekly basis, the performance is very poor. I'm trying to figure out where the bottlenecks are, how many times accounts are being scanned before actually modified/acted on, etc.

[OBP]

Eric this database is too large to post on here as an attachment so if you could let me have your email address I will email it to you.

For those who know VBA here is the main code.
Dim rs As Object, count As Integer, data() As Variant, rstable As Object, db As Object, action As String
If Forms![Selector Form]![Security Group] = "Viewer" Then
MsgBox "You do not have permission to create Records."
Me.Undo
Exit Sub
End If

Set rs = Me.RecordsetClone
fieldcount = rs.Fields.count - 1
rs.Close
Set rs = Nothing
Set db = CurrentDb
Set rstable = db.openrecordset("Action Audit Trail")
ReDim data(fieldcount, 2)
If Me.NewRecord Then
action = "Added Record"
With rstable
.AddNew
!UserID = Application.CurrentUser
!Form = Me.Name
!action = action
![Action Date/Time] = Now()
.Update
.Bookmark = .LastModified
End With
rstable.Close
Set rstable = Nothing
Exit Sub
Else
action = "Edited Record"
End If
Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
For count = 0 To fieldcount
data(count, 1) = rs(count).Name
data(count, 2) = rs(count).Value
If IsNull(data(count, 2)) Then data(count, 2) = "None"
For Each ctl In Forms(Me.Name).Controls
If ctl.ControlType = acTextBox Then
If ctl.Name = rs(count).Name And ctl.Value <> data(count, 2) Then
With rstable
.AddNew
!UserID = Application.CurrentUser
!Form = Me.Name
!FieldName = ctl.Name
!action = action
![Action Date/Time] = Now()
![Old Value] = data(count, 2)
![New Value] = ctl.Value
.Update
.Bookmark = .LastModified
MsgBox "added Record " & rs(count).Value & " becomes " & ctl.Value
End With
End If
End If
Next ctl
Next count
rs.Close
Set rs = Nothing
rstable.Close
Set rstable = Nothing

[Eric58132]

ok I've got it. as I said in the email (though I'll bring it out here for educational purposes), I'm ready to go.

[OBP]

I forgot to say that the Code above goes in the Before Update event of the Form.

[Eric58132]

Tony,

I coudln't actually get into your database. Upon opening it, I get the initial block that I am not authorized. There isn't any opportunity to enter the name "Tony". I imagine i'm simply doing something wrong, but I am unsure as to exactly what that is

[OBP]

Eric, did you use the Shortcut?
Or Join The System.MDW Workgroup as I suggested in my email?

[Eric58132]

huzzah! i'm in. I had to create a new C:/Access/Stuarts Database/ directory because it was ingrained into the startup but that obviously wasn't an issue.

will look at it after lunch.... you'll likely be gone but there's always next week.

have a good weekend everyone

[Eric58132]

before I get even further..... just gotta say.... this whole thing is very impressive and I"ll probably end up using it as a learning tool that goes far beyond what i'm trying to do now. :)

[OBP]

Glad you like it.

[Eric58132]

Happy to report that it's working! I'm pretty interested in the Login system that you have for this DB as well, because it doesn't seem to use a .mde file. I'm going to take a look at that today.

Thanks a ton for the help

[OBP]

Eric, that is Access's User level Security. You can use the Access>Main menu>Tools>Security>User Level Security Wizard to create that and it completely secures the database, but you do have to create the user accounts.

[Eric58132]

can this work in a multi-user environment, or am I forced to create .mde files? I've tried in the past but always get an error in creation. This would be a great substitute.

[OBP]

If you have Access 2000-2003 then this works very well in the Multi User environment, I wouldn't split the database, just use it as it is.
I worked for the Auto Industry and had this style on a major database that worked very well.

[Eric58132]

Ok.. got that up and going. My boss is going to love me for all of this.


One remaining issue.... is there any way to change the "On Open" properties for different groups? Our DB has currently been "locked down" with merely the 'Shift on Open' scheme to gain back-end access. With the new Logon, I can't hit shift in time. Ideally, Admins would go straight to the query/table directory, and Data Users would open directly to our pre-designed switchboard. Is there a location to do this?

[OBP]

If you create a User Table to match up with the Security logon ids and security groups that you create you can have the Database open how you want, first of all with an Autoexec macro that checks for you and then opens the database as normal, for all other other users it opens the Switchboard.
You can also have different Switchboards which can be controlled by a Query and hidden form that uses VBA to decide which group they belong to and then opens the appropriate switchboard or form.
You can also disable the Shift key as well, also the Close X on the main window, forcing users to use your means of exit.

[Eric58132]

Well, I think I can close this thread now. OBP you were a great help through this, and i truly mean it when I say i couldn't have done this without your database template. It has been and will continue to be an amazing resource for me. Thank you

[OBP]

Eric, my pleasure. :beerchug: