Get a value based on a form control value 1

[swaybright]

Hi all,
I feel like this is a dumb question that has probably been answered before, but I am having no luck with the site search.

I am trying to create a code that will generate email. I have read the FAQ concerning this so I think I have that ok. My problem is getting the correct main address value.

The form (based on the research table) on which I am attaching the button has a combo box that looks up the names of the researchers from the researcher table. I want to have the button email the researcher associated with the current record. I need to get the names from the researcher table based on the researcherid from the research table (the bound value of the combo box). Ultimately I want the email address to read "[FirstName].[LastName]@[Domain].com"
My code so far is below. I need to get code that will get the proper firstname and lastname.

'Declare variables
Dim txtaddress As String
Dim txtsubject As String
Dim txtmessage As String
Dim stext As String

'Define variables
'GlobalConstant is a table for fairly static variables
'notebook# and page# are fields on current record
txtaddress = FirstName & "." & LastName & "@" _
& [GlobalConstant]![EmailDomain] & ".com"
txtsubject = "Notebook Number " & Me![Notebook#].Value & "-" & _
Me![page#].Value
txtmessage = "Message generated by Registry Database." & vbCrLf _
& "Please send me any information you have about the " _
& "compound referenced in notebook page " _
& Me![Notebook#].Value & "-" & Me![page#].Value & "." _
& vbCrLf & vbCrLf & "Thank you"

stext = txtaddress & "&Subject=" & txtsubject & "&Body =" & _
txtmessage

Call ShellExecute(Me.hwnd, "open", stext, vbNullString, vbNullString, SW_SHOWNORMAL)

Thanks for any help

Shane

 

[hermanlaksko]

Try

http://www.helenfeddema.com/

she has a topic along those lines.

Herman

 

[swaybright]

Thanks Herman,

I realized that DLookup was what I wanted. Like I said, dumb question.

Shane