Find duplicate serial number for same stock number

[mike1hello]

I have a warehouse database. I have created a query which searches the table for duplicate serial numbers and give me a warning message, that works.
My Code

Private Sub SerialNumberTxt_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[SerialNumber]", "InventoryTransaction", "[SerialNumber] = '" & Me.SerialNumberTxt & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Serial Number Found! contact STOCK CONTROL!" & vbCrLf & "VERIFY SERIAL NUMBER!!!.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 Cancel = True
 Me.SerialNumberTxt.Undo
 Else:
 End If
End Sub

My problem is two parts can have the same serial number but must have different stock numbers. Example. I have a chair with stock number chair1234 and serial number 12345, then I have a freezer with stock number FRE98745 serial number 12345. These items can have the same stock number because they are different items. What I cannot have is duplicate serial numbers for the same stock number. How do I modify the code to group by same stock number and check the serial numbers of the group to check for the serial being currently entered does not already exist in the table.

Regards
Mike

[zmbd]

IMHO: Your best bet is to go to the table level and set-up a compound index based on these two fields and stop the double entry at that point.

Access2013+
- Open table in design view
- Ribon>Table Tools>Design>Show/Hide>Indexes

In the dialog that opens you will see that the [PrimaryKey] is already entered, if set for the table.

- In the column [Index Name]

