Remove Fields from Recordset

[Guest]

I have created a button on my contact form which launches an e-mail that will
list all interviews listed for that contact. I am using the GetString method
to convert the recordset to a string before inserting into the e-mail.
However, this also deposits the ContactID in the body of the e-mail. Is there
a way to remove the candidateID field after the recordset has been filtered
and before I invoke the GetString method? I have not yet found one. Please
see code below. Thank you!

Private Sub Command261_Click()
Dim rst As ADODB.Recordset
Dim sched As Variant
Dim emsubj As String

Set rst = New ADODB.Recordset

rst.Open "qryInterviewEmail", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source
= C:\Documents and Settings\Administrator\Desktop\mydatabase.mdb",
adOpenForwardOnly, adLockReadOnly
rst.Filter = "CandidateID = " & ContactID

emsubj = "Interview Schedule for " & Fullname

sched = rst.GetString

Call SendEmail("", "", "", emsubj, sched, "")

End Sub

 

[JethroUK©]

depends where abouts the ID is in the string - psuedo code:

sched = mid$(rst.GetString,2)

 

[Brendan Reynolds]

No, but you could do something like this instead ...

strSQL = "SELECT FirstField, SecondField FROM qryInterviewEmail WHERE
CandidateID = " & ContactID
rst.Open strSQL etc.

Your recordset will now include only the fields, and only the record, that
you want.

 

[Guest]

I like this solution. However, when I attempt to use an SQL statement as the
source for the recordset, I get an error that says "no value given for one or
more required parameters" My code is below. I have been screwing with this
for a while to no avail. Any ideas?

Private Sub Command261_Click()
Dim rst As ADODB.Recordset
Dim sched As Variant
Dim emsubj As String
Dim SQLstr As String

Set rst = New ADODB.Recordset

SQLstr = "SELECT InterviewTime, NameTitleOfficeBus AS Interviewer FROM
qryInterviewEmail WHERE CandidateID = " & ContactID

rst.Open SQLstr, "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =
C:\Documents and Settings\Administrator\Desktop\political.mdb", , , 1
'rst.Filter = "CandidateID = " & ContactID

emsubj = "Interview Schedule for " & Fullname

sched = rst.GetString

Call SendEmail("", "", "", emsubj, sched, "")

End Sub

 

[Brendan Reynolds]

Do the field names 'InterviewTime', 'NameTitleOffice' and 'CandidateID' all
appear in the SELECT clause of 'qryInterviewEmail'?

 

[Albert D.Kallal]

First, you don't need so much code.

Also, is the "query" qryInterviewEmail complete clean sql with NO
parameters? (you have to REMOVE all the parmaters..sicne you are
supplying the condtion in code now).

The code should be somthing like:

Dim rst As New ADODB.Recordset
Dim strSql As String
dim sched as String

strSql = "SELECT InterviewTime, NameTitleOfficeBus AS Interviewer " & _
" FROM qryInterviewEmail WHERE CandidateID = " & ContactID
Set rst = CurrentProject.Connection.Execute(strSql)

sched = rst!InterviewTime & " " & rst!, NameTitleOfficeBus

rst.Close
Set rst = Nothing

note how you can "build up" the string for sched. It is not clear which
fields you want from the sql..but as above I just concatenated them
together.

Also, note how cleaned up the code...you don't need to setup all the
connection string stuff either...it is built in....

 

[Albert D.Kallal]

oopps...

of couse:

sched = rst!InterviewTime & " " & rst!, NameTitleOfficeBus

-----------------------------------------^

should read

sched = rst!InterviewTime & " " & rst! NameTitleOfficeBus

 

[Guest]

You got it - there is a space in Interview Time

Thanks