Get Query Results into a Text field

[ebbo]

I have 3 elements to this question. I have a table (called cancellations) a form (called cancellations_form) and I have made a query called (e-mail_query).

I would like to display the results of my query in a text field on my form, Each row returned from the query should be displayed in a new line on the text field.

My query called (email_query) has the following columns

cancellation_id (PK), company_name (FK), cancellation_date, product, email_sent (yes/no)

Now for part 2. Once I get that code into a field, I have created a e-mail button that will send an e-mail to a specified address with the information. At this stage I would also like to update my table called cancellations and would like to mark a field called email_sent as TRUE for all those cancellation_ids that were sent in the e-mail.

Can anyone help me get started on both parts, if more info needed please ask.

 

[ebbo]

please someone im still stuck on this! It cant be that hard ! can it?

 

[ebbo]

please someone im still stuck on this! It cant be that hard ! can it?

 

[Toolguy]

Why do you want to use a txt box? A list box would be easier to process the infomation into a email out query and you can set the email flat to true from that query
Toolguy

 

[ebbo]

OK, but how do i actually code this?? Im not really good at this(VBA/Access), i just usually get some code and fudge it together..usually it works.lol

I would appreciate any way to do it.

I just need to get the information out from the query into a string via some kind of for/while loop. It needs to be a string - Because when i am sending the e-mail I am sending via a String parameter.

Once Ive sent the email I would like to set the email_sent value for each value to true!

So in my head some kind of logic along these lines:

Run Query

Loop through results WHILE NOT END OF RECORDS
{
KEEP ASSIGNING EACH RECORD TO A STRING AND CONCATANATE EACH STRING WITH A LINE SEPARATOR.
}

MyString = RESULTS


Send Email(MyString)
{
...................
...................
...................
UPDATE RECORD TO MARK E-MAILS AS SENT.
}

 

[DCrake]

How many fields have you got in this table/query? Is it going to be readable should you achieve your goal? Why not save the query to a file and send it as an attachment? Far easier.

David

 

[ebbo]

the query returns the following fields.

cancellation_id (PK), company_name (FK), cancellation_date, product, these fields are what will be sent in the e-mail

I do not want to make it too complicated by sending attachments - even though it might be better (if there is an easy way - can you show me, im using Lotus notes to send e-mail automatically). Right now I have a system that can send e-mail via a string parameter and its good enough. But improvements welcome. I just dont want to start going down a road that may not be required.

If my request is too complicated....then maybe i need to start looking for something else. But I cant see why it should be. There must be someone here who has pulled data out of a query and been able to get it into a string???? must be!!!!

 

[DCrake]

How to convert your query into a block of text:

First create a public function in a standard module

Public Function QueryToText() As String

Dim Rs As DAO.RecordSet
Dim strString As String

Set Rs = CurrentDb.OpenRecordSet("<<YourQueryNameHere>>")

Do Until Rs.EOF
   For x = 0 To Rs.Fields.Count - 1
     StrString = StrString & "|" & CStr(Rs(x))
   Next
   Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing

'Strip off the leading pipe delimeter

StrString = Mid(StrString,2)

QueryToText = StrString

End Function

Then prior to your SendMail()

Dim TmpString As String
TmpString = QueryToText()

You can then use the TmpString to parse into your email body.

Be aware that there may be limitiations to the size of your text block depending on the number of records returned by the query.

David

 

[ebbo]

I am Getting an error Run-Time Error '94' - Invalid use of Null, could you check what is wrong please?

Private Sub Send_Email_Click()
Dim TmpString As String
TmpString = QueryToText()
Call NotesMailSend("[EMAIL="me@myemail.com"]me@myemail.com[/EMAIL]", "New Cancellation Reminder", TmpString)
End Sub

Public Function QueryToText() As String
Dim Rs As DAO.Recordset
Dim strString As String
Set Rs = currentdb.OpenRecordset("Email_Query")
Do Until Rs.EOF
   For x = 0 To Rs.Fields.Count - 1
[COLOR=red]     strString = strString & "|" & CStr(Rs(x))[/COLOR]
   Next
   Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
'Strip off the leading pipe delimeter
strString = Mid(strString, 2)
QueryToText = strString
End Function

