Pulling email address from drop down Results

Hello everybody! I'm new to Access and new to these forums, so be gentle. I've searched the forums the best I can and can't seem to find a resolution to my problem... or at least what I'm doing wrong. Please help me learn Access. Ok, so my question. I am trying to pull an email address from my table called "Employees". The table itself has the columns "EmployeeID" (an autonumber for each employee in the db) "LastName" "FirstName" "Title" and "Email" inside of it. In my form "DraftRequestForm" I have a drop down box (named "ProductSpecialist") which pulls all Employees with the Title of "Product Specialist" and places it in the drop down box, combining their "FirstName" field and "LastName" field into one name using a Row Source value of:

	SELECT [FirstName] & " " & [LastName] AS Name FROM Employees WHERE (((Employees.Title)="Product Specialist")) ORDER BY
Employees.LastName, Employees.FirstName;

Now, my problem is that once the person fills out the form, they need the form emailed to whatever employee is selected in the "ProductSpecialist" drop down box. I'm using the following code below for when the button is clicked:

	Private Sub EmailButton_Click()   

Dim FieldValue As Variant
Dim psEmailTo As String

FieldValue = Forms!DraftRequestForm!ProductSpecialist
psEmailTo = DLookup("[Email]", "Employees", "[EmployeeID]=" & FieldValue)

DoCmd.SendObject , , , psEmailTo, , , "test subject", "test email body", True

End Sub

The email window pops up just fine in Outlook, but no matter how I format the DLookup, I can not get it to pull the Email address and place it in the To: field. What am I missing here? Remember, I'm brand new to Access so it could be something really simple. Any help would be greatly appreciated. Thanks!

Seems like this question has been beat to death but I've yet to find anything that works for me.

Access 2003

I've got a form with a drop down that runs a query with many email addresses in a field called "EmailAddress" by default. This field is in column 4, after a few other fields. I'd like to pull those email addresses from the results of the query, pass them to a variable, then based on that variable, create an email with those values seperated by colons without automatically sending it.

I've tried this code but could not get it to work:

Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String

Set MyDb = CurrentDb()
Set rsEmail = MyDb.OpenRecordset("My Query", dbOpenSnapshot)

With rsEmail
Do Until rsEmail.EOF
If IsNull(.Fields(4)) = False Then
sToName = .Fields(4)
sSubject = "Subject"
sMessageBody = "Email Body"

DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
End If
End With

Set MyDb = Nothing
Set rsEmail = Nothing

Hi all,

I'm designing a form that needs a populated drop down list so users can only select from certain entries. I've set up a combo box ("CB") for this purpose. Here's the tricky part: the data in the CB pulls from 5 fields in a table (name, address, phone, fax and email). I need to get the box to show the data like this:


I'm linked to the appropriate table and I can get it to show the drop with all fields (in a data sheet view) and I can select an option but when I select, it only displays the name, not the other 4 fields (i.e. click the drop down arrow and get "name/address/phone/fax/email" for all records, select a record and what shows in the box is "name" only).

I did something at one point where I got it to work, but it was missing a field (name was set up as "memo", not "text" in the table) and I screwed it up somehow and now I can't find my way back. Please help!!


I've searched n searched on here for the solution, mostly because I don't like to wait for someone to respond to my question (which isn't to say I've ever had to wait real long for a response, lol).

My database contains many, many linked tables (of which I only use maybe 10) from an oracle database. In the module I am currently working on, the user locates the contact they want to modify (address, phone number, email, etc) and clicks a button to open a Data Entry form that drops the contact data from the oracle tables into local access staging tables, so the user can change what they need to on the form. Later, after some QC, the staging tables will be used via tools in the oracle database to dump the changes into the main oracle database.

That primary form (where you can only view the data in the oracle table) contains 2 subforms that pulls from another table with the contact's phone numbers and emails/urls(typically just their email address), putting the phone numbers (business, emergency, fax, pager, etc) in one subform and emails/urls in the other. On the new Data Entry form, the phone numbers subform and emails/urls subform appear as listboxes.

So, once the user has made any changes to the address, the cursor tabs to the lstPhoneNumbers. I've already programmed the DoubleClick event to open a little data entry form where the double clicked phone number is dropped into the data entry form (another staging table) for the user to make changes. However, I also want to program so the user can simply hit enter (or arrow down to the number they want, then hit enter) and have the data entry form pop up. It WORKS, when the user uses the arrow key to "select" the number they want to use. But I have tried several ways to specify the default SELECTED row to the first one, so that when the user wants to change the FIRST phone number in the list, they simply ahve to hit enter.

What am I doing wrong?

I've tried putting this

	    Me.lstPhoneNumber.Selected(0) = True

in the GotFocus event for the listbox, in the onLoad event for the form, nothing seems to work!

I am in desparate need of help with creating a short cut for entering data, particularly wrt addresses. I, like many others would like to enter EITHER a post code number or a suburb name in a form (whichever one I have), and have the city, state, region and remaining post code/suburb field filled in automatically.

I am creating a kids club membership database and have a table with their name, address, phone, email etc. This table is called 'Kids Club'
I have another table with 8000+ lines of post code information for Australia. It's fields are 'Post Code', 'Suburb' & 'State' and this table is called 'PostCode'
There is not a primary key in the 'Kids Club'
table that correspondes to a field in the 'Post Code' table
Now, some australian post codes have various suburbs and some suburbs have different post codes in different states.
Also, I have already created 3 tables for City, Region, and Country, so that I was able to create a drop down box in the form to select this information from.

I have tried to do Dlookup expressions after reading peoples suggestions. I have read about doing simple queries where you join the tables and the 'data will automatically update', but I only started using access yesterday (I'm Serious) and I don't understand the lingo.

Could anyone be able to give me complete, step by step instructions to allow me to do what should be a simple proceedure.
I would simply like to enter a suburb name on a form, which automatically pulls the info out of the PostCode table and inserts the info in the City, State, and Post Code boxes in the form. However, some kids write either the post code or the suburb, so I would need to be able to do the same proceedure for a given post code.

Any help/advice would be GREATLY appreciated.


P.S. Any help given would need to be in simple, step by step terms, as I am a complete stranger with MS Access. Thanks again!!!

[This message has been edited by Davidekreed (edited 10-15-2001).]

[This message has been edited by Davidekreed (edited 10-15-2001).]

Not finding an answer? Try a Google search.