Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Callig a field from a sub form into an email in access

Status
Not open for further replies.
hvns

hvns

Technical User
Joined
Oct 2, 2007
Messages
25
Location
US
Hello,

I am sending an email based on the field data displayed in the current form window. A problem I am running into is that there is a subform within the current form and I am wanting to pull the information from that form into the email as well. The subform data = 2 fields (DateCreated) (Note) for each date... they are notes about the job. See attached phot for layout.

Any help is appreciated.
 
Sort by date Sort by votes
How are ya hvns . . .

Here's a function that returns all notes for the current [blue]UFC Quote[/blue]. Copy/paste the function to the code module of the main form:
Code: 
[blue]Public Function usrNotes() As String
   Dim sfrm As Form, rst As DAO.Recordset, Build As String
   Dim SL As String, DL As String

   Set sfrm = Forms![MainformName]![subFormName].Form
   Set rst = sfrm.RecordsetClone
   SL = vbNewLine 'Next Line
   DL = SL & SL 'Line Space!
   
   If Not rst.BOF Then
      Do Until rst.EOF
         If Build <> "" Then
            Build = Build & DL & "Note: " & rst!Date & SL & "   " & rst!Notes
         Else
            Build = "Note: " & rst!Date & SL & "   " & rst!Notes
         End If
      Loop
   End If
   
   usrNotes = Build

End Function[/blue]
How you use it is up to you!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
Woops! [blush] . . .

Forgot one important line:
Code: 
[blue]Public Function usrNotes() As String
   Dim sfrm As Form, rst As DAO.Recordset, Build As String
   Dim SL As String, DL As String

   Set sfrm = Forms![MainformName]![subFormName].Form
   Set rst = sfrm.RecordsetClone
   SL = vbNewLine 'Next Line
   DL = SL & SL 'Line Space!
   
   If Not rst.BOF Then
      Do Until rst.EOF
         If Build <> "" Then
            Build = Build & DL & "Note: " & rst!Date & SL & "   " & rst!Notes
         Else
            Build = "Note: " & rst!Date & SL & "   " & rst!Notes
         End If
         
         [purple]rst.MoveNext[/purple]
      Loop
   End If
   
   usrNotes = Build

End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
First of all - Thank you for assisting...

I have edited the code to pull the actual names / fields but I am getting a message: "Microsoft Accesscan't find the field 'frmQuote_Notes' referred to in your expression


CODE:

Public Function usrNotes() As String
Dim sfrm As Form, rst As DAO.Recordset, Build As String
Dim SL As String, DL As String

Set sfrm = Forms![frm_QuoteEntry1]![frmQuote_Notes].Form
Set rst = sfrm.RecordsetClone
SL = vbNewLine 'Next Line
DL = SL & SL 'Line Space!

If Not rst.BOF Then
Do Until rst.EOF
If Build <> "" Then
Build = Build & DL & "Notes: " & rst!Date_Reviewed & SL & " " & rst!Quote_Notes
Else
Build = "Note: " & rst!Date_Reviewed & SL & " " & rst!Quote_Notes
End If

rst.MoveNext
Loop
End If

usrNotes = Build

End Function
 
Upvote 0 Downvote
hvns . . .

The error indicates either the mainform name, subform name, or both don't exist! ... could be spelling.

Is it possible one of the [blue]underscores[/blue] is in the wrong place!

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
hvns . . .

We could go a number of threads before nailing names here. To try an shorten the thread and unless there's some proprietary issue ivolved ... it would greatly enhance matters if you could upload a copy of the db! You can use 4Shared for this ... [green]Its Free![/green]

Note: if your access verison is greater than 2k, convert a copy to 2k and upload that!

[blue]Your Thoughts/ . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
hvns . . .

Code: 
[blue]Change: Set sfrm = Forms![frm_QuoteEntry1]![frmQuote_Notes].Form
To    : Set sfrm = [frmQuote_Notes].Form[/blue]
See if that helps . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
TheAceMan1,


I tried to save it to 2k ( I am in 2007) but it will not allow me to save a copy.

I used the code above and I no longer receive the error but no notes are pulling into the email. Maybe a problem with how I am trying to pull the notes in - it is towards the middle of the code

Here is the code for the email generation:


Private Sub Command103_Click()
On Error GoTo Err_cmdCommand103_Click

Dim stWhere As String '-- Criteria for DLookup (ORIGINAL LINE OF CODE)


Dim stText As String '-- E-mail text

