Hello everybody,
I have a table in Access that shows products and its associated codes. Each product should have a code, but sometimes users make a mistake and give the same code to two different products. I call this the "duplication mistake"
To that extent, everything is normal. The fields of the above mentioned table are:
Name, Tipus, IdProvider, Price, CodeBar. The oly two which are important to us are Name and CodeBar.
To prevent duplication mistake results, but providing the opportunity to keep duplicated codes, I've added a button that warns user about this issue. So here is the code of that button.
Unfortunately, the result of the before mentioned algorithm is this (Please, see the attached link to see the screen capture):
[Result of this algorithm in a MsgBox][1]
The results are wrong. Instead of showing the Names of the products that share the duplicated BarCode, it shows the same Names, with no relation about the barcodes. I wish to point that the duplicated barcodes are ok, they are the ones. But the program fails when it's about to show the product names that have these duplicated codes.
Anyone can give a clue about this? What is making the second recordset to only save two product's name instead of one?
[1]: https://i.stack.imgur.com/XXvY1.jpg
I have a table in Access that shows products and its associated codes. Each product should have a code, but sometimes users make a mistake and give the same code to two different products. I call this the "duplication mistake"
To that extent, everything is normal. The fields of the above mentioned table are:
Name, Tipus, IdProvider, Price, CodeBar. The oly two which are important to us are Name and CodeBar.
To prevent duplication mistake results, but providing the opportunity to keep duplicated codes, I've added a button that warns user about this issue. So here is the code of that button.
Code:
Dim Magazine As Database
Dim Verification As Recordset
Dim CodProd As Recordset
Dim n As Integer
Dim way As String
Dim List As String
Dim SQL2 As String
Dim SQL As String
Dim CodProduct As String
Dim NameBarrasDuplicado As String
Set Magazine = CurrentDb
SQL = " SELECT Warehouse.CodeBar FROM Warehouse GROUP BY Warehouse.CodeBar HAVING (((Count(Warehouse.[CodeBar]))>1))"
Set Verification = Magazine.OpenRecordset(SQL, dbOpenDynaset)
If (Verification.RecordCount > 0) Then
SQL2 = "SELECT Warehouse.Name FROM Warehouse WHERE Warehouse.CodeBar = '" & Verification.Fields(0) & " ' "
Set CodProd = Magazine.OpenRecordset(SQL2, dbOpenDynaset)
CodProd.MoveFirst
Do Until Verification.EOF
Do Until CodProd.EOF
CodProduct = CodProd.Fields(0) & ", " & CodProduct & ""
MsgBox (CodProduct)
CodProd.MoveNext
Loop
CodProd.MoveFirst
List = List & ", " & Verification.Fields(0)
Verification.MoveNext
Loop
MsgBox ("Warehouse duplicated products are: " & CodProduct & " for these Barcodes : " & List & ".")
Else
MsgBox ("Everything OK, no duplicated codes found")
End If
Unfortunately, the result of the before mentioned algorithm is this (Please, see the attached link to see the screen capture):
[Result of this algorithm in a MsgBox][1]
The results are wrong. Instead of showing the Names of the products that share the duplicated BarCode, it shows the same Names, with no relation about the barcodes. I wish to point that the duplicated barcodes are ok, they are the ones. But the program fails when it's about to show the product names that have these duplicated codes.
Anyone can give a clue about this? What is making the second recordset to only save two product's name instead of one?
[1]: https://i.stack.imgur.com/XXvY1.jpg
Last edited: