How to pass value of an unbound field in a new record to a table for emailing (1 Viewer)

J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
Hello,

I'm trying to send an email based on a new form record that uses 3 unbound text boxes for it's data.

i have tried to send the email based upon the text box directly but the data is always blank, i'm assuming because the record is not updated yet. I figured i would need to use an append query and send this data to a separate table and then call the table into the email?

Am i on the right track here. If so how do i reference the unbound controls on the form into a blank query?
 
Minty

Minty

AWF VIP
Local time
Today, 07:30
Joined
Jul 26, 2013
Messages
10,371
You don't need to save the value - you can refer to it directly in the query and in the query designer it will even assist you with the syntax in later access versions.

In the criteria section type something like

[Forms]![YourFormName]![YourControlName]

And it should work.
 
J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
What would i use for the field name?
 
Minty

Minty

AWF VIP
Local time
Today, 07:30
Joined
Jul 26, 2013
Messages
10,371
What field name? I assumed you where running a separate query based on the form.

If not post up the code you tried to use to send the email from the unbound form - there is no reason that shouldn't work.
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 23:30
Joined
Sep 12, 2017
Messages
2,111
jjake said:
Hello,

I'm trying to send an email based on a new form record that uses 3 unbound text boxes for it's data.
Click to expand...

OK, lets start with the real issue, how are you trying to send an Email?

If a field is not bound it is not coming from or going to a table. This implies you are trying to send an Email some how and are having a problem.

Please post the code for sending the Email so we can help narrow down what is not working. If you get error message, please specify what message and where you get it.
 
J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
Code: 
Private Sub Command68_Click()

Refresh

Dim Response As Integer
    Response = MsgBox("Would you like to notify the MOC Initator of this response?", vbYesNo + vbQuestion, "Client Prompt")
If Response = vbYes Then

Refresh
   
Dim strEmailAddress
Dim StrSQL


StrSQL = "SELECT * FROM qryMOC"

strEmailAddress = Me.txtEmailResponse
'StrSubject = "A new production MOC has been created me.MOCNum"
strSubject = "Response to MOC# " & Me.MOCNum
StrEmail = "User Response: " & Me.txtDepartmentChoice & vbNewLine _
             & "" & vbNewLine _
             & "Comments: " & Me.UserComment & vbNewLine _


DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, StrEmail, False, False

MsgBox "Thank you. Your response has been submitted successfully."
 
DoCmd.Close acForm, "frmMOCReviewForm", acSaveYes

Else

DoCmd.Close acForm, "frmMOCReviewForm", acSaveYes

 End If
 
End Sub

Me.MOCNum, Me.txtDepartmentchoice and Me.UserComment are unbound fields in my form. They pull a value/text from different multiple combo boxes in my form and display them as text depending on what combo has been selected.
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 23:30
Joined
Sep 12, 2017
Messages
2,111
If the value is not the first column in each of your comboboxes record sources, you would use Me.YourComboBoxNameHere.Column(n) where n is the column you are looking for starting with 0.

I would also put a break or a msgbox prior to calling your DoCmd.SendObject to see what exactly you are filling in. You may find out your looking for the wrong problem.

As to saving these values, as you are not using a separate recordset to fill in your Email that would not affect your current issue.
 
J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
Regarding your previous message. There are no errors, the only problem i have is when entering the data into the form and then clicking and submitting the email, when the email is received the data i asked for in the VBA is not there. If i close the form and reopen the form and send the email again it will be there.


Also on the unbound text box, their results can vary depending on who is viewing the form.

Code: 
Private Sub Form_Load()

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 1 Then
Me.RDReview.Enabled = True
Else
Me.RDReview.Enabled = False
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 2 Then
Me.MaintenanceReview.Enabled = True

