coding a concatenation

[Ian]

I'm a newb so this probably much simpler than I'm making it...

Background: I have a DB that collects a users name / info and some data,
then when the user finishes entering the data, I want to send an email with a
single line of text generated by concatenating a couple of fields from a
separate query. Sending the email part I have figured out, but I cant seem
to open the query and get what I need out of it.

For this thread, assume:
query name: Query1
query fields: First_Name, Last_Name, Data
query returns 1 row of data (i.e. newest addition)

How do I code an event (button click from a form) to run the query, then
send an email to an address with a line of text like "[John] [Smith] entered
this [Data]"

Here's what I have so far:
Private Sub Continue_Button_Click()
Dim msg_title, msg_text, rec_set


DoCmd.SendObject , , , "(e-mail address removed)", , , msg_title, msg_text, 0

End Sub

All help greatly appreciated!
-Ian

 

[Danny Lesandrini]

Ian:

You probably want to create a recordset in code and pull the data from that.

Private Sub Continue_Button_Click()
Dim msg_title, msg_text, rec_set

Dim db As Dao.Database
Dim rs AS Dao.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM tblYourData WHERE ID=" & Me!ID)

If Not rs.BOF and Not rs.EOF Then
msg_text = "record for " & rs!FirstName & " was processed"
End If

Set db = Nothing
Set rs = Nothing

DoCmd.SendObject , , , "(e-mail address removed)", , , msg_title, msg_text, 0

End Sub

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

I'm a newb so this probably much simpler than I'm making it...

Background: I have a DB that collects a users name / info and some data,
then when the user finishes entering the data, I want to send an email with a
single line of text generated by concatenating a couple of fields from a
separate query. Sending the email part I have figured out, but I cant seem
to open the query and get what I need out of it.

For this thread, assume:
query name: Query1
query fields: First_Name, Last_Name, Data
query returns 1 row of data (i.e. newest addition)

How do I code an event (button click from a form) to run the query, then
send an email to an address with a line of text like "[John] [Smith] entered
this [Data]"

Here's what I have so far:
Private Sub Continue_Button_Click()
Dim msg_title, msg_text, rec_set


DoCmd.SendObject , , , "(e-mail address removed)", , , msg_title, msg_text, 0

End Sub

All help greatly appreciated!
-Ian

 

[Ian]

Getting closer... anyone have a better solution?

Private Sub Continue_Button_Click()
Dim msg_title, msg_text, rec_set

rec_set = Array(3)
With CurrentProject.Connection.Execute("Query1")
rec_set(0) = .Fields(0).Value
rec_set(1) = .Fields(1).Value
rec_set(2) = .Fields(2).Value
End With

msg_text = rec_set(0) & " " & rec_set(1) & " entered " & rec_set(2)

DoCmd.SendObject , , , "(e-mail address removed)", , , msg_title, msg_text, 0

End Sub

 

[Ian]

Not sure what I was thinking on the last entry... I knew better...

How about this:

Private Sub Continue_Button_Click()
Dim msg_title, Msg_Text, Rec_Set

With CurrentProject.Connection.Execute("Query1")
Rec_Set = Array(.Fields(0).Value, .Fields(1).Value, .Fields(2).Value)
End With

Msg_Text = Rec_Set(0) & " " & Rec_Set(1) & " entered " & Rec_Set(2)

DoCmd.SendObject , , , "(e-mail address removed)", , , msg_title, Msg_Text, 0

End Sub


Denny, I see where you're coming from, and that works too, so I guess my
question is:
They both work, any reason to use one over the other?

Thanks,
Ian

 

[Danny Lesandrini]

Hmmm. I've never seen that syntax before, but it does seem to work.

Your Array declaration has a problem, but the query execution seems to work. I created a generic function to call this and it
works. Just pass the query name and the ordinal position of the field, from 0 to n.

Function GetQueryValue(ByVal sQuery, ByVal iPos) As Variant
On Error Resume Next

GetQueryValue = CurrentProject.Connection.Execute(sQuery).Fields(iPos).Value

End Function