Solved How can I fix this code, so that the time data are logged to the CURRENT record an user is working on and NOT create a new entry??

[Sampoline]

I have a database that has been created for the purposes of file indexing. What this means for me is, an employee scans images and they will input various details (e.g. Their Name, Client/Requestor, Date Scanned, File Bar-code, Number of Images, Comments, etc) into the database form. This database will not only serve as a means to simply record employee statistics, but as a medium to track user efficiency, productivity and observe trends over time. This will help build a consensus for reports, figures and client quotes.

To achieve this, I needed to also find a way to track times too. So I went ahead and created timers. The employee's times were recorded using a start and end time.

So how I've gone about doing this is, I've created command buttons for Start, Pause/Unpause and End time.

I have two types of times being tracked:
1. The total time (the total elapsed time from when a user clicks on the Start button and stops the timer by clicking the End button)
2. The actual time (same concept but doesn't record time when the Pause button is clicked; which will be my file processing time)

I have 5 text fields that are running times:
txtSessionStart (the code I've written gives me the Short Date and 24-hr time AM/PM for Start and End time, by clicking the respective Start and End command buttons)
txtActualSeconds
txtActualMinutes
txtTotalSeconds
txtTotalMinutes

I've pretty much got everything working in my VBA code. All command functions work as I intended them to. But I've run into one issue. And it's a pretty big one.

The timer data isn't logging back into the row field pertinent to the current record that the user created. Instead what happens is, a new record is created by clicking the End timer button. So what I'm left with in the table, is a row with the file details and a separate row below it with the timer details.

Here is my code:

Private Sub cmdEnd_Click()

Dim rs As DAO.Recordset
Me.TimerInterval = 0
Set rs = CurrentDb.OpenRecordset("Select * From tblIndexing")
If Not IsNull(Me.txtSessionStart) And Not IsNull(Me.txtActualSeconds) And Not IsNull(Me.txtActualMinutes) And Not IsNull(Me.txtTotalSeconds) And Not IsNull(Me.txtTotalMinutes) Then
rs.AddNew
rs!StartOfTest = Me.txtSessionStart
rs!EndOfTest = Now()
rs!ActualMinutes = Me.txtActualMinutes
rs!ActualSeconds = Me.txtActualSeconds
rs!TotalMinutes = Me.txtTotalMinutes
rs!TotalSeconds = Me.txtTotalSeconds
rs.Update
End If
rs.Close
Set rs = Nothing
MsgBox "Data logged.", vbOKOnly, "Logged"

End Sub

I am 100% sure where my error is. I'm using DAO.Recordset, CurrentDb.OpenRecordset and rs.AddNew. Which is creating a new record and then updates my text fields with rs.Update. I can tell that's not the right way to go about it. My problem is, I have no idea how to fix it. I am an inexperienced Access/VBA user and this is my first major Database that I've created. So if anyone has the code that can fix this and explain the logic behind it, that would be very constructive for me. Thanks.

P.S. Please check the screenshots for further context. The arrows in the table are indicating where the numbers should actually be, which is pertinent to that record.

 

[jdraw]

Review rs.Addnew vs rs.Edit

 

[Gasman]

Why are you not just updating the record the user is on.?
I used code like this, perhaps you can amend to suit?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

 

[Sampoline]

Why are you not just updating the record the user is on.?
I used code like this, perhaps you can amend to suit?

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
    Me.CreatedDate = Now()
    Me.CreatedBy = Environ("username")
Else
    Me.AmendedDate = Now()
    Me.AmendedBy = Environ("username")
End If
End Sub

Sorry I'm not that advanced at all this. So what exactly does this code do and would that mean I don't have to change anything from my above code? Just add an beforeupdate event? What are you alluding to as created and amended date? Does this give me start and end times?
And I also want to retain the timers on the form. So I want the user to trigger the time with the buttons on the form. One with the total time and one with the processing time like mentioned before. And all that goes back into the table for that existing record.

 

[Gasman]

In that DB, I needed to know when a record was created and when if/it had been amended.
So just before the update of the record from the form, I would set the relevant fields depending on whether this was a new record just created, or being edited. So the created date fields would only be used once, but the amended fields would be overwritten each time.

You would update the relevant fields 'whilst' on the record.?

If you wish to go your route, you would need to locate that record using the primary key and use .Edit as mentioned by JDraw, however I am not sure whether Access would complain that the record was locked (by the user in the form on that record)

I've never been a fan of these Start and Stop limits (especially havig worked under such restrictions)
What is to stop me pressing the Start button after I have been in the record several minutes and then the End button and still continue to work on that record.? IE not provide 'true' values for whatever this timing is meant to report.?

 

[Sampoline]

In that DB, I needed to know when a record was created and when if/it had been amended.
So just before the update of the record from the form, I would set the relevant fields depending on whether this was a new record just created, or being edited. So the created date fields would only be used once, but the amended fields would be overwritten each time.

You would update the relevant fields 'whilst' on the record.?

If you wish to go your route, you would need to locate that record using the primary key and use .Edit as mentioned by JDraw, however I am not sure whether Access would complain that the record was locked (by the user in the form on that record)

I've never been a fan of these Start and Stop limits (especially havig worked under such restrictions)
What is to stop me pressing the Start button after I have been in the record several minutes and then the End button and still continue to work on that record.? IE not provide 'true' values for whatever this timing is meant to report.?

Hi Gasman, thanks for the humble suggestion. I actually gave your code a go and I see what it's doing. And you have a valid point about the issues of using buttons. I guess that is something I need to think about. I just really liked the idea of a timer that can give me these variable times. But you are right, a user could simply forget to click the button and then that would be an issue. Maybe I can add both methods in. With the method you suggested, I could make the fields visible only to administrative level like myself or my boss. And keep the timer concept as a trial to see how users respond to it. Not like more information hurts right? So if I want to also continue with my method, I would have to specify the primary key where exactly? Do you have an example using my code as a base? Sorry to push, you are like the first person who has been making sense to me in days haha!

 

[Gasman]

Well if I was given 3.5 minutes for data entry, I would 'forget' to click the start button. and perhaps click the End button a little early.
Something along the lines below, however remember I *think* Access would complain.
This is not something I have done myself, trying to update records when editing them on forms.

Private Sub cmdEnd_Click()

Dim rs As DAO.Recordset
Me.TimerInterval = 0

If Not IsNull(Me.txtSessionStart) And Not IsNull(Me.txtActualSeconds) And Not IsNull(Me.txtActualMinutes) And Not IsNull(Me.txtTotalSeconds) And Not IsNull(Me.txtTotalMinutes) Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblIndexing WHERE [yourIDfield] = " & Me.YourIDField)
    rs.Edit
    rs!StartOfTest = Me.txtSessionStart
    rs!EndOfTest = Now()
    rs!ActualMinutes = Me.txtActualMinutes
    rs!ActualSeconds = Me.txtActualSeconds
    rs!TotalMinutes = Me.txtTotalMinutes
    rs!TotalSeconds = Me.txtTotalSeconds
    rs.Update
    rs.Close
End If

Set rs = Nothing
MsgBox "Data logged.", vbOKOnly, "Logged"

End Sub

 

[Sampoline]

Well if I was given 3.5 minutes for data entry, I would 'forget' to click the start button. and perhaps click the End button a little early.
Something along the lines below, however remember I *think* Access would complain.
This is not something I have done myself, trying to update records when editing them on forms.

Private Sub cmdEnd_Click()

Dim rs As DAO.Recordset
Me.TimerInterval = 0

If Not IsNull(Me.txtSessionStart) And Not IsNull(Me.txtActualSeconds) And Not IsNull(Me.txtActualMinutes) And Not IsNull(Me.txtTotalSeconds) And Not IsNull(Me.txtTotalMinutes) Then
    Set rs = CurrentDb.OpenRecordset("Select * From tblIndexing WHERE [yourIDfield] = " & Me.YourIDField)
    rs.Edit
    rs!StartOfTest = Me.txtSessionStart
    rs!EndOfTest = Now()
    rs!ActualMinutes = Me.txtActualMinutes
    rs!ActualSeconds = Me.txtActualSeconds
    rs!TotalMinutes = Me.txtTotalMinutes
    rs!TotalSeconds = Me.txtTotalSeconds
    rs.Update
    rs.Close
End If

Set rs = Nothing
MsgBox "Data logged.", vbOKOnly, "Logged"

End Sub

Thanks so much man, this worked!! You've saved me further headache tonight. I did consider the rs.Edit but completely forgot to specify WHERE in my table *facepalm* . And you have a very valid point about the buttons, I will take you up on that and try to work on the other method you suggested with using amendment time instead. Thanks once again!

 

[Gasman]

TBH I am not eve sure how to implement it, but I am sure the experts here would?
My initial thoughts are to set the start time in the Current event and a flag to indicate it is running and unset/clear that flag when you update the record.

That way if you start to edit, move off a record, then go back to edit the previous record it is all under the same session.?
Let's see what the experts offer.

Good luck with it anyway.

 

[Pat Hartman]

If the times are in the record that is bound to the form, you should NOT be using DAO to update the record. Just put the times into bound fields. They don't need to be visible on the form.

 

[Sampoline]

TBH I am not eve sure how to implement it, but I am sure the experts here would?
My initial thoughts are to set the start time in the Current event and a flag to indicate it is running and unset/clear that flag when you update the record.

That way if you start to edit, move off a record, then go back to edit the previous record it is all under the same session.?
Let's see what the experts offer.

Good luck with it anyway.

Yeah it does seem quite complex. I might need to make a separate question based on this entirely. Thanks for the food for thought. Much appreciated.

 

[Sampoline]

If the times are in the record that is bound to the form, you should NOT be using DAO to update the record. Just put the times into bound fields. They don't need to be visible on the form.

Hi Pat, so the way I've done it is.. I have the time text fields that are unbound and then I created bound text fields that have the control source going back to those unbound fields. The bound fields are set to not visible in my Form Header. Don't know if that's what you meant, but yeah.

 

[Pat Hartman]

You don't need two sets of fields. You only need one - the bound fields. Your code simply places a value in the bound field. NO DAO update code at all. The whole cmd_end procedure is unnecessary since the times will be saved when the record is saved.

It is really poor practice to run DAO or action queries against your bound recordsource since you are conflicting with yourself.

 

[Sampoline]

You don't need two sets of fields. You only need one - the bound fields. Your code simply places a value in the bound field. NO DAO update code at all. The whole cmd_end procedure is unnecessary since the times will be saved when the record is saved.

It is really poor practice to run DAO or action queries against your bound recordsource since you are conflicting with yourself.

So how do I efficiently proceed with having a timer feature on my form (for the user to see) and that being saved (with my save button) into my table for that record. Using DAO was the only way I knew how. But I know it's not a good way of doing it. Sorry I'm an inexperienced coder.

 

[Pat Hartman]

As long as the time fields are bound to the record you are editing, simply putting a value in them will dirty the record and Access will save the record when you force the save which is unnecessary or when you leave the record by closing the form or scrolling to a different record.