Function NotesMailSend(strRecipient As String, strSubj As String, strBody As String)
On Error GoTo Err_NotesMailSend
   Dim objNotes As Object
   Dim objNotesDB As Object
   Dim objNotesMailDoc As Object
   Set objNotes = GetObject("", "Notes.Notessession")
   Set objNotesDB = objNotes.getdatabase("", "")
   Call objNotesDB.openmail
   Set objNotesMailDoc = objNotesDB.CreateDocument
   Call objNotesMailDoc.replaceitemvalue("SendTo", strRecipient)
   Call objNotesMailDoc.replaceitemvalue("Subject", strSubj)
   Call objNotesMailDoc.replaceitemvalue("Body", strBody)
   Call objNotesMailDoc.send(False)
   MsgBox "Mail Sent OK", vbInformation, "Sender notes-mail..."
   Set objNotes = Nothing
Exit_NotesMailSend:
   Exit Function
Err_NotesMailSend:
   MsgBox Err.Description
   Resume Exit_NotesMailSend
End Function

 

[DCrake]

Amend this line from

strString = strString & "|" & CStr(Rs(x))

To

strString = strString & "|" & CStr(Nz(Rs(x),""))

This renders any null fields in your query to blank strings (Not Null)

As a test if you place a Debug.Print TmpString after the function call and se what is being returned.

David

 

[ebbo]

excellent its working now.

I get the mail come through. I have 1 problem however I comes through pretty much very ugly. Like:

DXCLAN03|31/12/2009|Web||False|79||DXNEWS05|31/12/2009|CD||False|80||DXNEWS01|31/12/2009|CCH||False|81||DXWEAL01|30/03/2010|CCH||False|88||DXMAYO01|31/03/2010|CCH||False|91||DXHARO09|30/03/2010|PC Tax (Web)||False|96|

How would I go about formatting this text? I would like to place each row on a separate line and maybe add some headings like:

Company Code: DXCLAN03
Cancellation Date: 31/12/2009
Product: Web
Cancellation ID: 79
==========================
Company Code: DXNEWS05
Cancellation Date: 31/12/2009
Product: CD
Cancellation ID: 80

 

[DCrake]

Change the code accordingly

StrString = ""
Do Until Rs.EOF
  StrString = StrString = "Company Code:" & Nz(Rs(0),"Unknown") & VbCrLf
  StrString = StrString & "Cancellation Date:" & Cstr(Nz(Rs(1),"Null")) & VbCrLf
  StrString = StrString  & "Product:" & Nz(Rs(2),"Unknown") & VbCrLf
  StrString = StrString  & "Cancellation ID:" & Nz(Rs(3),"Unknown") & VbCrLf
  StrString = StrString  & "========================" & VbCrLf
  Rs.MoveNext
Loop
StrString = StrString & "End Of Report"

I have added the tag to the bottom to let know that the whole query has been written to the text file.

David

 

[ebbo]

Hello David,

I Now get the following output. I played around with the code for ages but I can not see why. Something seems wrong with the first part of the segment.

alseCancellation Date:30/03/2010
Product:Finstat
Specific:CUR1 Daily
Cancellation ID:96
========================
End Of Report

Here is my updated code.

The only thing I will say is my Query has the following fields and I dont want to use the "email_sent" in the output. for the e-mail. I dont know if this is messing up the text output. I wouldnt think so as it seems we are referencing the fields directly via (number).

Public Function QueryToText() As String
Dim Rs As DAO.Recordset
Dim strString As String
Set Rs = currentdb.OpenRecordset("Email_Query")

strString = ""
Do Until Rs.EOF
  strString = strString = "Company Code:" & Nz(Rs(0), "Unknown") & vbCrLf
  strString = strString & "Cancellation Date:" & CStr(Nz(Rs(1), "Null")) & vbCrLf
  strString = strString & "Product:" & Nz(Rs(2), "Unknown") & vbCrLf
  strString = strString & "Specific:" & Nz(Rs(3), "Unknown") & vbCrLf
  strString = strString & "Cancellation ID:" & Nz(Rs(4), "Unknown") & vbCrLf
  strString = strString & "========================" & vbCrLf
  Rs.MoveNext
Loop
strString = strString & "End Of Report"
 
Rs.Close
Set Rs = Nothing
'Strip off the leading pipe delimeter
strString = Mid(strString, 2)
QueryToText = strString
End Function