populate box when a code is selected. 2

[wvdba]

Hi.
i have a form. that has a pull-down list of codes. these codes and descriptions are in a table. when i select a code, i like to populate a box that has the corresponding description for that code. how can i do this please?
thanks much.

 

[PHV]

i have a form
Which kind of form ?
UserForm ? AccessForm ? ... ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvdba]

sorry about that.
it's an access form.

 

[PHV]

So, what is the RowSource property of the combo ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvdba]

the pulldown box select a code from a table. that table has these columns:
code description
aa advanced aeronautics
ac academic careers
i select the code aa
i like the box to have the description that corresponds to the code on the same row in this case - advanced aeronautics.
thanks.

 

[dhookom]

Change the Row Source to:

SELECT Code, Code & ": " & Description
FROM [table with no name give]
ORDER BY Code

Set the column count to 2
bound column to 1
Column widths to 0, 1.5


Duane
Hook'D on Access
MS Access MVP

 

[wvdba]

dhookom,
thanks for the reply.
when i click property of the box, i don't see a row source. what could cause that no "row source" showing up.
thanks.

 

[dhookom]

I have never seen a combo box or list box that didn't have a Row Source property. Are you sure you have selected only the combo box?

Duane
Hook'D on Access
MS Access MVP

 

[wvdba]

it's not a combo box or list box that i'm trying to get a value in. it's a text box that needs to be populated.
the code is selected from a list box - no problem.
when it's selected, the text box should have the description that corresponds to the code. example:
code description
aa advanced aeronautics
ac applied civil engineering

listbox text box
aa should have advanced aeronautics populated.

 

[dhookom]

Set the control source of the text box to:
=NameOfYourListBox.Column(1)


Duane
Hook'D on Access
MS Access MVP

 

[wvdba]

text box control source has:
=[combo21].[Column](1)
when i did that, i got this error in the textbox:
#Name?

 

[PHV]

Again, what is the RowSource property of your combo (combo21 ?)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[wvdba]

this is what's in the row source for drop-down box (combo21)
SELECT [Table1].[ID], [Table1].

 FROM Table1; 
which pulls the codes (aa, ac,....) from table1.

 

[PHV]

A starting point.
Set the RowSource to the following:
SELECT ID, code, description FROM Table1

Be sure the ColumnCount equal 3

In the AfterUpdate event procedure of combo21:
Me![name of textbox] = Me!combo21.Column(2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

PH's solution will take the value from the combo box's 3rd column and store it in a bound text box.

I typically never store the Description field in the table bound to the form since it is not standard practice. Based on the Row Source suggested by PH, I would set the text box control source to:
[tt][blue] =[combo21].[Column](2)[/blue][/tt]
This would display the description but not save it to the record source of the form.


Duane
Hook'D on Access
MS Access MVP

 

[wvdba]

still not working.
still getting the same error.
i didn't know this was going to be so complicated.
for combo21 i have this for row source:
SELECT ID, code, desc FROM Table1;
bound column = 3
for event procedure of combo21 i have:
Me![text28] = Me!combo21.Column(2)
text28 has no row source.
what should be in control source or data for text28?

 

[wvdba]

after update for combo21 has this in vb code:

Private Sub Combo21_AfterUpdate()
Me![Text28] = Me!Combo21.Column(2)
End Sub

text28
has nothing for control source
yessssssssss
it's working ok now.
thanks phv

 

[dhookom]

I would remove the code entirely and set the control source of text28 to:
=combo21.Column(2)
Truthfully, I would change the name of combo21 to something that satisfies my naming conventions and standards. Perhaps "cboSomethingCode".

A couple other hints:
1) Desc as a name for anything is a bad word

2) field names like ID and Code are very generic and provide no information regarding the values they are storing. If ID is a primary key autonumber, I typically use a name that identifies the table like "EmployeeID" or "StudyAreaID".

3) #Name? might be caused by naming a control with the name of a field.


Duane
Hook'D on Access
MS Access MVP

 

[wvdba]

dhookom,
thank you so much.
i tried your suggestion and it worked perfectly.