Re: Can I perform a DCount of records returned by SQL statement (not a saved query)

MLH:

The 'Too few parameters' error usually means that a query referenced in
a recordset has parameters that you aren't feeding to the query. My
guess is that your query qryVehiclesNowners5 has 5 parameters in
it...First, you'll need create a temporary querydef that is based on
your SQL statement. Then you'll need to pass the parameters to the
querydef object. Once that is done, you can then set your recordset to
the results of the querydef. Here's some sample code:

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim MySQL As String

MySQL = "SELECT DISTINCTROW qryVehiclesNowners5.SerialNum,
qryVehiclesNowners5.VDescr, qryVehiclesNowners5.Owner,
qryVehiclesNowners5.VehicleJobID , tblVehicleJobs.Reclaimed,
tblVehicleJobs.VSaleID, tblVehicleJobs.ENF262Written FROM
qryVehiclesNowners5 INNER JOIN tblVehicleJobs ON
qryVehiclesNowners5.VehicleJobID = tblVehicleJobs.VehicleJobID WHERE
(tblVehicleJobs.Reclaimed=No AND tblVehicleJobs.VSaleID Is Null AND
tblVehicleJobs.ENF262Written=True);"

Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("qryTemp", MySQL)

'repeat once for each parameter, with the ENTIRE parameter within
'one set of brackets. If your parameter is grabbed from a form, use
'something like this:
'qdf![Forms!FormName!FormField] = Forms!FormName!FormField
'otherwise, use something like these:

qdf![Parameter1] = "Whatever you want fed to the first parameter"
qdf![Parameter2] = "Whatever you want fed to the second parameter"

Set rst = qdf.OpenRecordset
rst.MoveLast
Me!TheCount = rst.RecordCount

dbs.QueryDefs.Delete qdf.NAME
rst.close
Set dbs = Nothing

The deleting of the querydef is VERY IMPORTANT, or it will error out
the second time you run it and tell you that the querydef already
exists. Remember that you'll need to wrap date parameters with #
signs, strings with single quotes, etc.

HTH,

Jana

.

Relevant Pages

  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Query Problem in Test.
    ... You've declared rs as an ADO Recordset, yet you're trying to use DAO to ... Dim paramMiles As ADODB.Parameter ... query works in the query designer. ... MsgBox "No Churches meet your criteria.", vbInformation, ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)
  • Re: Exporting data from continuous form to Excel spreadsheet
    ... I have overcome it by creating a Make Table Query. ... You can also use excel's copy from recordset method with a recordset. ... Dim sqlStatement As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: VBA email module
    ... property of the query to that statement, ... Dim dbsNorthwind As Database ... Dim prmEnd As Parameter ... ' Set parameter values and open recordset from ...
    (microsoft.public.access.modulesdaovba)