Querydef Paramater Query (1 Viewer)

Rank Am

Registered User.
Local time
Tomorrow, 01:48
Joined
Apr 30, 2005
Messages
68
Hi,
I need to use DAO (which I haven't used for years as all recent apps have been in SQL server or adp files) and can't remeber the syntax for creating a DAO recordset from a paramater query based upon 2 combo box string values on a form. I am absolutely fine with ADO but in this case I need to pass a DAO recordset to another application.

The Query SQL is detailed below and both filter fields are Text fields

SELECT ULD_TAG.*, ULD_TAG.TMP_ASSET, ULD_TAG.TMP_SYS
FROM ULD_TAG
WHERE (((ULD_TAG.TMP_ASSET) Like "*" And (ULD_TAG.TMP_ASSET)=[Forms]![frm_asset_filt]![cbo_asset]) AND ((ULD_TAG.TMP_SYS) Like "*" And (ULD_TAG.TMP_SYS)=[Forms]![frm_asset_filt]![cbo_sys]));

The code I am using gives an error value of too few parameters expected
Const strTable = "QRY_TRV"
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(strTable, dbOpenDynaset, dbReadOnly)

Thanks for your help

Regards

Jon
 
Last edited:

wazz

Super Moderator
Local time
Tomorrow, 01:48
Joined
Jun 29, 2004
Messages
1,711
i think it should be more like:

dim strSQL as string

strsql = "SELECT ULD_TAG.*
FROM ULD_TAG
WHERE ULD_TAG.TMP_ASSET Like '*' & [Forms]![frm_asset_filt]![cbo_asset] AND ULD_TAG.TMP_SYS Like '*' & [Forms]![frm_asset_filt]![cbo_sys];"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenDynaset, dbReadOnly)

the parameters will take the values from the 1st columns of the comboboxes (column 0). if you need a different column, change it to:
"...& [Forms]![frm_asset_filt]![cbo_sys].Column(1);"
 

Rank Am

Registered User.
Local time
Tomorrow, 01:48
Joined
Apr 30, 2005
Messages
68
Thanks, But it still gives "Too few parameters Expected" Error. I am pretty sure that I cannot open a DAO recordset from a parameter query using the db.Openrecordset method and have to use querydef instead but can't remember the syntax
 

wazz

Super Moderator
Local time
Tomorrow, 01:48
Joined
Jun 29, 2004
Messages
1,711
oh, you want to use a qdef. more like this then:
Code:
Set db = CurrentDb
Set qdef = db.QueryDefs("qryName")

[COLOR=green]'Evaluate and set the query's parameters.
[/COLOR]For Each prm In qdef.Parameters
    prm.Value = Eval(prm.Name)
Next prm
        
Set rs = qdef.OpenRecordset
 

Users who are viewing this thread

Top Bottom