VBA search from two Recordsets with Loops one into another (1 Viewer)

AccOUCH

Registered User.
Local time
Today, 08:14
Joined
Sep 27, 2018
Messages
25
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.

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:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:14
Joined
Oct 29, 2018
Messages
21,358
Hi,


Not sure why you're getting the stated result but I just have a quick question for you. In this line:
Code:
CodProduct = CodProd.Fields(0) & ", " & CodProducto & ""

Where is "CodProducto" coming from?
 

AccOUCH

Registered User.
Local time
Today, 08:14
Joined
Sep 27, 2018
Messages
25
Hi,


Not sure why you're getting the stated result but I just have a quick question for you. In this line:
Code:
CodProduct = CodProd.Fields(0) & ", " & CodProducto & ""

Where is "CodProducto" coming from?

From CodProduct, just edited now.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:14
Joined
May 21, 2018
Messages
8,463
Code:
SQL2 = "SELECT Warehouse.Name FROM Warehouse WHERE Warehouse.CodeBar = '" & Verification.Fields(0) & " ' "
Set CodProd = Magazine.OpenRecordset(SQL2, dbOpenDynaset)
CodProd.MoveFirst
That has to go inside the loop so that it changes for each new codebar. Now it is only the first warehouse
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:14
Joined
May 21, 2018
Messages
8,463
Code:
Do Until Verification.EOF
                 SQL2 = "SELECT Warehouse.Name FROM Warehouse WHERE Warehouse.CodeBar = '" & Verification.Fields(0) & " ' "
                 Set CodProd = Magazine.OpenRecordset(SQL2, dbOpenDynaset)
                 Do Until CodProd.EOF
                    CodProduct = CodProd.Fields(0) & ", " & CodProducto & ""
                    MsgBox (CodProduct)
                    CodProd.MoveNext
                Loop
              CodProd.MoveFirst
              List = List & ", " & Verification.Fields(0)
              Verification.MoveNext
        Loop
 

AccOUCH

Registered User.
Local time
Today, 08:14
Joined
Sep 27, 2018
Messages
25
Brilliant! It worked properly. You are a genius MajP
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:14
Joined
May 21, 2018
Messages
8,463
Thanks, but I think you just needed a fresh set of eyes. Those are the kind of errors (placement errors) that get hard to see.
 

Users who are viewing this thread

Top Bottom