Solved

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

Posted on 2012-04-03
11
372 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now