Date/Time stamp in table.

[PsYc0TiC]

Okay... I have a database that multiple users access on the network. I do not have it split and it is too late to deal with that.

Moving on...

I have been manually looking in the table to try to track data changes but it is too hard now so I decided to add date/time stamps to the records to help.

I currently track the userid's of the people updating records as well as for the few who are allowed to add new records.

I added the date/time stamp to the new record entry with no problem right in the field of the table. I already had the field in the table for that and had a field in the form where I would manually choose today's date. I just removed the field from the form and set the default value to the table field "=Now()". works perfect.

The new test record then has 2 different groups that would update that record with different data using 2 different forms. I added in 2 more fields "date/time" in the table called "VisUpdate" and LabUpdate" and set their default values to "=Now()" and added in the "Before_Update" of each ones input form this: in the visual data input form I put "Me! [VisUpdate] = Now()" and in the lab data input form I put "Me! [LabUpdate] = Now()".

When I add a new test record the date/time stamp works for that one.
When I update that test record using the Visual data input form it adds the date and time of that data update perfect.
When I try to do the second (last update of the record) data update on the test record I get a "compile error: syntax error" popup and it highlights the "Me! [LabUpdate] = Now()" line int he before_update for that form.

I need help understanding why it works in the visual data input form but doesn't work in the lab data update form.

Can you only have this feature once in a record?

 

[xenou]

Since it's a syntax error, I'd recommend you post your code that is erroring (preferably, using code tags). Mainly I point that out since the syntax looks fine so you probably want to post a little more of the relevant code...

Example (no problem for me):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastModified = Now()
    Me.LastModified2 = Now()
End Sub

 

[Joe4]

Okay... I have a database that multiple users access on the network. I do not have it split and it is too late to deal with that.

BTW, it is never too late to split the database! It doesn't have to be done at the very beginning.
As matter as fact, most databases I have split have been existing databases that have been around a few years.

 

[PsYc0TiC]

BTW, it is never too late to split the database! It doesn't have to be done at the very beginning.
As matter as fact, most databases I have split have been existing databases that have been around a few years.

It is definitely too late... the people using the database are so hard headed and stupid I would have a helluva time getting them to understand using the front end after everything I went through getting them to use it as is.

 

[PsYc0TiC]

Since it's a syntax error, I'd recommend you post your code that is erroring (preferably, using code tags). Mainly I point that out since the syntax looks fine so you probably want to post a little more of the relevant code...

Example (no problem for me):

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.LastModified = Now()
    Me.LastModified2 = Now()
End Sub

Visual data input form BeforeUpdate code:

Private Sub Form_BeforeUpdate(Cancel As Integer)    
Dim strMsg As String
    
    If Not blnGood Then
        Cancel = True
        strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
    Me![VisUpdate] = Now()
End Sub

Lab test data input form BeforeUpdate code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    
    If Not blnGood Then
        Cancel = True
        strMsg = "Please click the Update button to save your changes, " & vbNewLine & "or Escape to reset them."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
    Me![LabUpdate] = Now()
End Sub

 

[Joe4]

It is definitely too late... the people using the database are so hard headed and stupid I would have a helluva time getting them to understand using the front end after everything I went through getting them to use it as is.

Not if you know what you are doing!
It should be pretty transparent to them! As matter as fact, most of the users I have using the databases I created don't even realize that they are using Access, let alone know if the database is split or not.
They really shouldn't notice any difference, whether it is split or not.

 

[PsYc0TiC]

I found the syntax error... there was a space between Me! and the rest but it didn't solve the problem.

I get this error:
Run-time error '2465'
Microsoft Access can't find the field 'LabUpdate' referred to in your expression.

however the field is in fact in the table.

 

[PsYc0TiC]

Not if you know what you are doing!
It should be pretty transparent to them! As matter as fact, most of the users I have using the databases I created don't even realize that they are using Access, let alone know if the database is split or not.
They really shouldn't notice any difference, whether it is split or not.

I understand you not understanding... it is a very long story and there are so many users now... in the beginning when I realized I needed to split the database i kept getting an error whil trying to split and I eventually gave up and have hammered the users over and over to not download the database to their computers because it doesn't work that way.

Some are so dense they continually kept downloading the database not understanding how it was being updated with new tasks for them.

recently I discovered why I kept getting errors trying to split it but it is too late to re-train all of the people to now download the front end after I threatened them so much about it previously.

I have a whole new system for this in the works to replace this one but for the time being I need to add the date/time stamps so I can easily track their updates daily.

 

[Joe4]

The way I have gotten around it is the following (maybe it will be helpful for your new system):

1. They are not told where the back-end is kept.

2. I have a copy of the front-end on a public share that they all have access to.

3. I create a batch file (or VBScript file) that does the following:
- Creates a C:\Databases directory, if they do not have one
- Deletes the old copy of the front-end from the C:\Databases directory (if it exists)
- Copies a new version of the front-end from the public share to their C:\Databases folder
- Opens the front-end from the C:\Databases folder

So, all they have to do is to click on this button on their Desktop to run the database (which copies down a fresh version of the front-end every time).
One big advantage to doing it this way is it makes it very easy to push out new versions. All you have to do is replace the copy of the front-end in the public share folder. Since the button they click on replaces the front-end they are using every time, it will automatically update them.

So easy for them, easy for you.

 

[PsYc0TiC]

The way I have gotten around it is the following (maybe it will be helpful for your new system):

1. They are not told where the back-end is kept.

2. I have a copy of the front-end on a public share that they all have access to.

3. I create a batch file (or VBScript file) that does the following:
- Creates a C:\Databases directory, if they do not have one
- Deletes the old copy of the front-end from the C:\Databases directory (if it exists)
- Copies a new version of the front-end from the public share to their C:\Databases folder
- Opens the front-end from the C:\Databases folder

So, all they have to do is to click on this button on their Desktop to run the database (which copies down a fresh version of the front-end every time).
One big advantage to doing it this way is it makes it very easy to push out new versions. All you have to do is replace the copy of the front-end in the public share folder. Since the button they click on replaces the front-end they are using every time, it will automatically update them.

So easy for them, easy for you.

Thank you that is a good idea. Hoping someone knows the solution to my date/time stamp issue... this would help me so much.