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.

 

[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!

 

[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.

 

[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.

 

[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.

 

[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.

 

[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.

 

[Mtdew4243]

This reply is to both Uncle Gizmo and sneuberg. First sorry for the delay in responses and Thanks for all the information! Phase one of the project is now complete which is why I was absent for awhile. Phase two starts in two to three weeks so, I have already started to redesign my DB in a more traditional way. I should try to do it right.

Uncle, the barcode program is paid for but can be easily learned for the quick and dirty labels but also so sophisticated that it has features I haven't even scratched yet. you can link data to it in many ways, Access, Excel, tab del text... or if the label data doesn't change, make it right in the program. Made by Seagull Scientific it is called BarTender, I have the Pro version.

sneuberg, yes, my hope was for a brute force way of checking just the SN:'s. Because S/C is in its own field, I already have the Required set to Yes and the No Dup's turned on for it and it works great. The way I had my form set up it was very visual for the associate to see how he was progressing through the master case.

Just out of curiosity, was the brute force method you described checking just the fields on the active Form or also the data from a previous record. Just wondering if an associate was really trying to make an error and scanned the same barcode on two different records would it be caught the second time as well... if it was only entered once on the active Form.

What I really believe I need is to find a local group that occasionally gets together over a cup of coffee to discuss different DB projects. And to discuss the better way of designing DB's right from the beginning of table design.

Bed time here as well.

Thanks all!

 

[sneuberg]

Just out of curiosity, was the brute force method you described checking just the fields on the active Form or also the data from a previous record. Just wondering if an associate was really trying to make an error and scanned the same barcode on two different records would it be caught the second time as well... if it was only entered once on the active Form.

It only checks the active form. If you want to check the scanned serial number with all previous scanned numbers probably the best way to do that is to create a table with a single indexed (no duplicates) column for serial numbers the then inserting them into to this table as they are scanned. If a duplicate is scanned the attempted insertion with raise an error than can be trapped. I didn't do it that way because I didn't think you cared about the previous serial numbers and I believe my method is faster, albeit the time for a single record insertion would probably be acceptable. The tricky part of this might be incorporating the existing serial numbers in this check table. You could do that with a union query but you would need some code to strain out the duplicates if there are any.