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, with a variable and a Select item

Posted on 2012-09-11
6
Medium Priority
?
537 Views
Last Modified: 2012-09-12
Followup question where I needed help on the syntax of a currendb.execute INSERT INTO where the item's being Inserted where a list from a query.  

The code that currently works but I have to modify is:

    
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



WHAT I ATTEMPTED TO CHANGE strSQL IT TO Was
i = 1
z = DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl", "ShiftRptgMachPullCountCtlID")
Do Until i = Forms![frm_DataReporting]![LVLReportingTypeSelect]![txtCntActiveCurrencyDenominations]

        strSQL = "INSERT INTO ShiftReportingMachinePullDetails (ShiftRptgMachPullCountCtlID, DenominationID) z AND SELECT DenominationID FROM [qry_Denomination_Currency_Active] WHERE [CurrencyActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError
        i = i + 1
    Loop

Open in new window


Of course that doesn't work I THINK because I'm mixing a variable inserting with a Select inserting.....I THINK. :-(
0
Comment
Question by:wlwebb
6 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38389430
Try this:
    i = 1
    z = DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl"
    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
    Loop

Open in new window

0
 
LVL 17

Expert Comment

by:ramrom
ID: 38389435
What?
0
 

Author Comment

by:wlwebb
ID: 38389455
IROG,
Yes that did it!!! I didn't think I would include z since it wasn't in the query that info was being "selected" from.....


HOWEVER,

I'm still having trouble relative to the number of Denominations it's inserting...  

That's on question

Related EE question regarding - trouble re: number of inserted records
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Closing Comment

by:wlwebb
ID: 38389458
That did solve this question... thanks..
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38389489
I'm still having trouble relative to the number of Denominations it's inserting...  
I don't quite understand what you mean.  However, it looks like mbizup posted a response for you on the other thread.
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38390293
I'm a bit confused by this., because unless you include the Z in the WHERE clause of the SELECT statement, you are going to continuously expand the list of values.  For example, if you start out with:

ShiftRptgMachPullCountCtlID  DenominationID
                         1                                              1
                         1                                              2

after your first run of this, you will have

ShiftRptgMachPullCountCtlID  DenominationID
                         1                                              1
                         1                                              2
                         1                                              1
                         1                                              2

Then, after the 2nd time you run this, you will have:

ShiftRptgMachPullCountCtlID  DenominationID
                         1                                              1
                         1                                              2
                         1                                              1
                         1                                              2
                         1                                              1
                         1                                              1
                         1                                              2
                         1                                              2

My guess is, that what you really want at that point would be

ShiftRptgMachPullCountCtlID  DenominationID
                         1                                              1
                         1                                              2
                         2                                              1
                         2                                              2
                         3                                              1
                         3                                              2

If that is the case, then I would recommend:

    i = 1
    z = NZ(DMax("ShiftRptgMachPullCountCtlID", "ShiftReportingMachinePullCountCtl"),0) + 1
    strSQL = "INSERT INTO ShiftReportingMachinePullDetails " _
                  & "(ShiftRptgMachPullCountCtlID, DenominationID) " _
                  & "SELECT " & z & ", DenominationID " _
                  & "FROM [qry_Denomination_Currency_Active] " _
                  & "WHERE [CurrencyActiveDenominationSeq] >=1 " _
                  & "AND = [CurrencyActiveDenominationSeq] <= " _
                  & Forms![frm_DataReporting]![LVLReportingTypeSelect]![txtCntActiveCurrencyDenominations]
     CurrentDb.Execute strSQL, dbFailOnError

With this syntax, you don't need the loop
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

578 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