choose a suitable name such as
"IndxUnqStckSn"
In the column [Field Name] on the same row as the index name select the first table field [StockNumber]
While in this same row look at the bottom of the index dialog
Primary=No (IMHO compound indexes should be avoided for PK)
Unique=Yes (When finished this will prevent the same Stock/Sn)
Ignore Nulls=No
(This will prevent either field from being empty; however, if there is a point in time where or the other may need to be empty then set this to "Yes"

- In the empty row below the "IndxUnqStckSn"

[Index Name] = Leave Blank
[Field Name] = Select [SerialNumber]

-Close the dialog using the [X]
-Save the table

Now when a user attempts to enter the same stock/serial-number combination the Database will pop-up a message telling them that this isn't allowed.

The default error message is a bit, well, while correct not the most helpful.
You can trap this error at the from level by using the form's on_error event to create a custom error message. A simple example might be (here I also trap for missing required field value):

Option Compare Database
Option Explicit
'
' easier to keep track of what error I'm working with
Const zErrReqField = 3314
Const zErrDbleEntryIndex = 3022
'
Private Sub Form_Error(DataErr As Integer, Response As Integer)
  Dim zMsg As String
  Select Case DataErr
    Case zErrReqField
      zMsg = "Required information is missing for this record"
    Case zErrDbleEntryIndex
      zMsg = "Sorry, there is already a record with information for:" & _
        vbCrLf & "Stock Number: " & Me![data_pk] & _
        vbCrLf & "Serial Number: " & Me![data_pk]
    Case Else
      'allow Default message
      Response = acDataErrContinue
  End Select
  If Len(zMsg) > 0 Then
    MsgBox prompt:=zMsg
    Response = acDataErrContinue
  End If
End Sub

for simple forms I will trap these errors at the form level as I typically don't have too many such indexes - may have a lot of required fields and trapping them at this level will force either an [ESC] to abort the record/change or data entry in the required field.

If you are entering the record using something like:

With zRS
  .AddNew
  !Field1 = strNewValue
  .Update '<Error 3022 thrown here.
End With

then you can use the On Error Gotomethod and trap the error at the code level.

[PhilOfWalton]

Hi Mike

zmbd is spot on on suggesting creating the Unique Index combination of the Stock Number and Serial Number. Much depends on what tables you are using as to the exact 2 items that make up those two keys.

The problem that you will have is that until you have removed all duplicates, you will not be able to modify the table design. If you follow zmbd's instructions, without first removing duplicates, everything will appear to be OK until you press the Save button when you will get an error.

Hope you haven't too many duplicates so far.

I might suggest creating a query in stock nunber order and serial number order and manually look for duplicates or you can use a find duplicates query wizard. Then maybe add a letter to the serial number so as to remove duplicate.

Then modify the table

Phil

[NeoPa]

Hi Mike.

The first thing that springs to mind is that your code will work as required only for when adding new items. If you try to use it with modifications the check will find itself. Essentially you'll never get an amendment through after the record's been created. This may be desired behaviour, but if so then it should really have been explained as part of the question.

As for table design, I think that's a very good idea and would recommend you take that up. It's important to get such things right.

However, as was mentioned, you may not like the way errors are handled that way. Personally I'd design the form to warn you before attempting to break table restrictions, as well as adding the table restrictions. Does that make sense?

Fundamentally, what you need in the Criteria parameter for your DLookup() call is :

1. Check it matches the Serial Number.
2. Check it matches the Stock Code.
3. Check it doesn't match whatever unique identifier you have for the record in the table. Probably the PK. We don't know what that is yet, or even if you have one (You should of course!).

The DLookup() call should then be of the format :

Dim strSQL As String
 
With Me
    strSQL = Replace("([SerialNumber]='%SN') AND " _
                   & "([StockCode]='%SC') AND " _
                   & "([YourPK]<>%PK)" _
                   , "%SN", .SerialNumberTxt)
    strSQL = Replace(strSQL, "%SC", .StockCodeTxt)
    strSQL = Replace(strSQL, "%PK", .YourPKTxt)
    If Not IsNull(DLookup(Expr:="[SerialNumber]" _
                        , Domain:="[InventoryTransaction]" _
                        , Criteria:=strSQL)) Then
...
    End If
End With

Obviously you'll need to replace some of my references where I don't have information about your system, but the required changes should be obvious.

[mike1hello]

Hello NeoPa,
I tried the script provided and I am receiving error, that changes will create duplicate records.
It appears I will need to create a separate table for all serial numbers. I tried to add a table just for serial numbers and link it to the stock number table. Instead of adding a new record in the table with the additional serial number for the stock number it just replaced the serial number on file. I have 250 items currently with different Stock Numbers, which have serial numbers I must track coming into the warehouse and track going out. I am able to check for duplicate serial numbers, but the query does not group by stock number and only check that stock number group for duplicate serial numbers. I have found some items to have the same serial number format even though they are different items. I am very new to this. What is the best way to accomplish this? Should the serial number table be linked to the Inventory transactions table or the stock number table? The two tables Inventory transactions table and stock number table are linked together. The foreign key is in the inventory transactions table linking the stock number table. Which table do I link the serial number table to have many serial numbers linked to one NSN number?
Regards
Mike

[NeoPa]

Hi Mike.

You could start by reading again what some of the other experts have suggested below, and also have a look at Database Normalisation and Table Structures, but this new question is off-topic for this thread.

Feel free to ask it in a separate thread, but I strongly suggest you read up on the concepts beforehand or any answers will be meaningless, or just doing the job for you. Neither of which work well.

Don't worry. It's very common for new database designers to get it horribly wrong and have to start again once they've learned the basics. If you look through our forum here you'll come across many examples. The important thing is to learn that lesson as quickly and painlessly as possible and move on with a new understanding that just helps things to work.

Best of luck -Ade.

PS. When you are ready to post your new question do bear in mind that expressing it in comfortable English will enhance the likelihood people will be able to help you. Most experienced members know that any effort spent reading through and adjusting a question before submitting it is well worth it.

[PhilOfWalton]

Mike,

It would be very helpful if you could post an image of your relationship window, showing ALL the fields in all your tables.

For clarity in viewing the image, please keep the blank areas to a minimum, and check that it is readable before posting.

Phil

[NeoPa]

Just to be very clear Mike :
Any new images or question about the latest issue will need to be posted in a separate question thread. Please do so only after you've followed the instructions and learned some of the basics.
Anything further posted in here on this new subject is likely to be deleted.