Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 07 VBA  Recordset No Value Given for One or More Required Parameters

Posted on 2012-04-03
11
Medium Priority
?
397 Views
Last Modified: 2012-04-28
Below is some code I wrote to output results of a query to Excel.  It gets stumped at the line :mm.Open qrytext, , adOpenStatic with the error noted in the Title line.  

I know the field names and query and table names are correct because I copy/pasted the SQL from a WORKING access query and performed necessary " & _ breaks and ; removals.

Any suggestions?

Private Sub mm_items_button_Click()

Dim mycnxn As ADODB.Connection
Dim mm As New ADODB.Recordset
Dim icol As Integer
Dim irow As Integer
Dim xlobj As Object
Dim sheet As Object
Dim qrytext As String
Dim owner As String


Set mycnxn = CurrentProject.Connection
Set mm.ActiveConnection = mycnxn

Set xlobj = CreateObject("Excel.Application")
xlobj.Workbooks.Add
xlobj.Application.Visible = True

qrytext = "SELECT DISTINCT qry_pplc_create_prod_sheets.Company, qry_pplc_create_prod_sheets.Vendor, qry_pplc_create_prod_sheets.[Product Name], qry_pplc_create_prod_sheets.MaintStart, qry_pplc_create_prod_sheets.MaintEnd, qry_pplc_create_prod_sheets.[Finance Contract ID], qry_pplc_create_sheets_dollars.SumOfCost, qry_pplc_create_prod_sheets.CancelTerms, qry_pplc_create_prod_sheets.[Product Owner], qry_pplc_create_prod_sheets.[# of units purchased], qry_pplc_create_prod_sheets.[# of units on maintenance], qry_pplc_create_sheets_dollars.PROD_ID, qry_pplc_create_prod_sheets.ProdFunction, tbl_opco_percents.Mickey, tbl_opco_percents.mouse, tbl_opco_percents.donald, tbl_opco_percents.duck, tbl_opco_percents.method, qry_pplc_create_prod_sheets.Notes, qry_pplc_create_prod_sheets.PPLCRevDate" & _
" FROM (qry_pplc_create_prod_sheets LEFT JOIN qry_pplc_create_sheets_dollars ON qry_pplc_create_prod_sheets.[Product ID] = qry_pplc_create_sheets_dollars.PROD_ID) LEFT JOIN tbl_opco_percents ON qry_pplc_create_prod_sheets.[Product ID] = tbl_opco_percents.PRD_ID" & _
" ORDER BY qry_pplc_create_prod_sheets.[Finance Contract ID]"


mm.Open qrytext, , adOpenStatic



Set sheet = xlobj.ActiveWorkbook.Worksheets("sheet1")



mm.MoveFirst
irow = 1
    sheet.Cells(irow, 1).Value = owner

Do Until mm.EOF
owner = mm.Fields("Product Owner")
sheet.Activate
If owner <> mm.Fields("Product Owner") Then
    sheet.Cells(irow, 1).Value = owner
With sheet
irow = irow + 1
    .Cells(irow, 2).Value = "CO"
    .Cells(irow, 3).Value = "Product Name"
    .Cells(irow, 4).Value = "Function"
    .Cells(irow, 5).Value = "Maint Starts"
    .Cells(irow, 6).Value = "Maint Ends"
    .Cells(irow, 7).Value = "Cancel Terms"
    .Cells(irow, 8).Value = "# on Maint"
    .Cells(irow, 9).Value = "FID"
    .Cells(irow, 10).Value = "Annual Cost"
    .Cells(irow, 11).Value = "Notes"
   .Cells(irow, 12).Value = "Mgmt Fee Method"
  .Cells(irow, 13).Value = "a%"
   .Cells(irow, 14).Value = "b %"
    .Cells(irow, 15).Value = "c %"
   .Cells(irow, 16).Value = "d %"
    .Range("L:L").ColumnWidth = 45
End With
'research shading and bolding
Else
irow = irow + 1
    With sheet
    .Cells(irow, 2).Value = mm.Fields("Company")
    .Cells(irow, 3).Value = mm.Fields("Product Name")
    .Cells(irow, 4).Value = mm.Fields("ProdFunction")
    .Cells(irow, 5).Value = mm.Fields("MaintStart")
    .Cells(irow, 6).Value = mm.Fields("MaintEnd")
    .Cells(irow, 7).Value = mm.Fields("Cancel Terms")
    .Cells(irow, 8).Value = mm.Fields("# of units on Maintenance")
    .Cells(irow, 9).Value = mm.Fields("Finance Contract ID")
    .Cells(irow, 10).Value = mm.Fields("SumofCost")
    .Cells(irow, 11).Value = mm.Fields("Notes")
    .Cells(irow, 12).Value = mm.Fields("Method")
    .Cells(irow, 13).Value = mm.Fields("Mickey")
    .Cells(irow, 14).Value = mm.Fields("Mouse")
    .Cells(irow, 15).Value = mm.Fields("Donald")
    .Cells(irow, 16).Value = mm.Fields("Duck")
    irow = irow + 1
End With
End If
    mm.MoveNext
 
 Loop


