No Duplicates allowed on Form

[Mtdew4243]

Newbie Question #3: First, Thanks to Everyone who have been so helpful on all my previous questions regarding this current project.

Not sure this should be posted under Form or Table but here goes. (Also not sure if my explanations are to verbose but, I want to make sure everyone understands what I trying to accomplish.)

I have a project where I'm capturing Serial Numbers of all items in my Master Carton. There are 18 units of the same item and a Master Pack ID. All with barcodes. I created a table with 19 fields + the Auto generated Primary key field.

To make data entry easier, I then created a simple form with all 20 fields and eliminated the Tab Stop for the Primary Key field.

My issue is: As the employee is scanning the barcodes of the 18 units they sometimes scan the same barcode twice. (And don't catch it. So I have two of the same barcode and missed one as well.) Since the data isn't in the same "Field" the No Dups feature doesn't appear to work.

Is there a way to make sure there are no duplicates entries in the entire form or should I have designed the Table and Form differently? To my knowledge I can't have the same field on the same form more than once. If that is in error and I CAN have the same field on the form multiple times then, my table could be just two fields, Carton ID and Serial number and the No Dups would work. (Not real good at SQL yet so if there is a complicated statement to write, that might be an issue.)

I like the way my Form looks because having the 18 different data entry points to be populated, let's them visually know they have completed the carton.

(The serial numbers are too similar to do a fast visual scan of the form and too time consuming to verify.) I'm looking for a systems way to manage the error prevention.


Thanks in advance!
MtDew4243

 

[plog]

There are 18 units of the same item and a Master Pack ID. All with barcodes. I created a table with 19 fields + the Auto generated Primary key field.

Does that mean you have 1 field for each unit? If so you can stop worrying about your forms issue right now--you have a bigger problem. Your table is improperly structured.

You need a whole new table for all the units, as opposed to adding new columns for each one.

Can you post your table structure? A screenshot of your relationship view would be best.

 

[Uncle Gizmo]

My issue is: As the employee is scanning the barcodes of the 18 units they sometimes scan the same barcode twice. (And don't catch it. So I have two of the same barcode and missed one as well.) Since the data isn't in the same "Field" the No Dups feature doesn't appear to work.

Is there a way to make sure there are no duplicates entries in the entire form or should I have designed the Table and Form differently?

Yes your suspicions about the design are correct. If you had all of the serial numbers in one field in a table then it would be very easy to check for duplicates, and actually you could stop duplicates being added by making the field only accept unique values, very simple, very easy, and the correct way to do it.

 

[Uncle Gizmo]

I would add that I did database for thermo electron a few years ago, and although it was initially setup to insist that the serial numbers were unique, after a year or so, when items were returned for upgrades, then it became necessary to allow duplicate serial Numbers.

Edit:- 2015_12_28
Actually I'm not 100% sure on what I did but I suspect I added a batch number and made the combination of batch number and serial number unique so that I could still enter the serial number with a scanner at any-time but couldn't enter it twice in the same batch.

 

[Mtdew4243]

Attached are screen shots of the Table and Form.

I get the desire to have all the serial numbers in one field. But, I needed a really simple way to capture the data. By using a Form and a hand held scanner, the warehouse associate (limited PC skills) just needed to scan barcodes.

As part of a different question I asked here earlier and already have an answer to, I ultimately need the ability to Concatenate the 18 serial numbers of each carton to create a 2D - PDF417 barcode for each master carton.

We are getting the product in in 5 packs, repackaging to 2 packs, and finally shipping out in 9 - 2 pack (18 units) master packs. We need to track the serial numbers by Mater Carton and by Skid.

I need a solution for a limited skill staff while still providing the required solutions for our customer.

 

[plog]

You really need to read up on normalization (https://en.wikipedia.org/wiki/Database_normalization). Its the process of properly structuring your fields/tables. This is the first step of setting up a database.

You need to identify all the objects you need to track (Skids, cartons, customers, etc.). Those then become tables and you need to add the appropriate fields to them and set up the proper relationships among them.

 

[Mtdew4243]

Here is a blank DB of what I did.

 

[Mtdew4243]

I have had a entry level Access class two years ago and do remember some thing of normalization. (Though this current DB wouldn't prove that.) And I understand that for maintaining long term databases the need to structure them appropriately.

The last thing I want to do is upset anyone here that is trying to help me.

I originally created this in Excel and it worked, except for the user's PC level skills. They kept bumping buttons and the cursor moved to inappropriate cells and they couldn't catch it nor if they did, get it back to where it belonged.

My DB Form has eliminated that. To get the data to the Customer, I export to Excel and do a vlookup to merge the last of the data. (Yes, if I had time, I should be able to make it all work in Access.)

We are a piece work shop and do one off projects. Once this project is over, the database will be unused. I don't need to track customers, addresses, sales prices, or costs.

What I do Need: Our customer knows which serial numbers they sent us and they want to know what serial numbers they are getting back. They would like to know in which carton and by skid.

I have a final requirement to print 2D barcodes. And, via an Update Concatenate query I get the data into the format required. My labeling software points to my DB and after I run the query, every 10 to 20 cartons, I print the Master Carton labels.

Everything is working with my current DB but the ability to check for duplicates.

Finally, though the message reads, "lack of preparation on your part does not create an emergency on my part." We were to have the product two week ago and received it last Friday. The labeling requirements were given to us on Saturday. And the product is for a Black Friday sale...

I would love to discuss the values of a proper DB (and I really do mean that because I want to learn) but, my *** is against a wall. My first truck shipped today. I had 133 duplicates out of our first 54,000 scans. Good, but not good enough. That means I scanned some products twice and some not at all.

Is there a quick fix to what I have? PLEASE and THANK YOU!

 

[Uncle Gizmo]

We get this a lot here so we are very familiar with your particular problem, that of moving stuff from Excel into MS Access. It would be possible I think to write some VBA code which would look through all your text boxes and check for duplicates. But it means writing some quite complicated VBA code. There may well be someone here who would do it for you, as they see it as a challenge, the sort of thing I would do a few years back, and that may happen if you get lucky.

All I can do now is advise you to do things in the accepted way, the way that I and others understand. If you do this it's very easy for us to help you. However if you do decide to continue with your project in your own particular way then you will meet some challenges which will be quite interesting, however there won't be any accepted methods of solving them.

I think you should have a look at this thread here:- (Free Tool to do the conversion)

Excel In Access (Part 1)

which will explain how you can convert your existing table into one more suitable for using in MS Access and then I think you should look at this YouTube playlist here:-

Videos - Add a Check List to your DB

where I demonstrate how to create a checklist, and a further set of videos showing how to turn the check boxes into text boxes. I think that's what you want. The database will automatically fill up with a set of text boxes that you can then scan your barcodes into.

 

[Uncle Gizmo]

Whats:-
S/C
G1
UPC
Cma

 

[Mtdew4243]

Uncle Gizmo

YouTube link appears to be broken. Unless I can't get to it from the US. After /msacce... it shows the three dots like the entire link didn't post.

Also, the one thing I liked about my solution, I only needed to scan a carton barcode once and then 18 serial numbers. Unless I'm mistaken, I will need to scan it between every serial number if I want it to be associated to each serial number now. That will really slow data entry.

 

[Uncle Gizmo]

Whats:-
S/C
G1
UPC
Cma

And these?

 

[Mtdew4243]

S/C = Skid / Carton, the barcode I scan when starting a new Master Carton Data = S-001/C-01. For Skid 001 and Carton 01. I know that 63 Master Cartons fit on 1 Skid so, I pre-printed them all. The 2nd Master Carton label is S-001/C02... The 1st carton of the 2nd Skid is S-002/C01.

G1, UPC, QTY, Cma are all repeating / Static / Unchanging data required to make the 2D / PDF417 barcode I need to print on each carton. The Cma is actual a ",".

G1,UPC0811571016990,QTY18,

G1,UPC0811571016990,QTY18,5902103YDM0V,5831103YDNH2,5902103YDO9O,5831103YDNLB,5901103YDMMC,5902103YDXA0,5901103YDUKO,5901103YDZUN,5901103YDZMZ,5901103YDV9N,5901103YDUP7,5829103WTBGB,5901103YDZMI,5901103YDV5S,5902103YDXT2,5822103YDNTG,5902103YDMOW,5822103YDNXO

 

[Mtdew4243]

FYI, the "G1" data string is over 255 so it needs to be in a long text field.

 

[Uncle Gizmo]

Well I've done a very quick modification and this is what I think it should roughly look like. It needs a unique index so that duplicates are prevented. It needs a routine to concatenate all of the serial numbers together. I'm not sure how to do that, I will have to have a think! Maybe someone else could make a suggestion. It also need setting up properly so that when you add a new record, it feeds in the data. This should be quite simple, all you need to do is add some code to the carton barcode text box so that when you scan in the carton barcode and Trigger the after update event by moving away from that text box, then that should trigger the code that fills the sub form below.
There's two things there the unique index and the triggering of the Code to fill the subform... thats maybe you could have a look at? I'm off to bed now! If you haven't got anything done, or no ones has suggested how to concatenate the records into a string by morning I'll have a look, but it might be quite late on...

 

[sneuberg]

I agree that this structure is wrong, but I thought it might be fun to write some code that does what I think Mr. Mtdew4243 wants. One way of checking for duplicates is by brute force. The code below goes through all of the text boxes on the form and compares their values to the text box that has focus. It returns True if it found one. This checks ALL the text boxes so if serial number happens to be the same as S/C for example this isn't going to work as it is. It could be modified just to check those that start with SN if that is necessary. I thought I'd let you try it before I refine it.

Private Function HasDuplicate() As Boolean

Dim Ctrl As Control
Dim ctlCurrentControl As Control
Dim CurrentControlValue As Variant
Set ctlCurrentControl = Screen.ActiveControl
CurrentControlValue = ctlCurrentControl.Value
HasDuplicate = False
For Each Ctrl In Me
If Ctrl.ControlType = acTextBox And Ctrl.Name <> ctlCurrentControl.Name Then
If Ctrl.Value = ctlCurrentControl.Value Then
HasDuplicate = True
End If
End If
Next Ctrl

End Function


You would call this function in each text boxes' before update event as shown below for text box SN_1.

Private Sub SN_2_BeforeUpdate(Cancel As Integer)

If HasDuplicate Then
MsgBox "This value is a duplicate"
Cancel = True

End If

End Sub

I put the function and the before update events for SN_1 and SN_2 in the attached database. So try this out and let me know if it's going in the right direction and what you want in it. My bedtime is in about an hour so I probably won't get back to this until tomorrow.

 

[Uncle Gizmo]

Ah got it!

this is how to concatinate the records:-

Field Data Horizontally On Report 477 views

If the link dont work search the forum for :- "list details horizontally"

 

[Mtdew4243]

Holly Crap... Thanks! The code is a bit above me but, what I have stepped through so far, makes sense. It will take me some time to step through it all. Please I can't ask you to do more. I get this is some code you had and modified but that is more than you should have done.

I have arranged a meeting with my past instructor... he has a fondness for a local steak house. (And I get to expense it with the company I work for.) If your ever in Chicago I'll toss out the same offer.

 

[Uncle Gizmo]

The Cma is actual a ",".

Is Cma the text delimiter?

 

[gemma-the-husky]

but your general question. It's always about tables.

if you want a particular field to be unique, no duplicates, then you need to set the field to

Required = Yes
Indexed, no duplicates = True
maybe, depending on the datatype
Allow Zero Length = false

when you try to enter a duplicate in a form, you then get an error message. With some code you can intercept this, and replace it with a more helpful one of your own.