Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Prevent records from being over written

Posted on 2002-03-22
6
Medium Priority
?
166 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 58
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 58
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 58

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Suggested Courses

783 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