Access - currentdb.execute INSERT INTO with a From clause

test this


Dim i as Integer
dim strSQL as string
  i = 1
   
    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtCntActiveCurrencyDenominations]
strSQL = "INSERT INTO ShiftReportingMachinePullDetails (DenominationID)   SELECT DenominationID FROM [qry_Denomination_Currency_Active] WHERE [CurrencyActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError
        i = i + 1
    Loop

Thanks Mbiz

Just curious, does currentdb.execute Insert Into not work because it doesn't allow a From clause or because of another reason?

Is  [LVLReportingTypeSelect] a subform of  form frm_DataReporting ?

Cap1, Yes  [LVLReportingTypeSelect] is a subform of  form frm_DataReporting

PS, I am going to post a new question because I have to also add a "variable" (??? not sure that's the right term) to that Insert...

wlwebb,

You can use the Request Attention button to request a point split.   Capricorn1's comment modified a copy of  my post with a correction for the subform.

Cap1... I don't know.??????  Looking at the table ....pulldetails the denominationid's that right now it should be pulling in are 2, 3, 4, 5, 6 & 7.......... but it's only pulling 2, 3, 4, 5, 6.......

Right now my Denominations Table is DenominationsID
1 thru 7 but that query ignores number 1 (I don't want that particular DenomID coming into this table)  In that query I did a DCount to resequence the ID with an Expression:
CurrencyActiveDenominationSeq: DCount("DenominationID","qry_Denomination_Currency_Active","DenominationID <= " & [DenominationID])

I'm going to change it to your suggestion Cap and I understand why you point it out.

Mbiz......
WHere do I do that???????????

Cap 1

Made that change......  I am still only getting records created with DenomID 2 thru 6

This is the code for that unbound textbox [txtCntActiveCurrencyDenominations]

=DCount("[qry_Denomination_Currency_Active]![DenominationID]","[qry_Denomination_Currency_Active]","[qry_Denomination_Currency_Active]![DenominationID]")

If that modification were needed you would have gotten a compile error on Do Until line.

Are you saying that it compiled but produced unexpected results (I would have thought the .Form was needed too)?

Try this:

Dim i as Integer
dim strSQL as string
  i = 0
    
    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].[txtCntActiveCurrencyDenominations]
        i = i + 1
strSQL = "INSERT INTO ShiftReportingMachinePullDetails (DenominationID)   SELECT DenominationID FROM [qry_Denomination_Currency_Active] WHERE [CurrencyActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError

    Loop

Select allOpen in new window

If that doesn't work, try this - which has cap1's suggested modification:

Dim i as Integer
dim strSQL as string
  i = 0
    
    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtCntActiveCurrencyDenominations]
        i = i + 1
strSQL = "INSERT INTO ShiftReportingMachinePullDetails (DenominationID)   SELECT DenominationID FROM [qry_Denomination_Currency_Active] WHERE [CurrencyActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError

    Loop

Select allOpen in new window

try doing a compact and repair first.
then from VBA window  Debug>Compile
correct any errors raised

Since the actual original question was solved, I am going to open a new question regarding this outstanding issue.  I think that is only fair since it's a new problem not in the original question.

Irog answered my other question I opened just a minute ago.

After I post that question, I'll come back and put a link here to that question for ease of finding.

Cap 1
I was writing the new question to post and saw your new posting regarding compact and repair.... I'll give that a try before the new posting.

Did you test those last two comments?  I think they might have resolved it...

In the middle of that and debug.compile

Ok, all that works and no errors all debug done.  When I run this I'm still only getting DenomID's 2 thru 6....inserted as new records...

Since I think the original question was actually answered, I'm going to open a new question now.  Back in a sec with the link to that question.

New question link

Thanks Mbiz & Cap1 ... Always great to hear from you.

wlwebb,

You are welcome!


cap1,

I didn't want to say much before the question was reclosed, but it is worth mentioning that your comment at http:#a38389318 was a direct copy/paste of my earlier post - with a minor change/correction and no attribution.  

We've both been here long enough to know the importance of crediting others when using previous responses.  A comment such a "this is a correction (or addition) to the previous post by {expert's name}" is a simple courtesy that goes a long way.