使用访问表单中的信息填充电子邮件，如何在电子邮件中显示"已选择的引用表"选项，而不是主键编号

Question

我在表单中创建了一个按钮，该按钮将用某些文件填充电子邮件。其中一些字段是从另一个表中获取信息的下拉框。在这个例子中，我有一个标题字段(Mr、Mrs等(，表格显示Mr，但当这写到电子邮件中时，主键就在那里，而不是实际的标题"1〃；而不是"；先生；。

我尝试过使用看起来合乎逻辑的东西来从"；标题TBL"；(桌子的名字(但我似乎没有把它说对。请参阅下面的VBA代码，并让我知道如何引用实际标题，而不是该选项的主键。

谢谢

Private Sub TransferDanR_Click()
Dim ID As Variant
Dim Title As Variant
Dim First As Variant
Dim Last As Variant
Dim Addr1 As Variant
Dim Addr2 As Variant
Dim Postcode As Variant
Dim HomePhone As Variant
Dim MobilePhone As Variant
Dim Insurer As Variant
Dim RenewalDate As Variant
Dim PolicyNotes As Variant
Dim ContactNotes As Variant
Dim LGAgent As Variant
Dim objOutlook As Object
Dim objEmail As Object

ID = Forms("DataToDialFRM").ID
Title = Forms("DataToDialFRM").Title
First = Forms("DataToDialFRM").First
Last = Forms("DataToDialFRM").Last
Addr1 = Forms("DataToDialFRM").Addr1
Addr2 = Forms("DataToDialFRM").Addr2
Postcode = Forms("DataToDialFRM").Postcode
HomePhone = Forms("DataToDialFRM").HomePhone
MobilePhone = Forms("DataToDialFRM").MobilePhone
Insurer = Forms("DataToDialFRM").Insurer
RenewalDate = Forms("DataToDialFRM").RenewalDate
PolicyNotes = Forms("DataToDialFRM").PolicyNotes
ContactNotes = Forms("DataToDialFRM").ContactNotes
LGAgent = Forms("DataToDialFRM").LGAgent

Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(0)

With objEmail
.To = "emailaddress; emailaddress; emailaddress"
.Subject = ID & " " & Last & " from " & LGAgent & " (Automated Transfer Email)"
.HTMLBody = "<p>" & "Name: " & Title & ", " & First & ", " & Last & "<p>" & "Address: " & Addr1 & ", " & Addr2 & ", " & Postcode & "<p>" & "HomePhone: " & HomePhone & "<p>" & "MobilePhone: " & MobilePhone & "<p>" & "Insurer " & Insurer & "<p>" & "Renewal Date: " & RenewalDate & "<p>" & "Policy Notes: " & "<p>" & PolicyNotes & "<p>" & "Contact Notes: " & "<p>" & ContactNotes
.Display

End With

Set objEmail = Nothing
Set objOutlook = Nothing

End Sub

Answer 1

要检索组合框的显示值，请使用.Column属性。

例如，如果您的Title组合框有两列，分别为TitleID("1"(和Title("Mr."(，则需要.Column(1)，因为列索引为零。

您可以使用With语句来提高代码的可读性：

With Forms("DataToDialFRM")
ID = !ID
Title = !Title.Column(1)
First = !First
' ...
End With

使用bang!引用窗体控件，而不是窗体记录源值。