How to make an unbound field autopopulate conditionally from another unbound field?

[Griffin410]

To make it simple, I have a list of contact names and their email. I want to have a form with no control source and have 2 simple drop down boxes for their Name and their Email. What I would like to happen is the user choose a name from the drop down in field 1 and then the field below autopopulate their email in field 2. I understand I can use conditional IF/THEN and list out each email, but the contact list is ever changing, so I want to first drop down to link to the TblContacts, have the user pick the name from the list and then have the 2nd field autopopulate from that same TblContacts with their email respective to the Name entered in field 1. Would a Dlookup work for this? Something along the lines of this?


My form has two field [Traveler] and [Email]
The tblContacts two fields are [Name] and [Email]

DLookup("[Email]", "tblContact", "[Name] = [frmEmail]![Traveler]")

[June7]

If the form does not have RecordSource then there are no fields, just controls (combobox, textbox, etc).

Better would be to have the email as a column in the combobox RowSource (column can be hidden). Then expression in textbox could just reference the column by index. Index begins with 0. If the email is in third column the index would be 2.

=[comboboxname].[column](2)

[orange]

Did you try it? Did you get an error or unexpected result?
....removed code here.....

Ooops: Just saw June's post--she's right no recordsource!!!!!

[Griffin410]

Hi June, never used columns like this before but played around and was able to figure out the basics, thank you so much! Have a quick question, my setup is like this:

1 combobox [Traveler] and is just a simple RowSource of [QryTravelers] and thats it just one drop down with one column with all the names from that qry that are in the first column of the qry (I could only get it to take the first column so I just moved what I needed to the 1st column so it would work). Then, below is an unbound text box [Email] with the control source of

=[Traveler].[column](1)

which will grab the second column of that qryTraveler perfect. I however need the 3rd column so I changed to

=[Traveler].[column](2)

but just returns blank in that 2nd text box now. I can simply rearange the qryTraveler so the value is in the second column, but what am I missing if in the future I want to reference columns further down?

[June7]

Reference to table/query should include all the fields in the same order as in the table/query but you can more directly control which fields and their order with an SQL statement as the RowSource.

Whichever used, be sure to set other properties:
ControlSource (although not relevent in this case)
BoundColumn
ColumnCount
ColumnWidths

Review: http://www.datapigtechnologies.com/f...combobox3.html

[Griffin410]

You're awesome, thank you!