Dim DueDate As Variant '-- Quote Due date for e-mail text
Dim PrimeNumber As Variant '-- Prime Number contract number
Dim ProjectName As Variant '-- Project Name
Dim CustomerName As Variant '-- Customer Name
Dim DuetoContracts As Variant '-- Date due to contracts
Dim ContractType As Variant '-- Contract Type
Dim DPASRating As Variant '-- DPAS Rating
Dim Lines As Variant '-- Number of lines
Dim Cust_RFQ As Variant '-- Customers RFQ#
Dim CofC As Variant '-- Certificate of conform.
Dim DD1149 As Variant '-- DD1149
Dim DD250 As Variant '-- DD250
Dim CorG As Variant '-- Commercial or Government
Dim CustNeedDate As Variant '-- Customer Product Need Date
Dim JobType As Variant '-- Type of job P or E
Dim varDueDate As String '-- Quote Due date for e-mail text
Dim stSubject As String '-- Subject line of e-mail
Dim QuoteNumber As String '-- The QuoteNumber from form
Dim QuoteNote As Variant '-- Displays the quote note
Dim QuoteNoteDate As Variant '-- Displays the quote note due date
Dim usrNotes As String '-- calls quote notes


Dim errLoop As Error


If Not IsNull(Me.Contracts) Then
'varTo = DLookup("[ContractEmail]", "tbl_Information_Contracts", "tbl_Information_Contracts.ContractName = " & Me.Contracts)
stWhere = "tbl_Information_Contracts.Contracts_ID = " & Me.Contracts
varTo = DLookup("[ContractEmail]", "tbl_Information_Contracts", stWhere)
End If

If Not IsNull(Me.Project_Quoter) Then
varTo = varTo & "; " & DLookup("[ProductionEmail]", "tbl_Information_Production", "tbl_Information_Production.ID = " & Me.Project_Quoter)
End If

'If Not IsNull(Me.Quality) Then
' varTo = varTo & "; " & DLookup("[QualityEmail]", "tbl_Information_Quality", "tbl_Information_Quality.Quality_ID = " & Me.Quality)
'End If

If Not IsNull(Me.Buyer) Then
varTo = varTo & "; " & DLookup("[BuyerEmail]", "tbl_Information_Buyers", "tbl_Information_Buyers.ID = " & Me.Buyer)
End If

If Not IsNull(Me.Engineer) Then
varTo = varTo & "; " & DLookup("[EngineerEmail]", "tbl_Information_Engineering", "tbl_Information_Engineering.ID = " & Me.Engineer)
'stWhere = "tbl_Information_Engineering.ID = " & Me.Engineer
'varTo = DLookup("[EngineerEmail]", "tbl_Information_Engineering", stWhere)

End If



stSubject = " THIS IS A TEST - PLEASE IGNORE - New Request For Quote: " & Me.Quote_Number

QuoteNumber = Me.Quote_Number
DueDate = Me.Date_Due_To_Contracts
CustomerName = Me.Customer_ID.Column(1)
ContractType = Me.Contract_Type
ProjectName = Me.Project_Name
PrimeNumber = Me.Prime_Contract_Number
DPASRating = Me.DPAS_Rating
Lines = Me.No_Line_Items
IntendedUse = Me.IntendedUse
Cust_RFQ = Me.RFQ_
CorG = Me.CorG
JobType = Me.Job_Type
CustNeedDate = Me.ProductNeedDate
DuetoContracts = Me.Date_Due_To_Contracts
Notes = usrNotes
'NoteDate = [frmQuote_Notes].Form![Date_reviewed]

If Me.DD1149 = -1 Then
DD1149 = "Yes"
Else
DD1149 = "No"
End If

If Me.CofC = -1 Then
CofC = "Yes"
Else
CofC = "No"
End If

If Me.DD250 = -1 Then
DD250 = "Yes"
Else
DD250 = "No"
End If



stText = "You have been assigned a new ticket." & Chr$(13) & _
Chr$(13) & "Quote number: " & QuoteNumber & Chr$(13) & _
Chr$(13) & "Customer Name: " & CustomerName & Chr$(13) & _
Chr$(13) & "ProjectName: " & ProjectName & Chr$(13) & _
Chr$(13) & "Date Due to Contracts: " & DuetoContracts & Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Contract Type: " & ContractType & Chr$(13) & _
Chr$(13) & "Job Type: " & JobType & Chr$(13) & _
Chr$(13) & "Prime Number: " & PrimeNumber & Chr$(13) & _
Chr$(13) & "Intended Use: " & IntendedUse & Chr$(13) & _
Chr$(13) & "Number of Lines: " & Lines & Chr$(13) & _
Chr$(13) & "Customer's RFQ#: " & RFQ_ & Chr$(13) & _
Chr$(13) & "Customer's Product Need Date: " & CustNeedDate & Chr$(13) & _
Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Certificate of Conformance Needed: " & CofC & Chr$(13) & _
Chr$(13) & "DD1149 Needed: " & DD1149 & Chr$(13) & _
Chr$(13) & "DD250 Needed: " & DD250 & Chr$(13) & _
Chr$(13) & "Commercial or Government: " & CorG & Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Quote Notes: " & Notes & Chr$(13) & _
Chr$(13) & _
Chr$(13) & _
Chr$(13) & "This is an automated message." & _
" Please notify Contracts immediately if Bid Date cannot be met."


