le*********@nat pro.com (Roger) wrote in
<8c************ **************@ posting.google. com>:
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'
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("q ryEmailS")
qdf.Parameters( 0) = lngEntityId
Set rs = qdf.OpenRecords et(dbOpenDynase t)
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=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 <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!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 
