Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

Posted on 2012-09-11
Medium Priority
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
  • 4
  • 3

Author Comment

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

Accepted Solution

IrogSinta earned 2000 total points
ID: 38389533
Do Until i =
Do While i<=
LVL 29

Expert Comment

ID: 38389538
In addition, change these for clarity:
line 6
z = DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl")
textbox controlsource
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 38389542
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

ID: 38389548
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

ID: 38389571
Ok... I understand........Thanks

Author Closing Comment

ID: 38389572
Thank you as always!!!  And thank you for the additional comment!

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

577 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