Loading query into a recordset...

[Guest]

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

 

[Ken Snell [MVP]]

You don't say on which line you get the error, but I'm guessing it's related
to the prm variable. Disambiguate the Dim'g of prm:

Dim prm As DAO.Parameter

 

[Ken Snell [MVP]]

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 [MVP]]

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>

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>

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

 

[Guest]

Hi,

Actually, i've tried that before when i read someone else's posts, and i
received the following error:

Runtime error '2482':

MS Access can't find the name 'Please enter Rep Code below:' you entered in
the expression.

That expression 'Pls...' is in one of queries in Criteria as [Pls...].

Thank you Ken!!!

 

[Ken Snell [MVP]]

I'm not sure if this worked for you or it didn't work for you.

If it didn't, then post the new code after you added the extra lines.
Something is still not right with it....

--

Ken Snell
<MS ACCESS MVP>

Hi,

Actually, i've tried that before when i read someone else's posts, and i
received the following error:

Runtime error '2482':

MS Access can't find the name 'Please enter Rep Code below:' you entered in
the expression.

That expression 'Pls...' is in one of queries in Criteria as [Pls...].

Thank you Ken!!!

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

 

[Guest]

Hi there,

Thanks for all your inputs, below is the code with the new Parameter
settling loop added:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyDB As DAO.Database
Dim MyRecordSet As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Dim intA As Integer
Dim intB As Integer

Set MyDB = CurrentDb
Set qdf = MyDB.QueryDefs("Q - Rep Area")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set MyRecordSet = qdf.OpenRecordset()

MyRecordSet.MoveFirst

Do Until MyRecordSet.EOF = True

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
MyRecordSet.Close
Set MyDB = Nothing
Set qdf = Nothing
Set MyRecordSet = Nothing

End Sub

 

[Guest]

Hi there,

The codes I posted last time didn't (and doesn't) work. So i tried and used
a SQL string, instead of the name of the query. Now it stops at the following
line, saying that "Join expression not supported.":

Set MyRecordSet = MyDB.OpenRecordset(strQuerySQL, dbOpenDynaset)

The new code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyDB As DAO.Database
Dim MyRecordSet As DAO.Recordset
Dim strQuerySQL As String

Dim intA As Integer
Dim intB As Integer

Set MyDB = CurrentDb

strQuerySQL = "SELECT

.Name,

.Number,

.Week," & _
"[Query A].Field, [Query A].Code" & _
"FROM

INNER JOIN [Query A] ON

.Number = [Query A].Number" & _
"WHERE (((

.Week) = [Please enter Week # below:]))" & _
"ORDER BY

.Week,

.Day;"

Set MyRecordSet = MyDB.OpenRecordset(strQuerySQL, dbOpenDynaset)

MyRecordSet.MoveFirst

Do Until MyRecordSet.EOF = True
Loop

MyRecordSet.Close
Set MyDB = Nothing
Set MyRecordSet = Nothing
End Sub

Thank you in advance!

 

[Brendan Reynolds]

You're missing some crucial spaces. Your SQL string ends up looking like
this ...

SELECT

.Name,

.Number,

.Week,[Query A].Field, [Query
A].CodeFROM

INNER JOIN [Query A] ON

.Number = [Query
A].NumberWHERE (((

.Week) = [Please enter Week # below:]))ORDER BY

.Week,

.Day;

Note that there is a space missing between 'Code' and 'FROM' and between
'Number' and 'WHERE' and a space in front of 'ORDER'.

You're probably going to need square brackets around some of the field names
as well, as several of them are keywords or names of VBA functions.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Hi there,

The codes I posted last time didn't (and doesn't) work. So i tried and
used
a SQL string, instead of the name of the query. Now it stops at the
following
line, saying that "Join expression not supported.":

Set MyRecordSet = MyDB.OpenRecordset(strQuerySQL, dbOpenDynaset)

The new code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim MyDB As DAO.Database
Dim MyRecordSet As DAO.Recordset
Dim strQuerySQL As String

Dim intA As Integer
Dim intB As Integer

Set MyDB = CurrentDb

strQuerySQL = "SELECT

.Name,

.Number,

.Week," & _
"[Query A].Field, [Query A].Code" & _
"FROM

INNER JOIN [Query A] ON

.Number = [Query A].Number" &
_
"WHERE (((

.Week) = [Please enter Week # below:]))" & _
"ORDER BY

.Week,

.Day;"

Set MyRecordSet = MyDB.OpenRecordset(strQuerySQL, dbOpenDynaset)

MyRecordSet.MoveFirst

Do Until MyRecordSet.EOF = True
Loop

MyRecordSet.Close
Set MyDB = Nothing
Set MyRecordSet = Nothing
End Sub

Thank you in advance!

I'm not sure if this worked for you or it didn't work for you.

If it didn't, then post the new code after you added the extra lines.
Something is still not right with it....