Basic DAO.Recordset question

[deko]

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.OpenRecordse t("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

[Rick Brandt]

"deko" <dj****@hotmail .com> wrote in message
news:5U******** ********@newssv r27.news.prodig y.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.OpenRecordse t("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

[deko]

> 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.First Name as strS FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.First Name Is Not Null
UNION SELECT tblEntity.LastN ame FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.LastN ame Is Not Null
UNION SELECT tblEntity.Compa ny FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.Compa ny Is Not Null
UNION SELECT tblEmail.EmailA ddress FROM tblEmail
WHERE ((tblEmail.Enti ty_ID)=Forms!fr mMain!frm0.Form !Entity_ID)
AND tblEmail.EmailA ddress 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.OpenRecordse t("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!

[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******** ********@newssv r27.news.prodig y.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.OpenRecordse t("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

[Pieter Linden]

"deko" <dj****@hotmail .com> wrote in message news:<ha******* **********@news svr25.news.prod igy.com>...

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("M yQuery")

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

Set rs = qdf.OpenRecords et(dbOpenDynase t)
' or qdf.Execute dbFailOnError

</SNIP>
the whole article is here:
http://www.mvps.org/access/queries/qry0013.htm

[deko]

> 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.T o" property for all the
messages sent to that contact; but if you've just replied to a message,
sometimes "MailItem.T o" 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.Namespa ce
Dim ola As New Outlook.Applica tion
Dim olfsm As Outlook.MAPIFol der
Dim olmi As Outlook.MailIte m
Dim strEmail, j, i As String
DoCmd.Hourglass True
DoCmd.SetWarnin gs False
DoCmd.OpenQuery "qryEmailSentDe lete"
DoCmd.OpenQuery "qryEmailEntity " 'qdf here...
DoCmd.SetWarnin gs True
Set db = CurrentDb
Set olns = ola.GetNamespac e("MAPI")
Set olfsm = olns.GetDefault Folder(olFolder SentMail)
Set rst = db.OpenRecordse t("tblEmailSent ")
Set rste = db.OpenRecordse t("tblEmailEnti ty") '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.Sen tOn))
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.LogE rr ("modOutlook(Se ntMessages)")
Resume Exit_here
End Select
End Sub

[Dan Morgan]

The problem is the Forms!frmMain!f rm0.Form!Entity _ID. Write a function
to get the value of Forms!frmMain!f rm0.Form!Entity _ID such as:

function GetValue

GetValue = Forms!frmMain!f rm0.Form!Entity _ID

end function

change your query to reference the function rather than
Forms!frmMain!f rm0.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******* **********@news svr25.news.prod igy.com>...

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.First Name as strS FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.First Name Is Not Null
UNION SELECT tblEntity.LastN ame FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.LastN ame Is Not Null
UNION SELECT tblEntity.Compa ny FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.Compa ny Is Not Null
UNION SELECT tblEmail.EmailA ddress FROM tblEmail
WHERE ((tblEmail.Enti ty_ID)=Forms!fr mMain!frm0.Form !Entity_ID)
AND tblEmail.EmailA ddress 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.OpenRecordse t("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!

[deko]

> The problem is the Forms!frmMain!f rm0.Form!Entity _ID. Write a function

to get the value of Forms!frmMain!f rm0.Form!Entity _ID such as:

function GetValue

GetValue = Forms!frmMain!f rm0.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...

[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.c om> wrote in message
news:fe******** *************** ***@posting.goo gle.com...

The problem is the Forms!frmMain!f rm0.Form!Entity _ID. Write a function
to get the value of Forms!frmMain!f rm0.Form!Entity _ID such as:

function GetValue

GetValue = Forms!frmMain!f rm0.Form!Entity _ID

end function

change your query to reference the function rather than
Forms!frmMain!f rm0.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******* **********@news svr25.news.prod igy.com>...

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.First Name as strS FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.First Name Is Not Null
UNION SELECT tblEntity.LastN ame FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.LastN ame Is Not Null
UNION SELECT tblEntity.Compa ny FROM tblEntity
WHERE ((tblEntity.Ent ity_ID)=Forms!f rmMain!frm0.For m!Entity_ID)
AND tblEntity.Compa ny Is Not Null
UNION SELECT tblEmail.EmailA ddress FROM tblEmail
WHERE ((tblEmail.Enti ty_ID)=Forms!fr mMain!frm0.Form !Entity_ID)
AND tblEmail.EmailA ddress 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.OpenRecordse t("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!

[Dan Morgan]

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******* **********@news svr25.news.prod igy.com>...

The problem is the Forms!frmMain!f rm0.Form!Entity _ID. Write a function
to get the value of Forms!frmMain!f rm0.Form!Entity _ID such as:

function GetValue

GetValue = Forms!frmMain!f rm0.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...