Populate control based on result of a query

[Marc Meltzer]

I would like to have a textbox show the result of a query. The query
will only return one value, based on the following:

The table has two fields: Artist_Num and Artist_Name.

On my form, there is a combo box that shows all rows of Artist_Name.
When the user clicks on a particular Artist_Name, I want a textbox to
show the associated Artist_Num.

I've written the query as follows:

SELECT Artist_Num
FROM TBL_Artists
WHERE((Forms!FRM_Add_Album!Artist_Name=TBL_ARtists!Artist_Name));

The query seems to run fine, but I can't figure out how to get the
result into the textbox.

Any help is appreciated.
Marc

 

[Brian Bastl]

Your rowsource for the combo should be:

SELECT Artist_Num, Artist_Name
FROM TBL_Artists;

Set the bound column to 1
column count to 2
column widths 0"; 1.5"

Then in the afterupdate event procedure of the combobox, you'd set the value
of your text box.

Me.MyTextBox = Me.MyCombo.Column(0)

Brian

 

[Mr B]

The most simple way to accomplish what you want is to include the Artist_Num
as the first field in your combo box that now shows just the Artist Name.
Just add the Artist_Num field to your query (rowsource) of your combo box.
Then in the properties of the combo box on the Format tab, set the Column
count to 2. Then in the Column Widths property type: 0";2" This will make
column zero (first column) not show in your combo box but you can reference
it in code.

Then in the AfterUpdate event of your combo box, place the following code:
if Not IsNull(me.YourComboBoxName) then
Me.YourTextBoxName = Me.YourComboBoxName
End If

This will write the Artist Number to the "YourTextBoxName" text box when a
selection is made from the combo box.