Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

VB - Looping to Add records with a DAO instruction to test if record already exists

Hello all you experts!

I have a procedure that is taking way too long, and a lot longer than I imagined it would to run.

Essentially, I have a vb that adds records to a table.  Whatever the user has indicated they want added it will run to add.  HOWEVER, before it adds them, it tests to make sure the record hasn't already been defined in the table.  I do that test with a xxxx.DAO.Recordset and strSQLxx string

Not completely understanding DAO.Recordset and requery I don't know if my process is so slow because of my ordering or my testing.

My entire code is as follows.  Am I doing the SET rs..... correct or is there a better way?

Private Sub cmdSavedata()
Dim q As Long, r As Long
Dim s As Long, s1 As Long
Dim t As Long
Dim u As Long, u1 As Long
Dim v As Long
Dim b As Long, p As Long
Dim w As Long, x As Long, y As Long, z As Long
Dim strCo As String, strBkType As String, strState As String
Dim rs As DAO.Recordset
Dim strSQL As String

q = Me.cboSelectCounty.Column(0)
r = Me.cboSelectBkType.Column(0)

With Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Bks].Form
    If ![cboSelectBk].Visible = True Then
        s = ![cboSelectBk].Column(2) ' Main Book Nbr
        t = ![cboSelectBk].Column(3) ' Sub Book Nbr
        b = 1
    Else
        s = ![cboSelectBkBegin].Column(2) ' Main Book Nbr Begin Range
        s1 = ![cboSelectBkEnd].Column(2) ' Main Book Nbr End Range
        t = ![cboSelectBkBegin].Column(3) ' Sub Book Nbr
        b = (s1 - s) + 1
    End If
End With

With Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form
    If ![cboSelectPg].Visible = True Then
        u = ![cboSelectPg].Column(1) ' Main Page#
        p = 1
    Else
        u = ![cboSelectPgBegin].Column(1) ' Main Page# Begin Range
        u1 = ![cboSelectPgEnd].Column(1) ' Main Page# End Range
        p = (u1 - u) + 1
    End If
End With

If Nz(Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form![cboSelectSubPg], 1) = 1 Then
    v = 1
Else
    v = Me.[sfrmsys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty_Pgs].Form![cboSelectSubPg].Column(0) ' Sub Page #
End If

strState = Me.cboSelectState.Column(1)
strCo = Me.cboSelectCounty.Column(3)
strBkType = Me.cboSelectBkType.Column(2)

w = u
x = v
y = 0
z = 0

If b > 1 Then ' More than 1 Book#?
    If p > 1 Then ' More than 1 Page#?
        '*** Multiple Books & Pages
        Do Until z = b ' THIS RUNS IF MULTI BOOKS AND PAGES
            Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
            Forms![frm_ProcessingWait].Form![txtwait].SetFocus
            Do Until y = p
                strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
                Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
                If rs.RecordCount = 0 Then
                    CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
                Else
                    MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
                    "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
                End If
                u = u + 1
                y = y + 1
            Loop
            y = 0
            u = w
            z = z + 1
            s = s + 1
            Forms![frm_ProcessingWait].Form![txtBkNbr].Value = "Updating Book # " & s
            Forms![frm_ProcessingWait].Form![txtBkNbr].SetFocus
        Loop
    Else
        ' ***** Multiple Books, 1 Page only
        Do Until z = b ' THIS RUNS IF MULTI BOOKS BUT ONLY ONE PAGE
            Do Until y = p
                CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
                u = u + 1
                y = y + 1
            Loop
            z = z + 1
            t = t + 1
        Loop
    End If
