423,516 Members | 2,068 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,516 IT Pros & Developers. It's quick & easy.

Basic DAO.Recordset question

P: n/a
For some reason this does not seem to be working...
Am I missing something basic?

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here?

' qryEmailS contains only one column - "strS"
' I want to loop through each item in the rst
' must I use rst here? how about Array?

Do Until rst.EOF
Debug.Print rst!strS
rst.MoveNext
Loop
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
"deko" <dj****@hotmail.com> wrote in message
news:5U****************@newssvr27.news.prodigy.com ...
For some reason this does not seem to be working...
Am I missing something basic?

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here?

' qryEmailS contains only one column - "strS"
' I want to loop through each item in the rst
' must I use rst here? how about Array?

Do Until rst.EOF
Debug.Print rst!strS
rst.MoveNext
Loop


It looks good to me. What is it doing (or not doing)? Are you getting an
error? Do you have a reference to DAO set?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #2

P: n/a
> It looks good to me. What is it doing (or not doing)? Are you getting an
error? Do you have a reference to DAO set?


The error I'm getting is:

Error Number 3061: Too few parameters. Expected 1.

This is qryEmailS:

SELECT tblEntity.FirstName as strS FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.FirstName Is Not Null
UNION SELECT tblEntity.LastName FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.LastName Is Not Null
UNION SELECT tblEntity.Company FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.Company Is Not Null
UNION SELECT tblEmail.EmailAddress FROM tblEmail
WHERE ((tblEmail.Entity_ID)=Forms!frmMain!frm0.Form!Enti ty_ID)
AND tblEmail.EmailAddress Is Not Null;

What I'm trying to do is pretty simple - I need to check if any of the
results of qryEmailS are in a particular string:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS")

For Each olmi In olfsm.Items
Do Until rst.EOF
If (InStr(olmi.To, rst!strS)) > 0 Then
'do something here
End If
rst.MoveNext
Loop
Next

I need some way to iterate through each item in the query, and I'm thinking
a recordset is the way to go, unless there is some way to get the query
results into an array. I'm wondering if I'm getting that runtime error
because the query is a union query...

Thanks!
Nov 12 '05 #3

