Give Text Box Value From Query

[crhodus]

I have a dropdown list on my form named cmbProjNumber that displays Project Numbers. For the RowSource, I have the following SQL Statement:

SELECT [qryPopulate].[projnumber], [qryPopulate].[address], [qryPopulate].[city], [qryPopulate].[state] FROM qryPopulate;

I am displaying the values from [qryPopulate].[projnumber] in the dropdown list.

When the user selects a Project Number, the following code is executed so that the address is displayed in a text box on the form: Me!txtAdd = [Forms]![Main]![cmbProjNumber].Column(1)

I'm not saving the address, only displaying the address to the user. I am, however, saving the Project Number that the user selects from the dropdownlist.

My problem is that when the user first loads the form or browses through the records, the Project Number changes, but the Address field either displays nothing or displays the same address each time.

How can I have the address, that goes along with each Project Number, displayed when browsing through my records? (Note: My Project Number is a unique value - there are no duplicates)

I tried adding the following code to the On Current event for the form, but it does not work:
Me!txtAdd.ControlSource = "SELECT [qryPopulate].[address]FROM qryPopulate Where qryPopulate.projnumber = Me!cmbProjNumber;"

Thanks!

 

[pbaldy]

You don't need code to do this. If you keep it, I'd put it in the after update event of the combo box. Alternatively, this should work as the control source of the textbox, and it should change whenever the combo changes:

= [Forms]![Main]![cmbProjNumber].Column(1)

 

[pbaldy]

I didn't pick up on it being a continuous form, but I think my way will still work (if the setup is like I assume it to be).

 

[crhodus]

Thanks for your help!

There must be some code in the program that is preventing the code from working.

I created a simple form and used the followign code:

Private Sub cmbProjNumber_Change()
Me!txtAdd = [Forms]![Main]![cmbProjNumber].Column(1)
Me!txtCity = [Forms]![Main]![cmbProjNumber].Column(2)
Me!txtState = [Forms]![Main]![cmbProjNumber].Column(3)
End Sub

Private Sub Form_Current()
Me!txtAdd = [Forms]![Main]![cmbProjNumber].Column(1)
Me!txtCity = [Forms]![Main]![cmbProjNumber].Column(2)
Me!txtState = [Forms]![Main]![cmbProjNumber].Column(3)
End Sub


This seems to be working correctly. Guess I'll have to track down the other code in the original form that is preventing this from working properly. Thanks again!