filter query with recordset value

[Gary Dolliver]

Hi all,
is it possible to have a query be filtered by a recordset so that when the
loop is on a record, it would filter the query from the current record? For
example, I have a query that will list outstanding items by employee - each
record that the recordset runs through would be for one employee, is there a
way to then have the query result be for only that one employee? I am
attempting to send emails to employees (one per) and want an attachment to
show the items that relate to them. Here is the code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("MANAGEMENT_Typist_Notification_GROUP")
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
DoCmd.SendObject acSendQuery,
"MANAGEMENT_Typist_Notification_DETAIL", acFormatHTML, rst!email_address, , ,
"subject", "message", False, ""
rst.MoveNext
Loop
Else
MsgBox ("No Outstanding Items")
End If

The GROUP query lists the employees that need to be emailed, and the DETAIL
query lists all outstanding items. How can I filter the DETAIL query by what
the current record is? I tried using rst!employee in the DETAIL query as a
filter, but that did not work. Sorry if this does not make sense, been a
long day
-gary

 

[Graham Mandeno]

Hi Gary

One way is to store the current employee in a public variable and write a
small function that returns its value which the query can then use in a
filter clause.

In a standard module:

Public g_CurrentEmployee as <type>

Public Function CurrentEmployee() as <type>
CurrentEmployee = g_CurrentEmployee
End Function

(where <type> is String or Long depending on the data type of your employee
field)

Then, in your loop, before SendObject:

g_CurrentEmployee = rst!employee

Then, add a WHERE clause to your query:

WHERE [EmployeeField]=CurrentEmployee()

 

[Gary Dolliver]

Thank you Graham,
This works great! Thank you so much!
-gary

Hi Gary

One way is to store the current employee in a public variable and write a
small function that returns its value which the query can then use in a
filter clause.

In a standard module:

Public g_CurrentEmployee as <type>

Public Function CurrentEmployee() as <type>
CurrentEmployee = g_CurrentEmployee
End Function

(where <type> is String or Long depending on the data type of your employee
field)

Then, in your loop, before SendObject:

g_CurrentEmployee = rst!employee

Then, add a WHERE clause to your query:

WHERE [EmployeeField]=CurrentEmployee()

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all,
is it possible to have a query be filtered by a recordset so that when the
loop is on a record, it would filter the query from the current record?
For
example, I have a query that will list outstanding items by employee -
each
record that the recordset runs through would be for one employee, is there
a
way to then have the query result be for only that one employee? I am
attempting to send emails to employees (one per) and want an attachment to
show the items that relate to them. Here is the code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("MANAGEMENT_Typist_Notification_GROUP")
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
DoCmd.SendObject acSendQuery,
"MANAGEMENT_Typist_Notification_DETAIL", acFormatHTML, rst!email_address,
, ,
"subject", "message", False, ""
rst.MoveNext
Loop
Else
MsgBox ("No Outstanding Items")
End If

The GROUP query lists the employees that need to be emailed, and the
DETAIL
query lists all outstanding items. How can I filter the DETAIL query by
what
the current record is? I tried using rst!employee in the DETAIL query as
a
filter, but that did not work. Sorry if this does not make sense, been a
long day
-gary

 

[Graham Mandeno]

You're welcome, Gary. Thanks for the feedback!
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thank you Graham,
This works great! Thank you so much!
-gary

Hi Gary

One way is to store the current employee in a public variable and write a
small function that returns its value which the query can then use in a
filter clause.

In a standard module:

Public g_CurrentEmployee as <type>

Public Function CurrentEmployee() as <type>
CurrentEmployee = g_CurrentEmployee
End Function

(where <type> is String or Long depending on the data type of your
employee
field)

Then, in your loop, before SendObject:

g_CurrentEmployee = rst!employee

Then, add a WHERE clause to your query:

WHERE [EmployeeField]=CurrentEmployee()

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi all,
is it possible to have a query be filtered by a recordset so that when
the
loop is on a record, it would filter the query from the current record?
For
example, I have a query that will list outstanding items by employee -
each
record that the recordset runs through would be for one employee, is
there
a
way to then have the query result be for only that one employee? I am
attempting to send emails to employees (one per) and want an attachment
to
show the items that relate to them. Here is the code:
Dim rst As DAO.Recordset
Set rst =
CurrentDb.OpenRecordset("MANAGEMENT_Typist_Notification_GROUP")
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
DoCmd.SendObject acSendQuery,
"MANAGEMENT_Typist_Notification_DETAIL", acFormatHTML,
rst!email_address,
, ,
"subject", "message", False, ""
rst.MoveNext
Loop
Else
MsgBox ("No Outstanding Items")
End If

The GROUP query lists the employees that need to be emailed, and the
DETAIL
query lists all outstanding items. How can I filter the DETAIL query
by
what
the current record is? I tried using rst!employee in the DETAIL query
as
a
filter, but that did not work. Sorry if this does not make sense, been
a
long day
-gary