Need code to update record rather than Add New record. Help

[Summit_IT]

The issue is two records appear in the table. I need the task and the time on the same record.


[Private Sub cmdEnd_Click()Dim rs As DAO.Recordset
Me.TimerInterval = 0
Set rs = CurrentDb.OpenRecordset("Select * From tblLog")
rs.AddNew


rs!StartOfTest = Me.txtSessionStart
rs!EndOfTest = Now()
rs!ActualMinutes = Me.txtActualMinutes
rs!ActualSeconds = Me.txtActualSeconds
rs.Update
rs.Close
Set rs = Nothing
MsgBox "Data logged.", vbOKOnly, "Logged"
Me.txtActualMinutes = 0
Me.txtActualSeconds = 0
Me.txtSessionStart = Null
End Sub]

[davegri]

If the form is bound to tblLog fields, you will have the first record. There's no reason to do the rs.AddNew, which is the problem.

[pbaldy]

I think you'd need rs.Edit instead of rs.AddNew, but you'd also want to open the recordset to a particular record. It would without when there's only a single record, but when there are more that will always update the first record.

[Summit_IT]

I changed AddNew to Edit and got runtime error 3021. "No current record"

You mentioned "you'd also want to open the recordset to a particular record". How would I do that?

[pbaldy]

Are there no records in the table? You could test for that, but I still think you'd want to open the recordset to a particular record. Or is the form also bound to the table? That's probably what davegri meant, and I agree. Instead of manipulating a recordset, just update the values on the form.

[Summit_IT]

Are there no records in the table? You could test for that, but I still think you'd want to open the recordset to a particular record. Or is the form also bound to the table? That's probably what davegri meant, and I agree. Instead of manipulating a recordset, just update the values on the form.

Two fields are bound. TaskID and Task Description. I am looking to use FindFirst method.

[davegri]

Maybe you could tell us what the form is trying to do.
What does the Start Timing button do? Does it create a new record? Does it populate the start session textbox?
In the same fashion, what does the End Timing button do?
Do the Start Timing and End Timing buttons deal with only one record?
How does the Pause button interact with the above?

I suspect that what you want to accomplish can be done without the recordset code.

[pbaldy]

Why not bind them all? I think davegri was on the right track, I was not. If you already have that record on the form, you wouldn't normally use code to update it.

If a recordset was the right option, then I wouldn't use FindFirst, I'd open the recordset on an SQL statement that limited the results to the desired record.

[Summit_IT]

Maybe you could tell us what the form is trying to do.
What does the Start Timing button do? Does it create a new record? Does it populate the start session textbox?
In the same fashion, what does the End Timing button do?
Do the Start Timing and End Timing buttons deal with only one record?
How does the Pause button interact with the above?

I suspect that what you want to accomplish can be done without the recordset code.

Here is how the buttons work.
The timer subform works like this;

1. Tab 0 is Task Description. Once a task is selected and the tab button is pressed, a new record is added to tblLog. See pic 2.

Figure 1 Subform timer


Figure 2 tblLog with new record.

1. Click “Start Timing”. This starts timer and inserts date and time in Session Start (Unbound).
2. When “End Timing” is pushed, the unbound fields should populate the blank fields in tblTimer.
   
   1. StartofTest =Session Start, EndofTest= date and time end button was pushed, ActualMinutes, and ActualSeconds.

After changing AddNew to Edit the following issue’s were discovered;

1. Original problem disappeared.
2. New problem, after I stop a task, and start a new one. Repeat Item 1 thru 3.

I get this. Fig 3. The time was added to the first record. I need it to edit the last record.

I guess I need code to select the last record to edit?


Figure 3

[davegri]

I guess I need code to select the last record to edit?

Try something like this:
Set rs = CurrentDb.OpenRecordset("Select Top 1 * From tblLog Order By Log_ID")

If the * doesn't work, you will need to name the fields individually.

[Summit_IT]

Try something like this:
Set rs = CurrentDb.OpenRecordset("Select Top 1 * From tblLog Order By Log_ID")

If the * doesn't work, you will need to name the fields individually.

I figured out the issue.

Thanks,