[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Prevent records from being over written

Posted on 2002-03-22
6
Medium Priority
?
168 Views
Last Modified: 2008-03-06
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

0
Comment
Question by:partykdj1
6 Comments
 
LVL 59
ID: 6888948
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
 
LVL 59
ID: 6888952
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
 

Author Comment

by:partykdj1
ID: 6888978
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 6889006
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7063126
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
 
LVL 5

Expert Comment

by:Netminder
ID: 7080396
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question