Combo Box and Data Sources

[crhodus]

I have a comb box on my form named cmbUserName. I am using the following query to populate the combo box:

SELECT [qryByCity].[MyName], [qryByCity].[MyAddress], [qryByCity].[MyCity], [qryByCity].[MyState] FROM qryByCity;

**Note qryByCity is a query that is pulling data from a table named CompInfo.

Also on this form I have three text boxes: txtAdd, txtCity, txtState.

In the control source for txtAdd, I have the following:
=[Forms]![MyForm1]![cmbUserName].column(1)

In the control source for txtCity, I have the following:
=[Forms]![MyForm1]![cmbUserName].column(2)

In the control source for txtState, I have the following:
=[Forms]![MyForm1]![cmbUserName].column(3)

Whenever I select a user name from the combo box, txtAdd is populated with the address that corresponds with the user name but txtCity and txtState do not receive any data.

Why does txtAdd work correctly when a value is selected from the dropdown list bu my other two text boxes do not work?

Thanks!

 

[Fizzio]

When you run the query on its own, are those fields populated?

 

[crhodus]

Yes, when I run the SELECT statement, I receive data for all the fields.

 

[llkhoutx]

Instead of setting the control source for the text boxes, populate them on the combo box AfterUpdate event, ie

Private sub cmbUserName_AfterUpdate()
txtAdd=me![cmbUserName].column(1)
txtCity=Me![cmbUserName].column(2)
txtState=Me![cmbUserName].column(3)
end sub

Don't forget code checking for a Null combo box or resetting the combo box to null.

 

[Calvin]

I'll bet it's a refresh issue, if you insert a me.refresh or a me.requery in cmbUserName_AfterUpdate event it may solve your problem.
Also try changing
=[Forms]![MyForm1]![cmbUserName].column(1)
to
=Me.cmbUserName.Column(1)

but if you absolutly want the best solution try the following:

Private Sub cmbUserName_AfterUpdate()

    Me.txtAdd = Me.cmbUserName.Column(1)
    Me.txtCity = Me.cmbUserName.Column(2)
    Me.txtCity = Me.cmbUserName.Column(3)
    
End Sub

 

[crhodus]

I tried everyones suggestions but none seem to be working correctly. For some reason, I only receive data for txtAddress no matter what type of code I am using.

I ran the query that I am using to populate my dropdown list to make sure it is returning data for all the fields.

 

[Fizzio]

Sounds too easy but have you got the enough columns in the combo?

 

[crhodus]

I'm not exactly sure what your are asking. When I created the combo box, I used the wizard. I chose the user name to be displayed in the cobo box. After creating the combo box, I modified the query so that it would also return the address, city, and state.

Attached is a zipped copy of the mdb if this helps any.

Thanks!

 

[crhodus]

I changed the Column Count property for my combo box from 1 to 4. This seems to have to corrected the problem. Everything seems to be updating correctly now. I didn't know anyting about the Column Count.

I do have one slight thing that I need to correct now. Originally, my combo box only showed the user name. Since I changed the Column Count to 4, the User Name, Addres, City, and State are appearing in the dropdown. How can I modify the combo so that it only shows the user name again?

thanks for everyone's help!!

 

[indesisiv]

Set the column width to 0...

eg.

3,0,0,0
Would be about right.

Steve

 

[bradcccs]

Ah, Fizzio cracks it again.

Good work.

 

[crhodus]

Setting the column width corrected the other problem. Thanks to evereyone for helping me out with this !

 

[Fizzio]

cheers brad..