Else
Me.MaintenanceReview.Enabled = False
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 3 Then
Me.ProductionReview.Enabled = True
Else
Me.ProductionReview.Enabled = False
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 4 Then
Me.SafetyReview.Enabled = True
Else
Me.SafetyReview.Enabled = False
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 5 Then
Me.SiteManagerReview.Enabled = True
Else
Me.SiteManagerReview.Enabled = False
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 1 Then
Me!txtDepartmentChoice = Me!RDReview.Column(1)
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 2 Then
Me!txtDepartmentChoice = Me!MaintenanceReview.Column(1)
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 3 Then
Me!txtDepartmentChoice = Me!ProductionReview.Column(1)
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 4 Then
Me!txtDepartmentChoice = Me!SafetyReview.Column(1)
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 5 Then
Me!txtDepartmentChoice = Me!SiteManagerReview.Column(1)
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 1 Then
Me!UserComment = Me!RDReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 2 Then
Me!UserComment = Me!MaintenanceReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 3 Then
Me!UserComment = Me!ProductionReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 4 Then
Me!UserComment = Me!SafetyReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 5 Then
Me!UserComment = Me!SiteManagerReasoning
End If
 
M

Mark_

Longboard on the internet
Local time
Yesterday, 23:30
Joined
Sep 12, 2017
Messages
2,111
To be clear, prior to sending the Email you've verified that StrEmail contains what you want but it doesn't appear in the body of the Email, but if you exit and come back it does?
 
J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
Just to give you a run down from the start including an answer to your question.

User 1 creates new record in formnewrecord. User 1 closes form.

User 2 opens continuous form and selects the record above to open formReview

User 2 selects and answer in combobox and leaves a comment in text box.

User 2 submits email and closes formReview and user 1 receives email. Email reads

User Response:
Comments:

User 2 reopens formreview from continuous form and submits email again and closes form review.

User 1 receives email. Email reads,

User Response: Here is the selection from combo box
Comments: Here is the comment made in the text box.

I have check VBA on the first time it's opened and the second time but strEmail will not show me anything.
 
J

jjake

Registered User.
Local time
Today, 01:30
Joined
Oct 8, 2015
Messages
291
Well i ended up getting it by using a Dlookup on the table values.

Code: 
Private Sub Command68_Click()

Refresh

Dim StrResponse
Dim StrComment
Dim Response As Integer
    Response = MsgBox("Would you like to notify the MOC Initator of this response?", vbYesNo + vbQuestion, "Client Prompt")
If Response = vbYes Then

End If


If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 1 Then
StrResponse = Me.RDReview.Column(1)
StrComment = Me.RDReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 2 Then
StrResponse = Me.MaintenanceReview.Column(1)
StrComment = Me.MaintenanceReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 3 Then
StrResponse = Me.ProductionReview.Column(1)
StrComment = Me.ProductionReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 4 Then
StrResponse = Me.SafetyReview.Column(1)
StrComment = Me.SafetyReasoning
End If

If DLookup("[Department]", "[tblUsers]", "[UserTag]='" & Me.txtUser & "'") = 5 Then
StrResponse = Me.SiteManagerReview.Column(1)
StrComment = Me.SiteManagerReasoning
End If

Refresh
   
Dim strEmailAddress
Dim StrSQL

StrSQL = "SELECT * FROM qryMOC"

strEmailAddress = Me.txtEmailResponse
'StrSubject = "A new production MOC has been created me.MOCNum"
strSubject = "Response to MOC# " & Me.MOCNum
StrEmail = "User Response: " & StrResponse & vbNewLine _
             & "" & vbNewLine _
             & "Comments: " & StrComment & vbNewLine _


DoCmd.SendObject , , acFormatRTF, strEmailAddress, _
, , strSubject, StrEmail, False, False

MsgBox "Thank you. Your response has been submitted successfully."
 
DoCmd.Close acForm, "frmMOCReviewForm", acSaveYes

End Sub
 
You must log in or register to reply here.

Similar threads

M
Querying for idle projects. Yes/No field to autopopulate a text field with a set string, "no new update".
Replies
13
Views
272
Pat Hartman
P
C
Inserting multiple rows into an email created by VBA
Replies
6
Views
209
ebs17
E
S
Send email based on unbound form controls?
Replies
5
Views
424
Shecky
S
kstaelens
Transpose the result of a query and add it to another table
Replies
7
Views
277
kstaelens
kstaelens
Y
Key violations in append query
Replies
9
Views
369
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom