Re: Loading query into a recordset...

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 08/26/04 

Date: Thu, 26 Aug 2004 17:24:32 -0400

Between these two lines of code:

 Set qdf = MyDB.QueryDefs("Q- Rep Planner")
 Set MyRecordSet = qdf.OpenRecordset("Q- Rep Planner")

put these lines of code to evaluate the parameters:

 For Each prm in qdf.Parameters
    prm.Value = Eval(prm.Name)
 Next prm 

-- 
        Ken Snell
<MS ACCESS MVP>
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:uS1s6XxiEHA.2992@TK2MSFTNGP12.phx.gbl...
> Sorry -
>
> You don't need to use the name of the query in the OpenRecordset action.
You
> already have set the QueryDef object to that query:
>
> Set MyRecordSet = qdf.OpenRecordset()
>
> -- 
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "Goe" <Goe@discussions.microsoft.com> wrote in message
> news:23CA7B08-53BA-48B7-B98A-4AE763322365@microsoft.com...
> > Hi! Thank you for your kind attention and i would appreciate the advices
> you
> > give!
> >
> > Query B that draws its fields from Table A & Query A. The Query B has 2
> > criteria of the type [Please enter ... below:]. A Report displays the
> fields
> > in Query B.
> >
> > Ultimately, I would like to insert a page break (called NewDay) in the
> > Report when the "Day" field in Query B changes its value. So I thought I
> > should put Query B in a recordset, then loop thru the "Day" field of
each
> > record and detect changes. Now, I got a object type mismatch error when
i
> run
> > the following code.
> >
> > Dim MyDB As DAO.Database
> > Dim MyRecordSet As DAO.Recordset
> > Dim qdf As QueryDef
> > Dim prm As Parameter
> >
> > Dim intA As Integer
> > Dim intB As Integer
> >
> > Set MyDB = CurrentDb
> > Set qdf = MyDB.QueryDefs("Q- Rep Planner")
> > Set MyRecordSet = qdf.OpenRecordset("Q- Rep Planner")
> >
> > MyRecordSet.MoveFirst
> >
> > Do Until MyRecordSet.PercentPosition = 100
> > intA = MyRecordSet![Day]
> > MyRecordSet.MoveNext
> > intB = MyRecordSet![Day] - intA
> >     If intA >= 1 And intB = 1 Then
> >         Me![NewDay].Visible = True
> >     Else
> >         Me![NewDay].Visible = False
> >     End If
> > Loop
> >
> > Set MyDB = Nothing
> > Set qdf = Nothing
> > Set MyRecordSet = Nothing
> >
>
>

Relevant Pages

  • Re: ADODB Recordset Not Loading From Parameter Query
    ... The query itself runs fine when running manually and supplying the ... Dim con As ADODB.Connection ... Dim prm As ADODB.Parameter ... Set prm = cmd.CreateParameter("@LastName", adVarChar, adParamInput, 25) ...
    (microsoft.public.access.modulesdaovba)
  • Re: OpenRecordset
    ... query is taking values or criteria from fields on a form (which are treated ... Dim qdf As DAO.QueryDef ... Dim prm As DAO.Parameter ...
    (microsoft.public.access.formscoding)
  • Re: QueryDef
    ... enough to evaluate when the query is opened from the database window. ... Dim db1 As Database ... Dim prm As Parameter ...
    (microsoft.public.access.formscoding)
  • RE: Field selected by Combo Box
    ... Public Sub OpenTempQuery(strSQL As String) ... Dim dbs As DAO.Database ... Dim prm As DAO.Parameter ... button a query would run that would show the grouped field entries for their ...
    (microsoft.public.access.queries)
  • Re: Loading query into a recordset...
    ... You don't need to use the name of the query in the OpenRecordset action. ... already have set the QueryDef object to that query: ... > Dim MyRecordSet As DAO.Recordset ... > Set MyRecordSet = qdf.OpenRecordset ...
    (microsoft.public.access.modulesdaovba)