Avoiding duplicate numbers

[lindavon81]

My database is used for processing the shipping of electronic circuit boards
to our customers. Each customer has various unique assembly numbers, each
assembly number is to have a unique serial number. Serial numbers can be
repeated between assembly numbers, just not repeated on the same assembly
number. Each customer group contains many assembly numbers - some have over
100. I can't use the unique record because each customer's assemblies are put
together within one table, so I need to be able to find a way to write code
to prevent any given assembly number for a particular customer from having
duplicate serial numbers before leaving the plant. It is a final check that
we haven't duplicated a serial number for that assembly number prior to
shipping. We have to replace our serial number labels throughout the
manufacturing process, and errors are made. I need to be able to find a good
solution to ensure that they don't get out the door that way! Any suggestions
would be greatly appreciated!

 

[Tom van Stiphout]

On Tue, 16 Dec 2008 16:24:02 -0800, lindavon81

Start with putting a unique index over the combination of CustomerID +
AssemblyNo. Use the Indexes form to create this index.

-Tom.
Microsoft Access MVP

 

[OssieMac]

Hi,

If I understand your question correctly, it is the combination of Assembly
numbers and Serial numbers that cannot be duplicated. If this is correct then
I would concatenate the Assembly and Serial numbers on a form and have a
field in the table for the concatenated values that is Indexed (No
duplicates).

Each time the user updates either the Assembly number or the Serial number,
the after update events concatenates both values and places them in the third
concatenated field. You can then trap the error if duplicates appear and
revert to the oldvalues of the field.

In the following code assume:-
txtAssembly is the text box for Assembly number
txtSerial is the text box for the Serial number
txtAssemblySerial is the text box for the concatenated Assembly and Serial
numbers.

The code is not tested to the nth degree but I think it will put you on the
right track.

Option Compare Database
Option Explicit

Private Sub txtAssembly_AfterUpdate()
'Concatenate only if both fields populated otherwise _
will get duplicate Assembly or Serial numbers in _
the third field.
If Nz(Me.txtAssembly, "") <> "" And _
Nz(Me.txtSerial, "") <> "" Then
Me.txtAssemblySerial = Me.txtAssembly & Me.txtSerial
Else
Me.txtAssemblySerial = Null
End If

If Me.Dirty Then
On Error GoTo handleDuplicate
Me.Dirty = False
End If

Exit Sub

handleDuplicate:
On Error GoTo 0
'Call message to user
MsgBox "Duplicate combination of assembly and serial numbers"
Me.txtAssemblySerial = Null 'Remove the value
'Revert to original value of field
Me.txtAssembly = Me.txtAssembly.OldValue
End Sub

Private Sub txtSerial_AfterUpdate()
'Concatenate only if both fields populated otherwise _
will get duplicate Assembly or Serial numbers in _
the third field.
If Nz(Me.txtAssembly, "") <> "" And _
Nz(Me.txtSerial, "") <> "" Then
Me.txtAssemblySerial = Me.txtAssembly & Me.txtSerial
Else
Me.txtAssemblySerial = Null
End If
If Me.Dirty Then
On Error GoTo handleDuplicate
Me.Dirty = False
End If

Exit Sub

handleDuplicate:
On Error GoTo 0
MsgBox "Duplicate combination of assembly and serial numbers"
Me.txtAssemblySerial = Null 'Remove the value
'Revert to original value of field
Me.txtSerial = Me.txtSerial.OldValue
End Sub

 

[lindavon81]

I think that might do the trick for me - thanks a bunch. I'll let you know
how it works out.

 

[John W. Vinson]

If I understand your question correctly, it is the combination of Assembly
numbers and Serial numbers that cannot be duplicated. If this is correct then
I would concatenate the Assembly and Serial numbers on a form and have a
field in the table for the concatenated values that is Indexed (No
duplicates).

Each time the user updates either the Assembly number or the Serial number,
the after update events concatenates both values and places them in the third
concatenated field. You can then trap the error if duplicates appear and
revert to the oldvalues of the field.

I'd have to disagree with you here, OssieMac! Creating a redundant composite
field is probably neither necessary nor a good idea. You could get the same
effect by having a unique Index on the combination of the two fields, could
you not?

 

[OssieMac]

Hi John,

I always like feed back with better methods. However, can you please tell me
how to do this or point me to some info on it.

 

[John W. Vinson]

Hi John,

I always like feed back with better methods. However, can you please tell me
how to do this or point me to some info on it.

--

You can create a unique Index on up to ten fields. To do so using the user
interface, open the table in design view and choose the Index tool from the
toolbar (looks like lightning hitting a datasheet). Enter a distinctive index
name in the left column of the little grid; select the first fieldname to be
indexed (the primary sort field if you'll use the index for sorting) in the
right column. Then choose another field in the next row of the second column,
leaving the first column blank. Continue for all the fields in the index.
Check the Unique Values property checkbox to make the index unique.

You can trap the error triggered by violating the index in the Form's Error
event and issue a user friendly error message.

 

[OssieMac]

Thanks for that info John. Much appreciated. I haven't teste it yet but you
might like to comment on my thoughts below to Lindavon.

To Lindavon,
You might like to try John's suggestion. Looks much better and tidier than
my idea. However, I wonder how it will work if you initially enter the
Assembly numbers and then enter the Serial numbers at a later date. I should
think that entering multiple Assembly numbers without the Serial numbers at
the same time would create duplicates in the index????????

 

[John W. Vinson]

Thanks for that info John. Much appreciated. I haven't teste it yet but you
might like to comment on my thoughts below to Lindavon.

To Lindavon,
You might like to try John's suggestion. Looks much better and tidier than
my idea. However, I wonder how it will work if you initially enter the
Assembly numbers and then enter the Serial numbers at a later date. I should
think that entering multiple Assembly numbers without the Serial numbers at
the same time would create duplicates in the index????????

That depends on the "Ignore Nulls" setting of the index.

 

[OssieMac]

John,

Looks good. Thanks again for your input John. I am sure the OP will
apopreciate the simpler method.

Lindavon,

As per my previous post, John's answer looks like the one you should follow.
You should be able to trap the error similar to my original code. You just
don't have to concatenate the fields. If you need more help in doing this
then feel free to get back to me and it will give me an reason to test John's
method.