Passing a parameter through VBA

[testing1two]

Hi, I have the following Visual Basic code which I am using to dynamically pass the parameters "SAFP" and date 10/31/2014 to the query:

Option Compare Database

'------------------------------------------------------------
' Run_Risk_Assessment_Report
'
'------------------------------------------------------------
'Original macro code
Function Run_Risk_Assessment_Report()
On Error GoTo Run_Risk_Assessment_Report_Err


Dim dbs As DAO.Database
Dim test1 As DAO.QueryDef

Dim qdf As DAO.QueryDef


Set dbs = CurrentDb

Set qdf = dbs.QueryDefs("A) Rpt Qry - Select Data Master")



DoCmd.SetWarnings False
' Clear out the Master Report Table
DoCmd.OpenQuery "Clear out the Master Report Table", acViewNormal, acEdit
' Run the query to make / update the Main Report Table
DoCmd.OpenQuery "A) Rpt Qry - Select Data Master", acViewNormal, acEdit
' update the grand totals
DoCmd.OpenQuery "Update Grand Total", acViewNormal, acEdit
' update the percentages in the Main Report Table
DoCmd.OpenQuery "Update Percentages in Report Master Report Table", acViewNormal, acEdit
' Runs the report
DoCmd.OpenReport "Risk Assessment Summary", acViewNormal, "", "", acNormal


Run_Risk_Assessment_Report_Exit:
Exit Function

Run_Risk_Assessment_Report_Err:
MsgBox Error$
Resume Run_Risk_Assessment_Report_Exit

End Function

When I run the query, i get the error "Item not Found in this collection"

 

[essaytee]

Turn off error checking and run the script. It should break on the offending line, that should at least point you in the right direction.

Steve.

 

[testing1two]

It's this line where it breaks:

qdf.Parameters("MainData.PortfolioNameAbr").Value = "SAFP"

In the actual query, when I use the static value "SAFP" everything works fine.

My query looks like this:

 

[essaytee]

It's this line where it breaks:
In the actual query, when I use the static value "SAFP" everything works fine.

Try this ( I haven't tested it, but it comes straight out of Access 97 Developer's Handbook pg 248).

Dim db as Database
Dim qdf as QueryDef
Dim prm as Parameter
Dim rst as Recordset

Set db = CurrentDb()
Set qdf = db.QueryDefs("YourQueryName")
For Each prm In qdf.Parameters
   prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

 

[JHB]

Open your query in SQL-View (design view), look what you are calling the parameters, let's say the parameter for the field "PortfolioNameAbr" is called "TheSAFP", then where you have the text "SAFP" in the criteria for "PortfolioNameAbr" field, replace it with [TheSAFP].

 

[testing1two]

Looks like I solved the original problem but now I am running across another issue. My original program was created through Macro which works ok as long as I continue to type in the parameters for each recordset. here is the original code:

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Run_Risk_Assessment_Report1
'
'------------------------------------------------------------
Function Run_Risk_Assessment_Report1()
On Error GoTo Run_Risk_Assessment_Report1_Err

DoCmd.SetWarnings False
' Clear out the Master Report Table
DoCmd.OpenQuery "Clear out the Master Report Table", acViewNormal, acEdit
' Run the query to make / update the Main Report Table
DoCmd.OpenQuery "A) Rpt Qry - Select Data Master", acViewNormal, acEdit
' update the grand totals
DoCmd.OpenQuery "Update Grand Total", acViewNormal, acEdit
' update the percentages in the Main Report Table
DoCmd.OpenQuery "Update Percentages in Report Master Report Table", acViewNormal, acEdit
' Runs the report
DoCmd.OpenReport "Risk Assessment Summary", acViewNormal, "", "", acNormal


Run_Risk_Assessment_Report1_Exit:
Exit Function

Run_Risk_Assessment_Report1_Err:
MsgBox Error$
Resume Run_Risk_Assessment_Report1_Exit

End Function

