Access - Loop not creating expected number of new recs in a CurrentDb.Execute INSERT INTO

Posted on 2012-09-11
Last Modified: 2012-09-11
Hello All,
I am having trouble with a CurrentDb.Execute INSERT INTO that isn't creating the correct number of new records.
This is a tabbed form with multiple tabs each having a subform.

On the first tab I have an Unbound textbox to count the number of records in a query.  That textbox is named [txtCntActiveCurrencyDenominations]
The Control Source for that box is =DCount("[qry_Denomination_Currency_Active]![DenominationID]","[qry_Denomination_Currency_Active]","[qry_Denomination_Currency_Active]![DenominationID]")

The SQL of that query which is counts is
SELECT Denomination.DenominationID, Denomination.Denomination, Denomination.DenominationMultiple, Denomination.DenominationTypeID, Denomination.Active, DCount("DenominationID","qry_Denomination_Currency_Active","DenominationID <= " & [DenominationID]) AS CurrencyActiveDenominationSeq
FROM Denomination
WHERE (((Denomination.DenominationTypeID)=2) AND ((Denomination.Active)=-1));

Open in new window

Now then what I am expecting to happen in this case is that it will insert new records with a DenomintionID of 2, 3, 4, 5, 6 & 7
However, it is Inserting 2, 3, 4, 5 & 6  (NOT 7)

The code that Executes all of this is
Private Sub GenerateLVLMachineLines2(IsMachinePull As Boolean)
Dim bytCounter As Byte, lngMyRptgSeqID As Long, lngMyShiftRptgLVLCtlID As Long, i As Integer, z As Integer
Dim strSQL As String

lngMyRptgSeqID = GetMyShiftSeqID()
lngMyShiftRptgLVLCtlID = GetMyShiftRptgLVLCtlID()

    i = 1   ' This counter is for Inserting Active Currency Seq to Machine Pull tbl
    z = DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl", "ShiftRptgMachPullCountCtlID")

    Do Until bytCounter = Me.txtNbrMachines
            CurrentDb.Execute "INSERT INTO ShiftReportingLVL (ShiftRptgLVLCtlID, LVLPositionNbr, RptgSeqID, MachinePulled) VALUES (" & lngMyShiftRptgLVLCtlID & "," & bytCounter + 1 & "," & lngMyRptgSeqID & "," & True & ")", dbFailOnError
            bytCounter = bytCounter + 1
    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect]![txtCntActiveCurrencyDenominations]
        strSQL = "INSERT INTO ShiftReportingMachinePullDetails (ShiftRptgMachPullCountCtlID, DenominationID) SELECT " & z & ", DenominationID FROM [qry_Denomination_Currency_Active] WHERE [CurrencyActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError
        i = i + 1
End Sub

Open in new window

Question by:wlwebb

    Author Comment

    Just as an FYI, when I look at the form that unbound text box Dcount result is 6.
    LVL 29

    Accepted Solution

    Do Until i =
    Do While i<=
    LVL 29

    Expert Comment

    In addition, change these for clarity:
    line 6
    z = DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl")
    textbox controlsource

    Author Comment

    Irog......I think you meant line 9 but,
    Didn't understand that last comment.......
    Do I add textbox controlsource as a new line of code???? or am I replacing line 9
    LVL 29

    Expert Comment

    You're right, I meant replace line 9.  The 2nd code I gave you was a shortened version of what you have in your textbox named txtCntActiveCurrencyDenominations.

    Author Comment

    Ok... I understand........Thanks

    Author Closing Comment

    Thank you as always!!!  And thank you for the additional comment!

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now