Autofill more than one field from a combobox

  • Thread starter Thread starter Andy Roche
  • Start date Start date
A

Andy Roche

Please can someone help. I'm no expert (somewhere between
basic and intermediate level) on Access so please don't
use too much techno speak.

I'm trying to get a combobox to "autofill" more than one
field automatically with separate data on my form from the
four columns within my dropdown (one into each of other
fields). I've got the relationship between my two tables
right in principle as I can "autofill" one field, but
unfortunalety I'm failing to fill the rest.

Eg Job Cost No QB025 - in the combo (which is the bound
data), col 2 of the combo to fill the related WAD ref
B2.10.2 , col 3 of the combo to fill the related project
code MNT022 etc.

If I can achieve this it would reduce key-stokes
significantly.

How many feilds could I link to a single combobox.

Hope someone can help.

Thanks in advance.
 
Hi Andy,

There are a few ways to do this. The way that I do it is
to put some code in the AfterUpdate event of the combo box
to set the text boxes to the desired data when a row is
chosen in the combo box.

If you have 4 columns in the combo box then they're
numbered within Access as 0,1,2 and 3. The first column
being column 0. Since the combo box is already storing
your job cost number then you don't have to worry about
that one.

Let's assume that the three other text field names are
Text1, Text2 and Text3 for simplification.

Open your form in design view. Right-click on your combo
box and select Properties. Add the following code to the
AfterUpdate event:

Me.Text1 = Me.ComboBoxName.Column(1)
Me.Text2 = Me.ComboBoxName.Column(2)
Me.Text3 = Me.ComboboxName.Column(3)

When a selection is made in the combo box the
corresponding text fields will be automatically filled in.

I'm not sure if there is a limit to the number of columns
that you can have in a combo box but I would think that
screen space would be a problem if you have to many.

HTH
 
I have found this method from Andrew and it works
wonderfully. I do believe it will be simpler.

Michelle


Open your form in Design View
make sure you have given your text boxes a friendly name
(e.g. txtClient,
txtDescription etc.)
Create a query using the wizard that includes all the
fields you need and
save it (you could call it qryJob, for example)
Right click the combo (drop down) box, select Properties
On the Data tab, in Row Source type the name of the query
you have just saved
On the Data tab, in Bound Column type 1 (assuming that the
column you want
to disply in the combo box is the 1st in your query)
On the Format tab, in Column Count type the number of
columns in your query
(you have listed 4 below)
On the Format tab, in Column Widths type an appropriate
width for each
column (e.g. 3cm;0cm;0cm;0cm - this example will display
the 1st column only
with a width of 3cm)

Right click on the 1st text box and select Properties
On The Data tab, in Control Scource type:-
=NameOfYourComboBox.Column(1)
Substitute NameOfYourComboBox for the name of your combo
box!
The number in brackets is the number of the field you want
to display -
remember that the 1st column is column(0)!

Repeat the above step for each text box

Hopefully, it should all work!

Andrew
 
Hi Bob

I am trying to do the same thing with my form.
I am pulling from a query.
I have Name in the combo box
SocialSecurityNumber as text box
Salary as text box
My combo box control is name.
In the after update event procedure I have put
Me.SocialSecurityNumber=Name.Column(2)
Me.Salary=Name.Column(3)
I get an error
Compile Error
Invalid Qualifer
What am I doing wrong?
Any help is appreciated
Thank You
Roger
 
Back
Top