When I try to customize the code with passing parameters, SAFP and 10/31/2014, I get type mismatch error:

Function Run_Risk_Assessment_Report()
On Error GoTo Run_Risk_Assessment_Report_Err

DoCmd.SetWarnings False
' Clear out the Master Report Table
DoCmd.OpenQuery "Clear out the Master Report Table", acViewNormal, acEdit
' Run the query to make / update the Main Report Table

Dim dbs As DAO.Database

Dim qdf As DAO.QueryDef


Set dbs = CurrentDb
'from below original code
Set qdf = dbs.QueryDefs("A) Rpt Qry - Select Data Master")
'qdf.Execute

MsgBox ("database 2")

qdf.Parameters("[Enter Portfolio Name:]").Value = "SAFP"
MsgBox ("it worked")
qdf.Parameters("[Enter as of date:]").Value = "#10/31/2014#"
MsgBox ("before execute")
qdf.Execute


'DoCmd.OpenQuery "A) Rpt Qry - Select Data Master", acViewNormal, acEdit
' update the grand totals



DoCmd.OpenQuery "Update Grand Total", acViewNormal, acEdit
' update the percentages in the Main Report Table
DoCmd.OpenQuery "Update Percentages in Report Master Report Table", acViewNormal, acEdit
' Runs the report
DoCmd.OpenReport "Risk Assessment Summary", acViewNormal, "", "", acNormal


Run_Risk_Assessment_Report_Exit:
Exit Function

Run_Risk_Assessment_Report_Err:
MsgBox Error$
Resume Run_Risk_Assessment_Report_Exit

End Function

The code breaks at:

qdf.Execute

I get "Data Type mismatch in expression" error.

At the core of the problem, I am replacing

DoCmd.OpenQuery "A) Rpt Qry - Select Data Master", acViewNormal, acEdit

by:

Dim dbs As DAO.Database

Dim qdf As DAO.QueryDef


Set dbs = CurrentDb
'from below original code
Set qdf = dbs.QueryDefs("A) Rpt Qry - Select Data Master")
'qdf.Execute

MsgBox ("database 2")

qdf.Parameters("[Enter Portfolio Name:]").Value = "SAFP"
MsgBox ("it worked")
qdf.Parameters("[Enter as of date:]").Value = "#10/31/2014#"
MsgBox ("before execute")
qdf.Execute

in order to pass the parameter to the code. Any help will be appreciated.

 

[essaytee]

For testing purposes, what happens if you comment out the Parameter lines? If it's not the same failure what happens when commenting out either of the Parameter lines?

Can you upload an image of your existing query or at least showing the parameters part.

Another link Look at the second code snippet, it's more or less the same as yours so I'm suspecting a Parameter miss-spelling in yours. Not sure if this fixes it but remove the square brackets from your code version.

 

[JHB]

Show the SQL-String from the query.
Else post your database with some sample data, (ZIP IT) + name of the form/query in which you've the problem.

 

[testing1two]

I have run the query without the parameter and it works fine. The name of the query is "A) Rpt Qry - Select Data Master".

Attaching the database with some bogus data.

 

[JHB]

The problem is, you haven't created a parameter query!!
A parameter query starts with the keyword "PARAMETERS" followed by the "name" of the parameter + the type of the parameter:
Like below:

PARAMETERS [Enter Portfolio Name:] Text ( 255 ), [Enter as of date:] DateTime;
INSERT INTO MasterRe ....

I have attached the database, open the form I've created and click the button.
The query runs now, but the report doesn't, it ask for some parameters, you need to fix that.

You find the menu choose for parameters in the query view, just beside the sigma sign, (right side).

 

[testing1two]

Thanks for this. I found the Parameters in Query view, but where do I enter the "SAFP" and "10/31/2014" as parameters in this view?

How can I ask the user to enter the date every time the macro runs. In this case the first date is 10/31/2014, Currency is US (everytime)?

I would like to supply the portfolio names either in the macro or read from the table: "Portfolio List"

 

