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]

Re: Basic DAO.Recordset question

"deko" <dje422@hotmail .com> wrote in message
news:5UjLb.7389 $de.2944@newssv r27.news.prodig y.com...[color=blue]
> 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[/color]

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]

Re: Basic DAO.Recordset question

> It looks good to me. What is it doing (or not doing)? Are you getting an[color=blue]
> error? Do you have a reference to DAO set?[/color]

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]

Re: Basic DAO.Recordset question

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" <dje422@hotmail .com> wrote in message
news:5UjLb.7389 $de.2944@newssv r27.news.prodig y.com...[color=blue]
> 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
>
>[/color]

[Pieter Linden]

Re: Basic DAO.Recordset question

"deko" <dje422@hotmail .com> wrote in message news:<halLb.859 6$bW1.7196@news svr25.news.prod igy.com>...[color=blue][color=green]
> > 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?[/color]
>
> The error I'm getting is:
>
> Error Number 3061: Too few parameters. Expected 1.
>[/color]
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]

Re: Basic DAO.Recordset question

> the whole article is here:[color=blue]
> http://www.mvps.org/access/queries/qry0013.htm[/color]

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]

Re: Basic DAO.Recordset question

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" <dje422@hotmail .com> wrote in message news:<halLb.859 6$bW1.7196@news svr25.news.prod igy.com>...[color=blue][color=green]
> > 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?[/color]
>
> 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![/color]

[deko]

Re: Basic DAO.Recordset question

> The problem is the Forms!frmMain!f rm0.Form!Entity _ID. Write a function[color=blue]
> 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[/color]

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]

Re: Basic DAO.Recordset question

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" <usddcm@yahoo.c om> wrote in message
news:fe4ad91b.0 401090430.4b64a cf7@posting.goo gle.com...[color=blue]
> 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" <dje422@hotmail .com> wrote in message[/color]
news:<halLb.859 6$bW1.7196@news svr25.news.prod igy.com>...[color=blue][color=green][color=darkred]
> > > It looks good to me. What is it doing (or not doing)? Are you[/color][/color][/color]
getting an[color=blue][color=green][color=darkred]
> > > error? Do you have a reference to DAO set?[/color]
> >
> > 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[/color][/color]
thinking[color=blue][color=green]
> > 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![/color][/color]

[Dan Morgan]

Re: Basic DAO.Recordset question

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" <dje422@hotmail .com> wrote in message news:<iHBLb.896 5$Y62.4439@news svr25.news.prod igy.com>...[color=blue][color=green]
> > 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[/color]
>
> 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...[/color]

[Roger]

Re: Basic DAO.Recordset question

the missing parameter is Forms!frmMain!f rm0.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("q ryEmailS")
qdf.Parameters( 0) = lngEntityId
Set rs = qdf.OpenRecords et(dbOpenDynase t)
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" <dje422@hotmail .com> wrote in message news:<halLb.859 6$bW1.7196@news svr25.news.prod igy.com>...[color=blue][color=green]
> > 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?[/color]
>
> 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![/color]

[David W. Fenton]

Re: Basic DAO.Recordset question

lesperancer@nat pro.com (Roger) wrote in
<8c7a509f.04011 00558.22c229f8@ posting.google. com>:
[color=blue]
>the missing parameter is Forms!frmMain!f rm0.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'[/color]

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.
[color=blue]
>so you have 2 choices...
> 1) make sure frmMain is open with a valid value in entity_id[/color]

That won't matter one iota -- DAO won't know what it is whether the
form is open or not.
[color=blue]
> 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("q ryEmailS")
> qdf.Parameters( 0) = lngEntityId
> Set rs = qdf.OpenRecords et(dbOpenDynase t)
> Debug.Print rs!strS[/color]

This is one way to accomplish, yes (though you'd want to have
cleanup code for the querydef, recordset and database variables).
[color=blue]
> if you want to search for all entity id's, make a new query
> that
>doesn't
> have a WHERE clause[/color]

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=For ms!frmMain!frm0 .Form!Entity_ID
strSQL = "SELECT tblEntity.First Name as strS FROM tblEntity"
strSQL = strSQL & " WHERE tblEntity.Entit y_ID=" & lngEntityID
strSQL = strSQL & " AND tblEntity.First Name Is Not Null"
strSQL = strSQL & " UNION SELECT tblEntity.LastN ame FROM
tblEntity" strSQL = strSQL & " WHERE tblEntity.Entit y_ID=" &
lngEntityID" strSQL = strSQL & " AND tblEntity.LastN ame Is Not
Null" strSQL = strSQL & " UNION SELECT tblEntity.Compa ny FROM
tblEntity" strSQL = strSQL & " WHERE tblEntity.Entit y_ID=" &
lngEntityID" strSQL = strSQL & " AND tblEntity.Compa ny Is Not
Null" strSQL = strSQL & " UNION SELECT tblEmail.EmailA ddress"
strSQL = strSQL & " FROM tblEmail"
strSQL = strSQL & " WHERE tblEmail.Entity _ID=" & lngEntityID
strSQL = strSQL & " AND tblEmail.EmailA ddress Is Not Null;"

Set db = CurrentDB()
Set rst = db.OpenRecordse t(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.First Name, tblEntity.LastN ame,
tblEntity.Compa ny, tblEmail.EmailA ddress
FROM tblEntity INNER JOIN tblEmail
ON tblEntity.Entit yID = tblEmail.Entity ID;

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.Fi rstName) Is Not Null)
OR ((tblEntity.Las tName) Is Not Null)
OR ((tblEntity.Com pany) Is Not Null)
OR ((tblEmail.Emai lAddress) 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.Fi rstName) Is Not Null)
OR ((tblEntity.Las tName) Is Not Null)
OR ((tblEntity.Com pany) Is Not Null))
AND ((tblEmail.Emai lAddress) 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 <email@address. com>

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

FirstName LastName, Company <email@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!f rm0.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!f rm0.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(strFor m) Then
GetValue = Forms(strForm). Controls(strCon trol)
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(strFor m) Then
If Len(strSubForm) = 0 Then
GetValue = Forms(strForm). Controls(strCon trol)
Else
GetValue = _
Forms(strForm). Controls(strSub Form).Form.Cont rols(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

[deko]

Re: Basic DAO.Recordset question

Hello, and thanks for all the replys.

This seems to be working:

Set qdf = db.QueryDefs("q ryEmailAddresse s")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rst = qdf.OpenRecords et(dbOpenSnapsh ot) 'all I need is a read only rst,
so I figure dbOpenSnalshot is quicker

Here is query EmailAddresses:

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;


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.

[WillWeGetOurFreedomBack]

Re: Basic DAO.Recordset question

On Thu, 08 Jan 2004 21:24:17 GMT, "deko" <dje422@hotmail .com> wrote:
[color=blue]
>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?[/color]
This needs to include the record type parameter, such as:

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

[Rick Brandt]

Re: Basic DAO.Recordset question

<WillWeGetOurFr eedomBack> wrote in message
news:n2o100puo3 cam4g8hs5fdo1b6 da27gokul@4ax.c om...[color=blue]
> On Thu, 08 Jan 2004 21:24:17 GMT, "deko" <dje422@hotmail .com> wrote:
>[color=green]
> >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?[/color]
> This needs to include the record type parameter, such as:
>
> Set rst = db.OpenRecordse t("qryEmailS" , dbOpenDynaset)
> Furthermore, in this statement, I believe the string ""qryEmailS "
> needs to be a valid SQL statement.[/color]

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