Autofil a Text Box

[Brampton76]

I have a Form with 2 Combo Boxes and one Text Box. The Combo Boxes take data
from a Table with 3 columns. When Combo Box 1 selection is made and Combo
Box 2 selection is made I would like the Text Box to autofill with the data
from the third column. All 3 selections are then saved so that I can do some
calculations etc.

I have been tinkering with the AfterUpdate in the second Combo Box but going
round in circles and would be grateful for some advice.

 

[Damon Heron]

YourTable:
Field1 Field2 Field3
AAA BBB CCC
XXX YYY ZZZ

If you select AAA in ComboBox1, why would you need ComboBox2 to select BBB?
Or, are you selecting YYY with ComboBox2? In which case, Column3 would be
both
CCC and ZZZ. Can you give more info as to what you are trying to do?

Damon

 

[Brampton76]

Hi Damon,

Field1 Field2 Field3
aaa 123 blue
aaa 345 red
aaa 678 green
aaa 910 black
aaa 112 yellow
bbb 123 blue
etc

Field1 will have the same data in groups of 5, Field2 will have a different
option for each of the contents of Field1 (5 diferent options) and Field3 is
again a different content (a cash amount). My aim would be to select 'aaa'
'910' and get the Text Box to auto fill with 'black'.

 

[Damon Heron]

You only need one combobox, lets call it combobox1. Set the control source
of combobox1 to your table. In the combobox properties, set the column
count to 3, column width 1";1";0" and make your combobox wide enough to see
both field 1 & 2. The columns in the combobox are zero based, so
me.combobox1.column(0) would equal field1, column(1) = field2, etc. In the
after update event of the combobox, enter:

me.yourtextboxname= me.combobox1.column(2)

Although you really don't need the textbox to show column 2 if all you want
to do is calculations. To multiply field2 (345) * field3, you would simply
have:
me.yourtextboxname= me.combobox1.column(1) * me.combobox1.column(2)


Damon

 

[Brampton76]

Damon,

Many thanks, this is new to me so I will try this tomorrow. I seem to have
an awful lot to learn! Again, many thanks for your time.

 

[Brampton76]

I am nearly there now I think! I can see the 2 fields in the combo box and
then make a selection - however, only the first field remains after the
selection. Then, I entered the following into the after update event of the
combo box me.txtCost=me.cboOption.column(3) but received an error message
indicating that access cannot find the macro 'me'. I appear to be so close
yet so far! Any further thoughts please?

 

[Damon Heron]

Unless you have a primary key in your table, then your line should be
......column(2).
Remember, it is zero-based. If that doesn't cure the problem, then try the
full name (me is the name of the current form)
like this: myForm.txtCost.value = myForm.cboOption.column(2)

Damon

 

[Damon Heron]

Oops! the fully formed line should be:
Forms!yourformname.txtCost.Value= forms!yourformname.cboOption.column(2)

Damon

 

[Brampton76]

Many thanks Damon. Still no joy though. My
Forms!frmCompetitionEntry.txtcost.Value=Forms!frmCompetitionEntry.cboOption.Column(3)
(there is a primary key) continues to give me the macro not found message so
I put a '=' at the beginning - but still no auto fill. I think I have taken
up enough of your time now, and will try and find another way around the
question until I have learnt much more about Access. Again, many thanks for
your time.

 

[Damon Heron]

Okay. My only other recommendation would be to put a breakpoint in your
code and follow step by step (F8 key) thru the code. Also google MS Access
with the error msg and see if others have had the same problem. It is a
possibility that there is something on your form that is not loading fast
enough, or you have a subform???
Anyway, good luck to you.

Damon

 

[Herman Wellington]

Hi Damon,

I read thru your conversation and found it is same for my case. But I have
subform in it. Will it cause any problem and how should I go around it.

Thanks.
Herman