P: n/a
TC
Your code is fine. If you are getting error 3061 (Too few parameters.
Expected 1), the query requires a parameter (which it apparanetly doesn't),
or it has a syntax error. Open the form to which the query refers, enter the
exact same values that you used before, then run the query manually (not
from code) & see what happens. I bet it blows up!

HTH,
TC
"deko" <dj****@hotmail.com> wrote in message
news:5U****************@newssvr27.news.prodigy.com ...
For some reason this does not seem to be working...
Am I missing something basic?

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here?

' qryEmailS contains only one column - "strS"
' I want to loop through each item in the rst
' must I use rst here? how about Array?

Do Until rst.EOF
Debug.Print rst!strS
rst.MoveNext
Loop

Nov 12 '05 #4

P: n/a
"deko" <dj****@hotmail.com> wrote in message news:<ha*****************@newssvr25.news.prodigy.c om>...
It looks good to me. What is it doing (or not doing)? Are you getting an
error? Do you have a reference to DAO set?


The error I'm getting is:

Error Number 3061: Too few parameters. Expected 1.

First things first - you have to get the recordset to open. To do
that you have to deal with the above error. Then you can loop through
the recordset to your heart's content.

So...
<SNIP>
With Access queries that contain paramters that are all references to
Access controls on open forms, you can simulate the expression service
that Access provides when the queries are run through the user
interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")

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

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

</SNIP>
the whole article is here:
http://www.mvps.org/access/queries/qry0013.htm
Nov 12 '05 #5

P: n/a
> the whole article is here:
http://www.mvps.org/access/queries/qry0013.htm


I looked at that site - that is definitely my problem. What I've done in
the mean time is use a MakeTable query to dump the query results into a
table, then define the recordset based on the table. I'm not sure how much
of a performance hit I'm taking, but it seems to be working. See complete
code below.

What this sub does is loop through every message in the Outlook Sent Items
folder and populates a table with DateSent, Subject and Recipient of every
message that was sent to each contact in the mdb (there's a button on the
mdb's Contacts form to show the results - ad hoc in a popup form -
individually for each contact. It would take too long to do every contact
at once).

The joker in the pack is the limitation of the Outlook Object Model: there
is no way to always get a fully qualified email address out of the MailItem
property. It depends on how the contact is saved in the Outlook Contacts
folder - if the "DisplayAs" field contains the email address, you can
usually get the email address out of the "MailItem.To" property for all the
messages sent to that contact; but if you've just replied to a message,
sometimes "MailItem.To" has only the contact's name, e.g. 'John Smith' -
this means my code would not find that message - because I am looking for
the contact's email address.

Public Sub SentMessages()
On Error GoTo HandleErr
Dim rst, rste As DAO.Recordset
Dim db As DAO.Database
Dim olns As Outlook.Namespace
Dim ola As New Outlook.Application
Dim olfsm As Outlook.MAPIFolder
Dim olmi As Outlook.MailItem
Dim strEmail, j, i As String
DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryEmailSentDelete"
DoCmd.OpenQuery "qryEmailEntity" 'qdf here...
DoCmd.SetWarnings True
Set db = CurrentDb
Set olns = ola.GetNamespace("MAPI")
Set olfsm = olns.GetDefaultFolder(olFolderSentMail)
Set rst = db.OpenRecordset("tblEmailSent")
Set rste = db.OpenRecordset("tblEmailEntity") 'use qdf here to avoid
table creation?
Do Until rste.EOF
If InStr(1, rste!s, "#", vbTextCompare) Then 'ignore comments next
to email address
j = InStr(1, rste!s, "#", vbTextCompare)
i = "1"
Else
j = Nz(Len(rste!s), 0)
i = "0"
End If
strEmail = Left(rste!s, j - i)
'Debug.Print "strEmail = " & strEmail
For Each olmi In olfsm.Items
DoEvents
'Debug.Print "unmatched olmt.to = " & olmi.To
If (InStr(olmi.To, strEmail)) > 0 Then
rst.AddNew
rst!Sent = (CDate(olmi.SentOn))
rst!Subject = olmi.Subject
rst!Recipient = olmi.To
'Debug.Print "matched olmi.To = " & olmi.To
rst.Update
End If
Next
rste.MoveNext
Loop
Exit_here:
DoCmd.Hourglass False
rst.Close
rste.Close
Set olns = Nothing
Set olfsm = Nothing
Set rst = Nothing
Set rste = Nothing
Set db = Nothing
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
modHandler.LogErr ("modOutlook(SentMessages)")
Resume Exit_here
End Select
End Sub
Nov 12 '05 #6

P: n/a
The problem is the Forms!frmMain!frm0.Form!Entity_ID. Write a function
to get the value of Forms!frmMain!frm0.Form!Entity_ID such as:

function GetValue

GetValue = Forms!frmMain!frm0.Form!Entity_ID

end function

change your query to reference the function rather than
Forms!frmMain!frm0.Form!Entity_ID.

I think it has something to do with DAO not knowing about Access
forms?
"deko" <dj****@hotmail.com> wrote in message news:<ha*****************@newssvr25.news.prodigy.c om>...
It looks good to me. What is it doing (or not doing)? Are you getting an
error? Do you have a reference to DAO set?


The error I'm getting is:

Error Number 3061: Too few parameters. Expected 1.

This is qryEmailS:

SELECT tblEntity.FirstName as strS FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.FirstName Is Not Null
UNION SELECT tblEntity.LastName FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.LastName Is Not Null
UNION SELECT tblEntity.Company FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.Company Is Not Null
UNION SELECT tblEmail.EmailAddress FROM tblEmail
WHERE ((tblEmail.Entity_ID)=Forms!frmMain!frm0.Form!Enti ty_ID)
AND tblEmail.EmailAddress Is Not Null;

What I'm trying to do is pretty simple - I need to check if any of the
results of qryEmailS are in a particular string:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS")

For Each olmi In olfsm.Items
Do Until rst.EOF
If (InStr(olmi.To, rst!strS)) > 0 Then
'do something here
End If
rst.MoveNext
Loop
Next

I need some way to iterate through each item in the query, and I'm thinking
a recordset is the way to go, unless there is some way to get the query
results into an array. I'm wondering if I'm getting that runtime error
because the query is a union query...

Thanks!

Nov 12 '05 #7

P: n/a
> The problem is the Forms!frmMain!frm0.Form!Entity_ID. Write a function
to get the value of Forms!frmMain!frm0.Form!Entity_ID such as:

function GetValue

GetValue = Forms!frmMain!frm0.Form!Entity_ID

end function


hmmm... that sounds interesting. but how do I pass that to a query? I've
discovered that any recordset operation that uses a query that has a
reference to a form fails - the work around I've been using is to save the
form value I'm interested in (value3) to a table (tblC), then use a nested
query:

SELECT tblA.value1 FROM tblA WHERE tblA.value2 IN (Select value3 FROM tblC)

This mean the added overhead of writing the value to a table everytime I
need a recordset. If I could find a better way, that would be great...

I've been looking at this site:

http://www.mvps.org/access/queries/qry0013.htm

but have not figured out how to use QueryDefs yet...
Nov 12 '05 #8

P: n/a
TC
Not true. Queries can certianly refer to form variables (although
personally, I think that ids a terrible idea from a software design
perspective).

deko, have you tried what I suggested?

TC
"Dan Morgan" <us****@yahoo.com> wrote in message
news:fe**************************@posting.google.c om...
The problem is the Forms!frmMain!frm0.Form!Entity_ID. Write a function
to get the value of Forms!frmMain!frm0.Form!Entity_ID such as:

function GetValue

GetValue = Forms!frmMain!frm0.Form!Entity_ID

end function

change your query to reference the function rather than
Forms!frmMain!frm0.Form!Entity_ID.

I think it has something to do with DAO not knowing about Access
forms?
"deko" <dj****@hotmail.com> wrote in message

news:<ha*****************@newssvr25.news.prodigy.c om>...
It looks good to me. What is it doing (or not doing)? Are you getting an error? Do you have a reference to DAO set?


The error I'm getting is:

Error Number 3061: Too few parameters. Expected 1.

This is qryEmailS:

SELECT tblEntity.FirstName as strS FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.FirstName Is Not Null
UNION SELECT tblEntity.LastName FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.LastName Is Not Null
UNION SELECT tblEntity.Company FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.Company Is Not Null
UNION SELECT tblEmail.EmailAddress FROM tblEmail
WHERE ((tblEmail.Entity_ID)=Forms!frmMain!frm0.Form!Enti ty_ID)
AND tblEmail.EmailAddress Is Not Null;

What I'm trying to do is pretty simple - I need to check if any of the
results of qryEmailS are in a particular string:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS")

For Each olmi In olfsm.Items
Do Until rst.EOF
If (InStr(olmi.To, rst!strS)) > 0 Then
'do something here
End If
rst.MoveNext
Loop
Next

I need some way to iterate through each item in the query, and I'm thinking a recordset is the way to go, unless there is some way to get the query
results into an array. I'm wondering if I'm getting that runtime error
because the query is a union query...

Thanks!

Nov 12 '05 #9

P: n/a
In the query builder criteria use the function as the criteria rather
than the form control. You will then be able to use the query as a
recordset in DAO.

With regard to another posting. Yes form controls can be used in
queries, but DAO gets cranky when you open a recordset based on the
query.

"deko" <dj****@hotmail.com> wrote in message news:<iH*****************@newssvr25.news.prodigy.c om>...
The problem is the Forms!frmMain!frm0.Form!Entity_ID. Write a function
to get the value of Forms!frmMain!frm0.Form!Entity_ID such as:

function GetValue

GetValue = Forms!frmMain!frm0.Form!Entity_ID

end function


hmmm... that sounds interesting. but how do I pass that to a query? I've
discovered that any recordset operation that uses a query that has a
reference to a form fails - the work around I've been using is to save the
form value I'm interested in (value3) to a table (tblC), then use a nested
query:

SELECT tblA.value1 FROM tblA WHERE tblA.value2 IN (Select value3 FROM tblC)

This mean the added overhead of writing the value to a table everytime I
need a recordset. If I could find a better way, that would be great...

I've been looking at this site:

http://www.mvps.org/access/queries/qry0013.htm

but have not figured out how to use QueryDefs yet...

Nov 12 '05 #10

P: n/a
the missing parameter is Forms!frmMain!frm0.Form!Entity_ID
if you were to close the form 'frmMain' and you were to run the query,
you'd get an input box prompting for 'entity id'

so you have 2 choices...
1) make sure frmMain is open with a valid value in entity_id

