Using a combo box to filter a query

[Guest]

I am trying to utilize a combo box to allow the user to select an indivudual
from a list which will filter the query and provide a mailing list for just
that sale reps accounts. I would then like that list email in .xls format.

Here is my code:
Dim strQueryName
Dim strText
Dim strSQL
Dim strWhere

strQueryName = "XmasCardList"

strText = "Attached please find your 2005 Holiday Card List." & vbCrLf &
vbCrLf & "Thank you."

strWhere = "[LO] = """ & Forms![Distribution]!cboXmasListSort & """"

'DoCmd.SendObject acQuery, strQueryName, "MicrosoftExcelBiff8(*.xls)", _
'DLookup("[LOEmail]", "XmasCardList", strWhere), _
'"", , "Holiday Card List", _
'strText, False, ""

I can get it to work as a report but I wanted to provide the excel list so
the recipient can utilize mailmerge for labels.

Any help??

 

[[MVP] S.Clark]

docmd.transferspreadsheet might net better results than the sendobject.

 

[David Lloyd]

Ken:

Have you tried putting a WHERE criteria in your query referencing the form
combo box? For example, in the Query Designer if you add a criteria for the
Sales Rep and then specify the combo box as the criteria, it will limit the
query result based on the combo box (as long as the form the combo box is on
is open when the SendObject method is called). The criteria for the sales
rep would look something like:

Forms!Distribution!cboXmasListSort

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I am trying to utilize a combo box to allow the user to select an indivudual
from a list which will filter the query and provide a mailing list for just
that sale reps accounts. I would then like that list email in .xls format.

Here is my code:
Dim strQueryName
Dim strText
Dim strSQL
Dim strWhere

strQueryName = "XmasCardList"

strText = "Attached please find your 2005 Holiday Card List." & vbCrLf &
vbCrLf & "Thank you."

strWhere = "[LO] = """ & Forms![Distribution]!cboXmasListSort & """"

'DoCmd.SendObject acQuery, strQueryName, "MicrosoftExcelBiff8(*.xls)", _
'DLookup("[LOEmail]", "XmasCardList", strWhere), _
'"", , "Holiday Card List", _
'strText, False, ""

I can get it to work as a report but I wanted to provide the excel list so
the recipient can utilize mailmerge for labels.

Any help??