Solved

Prevent records from being over written

Posted on 2002-03-22
6
156 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

Expert Comment

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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now