2) don't open the form and use qrydef to set the parameter
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
dim lngEntityId as long ' the entity ID that you want to search
for

Set db = CurrentDb
Set qdf = db.QueryDefs("qryEmailS")
qdf.Parameters(0) = lngEntityId
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Debug.Print rs!strS

if you want to search for all entity id's, make a new query that
doesn't
have a WHERE clause
"deko" <dj****@hotmail.com> wrote in message news:<ha*****************@newssvr25.news.prodigy.c om>...
It looks good to me. What is it doing (or not doing)? Are you getting an
error? Do you have a reference to DAO set?


The error I'm getting is:

Error Number 3061: Too few parameters. Expected 1.

This is qryEmailS:

SELECT tblEntity.FirstName as strS FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.FirstName Is Not Null
UNION SELECT tblEntity.LastName FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.LastName Is Not Null
UNION SELECT tblEntity.Company FROM tblEntity
WHERE ((tblEntity.Entity_ID)=Forms!frmMain!frm0.Form!Ent ity_ID)
AND tblEntity.Company Is Not Null
UNION SELECT tblEmail.EmailAddress FROM tblEmail
WHERE ((tblEmail.Entity_ID)=Forms!frmMain!frm0.Form!Enti ty_ID)
AND tblEmail.EmailAddress Is Not Null;

