Access VBA loop through SQL stored procedure recordset into subform

Question

I am working on a solution for a SQL database with Access form for data entry. In this application, I have customers, and am working to provide a sub-form that will show any possible duplicates.

In another post, I have found a solution for a stored procedure, which will identify 3 types of duplicates (exact, ones with a "difference" factor between 3 columns, and ones with the same name, where one address is null and one is not). This stored procedure also looks for one of the dupes, in each of the 3 types, to be the ID of the current customer I am evaluating.

That solution is here: Stack Overflow Post 64932557

Now, on the Access side, I made a private function on the main Customer's form, where I run the stored procedure, pass the ID parameter, and then seek to only show the subform and subform tab/page if there are results. All of that seems to work, but then when I loop through the recordset output from the stored procedure, I need to map that to the unbound fields of the subform. It does this for 1 of the duplicates, but not for all the results.

I am testing with a record that has 3 duplicates (including it's own record being returned). I only get 1 record on the continuous subform, and I should get 2 if not 3, if it will include it's own record.

I run this function as part of my navigation on the form, as the user goes to the next record, previous, uses a combo to jump to a record, or brings up a form to search for a record and then goes to that record.

Private Function FindDuplicates()
    Dim cmd As New ADODB.Command
    Dim conn As ADODB.Connection
    Dim prm As ADODB.Parameter
    Dim strConn As String
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim dRecs As Integer

    strConn = "Provider=sqloledb;Server=MySQLServerName;Database=MyDBName;Trusted_Connection=yes;"

    Set conn = New ADODB.Connection
    conn.Open strConn

    Set cmd = New ADODB.Command
    cmd.CommandText = "sp_FindMyDuplicates"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = conn

    Set prm = cmd.CreateParameter("CID", adInteger, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("CID").Value = Me.ID

    'Execute the stored procedure
    Set rs = cmd.Execute
    dRecs = -1
    With rs

'        Debug.Print .RecordCount & " is record count"
    
        If (rs.EOF = True) And (rs.BOF = True) Then
            Me.pgDuplicates.Visible = False
        Else
            Me.pgDuplicates.Visible = True
    
            If Not .BOF And Not .EOF Then
                
                While (Not .EOF)
                    dRecs = dRecs + 1
                    'Debug.Print "customer ID: " & rs.Fields("ID") & " customer name: " & rs.Fields("FirstName")
                    Me.frmCustomers_subDuplicates.Form.txtFirst = rs.Fields("FirstName")
                    Me.frmCustomers_subDuplicates.Form.txtLast = rs.Fields("LastName")
                    Me.frmCustomers_subDuplicates.Form.txtAddress1 = rs.Fields("Add1")
                    Me.frmCustomers_subDuplicates.Form.txtAddress2 = rs.Fields("Add2")
                    Me.frmCustomers_subDuplicates.Form.txtCity = rs.Fields("City")
                    Me.frmCustomers_subDuplicates.Form.txtState = rs.Fields("State")
                    Me.frmCustomers_subDuplicates.Form.txtZip = rs.Fields("Zip")
                .MoveNext
                Wend
                Me.frmCustomers_subDuplicates.Form.txtDuplicateCount = dRecs & " Duplicates Found"
            End If
        End If
    
        .Close
    End With
    'Close the connection
    conn.Close
    
End Function

Anyone see why I am not getting all the records in the recordset?

Side note: if this is for SQL Server - you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 1, 2020 at 17:42
Well that's annoying. I like using sp because I like denoting the type of object I'm calling. I also use vw, and tbl prefixes. I can't see changing all my schemas in all my dbs, but I should certainly stop this going forward, I guess. Thanks for the heads up. — missscripty
– missscripty, Commented Jan 8, 2021 at 16:51
If you really MUST use a prefix - why not just use usp (for user stored procedure) or something like that? But really - based on a good naming strategy, prefixes really aren't that needed anymore...... — marc_s
– marc_s, Commented Jan 8, 2021 at 16:52

Olivier Jacot-Descombes · Accepted Answer · 2020-12-01 18:03:33Z

0

You are initializing dRecs with -1 instead of 0. So it will display one less.

It also seems that you are assigning the values to the same textboxes all the time, without adding new lines in the subform.

Insert the line

Me.frmCustomers_subDuplicates.SetFocus

before the loop and insert the line

DoCmd.GoToRecord , , acNewRec

after the line dRecs = dRecs + 1 to always insert a new record in the subform.

answered Dec 1, 2020 at 18:03

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

missscripty · Accepted Answer · 2021-01-08 17:00:04Z

I ended up getting it worked out. Thank you all for your helpful comments and suggestions.

Private Function FindDuplicates()
Dim cmd As New ADODB.Command
Dim conn As ADODB.Connection
Dim prm As ADODB.Parameter
Dim strConn As String
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim dRecs As Integer

If Not Me.NewRecord Then

    strConn = "Provider=sqloledb;Server=ServerName;Database=DatabaseName;Trusted_Connection=yes;"

    Set conn = New ADODB.Connection
    conn.Open strConn

    Set cmd = New ADODB.Command
    cmd.CommandText = "sp_FindMyDuplicates"
    cmd.CommandType = adCmdStoredProc
    cmd.ActiveConnection = conn

    Set prm = cmd.CreateParameter("CID", adInteger, adParamInput)
    cmd.Parameters.Append prm
    cmd.Parameters("CID").Value = Me.ID

    'Execute the Stored Procedure
    cmd.Execute
    If DCount("ID", "tblCustomerDupesTemp", "ID = " & Me.ID) = 0 Then
        Me.pgDuplicates.Visible = False
    Else
        Me.pgDuplicates.Visible = True
        Me.frmCustomer_subDuplicates.Form.Filter = "[ID] <> " & Me.ID & " And [AnchorID] = " & Me.ID
        Me.frmCustomer_subDuplicates.Form.FilterOn = True
        Me.frmCustomer_subDuplicates.Form.txtDuplicateCount = CStr(Me.frmCustomer_subDuplicates.Form.CurrentRecord) & " of " & _
        DCount("ID", "tblCustomerDupesTemp", "ID <> " & Me.ID) & " Duplicate Customer(s)"
        Me.frmCustomer_subDuplicates.Form.Requery
    End If
End If
End Function

Collectives™ on Stack Overflow

Access VBA loop through SQL stored procedure recordset into subform

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related