Textboxes won't update based on Combobox Value 1

[Faded]

Hi.

I have a combobox that gets it's values from a query. Then upon updating the combobox, two textboxes should update their values based on what is selected.

Here is the query called qryRequestor (simplified):

SELECT alias, dept, phone
FROM list

Then in the Combobox properties, I have row source type set to Query/Table and row source set to qryRequestor

In the cboRequestor_AfterUpdate() event in VBA code, I have the following:

Me!txtDepartment = Me![cboRequestor].Column(1)
Me!txtPhone = Me![cboRequestor].??????

where txtDepartment and txtPhone are the textboxes that need to be updated. The txtDepartment on updates just fine, but I can't get the txtPhone one to update. All of the properties are identical, yet it just won't work. What do I replace the question marks with to get the phone field inserted into the textbox?

Thanks in advance.
Faded

 

[Isadore]

Faded, I'm only a tech here so taking a shot in the dark. Let's say the combobox can be "bound" to both Column 1 and Column 2. Then I couldn't help you, because as you said it should work. But I've never had a combobox bound to more than one field, primarily because I've lived without that feature...but I seem to remember something along the way in which both columns could be captured at one time...sort of like in your example above, where Column(1) and Column(2) should be recognized. Maybe it should be Column(0) and Column(1)..don't know.

If I were faced with this problem I'd probably put in the "On Current" event of the form the code which would populate your txtPhone box. Using a DLookup line in the code I'd use your Column(1) value which comes back ok, and then look up the value for txtPhone and set it to that value..

Me.txtPHone = DLookUp("...........and so on)

Just an idea. I am interested in finding an argument for grabing both Columns, or multiple columns from a combo box at one time so maybe someone will share that with us.

 

[pdldavis]

How about Me!txtPhone = Me![cboRequestor].column(0), where column(0) is acutally the first column and column(1) is the second?

 

[LonnieJohnson]

Column(1) and Column(2) are correct. Do you have the number of columns set to three in the combo box's property window?

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.

 

[Faded]

Thanks to all of you who gave advice. In the end It was that the Column Count property was set to 1. As soon as I changed it to 2 the txtPhone value was set to the phone number.

The code now reads:

Me!txtDepartment = Me![cboRequestor].Column(1)
Me!txtPhone = Me![cboRequestor].(2)

and the Column Count Property is set to 2.

Thanks to all of you who helped!

Faded

 

[Isadore]

Excellent example Faded

So, if you create a combobox or listbox with say, 3 columns, set your "column count" and then

Me.txtA = Me.cmbA.Column(1)
Me.txtB = Me.cmbA.Column(2)
Me.txtC = Me.cmbA.Column(3)

thereby capturing all three values brought in by an underlying SQL or query...

Useful technique...

 

[LonnieJohnson]

Actually the column value in the Column expression is a zero based number. Thus, the first column is Column(0), the second is Column(1), and so on.

prodevmg@yahoo.com
ProDev, MS Access Applications B-)

http://www.galaxymall.com/software/PRODEV

May God bless you in the year 2002.