How to stop MS access from overwriting previous record?

Question

How can I stop access 2016 to stop wiping the data from previously saved records?

Any improvements/suggestions are greatly appreciated!

(This is the best I could do with my limited knowledge)

There are the following fields: Text7 ,ID, UPK, CheckTime. The data to be saved back to the table is only the checktime field for each scanned item.

Table:

-ID Autonumber | -UPK Number | -CheckTime Date/Time |-Text7 (is the only unbound control)

I need to be able to do this:

scan a value on Text7
after the update search the table for this value (which is already) in the table see the Text7_AfterUpdate event below
Text7_AfterUpdate event will populate the data into the form
Automatically calculate Now() and add it to the CheckTime field (this field is empty in the table)
Save the record
Clear all txtboxes
Set focus back on Text7
Move to the last empty record and be ready to receive the next scan

**EDITS thank you @Jiggles32 **

NOTE: scrapped the VBA code too cumbersome for my knowledge level, tried @Minty's suggestion and went with a query

I am honestly confused as to what your SaveAllRecords function is supposed to do. The code that is posted basically does nothing. If Me.Dirty [no expression] Then. All that code appears to do is set a variable called Checkin_Time equal to "" (which it does regardless of the previous statement). Would you kindly clarify what you are attempting to do with this? To be more specific, you mentioned you want item number 5. to "Save the record", what record and to where? What does your table structure look like? — Jiggles32
Does your code ever actually fire? Is Text 7 bound or unbound? Is it text or a number? If its text you should be getting a type mismatch in the rs.findfirst. Have you added some debugging to see what you code is actually doing ? @Jiggles the IF Me.dirty is boolean so you don't need the = true bit, as it's implied. — Minty
@Jiggles32 I have clarified the code a little more thank you — sam goto
@Minty I had added some notes at the top, Text7 is unbound. Yes the code fires up with not errors other than the original issue mentioned of overwriting the date from the previous record. — sam goto
I think I would simply run an update query on the ID scanned from an unbound form and based on it not having checkdate. If the ID doesn't exist pop up a warning to check the scan. — Minty

Jiggles32 Jiggles32 · Accepted Answer · 2018-07-20T15:30:02

I think I am seeing this a little more clearly now. It looks like you want the save to occur on a New Record, not one that is already present.

This is what is causing that issue:

rs.FindFirst "[UPK]=" & Text7
If rs.NoMatch Then
    MsgBox "Sorry, no such record '" & Text7 & "' was found.", _
           vbOKOnly + vbInformation
Else
    Me.Recordset.Bookmark = rs.Bookmark
End If
rs.Close

Basically, this portion of code is finding the first instance of a record that contains the UPK value that is in Text7 and sets that record to the current Recordset.Bookmark information on the form. Therefore, if there is already a value with the same UPK in the table, it will just overwrite that value.

I am certain you can accomplish your desired results with this:

Dim ctrl As Control

...

'Update CheckTime to current time (for current UPK), save, and close
rs.AddNew
rs!UPK = Me.Text7
rs!CheckTime = Now()
rs.Update
rs.Close
Set rs = Nothing

'Clear all text boxes
For Each ctrl In Me.Controls
    If (ctrl.ControlType = acTextBox) Then
        ctrl.Value = Null
    End If
Next ctrl

'Reset focus on Text7
Me.Text7.SetFocus

You will still need to setup the recordset, etc. in the ... section.

How to stop MS access from overwriting previous record?

1 Answers