Prevent records from being over written

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
    CurrentProject.Connection.Execute sSql
    Forms!frmUpdateLabels!cStartLabel = ""
    Forms!frmUpdateLabels!cEndLabel = ""



UpdateExit:
    On Error GoTo 0
    Exit Sub
   
UpdateError:
    MsgBox Err.Description, vbCritical, "Update"
    Resume UpdateExit


End Sub

partykdj1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
Daryl,


<<The serial numbers are generated via an auto number. there should not be a need for a re-print. I am
still lost on what to do. I could could strip my data and send you the design if need be.>>

  So you want to prevent somebody from re-printing entirely then? See what I'm getting at?  You have a couple of different ways to go:

1. Don't allow re-prints at all - no way then that the current record can be overwritten.

2. Allow re-prints, but don't update the existing record, and don't record that a re-print was done.

3. Allow re-prints, and record the fact that it was re-printed by appending new records to the table.

  I'm not sure what it is your end goal is.

  Feel free to send the MDB along.  Me e-mail address is in my profile (click on my member name).

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Questions:

 Do you want to allow those serial numbers to be printed again?  If so, do you want to record the fact that they were printed more then once by two different people?

 What if the serial number ranges overlap?

  I think you can see what I'm getting at.  What you really need to do is add a new table that records each printing with it's serial number range and who did the print.  That would be the best approach.

Bounce back with comments/questions.

Jim.

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Actually, without seeing the design, I'd say rather then update the current table, simply append new records to it for each printing.

Jim.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
partykdj1Author Commented:
Jim,

The serial numbers are generated via an auto number. there should not be a need for a re-print. I am still lost on what to do. I could could strip my data and send you the design if need be.

Daryl
0
 
nico5038Commented:
for partykdj1

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: JDettman  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.