Linking Combo Box to Text Box

[Guest]

Hi

I am a very very new rookie and I am tryng to link 3 text boxes to the
results in a combo box. I can only link one text box so far. My question is
how to link the other two.

The combo box is called Carr Code and references a query called Carr Lookup.
Carr Lookup contains 4 columns, Carr code, Carr Name , Manager Name and Duty
ref, when the Carr Code is selected in the combo box I want the Carr Name,
Manager Name and Duty ref text boxes to be automatically populated.

The first text box uses the control source as - =[Carr Code].[column](1) and
displays the Carr Name, but when I do what I thought was the logical thing
and add this to the second text box - =[Carr Code].[column](2) nothing is
displayed.

I've had a look at the other posts but I really don't understand the answers.

Many thanks

 

[missinglinq via AccessMonster.com]

The simplest way is to let Access do the work for you. First you must have
your form set up to display the record you want to retrieve, i.e. you must
have text boxes set up with the appropriate Control Sources. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
Click on the field(s) you're searching by to move it to the right side.
Hit Next.
Size the column appropriately.
Hit Finish

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type.
Hit <Enter> and the record will be retrieved.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Column numbers start with 0, so in your case,
Carr code = Column(0)
Carr Name = Column(1)
Manager Name = Column(2)
Duty = Column(3)

One technique would be to use the After Update event of the combo box and
programmatically populate the text boxes:

With Me
.txtCarrCode = Me.MyCombo.Column(0)
.txtCarrName = Me.MyCombo.Column(1)
.txtMgrName = Me.MyCombo.Column(2)
.txtDuty = Me.MyCombo.Column(3)
End With

 

[Guest]

Hi

thanks, that worked really well but I have a follow on question. In my
combo box I can now see all the columns that appear in the query linked to
the combo box ie. Carr Code, Carr Name, Manager Name and Duty Ref. Is there
any way of just showing the Carr Code and then the rest of the data flowing
through to the text boxes?

Many thanks

 

[BruceM]

Set the column width to 0" for columns that are not to be displayed (combo
box Property Sheet, Format tab).

 

[Guest]

Hi BruceM

That worked perfectly thanks so much for your help!

 

[missinglinq via AccessMonster.com]

Actually next time, lscde00, if the field Carr Code is unique, you can simply
put that one field in the combobox; all the fields don't have to be in the
combobox for them to be filled in on the form!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[Guest]

Hi again

I've hit a problem with this. When I created the original form I had
everything working perfectly by linking the row source of the text box to the
Combo box. However, I now want to add a few more fields which would bring
the total of the linked text boxes to 22. This doesn't seem to work - the
text boxes from 19 to 22 are blank.

I came back to what you wrote about the Carr Code being unique - which it
is. I have a query that brings back all the columns I need for the form. I
have a combo box to select the carr code and then I want the rest of the text
boxes filled with the relevant data to the carr code selected.

I tried to just put the source of the text box as the column in the query
but I just get the #Name? error. I can see that it is because the text bx
doesn't know the results of the Combo box but I don't know how to change this.

Basically my question is in two parts - is there a limit on the number of
text boxes that can be linked to a combo box (I'm thinking yes as the set up
wizard seemed to put a limit on it) and if there is how can I go about
linking these extra text boxes I want.

Please remember I'm really new to this and don't understand all the VB
language!

Many thanks