Solved

Prevent records from being over written

Posted on 2002-03-22
6
159 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 57
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 57
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 75 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

777 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