Assign result of query to a VBA variable 2

[Valeriya]

Hi, guys!
I have a quick question. How do I store results of a query(result is multirecord)in a variable? Once I do that I want to send the contents of that variable as email from VBA?

Thanks much!

Valeriya

 

[Remou]

You can use SendObject to send the query as an attachment. If you want the result in the body of the email, you can use Outlook (Embed Query Results in an Email
thread705-1158251) to get nicely formatted results. Or you can use SendObject :

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTable")
i = 1
Do While i < 10
strMessage = strMessage & rs!Field1 & rs!Field2 & vbCrLf
rs.MoveNext
i = i + 1
Loop

DoCmd.SendObject acSendNoObject, , , "To@ToDotCom", , , "The Results", strmessage, True

 

[Valeriya]

Hi Remou,

Thank you very much for the valuable advice! Quick follow up question, if you do know the exact number of record you set i = #of records. However, in my case I do not know how many records the query will return, so I guess the way I set i would be, i = EOF, Do While i < EOF, i = i + 1...Am I correct?

Thanks!

Valeriya

 

[Zion7]

...No, you would write,

Do Until rs.EOF

....

rs.MoveNext
Loop

rs.Close: set rs = Nothing

just for the record, why loop?
try getString(ADO), or GetRows(both DAO & ADO)

If Not rs.EOF And Not rs.BOF Then
strMessage = rs.GetString(adClipString,"; ",vbCrLf)
End if

rs.Close: set rs = Nothing

 

[Valeriya]

Hi Zion7,

Thank you very much for your response! I have tried to use your method,however, GetString() is not recognized when I compile the code. Do I need to add a certain library? Also my recordset is definded as: Dim rs As DAO.Recordset. I assume I may have to use GetRows (since it is both DAO and ADO), but I'm not familiar with this function. I looked at it's arguments in VBA help, but it is not real clear. Basically I need to get all records until EOF.

Thanks you!

Valeriya

 

[cmmrfrds]

GetString gives a nicely formatted output with line feeds, but it will need to be an ADO recordset.

Microsoft ActiveX data objects 2.6 library

 

[Valeriya]

Hi Zion7, it is me again - The one that has more questions than answers.

Since, I was not sure how to use GetRows, I went ahead and did the looping. The code compiled ok, however, when I try to run code I get the following error message: "Run-time error '3141': The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

Can you please tell me what the problem is?
Here is my "wonderful code"

Private Sub Customer_Info_Update()

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT IIf([Rep_Name] Like '*Not Applica*','DISTRIBUTOR','HOSPITAL') AS Type," & _
"[0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber, IIf([dbo_MKT_CustomerReps].[CustName]<>[0000 CONS ACCOUNT, RVP, DAM, REGION]." & _
"[AccountName],'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[CustName],[dbo_MKT_CustomerReps].[CustName]) AS NewAccountName" & _
"FROM dbo_MKT_CustomerReps LEFT JOIN [0000 CONS ACCOUNT, RVP, DAM, REGION] ON dbo_MKT_CustomerReps.CustNbr =" & _
"[0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber" & _
"WHERE ((([0000 CONS ACCOUNT, RVP, DAM, REGION].AccountNumber) Is Not Null) AND ((IIf([dbo_MKT_CustomerReps].[CustName]<>[0000 CONS ACCOUNT," & _
"RVP, DAM, REGION].[AccountName],'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[CustName],[dbo_MKT_CustomerReps].[CustName]))" & _
"Like 'Will be Updated*'));")

 
i = 1

Do Until rs.EOF

strMessage = strMessage & rs![Type] & rs![AccountName] & rs![NewAccountName] & vbCrLf
rs.MoveNext
i = i + 1
Loop

rs.Close: Set rs = Nothing


DoCmd.SendObject acSendNoObject, , , "valeriya.kupreychuk@medtronic.com", , , "The Results", strMessage, True


End Sub

Many thanks! What would I do without you guys....

Valeriya.

 

[PHV]

Lack of space before FROM, WHERE and Like.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Valeriya, as PHV said, and,
to columns in your WHERE statement, do not have assigned values?

IIf([dbo_MKT_CustomerReps].[CustName]<>[0000 CONS ACCOUNT," ??

RVP, DAM, REGION].[AccountName], = ?

'Will be Updated to ' & ' ' & [dbo_MKT_CustomerReps].[CustName] = ?

And, your strimg message will not have any spaces
between the values

strMessage = strMessage & rs![Type] & rs![AccountName] & rs![NewAccountName] & vbCrLf

should be??
strMessage = strMessage & ", " & rs![Type] & ", " & rs![AccountName] & ", " & rs![NewAccountName] & vbCrLf

 

[Valeriya]

I've finally got it fixed!
Thanks a lot, Zion7!