Solved

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

Posted on 2012-04-03
11
376 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

911 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

22 Experts available now in Live!

Get 1:1 Help Now