End Sub
0
Comment
Question by:jwandmrsquared
  • 6
  • 5
11 Comments
 

Author Comment

by:jwandmrsquared
ID: 37803812
I DO have parameters set up for the underlying qry_pplc_create_prod_sheets but I thought that wouldn't matter since I am using the RESULTS of a saved query (one that shows in the query list).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37804354
1. Trouble shooting basics...
Before ever creating any recordset code, does the SQL string run on its own?

2. There is no need for line continuation characters in an SQL string.
So remove them.
Again, test the Raw SQL before ever worrying about "readability"

So,
..if you create a new query, and drop this in as the SQL, what happens:

SELECT DISTINCT qry_pplc_create_prod_sheets.Company, qry_pplc_create_prod_sheets.Vendor, qry_pplc_create_prod_sheets.[Product Name], qry_pplc_create_prod_sheets.MaintStart, qry_pplc_create_prod_sheets.MaintEnd, qry_pplc_create_prod_sheets.[Finance Contract ID], qry_pplc_create_sheets_dollars.SumOfCost, qry_pplc_create_prod_sheets.CancelTerms, qry_pplc_create_prod_sheets.[Product Owner], qry_pplc_create_prod_sheets.[# of units purchased], qry_pplc_create_prod_sheets.[# of units on maintenance], qry_pplc_create_sheets_dollars.PROD_ID, qry_pplc_create_prod_sheets.ProdFunction, tbl_opco_percents.Mickey, tbl_opco_percents.mouse, tbl_opco_percents.donald, tbl_opco_percents.duck, tbl_opco_percents.method, qry_pplc_create_prod_sheets.Notes, qry_pplc_create_prod_sheets.PPLCRevDate FROM (qry_pplc_create_prod_sheets LEFT JOIN qry_pplc_create_sheets_dollars ON qry_pplc_create_prod_sheets.[Product ID] = qry_pplc_create_sheets_dollars.PROD_ID) LEFT JOIN tbl_opco_percents ON qry_pplc_create_prod_sheets.[Product ID] = tbl_opco_percents.PRD_ID ORDER BY qry_pplc_create_prod_sheets.[Finance Contract ID]
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37804375
OOPS!
Sorry, I see that you already confirmed what I stated above...


Since you are only using certain things once, try this simple syntax first:


    Dim rst As New ADODB.Recordset
    Dim strSql As String
   
    strSql = "SELECT ...... FROM ....."
    rst.Open strSql, CurrentProject.Connection
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:jwandmrsquared
ID: 37805924
Boag - I did try that already, removing all the joins, and it still fails with the same error.  I did finally find an obscure microsoft support article last night basically saying I have to populate ANY parameters for ADODB recordsets in my code no matter how far back they go.  There is a and ADODB command that does this but I've never used it.  Does anyone know of a better way than the ADODB command for parameters? Should I just create a table with the results and SQL that?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37806413
So you are saying that the code I posted does not work for you?

<microsoft support article last night basically saying I have to populate ANY parameters for ADODB recordsets in my code no matter how far back they go.>
Please clearly define what you are calling a "Parameter" in this context...?
I don't see any parameters in your SQL...
    WHERE SomeField=[Enter A Value]
Please post this link
0
 

Author Comment

by:jwandmrsquared
ID: 37806709
The query called "qry_pplc_create_prod_sheets" is constructed from a table and the results of "qry_pplc_create_prod_sheets_dollars".  qry_pplc_create_prod_sheets has one parameter, and qry_pplc_creat_prod_sheets_Dollars has a different parameter and a few criteria.  I'm thinking the criteria are fine, but somehow the ADODB command is confused by the parameters, even though if I run the above SQL statement in the Access Query window it works just fine.

Is it possible that I have to define those parameters within the ADODB object? If so, is it ADODB.Parameters that doest this? DOes it matter that my upline queries have parameter criteria, or is the error being generated by something else?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37806825
...Oh,...
That was not clear from your original posts...

In any event if the parameter is available when the code is run, it should be fine...

Have you considered using DAO for this instead...
0
 

Author Comment

by:jwandmrsquared
ID: 37806920
I've been warned to stay away from DAO so no, I haven't used DAO.  The parameter is available but there is the magic in VBA that says I have t point to the parameter values and that is where it gets muddy.  There is the adodb.command object to connect to the database and then the adodb.parameter to somehow pass the parameter to the underlying query.  Since this is way above my pay grade, I cheated and created a staging table from the results of the sql above and am using that as my recordset.  I would like to know how to do this parameter business in VBA though.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37808041
<I've been warned to stay away from DAO>
By whom?

There is a very old debate about DAO vs ADO.

The bottom line is that DAO is better if your data is in Access. (which your is...)
0
 

Accepted Solution

by:
jwandmrsquared earned 0 total points
ID: 37861140
I ended up creating a staging table from the results of the query series above, and then tying my SQL to that table.  Parameters in upstream queries + ado = unnecessary complexity.
0
 

Author Closing Comment

by:jwandmrsquared
ID: 37905352
I never really got an answer about how to declare a parameter from an upstream query; it was possibly my error in not being clear on the query-chain construct.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

876 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