Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-04-03
11
Medium Priority
?
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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…

715 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