Else
    If p > 1 Then ' More than 1 Page#?
    '*** One Book With Multiple Pages
        Do Until y = p ' THIS RUNS IF ONLY ONE BOOK AND MULTI PAGES
            strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
            Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
            If rs.RecordCount = 0 Then
                CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
            Else
                MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
                "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
            End If
            u = u + 1
            y = y + 1
        Loop
    Else
    
    ' **** RUNS IF ONLY ONE BOOK AND ONE PAGE
        strSQL = "SELECT CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID FROM qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty WHERE CountyCodeID=" & q & " AND RecBkTypeID=" & r & " AND recBkNbr=" & s & " AND RecBkNbrSubID=" & t & " AND RecBkPg=" & u & " AND RecBkPgSubID=" & v
        Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
        If rs.RecordCount = 0 Then
            CurrentDb.Execute "INSERT INTO qrysys_ValidRecBkTypeRecBkNbrBkNbrSubPgNbrPgNbrSubByCounty (CountyCodeID, RecBkTypeID, recBkNbr, RecBkNbrSubID, RecBkPg, RecBkPgSubID) VALUES(" & q & "," & r & "," & s & "," & t & "," & u & "," & v & ")", dbFailOnError
        Else
            MsgBox "The " & strBkType & " Book # and Page# you entered has already been created for " & strCo & ", " & strState & " " & strBkType & "s." & vbNewLine & vbNewLine & _
            "Please review the Book Type, Book # and Page # to make sure you are not trying to create duplicate information.", vbOKOnly + vbExclamation, "Error - Duplicate Info"
        End If
    End If

End If

rs.Close
Set rs = Nothing
DoCmd.Close acForm, "frm_ProcessingWait", acSaveNo

End Sub

Open in new window

SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<off topic> You're up awfully late, cap :) (or very, very early)
Avatar of wlwebb

ASKER

Yes.... All of them are.  This is a many to many table.

Note, even the recBkNbr and RecBkPg are indexed these are actually ID fields that I just used the LongNumber as the Key
Avatar of wlwebb

ASKER

PS..... I don't sleep very much.......
(very, very early) ;-)
Avatar of wlwebb

ASKER

As a Clarification....

I have a Table for each of those items.  (That way Admin can control Book Types, Book Numbers (and a Book Sub Number), Book Page Number begin and ending (and a Book Page sub number)
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

Cap........
So you get a mental pic.....

County's have Book Types

Book Types have Book Numbers (and Book Sub Numbers)

Book Numbers have Book Pages (and Book Sub Pages)

I have a Table for County's
I have a Table for Book Types
I have a Table for Book Numbers
I have a Table for Book Sub Numbers
I have a Table for Book Pages
I have a Table for Book Sub Pages

Step one is Define Book # With Book Number + Book Sub Number
Step two is Define Book Pg with Book Page + Book Sub Page
Step three Define County and Book Type

Step four Define County+Book Type with Book #s
Step five Define County+BookType+Book #'s with Book Pg (with sub pg_

Thus the qry you all are looking at is the combination of all of those limitations
Avatar of wlwebb

ASKER

Scott

Will changing that code change how it tests with each looping in terms of speed ?????
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Will changing that code change how it tests with each looping in terms of speed ?????

Open in new window

That's hard to say without testing, but anytime you hit the database you should request as little information as needed. You're looking for a Count only, so there's no reason to return other fields (unless you use them down the line for something else).

Regarding Dale's suggestion:

The value of "u" changes for each iteration of the Inner Loop, and the value of "s" changes for each iteration of the Outer Loop. The initial creation of the recordset includes those values in the criteria, but is OUTSIDE of those loops, so it would seem that you need to run your SQL again for each IL and each OL iteration.

The use of the existing recordset may make a difference in speed, assuming that recordset is updateable. With a M-2-M join this is often not the case.

Curious still how long the operation runs, and how many records in total you're inserting.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

I am awarding all responders equal points based on nbr of responses.  The input is great so I am also going to open a NEW QUESTION detailing more about what I've got/done so that more points can be awarded than just the original 500 because the issue is most critical
Avatar of wlwebb

ASKER

EE Moderator.
If you review the original question, the questions answers led to the realization that it should have been broken down into smaller portions.  The discussion on original link led to that conclusion.  However the ultimate solution has not be achieved.  The code here only works if you add multi page to multi books.  The original code I posted had various combinations if single book, multi page,  multi book single page, single book single page etc..