'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1


Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdCommand103_Click:
Exit Sub

Err_cmdCommand103_Click:
MsgBox Err.Description
Resume Exit_cmdCommand103_Click

End Sub
 
Upvote 0 Downvote
Try converting to 2000 - 2003 . . .

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
I get the same message that it will not allow it for 2k or 2002-2003 versions.
 
Upvote 0 Downvote

What version are you using?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
hvns said:
[blue]I used the code above and I no longer receive the error but [purple]no notes are pulling into the email.[/purple][/blue]
Click to expand...
... and I believe your referring to the line:
Code: 
[blue]'NoteDate = [frmQuote_Notes].Form![Date_reviewed][/blue]
Your still having a subform referencing problem. Try this:
[ol][li]Open the mainform in design view (you should see the subform.[/li]
[li]Click a textbox on the mainform.[/li]
[li]Call up the properties window and select the [blue]Other Tab[/blue]. At the top in the [blue]Name[/blue] property, you should see the name of the textbox you selected![/li]
[li]Now select the [blue]outline[/blue] of the subform and let me know what the [blue]Name[/blue] property reveals![/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Upvote 0 Downvote
That is a line I was trying to see if it would work. The line that should work starts here and it is called out below towards the middle to populate the email sa Notes


Notes = usrNotes '-- ACEMAN this is the line that should work

'NoteDate = [frmQuote_Notes].Form![Date_reviewed] '--- this is a junk line, thought I removed it




If Me.DD1149 = -1 Then
DD1149 = "Yes"
Else
DD1149 = "No"
End If

If Me.CofC = -1 Then
CofC = "Yes"
Else
CofC = "No"
End If

If Me.DD250 = -1 Then
DD250 = "Yes"
Else
DD250 = "No"
End If



stText = "You have been assigned a new ticket." & Chr$(13) & _
Chr$(13) & "Quote number: " & QuoteNumber & Chr$(13) & _
Chr$(13) & "Customer Name: " & CustomerName & Chr$(13) & _
Chr$(13) & "ProjectName: " & ProjectName & Chr$(13) & _
Chr$(13) & "Date Due to Contracts: " & DuetoContracts & Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Contract Type: " & ContractType & Chr$(13) & _
Chr$(13) & "Job Type: " & JobType & Chr$(13) & _
Chr$(13) & "Prime Number: " & PrimeNumber & Chr$(13) & _
Chr$(13) & "Intended Use: " & IntendedUse & Chr$(13) & _
Chr$(13) & "Number of Lines: " & Lines & Chr$(13) & _
Chr$(13) & "Customer's RFQ#: " & RFQ_ & Chr$(13) & _
Chr$(13) & "Customer's Product Need Date: " & CustNeedDate & Chr$(13) & _
Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Certificate of Conformance Needed: " & CofC & Chr$(13) & _
Chr$(13) & "DD1149 Needed: " & DD1149 & Chr$(13) & _
Chr$(13) & "DD250 Needed: " & DD250 & Chr$(13) & _
Chr$(13) & "Commercial or Government: " & CorG & Chr$(13) & _
Chr$(13) & _
Chr$(13) & "Quote Notes: " & Notes & Chr$(13) & _
Chr$(13) & _
Chr$(13) & _
Chr$(13) & "This is an automated message." & _
" Please notify Contracts immediately if Bid Date cannot be met."


'Write the e-mail content for sending to assignee
DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1


Exit Sub

Err_Execute:

' Notify user of any errors that result from
' executing the query.
If DBEngine.Errors.Count > 0 Then
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If

Resume Next


Exit_cmdCommand103_Click:
Exit Sub

Err_cmdCommand103_Click:
MsgBox Err.Description
Resume Exit_cmdCommand103_Click

End Sub
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Solved
Subform not updating in parent form
Replies
10
Views
1K
Kennelbloke
Kennelbloke
MattGreer
  • Locked
  • Question Question
Add Field Description to a Form
Replies
5
Views
669
Andrzejek
Andrzejek
Bubba002
  • Locked
  • Question Question
Pass a subform fieldname-ID as a string variable in CF expression
2 3
Replies
50
Views
4K
dhookom
dhookom
Xavierparkash
  • Locked
  • Question Question
MS Access Slow Search in Linked Database
Replies
0
Views
636
Xavierparkash
Xavierparkash
TomSalvato
  • Locked
  • Question Question
Getting the Monday date from a week number
Replies
2
Views
1K
TomSalvato
TomSalvato

Part and Inventory Search

Sponsor

Back
Top