[JHB]

I'm not sure I really understand you question:
Try to run the query direct from the navigation window, is it what you're asking about?
You can also create a form in which both parameters are input/chosen from a textcontrol/combobox etc.
If the above isn't what you are asking for, then explain exactly you want to do!

 

[testing1two]

Yes, I need another parameter (date 10/31/2014) to be passed to the same query.

I also need to find out where is the code for the parameter placed in Access?

PARAMETERS [Enter Portfolio Name:] Text ( 255 ), [Enter as of date:] DateTime;
INSERT INTO MasterRe ....

Where do I put this?

 

[JHB]

Didn't you look in the attached database, the code behind the button on the form and the function in the module and the SQL-string for the query "A) Rpt Qry - Select Data Master"?
The code in the module is exactly the same as you showed in your post # 6!

 

[testing1two]

I got the parameters part for the 1st query. If I want to run the macro with several portfolios, I can use a loop to push all the parameter names (Porfolios: SAFP, ABCD, etc.) and keep the date fixed.

Two other questions:

1. Is it possible to pass parameters through vba to a SELECT query using the method above?

I have two queries which are asking for:

"Enter region - US or EUR"

and then date: 10/31/2014

2. The other problem I have on this is:

I am getting a prompt:
SumOfWARFAmount

And

SumOfParAmountTraded

I just click OK without entering anything and the macro just runs. Is there anyway to suppress this?

 

[JHB]

..
Two other questions:

1. Is it possible to pass parameters through vba to a SELECT query using the method above?

Yes - but why didn't you just try it, a few minutes work, then you could have answer your own question?

..
2. The other problem I have on this is:

I am getting a prompt:
SumOfWARFAmount

And

SumOfParAmountTraded

I just click OK without entering anything and the macro just runs. Is there anyway to suppress this?

You get that type of errors because SumOfWARFAmountand and SumOfParAmountTraded is unknown for Access.
You've to find out in which query the SumOfWARFAmountand and SumOfParAmountTraded are used.

 

[testing1two]

Yes - but why didn't you just try it, a few minutes work, then you could have answer your own question?

I tried it with this code:

Set qdf = dbs.QueryDefs("h-1) LRR across all portfolios")
'qdf.Execute
'
MsgBox ("database 3")
'
qdf.Parameters("[Enter region - US or EUR]").Value = "US"
MsgBox ("2nd query US")
qdf.Parameters("[Enter as of date]").Value = "10/31/2014"
MsgBox ("2nd query date")
qdf.Execute




'DoCmd.OpenQuery "Update Grand Total", acViewNormal, acEdit
' update the percentages in the Main Report Table

And got the error. " You cannot execute a Select query"

It looks like that the above is part of a larger "Update Grand Total" Query

You get that type of errors because SumOfWARFAmountand and SumOfParAmountTraded is unknown for Access.
You've to find out in which query the SumOfWARFAmountand and SumOfParAmountTraded are used.

The expression:

Expr1: [SumOfWARFamount]/[SumOfParAmountTraded]

is in query: "f-3 Cumulative WARF All Portfolio"

I don't have to enter any amount as it is calculated from the query. But I still have to click OK.

 

[JHB]

I'll get back to you later, I'm not having the time now.
Only one question!
Why are you putting in hardcode values in the VBA code, why not putting these values directly in the queries then, because each time you've to change it you've to go into the VBA code?

 

[smig]

You cant execute a select query because a select query do nothing.
You can only execute a query that do something - add, delete, edit, create table....

 

[testing1two]

I'll get back to you later, I'm not having the time now.
Only one question!
Why are you putting in hardcode values in the VBA code, why not putting these values directly in the queries then, because each time you've to change it you've to go into the VBA code?

The plan is to have a macro cycle through all of my portfolios and enter date, region (US) only once. Otherwise I have to run the macro manually and wait for the prompts to enter the portfolio name and date and region every time for each portfolio (i have 22 of them).