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

x
?
Solved

Access - currentdb.execute INSERT INTO with a From clause

Posted on 2012-09-11
24
Medium Priority
?
697 Views
Last Modified: 2012-09-15
Hello everyone,

Struggling with another currentdb.execute clause......

Trying to INSERT INTO a table named [ShiftReportingMachinePullDetails] the field DenominationID fields FROM a query [qry_Denomination_Currency_Active].

The SQL of my query 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


The portion of the overall code I have for the INSERT INTO Private sub is
Dim i as Integer
  i = 1 
    
    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].[txtCntActiveCurrencyDenominations]
        CurrentDb.Execute "INSERT INTO ShiftReportingMachinePullDetails (DenominationID) VALUES (" & DenominationID & ")" & " FROM [qry_Denomination_Currency_Active]" & " WHERE [CurrencyActiveDenominationSeq]= " & i, dbFailOnError
        i = i + 1
    Loop

Open in new window


I keep getting Compile error:
Variable not defined.
0
Comment
Question by:wlwebb
  • 12
  • 7
  • 4
23 Comments
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 38389264
try this:

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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38389318
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
0
 

Author Comment

by:wlwebb
ID: 38389364
Thanks Mbiz

Just curious, does currentdb.execute Insert Into not work because it doesn't allow a From clause or because of another reason?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 1000 total points
ID: 38389374
The syntax was a little bit off.

If you are want to select fields from a table or query, you need a complete subquery (you were missing the SELECT clause.

ie:

INSERT INTO Tablea (Field1)
SELECT Field1 FROM TableOrQueryB  WHERE etc '<---- this part needs to be a syntactically correct query
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38389394
Is  [LVLReportingTypeSelect] a subform of  form frm_DataReporting ?
0
 

Author Comment

by:wlwebb
ID: 38389409
Cap1, Yes  [LVLReportingTypeSelect] is a subform of  form frm_DataReporting
0
 

Author Comment

by:wlwebb
ID: 38389411
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...
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 total points
ID: 38389413
then i am wondering that you are not getting any error on this line

 Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].[txtCntActiveCurrencyDenominations]


where it should be


    Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtCntActiveCurrencyDenominations]
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38389420
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.
0
 

Author Comment

by:wlwebb
ID: 38389438
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.
0
 

Author Comment

by:wlwebb
ID: 38389440
Mbiz......
WHere do I do that???????????
0
 

Author Comment

by:wlwebb
ID: 38389448
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]")
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38389461
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

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38389468
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

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38389473
try doing a compact and repair first.
then from VBA window  Debug>Compile
correct any errors raised
0
 

Author Comment

by:wlwebb
ID: 38389477
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.
0
 

Author Comment

by:wlwebb
ID: 38389481
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38389484
Did you test those last two comments?  I think they might have resolved it...
0
 

Author Comment

by:wlwebb
ID: 38389510
In the middle of that and debug.compile
0
 

Author Comment

by:wlwebb
ID: 38389516
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.
0
 

Author Comment

by:wlwebb
ID: 38389519
0
 

Author Closing Comment

by:wlwebb
ID: 38393592
Thanks Mbiz & Cap1 ... Always great to hear from you.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38394268
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.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

572 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