How do you create a parameterized query in MS Access 2003 and use other queries/forms to fill the parameters and obtain a resultset

Question

I'd like to be able to create a parameterized query in MS Access 2003 and feed the values of certain form elements to that query and then get the corresponding resultset back and do some basic calculations with them. I'm coming up short in figuring out how to get the parameters of the query to be populated by the form elements. If I have to use VBA, that's fine.

Melindamelinde

Answer 1

References to the controls on the form can be used directly in Access queries, though it's important to define them as parameters (otherwise, results in recent versions of Access can be unpredictable where they were once reliable).

For instance, if you want to filter a query by the LastName control on MyForm, you'd use this as your criteria:

LastName = Forms!MyForm!LastName

Then you'd define the form reference as a parameter. The resulting SQL might look something like this:

PARAMETERS [[Forms]!MyForm![LastName]] Text ( 255 );
SELECT tblCustomers.*
FROM tblCustomers
WHERE tblCustomers.LastName=[Forms]![MyForm]![LastName];

I would, however, ask why you need to have a saved query for this purpose. What are you doing with the results? Displaying them in a form or report? If so, you can do this in the Recordsource of the form/report and leave your saved query untouched by the parameters, so it can be used in other contexts without popping up the prompts to fill out the parameters.

On the other hand, if you're doing something in code, just write the SQL on the fly and use the literal value of the form control for constructing your WHERE clause.

Bonnett

David is right. You can use the syntax he showed to reference form controls as criteria with no VBA at all if you just want to return records and sum them up, etc. If you need to do anything more complicated than that, I'd also recommend using VBA & the RecordSource property on the fly. – Caelum

Answer 2

Here is a snippet of code. It updates a table using the parameter txtHospital:

Set db = CurrentDb

Set qdf = db.QueryDefs("AddHospital")
qdf.Parameters!txtHospital = Trim(Me.HospName)
qdf.ReturnsRecords = False

qdf.Execute dbFailOnError

intResult = qdf.RecordsAffected

Here is a sample of the SQL:

PARAMETERS txtHospital Text(255); 

INSERT INTO tblHospitals ( 
[Hospital] )

VALUES ( 
[txtHospital] )

Marivaux

Answer 3

There are three traditional ways to get around this issue:

1. Name the parameter something cleaver so that the user will be prompted to enter the value when the query is run.
2. Reference field on a form (possibly hidden)
3. Build the query on the fly, and don't use parameters.

I think it's just wrong to me that you would ave to inject something like [?enter ISO code of the country] or references to fields on your form like : [Forms]![MyForm]![LastName].

It means we can't re-use the same query in more than one place, with different fields supplying the data or have to rely on the user not to foul up the data entry when the query is run. As I recall, it may be hard to use the same value more than once with the user entered parameter.

Typically I've chosen the last option an built the query on the fly, and updated the query object as needed. However, that's rife for an SQL injection attack (accidental or on purpose knowing my users), and it's just icky.

So I did some digging and I found the following here (http://forums.devarticles.com/microsoft-access-development-49/pass-parameters-from-vba-to-query-62367.html):

'Ed. Start - for completion of the example
dim qryStartDate as date
dim qryEndDate as date
qryStartDate = #2001-01-01# 
qryEndDate = #2010-01-01#   
'Ed. End

'QUOTEING "stallyon": To pass parameters to a query in VBA 
'                     is really quite simple:

'First we'll set some variables:
Dim qdf As Querydef
Dim rst As Recordset

'then we'll open up the query:
Set qdf = CurrentDB.QueryDefs(qryname)

'Now we'll assign values to the query using the parameters option:
qdf.Parameters(0) = qryStartDate
qdf.Parameters(1) = qryEndDate

'Now we'll convert the querydef to a recordset and run it
Set rst = qdf.OpenRecordset

'Run some code on the recordset
'Close all objects
rst.Close
qdf.Close
Set rst = Nothing
Set qdf = Nothing

(I haven't tested it myself, just something I collected in my travels, because every once in a while I've wanted to do this to, but ended up using one of my previously mentioned kludges)

Edit I finally had cause to use this. Here's the actual code.

'...
Dim qdf As DAO.QueryDef
Dim prmOne As DAO.Parameter
Dim prmTwo As DAO.Parameter
Dim rst as recordset
    '...
    'open up the query:
    Set qdf = db.QueryDefs("my_two_param_query") 'params called param_one and 
                                                 'param_two

    'link your DAP.Parameters to the query
    Set prmOne = qdf.Parameters!param_one
    Set prmTwo = qdf.Parameters!param_two

    'set the values of the parameters
    prmOne = 1 
    prmTwo = 2

    Set rst = qdf.OpenRecordset(dbOpenDynaset, _
                                            dbSeeChanges)
    '... treat the recordset as normal

    'make sure you clean up after your self
    Set rst = Nothing
    Set prmOne = Nothing
    Set prmTwo = Nothing
    Set qdf = Nothing

Hindquarter

Answer 4

Let's take an example. the parameterized query looks like that:

Select Tbl_Country.* From Tbl_Country WHERE id_Country = _
    [?enter ISO code of the country]

and you'd like to be able to get this value (the [?enter ... country] one) from a form, where you have your controls and some data in it. Well... this might be possible, but it requires some code normalisation.

One solution would be to have your form controls named after a certain logic, such as fid_Country for the control that will hold an id_Country value. Your can then have your query as a string:

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = [fid_country]"

Once you have entered all requested data in your form, press your "query" button. The logic will browse all controls and check if they are in the query, eventually replacing the parameter by the control's value:

Dim ctl as Control
For each ctl in Me.controls
    If instr(qr,"[" & ctl.name & "]") > 0 Then
         qr = replace(qr,"[" & ctl.name & "]",ctl.value)
    End if
Next i

Doing so, you will have a fully updated query, where parameters have been replaced by real data. Depending on the type of fid_country (string, GUID, date, etc), you could have to add some extra double quotes or not, to get a final query such as:

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = ""GB"""

Which is a fully Access compatible query you can use to open a recordset:

Set rsQuery = currentDb.openRecordset(qr)

I think you are done here.

This subject is critical when your objective is to developp Access applications. You have to offer users a standard way to query data from their GUI, not only to launch queries, but also to filter continuous forms (just in the way Excel do it with its "autofilter" option) and manage reports parameters. Good luck!

Dentalium

Answer 5

the easy method is here Microsoft 'setparameter' info page

DoCmd.SetParameter "frontMthOffset", -3
DoCmd.SetParameter "endMthOffset", -2
DoCmd.OpenQuery "QryShowDifference_ValuesChangedBetweenSELECTEDMonths"

where the SQL of the Access query includes [frontMthOffset] actually in the SQL. e.g.

"select blah from mytable where dateoffset=[frontMthOffset]"

It all just works!

Pacifically