populate an email with information from an access form, how to have the referrenced tables selected-CodePudding

I've created a button within a form that will populate an email with certain fileds. some of these fields are drop down boxes which get their information from another table. In this example I have an field for the title (Mr, Mrs, etc), the form shows Mr but when this is written to an email the primary key is there in place of the actual title. "1" instead of "Mr.".

I've tried using what seems logical to get the info from the "TitleTBL"(name of table) but I don't seem to be getting it right. please see the VBA code below and let me know how to reference the actual Title and not the primary key of that option.

Thank You

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

CodePudding user response：

To retrieve the display value of a combobox you use the .Column property.

E.g. if your Title combobox has 2 columns with TitleID ("1") and Title ("Mr."), you need .Column(1) because the column index is zero-bound.

You can make your code better readable by using a With statement:

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

Use bang ! to refer to form controls, not form recordsource values.