Command button to copy certain fields

[craigprice]

I currently have a form for a handover process and am looking to have a button to ideally take a section of inputted information from the form and place this into an email.

IncidentReferenceNumberName1
DescriptionSignificant1
ActionTakenSignificant1
CurrentStatusSignificant1
TimeLoggedSignificant1
TimeResolvedSignificant1
NumberofcallsreceivedSignificant1

Are my fields, this goes up to 4.

I want to copy all these fields from my form, ideally into a new email but if not possible then atleast just to clipboard, is there any way to do this?

Thanks in advance.

 

[CJ_London]

This link should help - the primary code is in post #9

http://www.access-programmers.co.uk/forums/showthread.php?t=243006

 

[craigprice]

Thanks CJ, I'm still struggling with this, it keeps expecting an end sub, not sure why :S

 

[Catalina]

I you post your code here someone will help you out.

Catalina

 

[craigprice]

This is what I've tried so far using CJ's code.

Private Sub Significant_Notification_Click()
Private Function BuildBody(MemID As Long) As String
Dim MyDb As Database
Dim Rst As Recordset
Dim BodyStr As String

Set MyDb = CurrentDb
Set Rst = MyDb.OpenRecordset("SELECT IncidentReferenceNumberName1, DescriptionSignificant1") ' change to what you want to report and from what table(s) - try creating in query builder then copy th text to here

BodyStr = ""
While Not Rst.EOF
BodyStr = BodyStr & Rst.Fields(0) & " " & Rst.Fields(1) & " " & Rst.Fields(2) & vbCrLf '(linefeed)
Rst.MoveNext
Wend
If BodyStr <> "" Then BodyStr = "ID " & "Item " & "Quantity " & vbCrLf & BodyStr 'add a header
BuildBody = BodyStr
Set Rst = Nothing
Set MyDb = Nothing
End Function

Set Rst = MyDb.OpenRecordset("SELECT * FROM members where ..there's something to send..") 'note body needs to be one field, if it is multiline (e.g. a list) then you'll either need to send as a table or query attachment or build up the body line by line before sending

While Not Rst.EOF
BodyStr = BuildBody(Rst.Fields("memID"))
if bodystr<>"" then docmd.sendobject ... rst.fields("IncidentReferenceNumberName1"), rst.fields("Subject Line"),bodystr
Rst.MoveNext
Wend
End Function

I have also tried:

Private Function Significant_Notification_Click(MemID As Long) As String
Dim MyDb As Database
Dim Rst As Recordset
Dim BodyStr As String

Set MyDb = CurrentDb
Set Rst = MyDb.OpenRecordset("SELECT IncidentReferenceNumberName1, DescriptionSignificant1") ' change to what you want to report and from what table(s) - try creating in query builder then copy th text to here

BodyStr = ""
While Not Rst.EOF
BodyStr = BodyStr & Rst.Fields(0) & " " & Rst.Fields(1) & " " & Rst.Fields(2) & vbCrLf '(linefeed)
Rst.MoveNext
Wend
If BodyStr <> "" Then BodyStr = "IncidentReferenceNumberName1 " & "DescriptionSignificant1 " & vbCrLf & BodyStr 'add a header
BuildBody = BodyStr
Set Rst = Nothing
Set MyDb = Nothing
End Function

Set Rst = MyDb.OpenRecordset("SELECT * FROM members where ..there's something to send..") 'note body needs to be one field, if it is multiline (e.g. a list) then you'll either need to send as a table or query attachment or build up the body line by line before sending

While Not Rst.EOF
BodyStr = BuildBody(Rst.Fields("IncidentReferenceNumberName1"))

Rst.MoveNext
Wend
End Function

 

[CJ_London]

This is your end sub problem

Private Sub Significant_Notification_Click()
Private Function BuildBody(MemID As Long) As String

You have a function nested within a sub without an end sub

There are some other bits which need completing as well - you'll need to use your field/table names

Set Rst = MyDb.OpenRecordset("SELECT IncidentReferenceNumberName1, DescriptionSignificant1 [COLOR=red]FROM YourTable WHERE ID=MEmID[/COLOR]") 
 
Set Rst = MyDb.OpenRecordset("SELECT * FROM [COLOR=red]members where ..there's something to send.."[/COLOR]) 
 
if bodystr<>"" then docmd.sendobject [COLOR=red]... rst.fields("IncidentReferenceNumberName1"), rst.fields("Subject Line"),[/COLOR]bodystr

 

[craigprice]

When you are refering to MemID, what is that.

For example you've said WHERE ID=MEmID?

 

[CJ_London]

MemID is a parameter in the BodyBuild function
ID is the ID for your table