What I'm trying to do is pretty simple - I need to check if any of the
results of qryEmailS are in a particular string:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS")

For Each olmi In olfsm.Items
Do Until rst.EOF
If (InStr(olmi.To, rst!strS)) > 0 Then
'do something here
End If
rst.MoveNext
Loop
Next

I need some way to iterate through each item in the query, and I'm thinking
a recordset is the way to go, unless there is some way to get the query
results into an array. I'm wondering if I'm getting that runtime error
because the query is a union query...

Thanks!

Nov 12 '05 #11

P: n/a
le*********@natpro.com (Roger) wrote in
<8c**************************@posting.google.com >:
the missing parameter is Forms!frmMain!frm0.Form!Entity_ID
if you were to close the form 'frmMain' and you were to run the
query, you'd get an input box prompting for 'entity id'
It's only a missing parameter because DAO does not use the Access
Expression Service to resolve references to Access objects.
Remember, though a form is created by Access and stored in a Jet
database, it is not something that is structurally known or
understood by Jet. DAO is an interface to Jet that Access can use.
When you mix in Access objects in a DAO context, it is simply
unknown.
so you have 2 choices...
1) make sure frmMain is open with a valid value in entity_id
That won't matter one iota -- DAO won't know what it is whether the
form is open or not.
2) don't open the form and use qrydef to set the parameter
Dim db As Database
Dim rs As Recordset
Dim qdf As QueryDef
dim lngEntityId as long ' the entity ID that you want to
search
for

Set db = CurrentDb
Set qdf = db.QueryDefs("qryEmailS")
qdf.Parameters(0) = lngEntityId
Set rs = qdf.OpenRecordset(dbOpenDynaset)
Debug.Print rs!strS
This is one way to accomplish, yes (though you'd want to have
cleanup code for the querydef, recordset and database variables).
if you want to search for all entity id's, make a new query
that
doesn't
have a WHERE clause


