Returning Value from SQL statement into VB string

[macdan82]

Hello, first post from an access rookie who is desperately trying to cobble this together.
I am trying to use VB to create an e m a i l that sends via outlook and passes the value from an sql statement into the body.
I'm working on a local database with 2 tables named: businesslist and staff
What i am attempting to do is return a staff name into the body of the e m a i l where the staff initials have been selected from a combo box on a form, the staff initials have a unique key number which i have successfully pulled into the sql but for some reason when i go to include the string into the body it prints the statement and not the value i desire.
Any help would be greatly appreciated as the code below has taken 3 days to mash together using various tips i've gleaned from this forum.
(have had to add spaces to the word email as it think i'm including an address and my post count is currently 0)

Code is as follows:

Private Sub sendit_Click()

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

Dim Db As DAO.Database
Dim Rcs As DAO.Recordset

Set Db = CurrentDb
Set Rcs = CurrentDb.OpenRecordset("select * from staff")

Dim mymsg As String
Dim StrSql As String
Dim strStaff As String

strStaff = Me.StaffSelect.Value
StrSql = "SELECT staffname FROM staff where staff.staffkey=" & strStaff & ";"

With objEmail
.To = Me![e m a i l obtained]
.Subject = "E m a i l Subject Hard Coded"

mymsg = "Hello "
mymsg = mymsg & vbCrLf

.Body = mymsg & StrSql

.Display

End With

Exit_Here:
Set objOutlook = Nothing
Exit Sub

Error_Handler:
MsgBox Err & ": " & Err.Description
Resume Exit_Here

End Sub

Many thanks in advance!

 

[NickHa]

You are assigning the literal "SELECT staffname FROM staff where staff.staffkey=" etc to StrSql without executing the query. If you
Set Rcs = CurrentDb.OpenRecordset(StrSql) and then use
.Body = mymsg & Rcs!staffname
it should do what you want.

 

[macdan82]

NickHa - thats a huge leap forward - thank you so much - its now showing the staffname field value as opposed to the entire statment just as a result of changing the reference in the body to rcs!staffname, however (sadly there is always a but!) it appears that its showing the first value that appears in the staff table as opposed to the record referenced to in where staff.staffkey=" & strStaff & "

having made the following changes i now get "duplicate declaration in current scope"?

Set Db = CurrentDb
Set Rcs = CurrentDb.OpenRecordset(StrSql)

Dim mymsg As String
Dim StrSql As String *this is where the error is pointing at
Dim strStaff As String

StrSql = "SELECT staffname FROM staff where staff.staffkey=" & strStaff & ";"
strStaff = Me.StaffSelect.Value

Thank you once again

 

[NickHa]

I think this may be an order of execution problem? You need to set up the text in Strstaff first, then the query string in StrSql before doing the Set Rcd=CurrentDB.Openrecordset(StrSql). I suspect you are picking up the first record in the record set if the code is in the same sequence as your original example.

 

[vbaInet]

More on working with recordsets here:

http://allenbrowne.com/ser-29.html

 

[macdan82]

NickHa, if i could click thanks twice i would - it was exactly that ( the order of execution) and that has worked a charm!
Thank you again