423,350 Members | 2,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,350 IT Pros & Developers. It's quick & easy.

Find duplicate serial number for same stock number

P: 2
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub SerialNumberTxt_BeforeUpdate(Cancel As Integer)
  2. Dim Answer As Variant
  3. Answer = DLookup("[SerialNumber]", "InventoryTransaction", "[SerialNumber] = '" & Me.SerialNumberTxt & "'")
  4.  If Not IsNull(Answer) Then
  5.  MsgBox "Duplicate Serial Number Found! contact STOCK CONTROL!" & vbCrLf & "VERIFY SERIAL NUMBER!!!.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
  6.  Cancel = True
  7.  Me.SerialNumberTxt.Undo
  8.  Else:
  9.  End If
  10. 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
2 Weeks Ago #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,279
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):
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. '
  4. ' easier to keep track of what error I'm working with
  5. Const zErrReqField = 3314
  6. Const zErrDbleEntryIndex = 3022
  7. '
  8. Private Sub Form_Error(DataErr As Integer, Response As Integer)
  9.   Dim zMsg As String
  10.   Select Case DataErr
  11.     Case zErrReqField
  12.       zMsg = "Required information is missing for this record"
  13.     Case zErrDbleEntryIndex
  14.       zMsg = "Sorry, there is already a record with information for:" & _
  15.         vbCrLf & "Stock Number: " & Me![data_pk] & _
  16.         vbCrLf & "Serial Number: " & Me![data_pk]
  17.     Case Else
  18.       'allow Default message
  19.       Response = acDataErrContinue
  20.   End Select
  21.   If Len(zMsg) > 0 Then
  22.     MsgBox prompt:=zMsg
  23.     Response = acDataErrContinue
  24.   End If
  25. 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:
Expand|Select|Wrap|Line Numbers
  1. With zRS
  2.   .AddNew
  3.   !Field1 = strNewValue
  4.   .Update '<Error 3022 thrown here.
  5. End With
then you can use the On Error Gotomethod and trap the error at the code level.
2 Weeks Ago #2

PhilOfWalton
Expert 100+
P: 1,297
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
2 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,007
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 :
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.  
  3. With Me
  4.     strSQL = Replace("([SerialNumber]='%SN') AND " _
  5.                    & "([StockCode]='%SC') AND " _
  6.                    & "([YourPK]<>%PK)" _
  7.                    , "%SN", .SerialNumberTxt)
  8.     strSQL = Replace(strSQL, "%SC", .StockCodeTxt)
  9.     strSQL = Replace(strSQL, "%PK", .YourPKTxt)
  10.     If Not IsNull(DLookup(Expr:="[SerialNumber]" _
  11.                         , Domain:="[InventoryTransaction]" _
  12.                         , Criteria:=strSQL)) Then
  13. ...
  14.     End If
  15. 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.
2 Weeks Ago #4

P: 2
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
2 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,007
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.
2 Weeks Ago #6

PhilOfWalton
Expert 100+
P: 1,297
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
2 Weeks Ago #7

NeoPa
Expert Mod 15k+
P: 31,007
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.
2 Weeks Ago #8

Post your reply

Sign in to post your reply or Sign up for a free account.