How dim a textbox that is reliant on a combo box

[Jwnagz94]

I want to select a value from the text box based on the value that is inputted into my combo box

You will find my current code on the bottom. I have dimmed the combo box, I want column 3 of the combo box named Primary Email. For the life of me I cannot figure it out. Any help would be helpful and appreicated

Private Sub Command46_Click()

Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim MyBodyText As String
Dim Form_Table1 As Form
Dim Test0 As ComboBox

' First, we open Outlook...
Set MyOutlook = New Outlook.Application

' Set up the database and query connections
Set Frm = Form()
Set MailList = Test0

' This creates the e-mail
Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = MailList("Primary Email")

' This assigns the FROM
MyMail.SentOnBehalfOfName = "Test"

'This gives it a subject
MyMail.Subject = "Test Subject"

'This gives it the body
MyBodyText = "Good Morning" & vbNewLine & vbNewLine _

MyMail.Body = MyBodyText

' MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

MyMail.Display

'And on to the next one...
MailList.MoveNext

'Cleanup after ourselves

Set MyMail = Nothing

'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing

End Sub

 

[xenou]

The way I remember comboboxes working is that the value of the combobox is (usually) a key, and other columns are just there for display purposes. So you need to look up the value in the original table, which can be done with a DLOOKUP() formula.

For instance, if the table is States and the key your combobox value you can get a result from the table using dlookup:

=DLookup("StateName","States", "ID=" & [Forms]![Form1]![cboState])

whether you dim form objects or not is up to you, although a combobox and a textbox are not the same thing (neither is a listbox, for that matter - they all work differently).

 

[Jwnagz94]

Thank you for the reply. I am very new to access and if you could explain how this formula works

 

[xenou]

This page has a lot of information on how to use comboboxes:
http://fisher.osu.edu/~muhanna.1/837/MSAccess/tutorials/combo.pdf

The DLookup function is easy to use but you must of course practice until you are comfortable with it:
MS Access: DLookup Function

 

[Jwnagz94]

MyMail.To = DLookup("Primary Email", "Table1" & [Forms]![Test for Tabs]![cboTable1])

So this is what i wrote but i do not think i have the hang of it.

My Field name is = Primary Email
My combo Box name= Test 0
Form name = Test for tabs
Table name= Table 1
But i dont think i wrote it right. If you could please give me some pointers.



My objective is to be able to send an email to the primary email populated off the combobox

 

[xenou]

Your combobox is called Test 0 so that's what you should use when getting its value:
MyMail.To = DLookup("Primary Email", "Table1" & [Forms]![Test for Tabs]![Test 0])

Looks like you can also try:
MyMail.To = [Test 0].Column(2)
or
MyMail.To = [Test 0].Column(3)

depending on whether you count columns starting from 0 or 1, when you say its column 3. I'm assuming you are using form code. If not, this will probably need to be changed accordingly.

 

[Jwnagz94]

So this dlookup is not picking up my field from my table. So in my case its not reading primary email as a field in my table.

 

[xenou]

Verify what the actual values are so you can test the DLookup on those values. Also it makes a difference if you are using text or numeric values to lookup - text values must be in single quotes.

 

[Jwnagz94]

Got it to work! Thank you!!