Prevent records from being over written
Posted on 2002-03-22
ok here it goes I hope someone can help me in the simplest terms possible (new to access). THE SETUP:
I created a label database in access 2000. What happens is when a user selects a part number and presses a print button I kick off a bunch of queries which sends the record to a txt file (a sequential serial number is also generated) which I then in turn have a thrid party piece of software produce the label (all out of access). The records are eventually stored in a table called finaltable which contains fields serial number, build date, and time. THE PROBLEM:
Before the label actually goes on a finished product I wanted a way to track which user puts a label on the product along with a date and a time. Since I am a novice at access a co-worker (who is no longer here) created a seperate database which has my finaltable (linked). It looks like he added two new fields useddate and used user. There is also a table for username and password. What happens is in the first form you must put in a user name and password. That opens a form called frmUpdated labels in which you put in your starting a ending serial number. Once you press the update button it populates the useddate field along with the useduser (from the login) which also populates my finaltable in my original db (I am assuming that it why the tables are linked). It works great but the problem is once those records are updated, nothing prevents me from logging in under a different user and putting in the same serial numbers and the useddate and useduser fields get over written. Once a record has been updated I need some sort way to prevent the useddate and useduser fields from being over written.
Sorry if this post is too long. I would be more than happy to send my file to someone if I was not clear enough and make a call for some help over the phone if necessary.
Here is the code behind his update button which is over my head.
Private Sub cUpdate_Click()
On Error GoTo UpdateError
sSql = "Update FinalTable Set UsedDate = #" & Now & "#"
sSql = sSql & ", UsedUSer = '" & Forms!frmUpdateLabels!cUserName & "'"
sSql = sSql & " Where SerialNumber between " & Forms!frmUpdateLabels!cStartLabel
sSql = sSql & " and " & Forms!frmUpdateLabels!cEndLabel
Forms!frmUpdateLabels!cStartLabel = ""
Forms!frmUpdateLabels!cEndLabel = ""
On Error GoTo 0
MsgBox Err.Description, vbCritical, "Update"