[Question] Duplicate Data

[papasilo]

Hi,

I just need suggestion on how to get rid of this Duplication Data Entry.

What I did in my program is that
I use a temporary table before I save it to the main database

Supposed to be the output will be:

Computer1 orders: Computer2 orders:
Sales No1 Sales No2
-Soap -Pencil
-Shampoo -Paper

But what happens is
Sales No1
-Soap
-Shampoo
-Pencil
-Paper

Do you have any idea how to handle this problem?
FYI, I use DMAX() function to get the lastTransaction Number..
the code is trigger during saving.

Why I didn't use Autonumber or Autoincrement?.. because the customer requires a series number.
So if I use Autocrement... let say 1,2,3.. and suddenly I deleted / canceled the transaction 4.. it would look like this 1,2,3,5,6..

I hope you will understand what I mean..

Thanks

 

[jdraw]

Autonumbers are for Access/system use and should mean nothing to the user. (Autonumbers aremeant to be unique, not necessarily sequential). You can still add a number that you can control to satisfy the incrementing number issue.

Several have posted on the Dmax(x) + 1 to get the incremental number.

It appears from your description you are dealing separate records. Perhaps you are switching from Order1 to Order2 in your mind, but in reality all of the items are assigned to whatever Order you are working on.

So what if the number series has a hole in it when you delete a record? What difference does it make?

Let me suggest an example to show how foolish a sequence number with no holes might be. Suppose each person at the time of birth is assigned your sequential number, and you are bound to keep the sequence. Let's say that everything goes well for the first 100 people. Then, tragically person 56 is killed. Would you renumber 57 to 56, 58 to 57?
So, what is significant about numbers in sequence...?

Well there is a counter argument (sort of) that may make some sense up to a point.
You have a series of preprinted checks each numbered in sequence. When you test a program or have a printer jam.... you must account for and reconcile every check number. You want to prevent abuse, theft, fraud etc, and you must satisfy the auditors so there is a case for some number sequences--but even there you aren't filling the gaps, you're just accounting for the gaps.

As for your tables , Orders and Items issue, I'd recommend doing some research on Normalization and 3NF. Even if it's just a refresher, I'm sure it will be helpful.
I have no idea why you need a temporary table, perhaps you could explain the process in detail.

 

[The_Doc_Man]

Depending on number of simultaneous users, it might be impossible to prevent the "DMax + 1" trick from generating duplicate numbers, but you can at least protect yourself by making the properties of that index "No Dups" - so if you got unlucky, at least the attempt to write the bad data would be trapped.

If you do that and put a trap on the update action, you can see if you get back an error and if so, generate a new number. You don't say if you are using VBA or just the form's normal update abilities. To do this right and keep it as transparent to your users as possible, I think you need to use VBA and set a trap handler around the VBA code that does the actual update. If the trap fires, you can determine whether the code was a "duplicate data" case and take corrective action, but let the other traps "do their thing" in whatever other way you handle traps and errors.

 

[papasilo]

Hi!
Thanks for your quick reply..

@jdraw - Why did I use an temporary table? because I didn't use Access as a main database what I use is MySQL.... I can't really explain why this program was design with temp table to main database.. this project is not mine actually.. I just need some suggestion/way around on how to handle this loop hole other than redesign the table/flow.

@the_doc_man - emm,, okay i tried if mySql has that kind of function tagging the field with No-Dups allowed. Thanks! ^^