There are two approaches to this other than setting the parameter
of the querydef. One would be to leave the query as it stands and
instead define the SQL for your recordset inline:

This is qryEmailS:

Dim lngEntityID As Long
Dim strSQL As String
Dim db As DAO.Database
Dim rst As DAO.Recordset

lngEntityID=Forms!frmMain!frm0.Form!Entity_ID
strSQL = "SELECT tblEntity.FirstName as strS FROM tblEntity"
strSQL = strSQL & " WHERE tblEntity.Entity_ID=" & lngEntityID
strSQL = strSQL & " AND tblEntity.FirstName Is Not Null"
strSQL = strSQL & " UNION SELECT tblEntity.LastName FROM
tblEntity" strSQL = strSQL & " WHERE tblEntity.Entity_ID=" &
lngEntityID" strSQL = strSQL & " AND tblEntity.LastName Is Not
Null" strSQL = strSQL & " UNION SELECT tblEntity.Company FROM
tblEntity" strSQL = strSQL & " WHERE tblEntity.Entity_ID=" &
lngEntityID" strSQL = strSQL & " AND tblEntity.Company Is Not
Null" strSQL = strSQL & " UNION SELECT tblEmail.EmailAddress"
strSQL = strSQL & " FROM tblEmail"
strSQL = strSQL & " WHERE tblEmail.Entity_ID=" & lngEntityID
strSQL = strSQL & " AND tblEmail.EmailAddress Is Not Null;"

Set db = CurrentDB()
Set rst = db.OpenRecordset(strSQL)

Then do your thing.

Parenthetically, I'm not entirely sure I understand why you'd want
to do this kind of thing. Fundamentally, whenever you do a UNION of
several fields from a single table, it's a pointer that there may
be something wrong with your structure. In your case, do you need a
result set that has FirstName, LastName, Company and EMailAddress
all in one column, or do you just want the name, company and email
of the entity? If the latter, then you don't need a UNION with a
loop at all, just a join between tblEntity and tblEMail (an inner
join, because a query for constructing email addresses isn't much
use if it includes people who have none!) and then concatenate the
name, company and email from the result.

So, your query could be something like this:

SELECT tblEntity.FirstName, tblEntity.LastName,
tblEntity.Company, tblEmail.EmailAddress
FROM tblEntity INNER JOIN tblEmail
ON tblEntity.EntityID = tblEmail.EntityID;

If you really want to eliminate Null records (not sure why you'd
have any records where all four would be Null), you can add this
WHERE clause:

WHERE (((tblEntity.FirstName) Is Not Null)
OR ((tblEntity.LastName) Is Not Null)
OR ((tblEntity.Company) Is Not Null)
OR ((tblEmail.EmailAddress) Is Not Null))

The last is only necessary if you're afraid that tblEmail could
have a record with a blank email address. Actually, in that
instance, you'd want this, instead:

WHERE (((tblEntity.FirstName) Is Not Null)
OR ((tblEntity.LastName) Is Not Null)
OR ((tblEntity.Company) Is Not Null))
AND ((tblEmail.EmailAddress) Is Not Null)

The concatenation of the results could be done either with IF/ELSE
blocks or you could do it with a loop through the Fields collection
of the recordset. I'd not recommend the latter, as you need to
treat different fields in different ways, assuming you're going for
a result like:

FirstName LastName <em***@address.com>

Also, you'd want to skip the company name if there's a name, unless
you want:

FirstName LastName, Company <em***@address.com>

And if you want that, you'd have two of your four fields treated
differently, so that a loop would make no sense.

