Access表单VBA邮件生成：如何让关联表选项显示文本而非主键值

Got it, let's sort out this issue where your Title dropdown is pulling the primary key (like "1") instead of the actual display text (like "Mr.") when generating your email. Here are two simple, reliable fixes:

Method 1: Use the Combo Box's .Column Property #

Access combo boxes can store multiple columns if you set them up that way. If your Title dropdown shows the text in the second column (remember, columns are zero-indexed), you can directly grab that column's value instead of the bound primary key:

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
 ' Grab the display text from the second column (adjust index if your setup differs)
 Title = Forms("DataToDialFRM").Title.Column(1)
 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

Quick note: If your display text is in the third column of the combo box, use Column(2) instead. Double-check your combo box's row source setup to confirm the correct index.

Method 2: Use DLookup to Fetch Text Directly from TitleTBL #

If you'd rather pull the value straight from the source table (instead of relying on the combo box's columns), use DLookup to match the stored ID to its corresponding text:

' Replace the original Title assignment line with this:
Title = DLookup("TitleDisplayField", "TitleTBL", "ID = " & Forms("DataToDialFRM").Title)

Important: Swap "TitleDisplayField" with the actual name of the field in TitleTBL that holds the text (like "TitleText" or "Salutation"). This will query the table to get the exact text linked to the ID stored in your form's Title control.

Why Your Original Code Didn't Work #

When you bind a combo box to a primary key field, the control's default return value is the bound column (the ID). The friendly display text lives in a separate column of the combo box's row source, which isn't retrieved unless you explicitly reference it via .Column or look it up in the underlying table.

内容的提问来源于stack exchange，提问作者James