Retrieve Records from Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a continuous form (based on Query) that displays all "current"
employees and each email address. I already have a button to create an email
for EACH employee, but I'd like to create a button to "email all staff."

I think I need to create an unbound text box (not visible) that gets filled
in "on current" with all email address in one text box (ex:
(e-mail address removed), (e-mail address removed), etc) and then the button on need to
launch an email based on this one text box.

How might I retrieve these records from the query and place them in one text
box?
or any other ideas?
thanks
 
I have a continuous form (based on Query) that displays all "current"
employees and each email address. I already have a button to create an email
for EACH employee, but I'd like to create a button to "email all staff."

I think I need to create an unbound text box (not visible) that gets filled
in "on current" with all email address in one text box (ex:
(e-mail address removed), (e-mail address removed), etc) and then the button on need to
launch an email based on this one text box.

How might I retrieve these records from the query and place them in one text
box?
or any other ideas?
thanks
Click to expand...

Private Sub Mybottom_click
Dim stTmp as string
with Me.RecordsetClone
.movefirst
while not .eof
stTmp=stTmp &";" &.eMailField
.movenext
wend
end with

'send your email, the recipients are in stTmp
'as delimiter I have choosen [;]
end sub
 
Works great....but..... The resulting text box returns all the email
addresses in the list, but when I use a command button to send them all into
one email address, only some of them come in. I didn't count exactly but it
looks about 255 characters long. Is there a way around this? I did a
copy/paste, and sure enough all of the email addresses came in that time...
here's my code:

Query for subform:
SELECT tblEmployees.FirstName, tblEmployees.LastName,
tblEmployees.EmailName, tblEmployees.Extension, tblEmployees.DateLeave,
[FirstName] & " " & " " & [LastName] AS Name FROM tblEmployees WHERE
(((tblEmployees.FirstName)<>"unknown") And ((tblEmployees.DateLeave) Is
Null)) ORDER BY tblEmployees.FirstName, tblEmployees.LastName;

On current for subform:
Private Sub Form_Current()
Dim stTmp As String
With Me.RecordsetClone
.MoveFirst
While Not .EOF
stTmp = stTmp & " " & .EmailName
.MoveNext
Wend
End With
Me.Allemployees = stTmp
end sub

Macro on command button to send email:
SendObject
=[Forms]![frmProjectQuickREF]![frmProjectQuickREFSUB2]![allemployees]

all employees is an unbound text box and the 'can grow' is set to yes

Any thoughts?


Andi Mayer said:
I have a continuous form (based on Query) that displays all "current"
employees and each email address. I already have a button to create an email
for EACH employee, but I'd like to create a button to "email all staff."

I think I need to create an unbound text box (not visible) that gets filled
in "on current" with all email address in one text box (ex:
(e-mail address removed), (e-mail address removed), etc) and then the button on need to
launch an email based on this one text box.

How might I retrieve these records from the query and place them in one text
box?
or any other ideas?
thanks
Click to expand...

Private Sub Mybottom_click
Dim stTmp as string
with Me.RecordsetClone
.movefirst
while not .eof
stTmp=stTmp &";" &.eMailField
.movenext
wend
end with

'send your email, the recipients are in stTmp
'as delimiter I have choosen [;]
end sub
Click to expand...
 
Back
Top