Now, assuming that you actually do need the UNION to get all the
data into a single column (though I'm not convinced that you do),
the original SQL will get you what you want. However, it means you
now have to maintain both the Query and the SQL string in your
code. So, you might want to fix up the Query to be usable in DAO.
One option is to set the parameters. However, you have to remember
to do that each time you use it. Another option is, as Dan Morgan
suggested, to use a function in your saved Query that returns the
value you need. DAO can handle these user-defined functions (this
is one of the things that makes DAO rather unsafe, and this has
been patched in Jet 4 SP8). Rather than having a specific function,
I would suggest a generic one. So, we would take Dan's code:

Function GetValue
GetValue = Forms!frmMain!frm0.Form!Entity_ID
End Function

First off, it's a very bad idea to not explicitly declare the
return type of a function, so it really should be:

Function GetValue As Variant
GetValue = Forms!frmMain!frm0.Form!Entity_ID
End Function

Now, this is actually 100% equivalent, as undeclared function
return types will implicitly be variants, but it's much better
code, as you can tell at a glance what the code returns (you also
get the return type in Intellisense), but also because you are not
depending on implicit behavior that might change when run under
different variants of VB/A.

Now, to make it generic, you need to pass it the form and control:

Function GetValue (ByVal strForm As String, _
ByVal strControl As String) As Variant
If IsLoaded(strForm) Then
GetValue = Forms(strForm).Controls(strControl)
End If
End Function

You'd probably want an error handler in case the control doesn't
exist.

In your query, however, this wouldn't work, because you're
referring to a control on a subform. I've never actually
encountered this kind of issue, so my code for this doesn't account
for it. The simplest way I can think of is to add an optional
argument for the subform:

Function GetValue (ByVal strForm As String, _
ByVal strControl As String, _
Optional strSubForm as String) As Variant
If IsLoaded(strForm) Then
If Len(strSubForm) = 0 Then
GetValue = Forms(strForm).Controls(strControl)
Else
GetValue = _
Forms(strForm).Controls(strSubForm).Form.Controls( strControl)
End If
End If
End Function

I wasn't entirely sure this kind of thing works, but, yes, it does
work. It's kind of messy and probably not preferable to setting the
parameters, but it gives you an idea of something you can do to get
around this kind of problem.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #12

P: n/a
Hello, and thanks for all the replys.

This seems to be working:

Set qdf = db.QueryDefs("qryEmailAddresses")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecordset(dbOpenSnapshot) 'all I need is a read only rst,
so I figure dbOpenSnalshot is quicker

Here is query EmailAddresses:

SELECT tblEmail.EmailAddress
FROM tblEmail
WHERE ((tblEmail.Entity_ID)=Forms!frmMain!frm0.Form!Enti ty_ID) And
tblEmail.EmailAddress Is Not Null;
That Union query I was using before was a mess, to be sure. Nevertheless,
the problem was not with the query as some have suggested. It seems the
problem is simply this: you cannot build a DAO Recordset from a query with
parameters referencing an Access form. I've discovered this by trial and
error, but a more detailed explanation is at
http://www.mvps.org/access/queries/qry0013.htm - in short, it has something
to do with the Access Expression Service not resolving references when using
VBA to open a recordset.

Becuase the query parameter in question is on an open form, I can use the
method above to supply the required parameter value.

I have not explored the option of using a Function for returning the value I
need, but David Fenton's post does a great job of explaining how to do
that - thanks, David.
Nov 12 '05 #13

P: n/a
On Thu, 08 Jan 2004 21:24:17 GMT, "deko" <dj****@hotmail.com> wrote:
For some reason this does not seem to be working...
Am I missing something basic?

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here?

This needs to include the record type parameter, such as:

Set rst = db.OpenRecordset("qryEmailS", dbOpenDynaset)
Furthermore, in this statement, I believe the string ""qryEmailS"
needs to be a valid SQL statement.
Nov 12 '05 #14

P: n/a
<WillWeGetOurFreedomBack> wrote in message
news:n2********************************@4ax.com...
On Thu, 08 Jan 2004 21:24:17 GMT, "deko" <dj****@hotmail.com> wrote:
For some reason this does not seem to be working...
Am I missing something basic?

Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rst = db.OpenRecordset("qryEmailS") ' <= can I use query here?

This needs to include the record type parameter, such as:

Set rst = db.OpenRecordset("qryEmailS", dbOpenDynaset)
Furthermore, in this statement, I believe the string ""qryEmailS"
needs to be a valid SQL statement.


It can be a SQL statement or the name of a query or table.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.