Solved

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

Posted on 2012-04-03
11
381 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Excel Pivot Table and MS Access Pivot Table 4 82
Access lists formating 8 50
MS ACCESS VBA FORMATTING 9 63
Queries: Select, then Append, then Delete 8 41
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 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