Query wants parameter value

B

Bob H

I have a tools database which been used for over 12 months on a network
at work. Just lately I have noticed that on the main tools query, when I
open it to do some filtering for locations or tools, I am asked for a
parameter. If I just click ok, and not enter anything, the query runs.
It didn't used to ask for a parameter, so I don't understand why it is
doing so now.
This query is used to select the different tool types from a combo box
in the main form, so I don't want to change anything that would stop the
selection working.

The SQL for the said query is below:

SELECT tblTools.ToolTypeID, tblTools.Manufacturer, tblTools.Product,
tblTools.Size, tblTools.[Lenght Size], tblTools.SWL, tblTools.Drive,
tblTools.Range, tblTools.Increment, tblTools.ManufSerialNo,
tblTools.SerialNo, tblTools.AssetNo, tblTools.MPSENo,
tblTools.LastTestDate, DateAdd([PeriodTypeID],[Freq],[LastTestDate]) AS
NextTestDate, tblTools.CertificateNo, tblTools.LocationID,
tblTools.Notes, tblTools.Calibrate, tblTools.DateAdded, tblTools.DateEdited
FROM tblToolType INNER JOIN tblTools ON tblToolType.ToolTypeID =
tblTools.ToolTypeID
WHERE (((tblTools.ToolTypeID)=[Forms]![Tools]![cboSelectToolType])) OR
((([Forms]![Tools]![cboSelectToolType])=0 Or
([Forms]![Tools]![cboSelectToolType]) Is Null));


Thanks
 
G

ghetto_banjo

if you run the query and your "Tools" form is closed, it will always
ask for that parameter. If it still asks for the parameter when the
form is open, make sure the name of the control, cboSelectToolType, is
the exact same in the query and on the form.
 
V

vanderghast

If you run the query on CurrentDb, rather than using it, say, as record
source of a form, then you have to resolve the reference to that parameter
by yourself, by first opening a queryDef object, and then, for each of its
parameter in Parameters collection, assign a value to the parameter.

Dim qdf As QueryDef : Set qdf = CurrentDb.QueryDefs("queryName")
Dim param As DAO.Parameter
For each param in qdf.Parameters
param.value = eval(param.name) ' only as EXAMPLE
Next param
Dim rst as DAO.Recordset
Set rst = qdf.OpenRecordset( ... options... )
' not: = CurrentDb.OpenRecordset("queryName", ... options... )



Vanderghast, Access MVP
 
Top