Referring to text control in a form

  • Thread starter Thread starter Piri
  • Start date Start date
P

Piri

Access 2007
I have a series of unbound combo list boxes on a data input form:
Material01
Material02
Material03 etc - up to 10
One of the items listed is "Other"
When the operator selects "Other" I would like them to enter a
description in an existing unbound text box alongside the Combo list.
These text boxes are named MaterialDesc01, 02, 03 etc to 10
I have created a small pop-up form for the user to enter that
description so I can apply rules to the input, which is stored as a
variable for inserting into the appropriate text field.

Rather that writing a procedure for each combo AfterUpdate event I was
looking to be able to recycle the same bit of module code that
identified the active combo, extracted the number from the end of its
name (01,02,03 etc) and used that to identify the text field into
which to insert the description string.
So far I have
Set ctlCurrentControl = Screen.ActiveControl
strControlName = ctlCurrentControl.Name ' this gives me the active
control okay
ControlIDNo = Right(strControlName, 2) 'this gives me the active
combo number - ControlIDNo is a string variable

What I would like to be able to do is reference the text box
MaterialDesc of the same number in the form so I can insert the string
from the user input form.

I have tried using a Select Case statement to match combo to text box
but still cannot get it to work.

If I can do this I can use this method in other areas of my project.
Any help gratefully appreciated.

Piri
 
Piri,
First, it would appear that your table design may have some problems.
Sounds like Materials should be in a separate table, and related to
something, One to Many. For example a ONE Assembly and
MANY Materials, or a ONE Job and MANY Materials, etc... etc...
What happens if the Job needed more than 10 Materials? Even if you
think you never will, you should always design it so that will never be an
issue.
As you can see... you're already running into "repetitive - mutiple
coding" issues with this design.

This solution should work for your current design... but I would
recommend that you seriously consider the above suggestion...

Make your combos 2 columns, with Material in the first column, and the
Description in the second column. Set your combo for No Of Cols = 2, and
set widths for 2 columns.
For the first combo, add an unbound text control, named Description01,
with a Control Source of...
= Material01.Column(1)
(combo columns are numbered left to right 1, 2, 3, 4, etc...)
Whenever a material is selected in Material01, Description01 will
"display" the associated Description.
Do the same for all ten combos.
Note: It's not necessary to "capture" description, since it can be
re-related to it's associated Material in any subsequent form, query, or
report.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Thanks Al,
Sorry, maybe I did not explain myself correctly
I am using unbound forms and controls.
From a main form the operator is asked to select a material from a
combo list, with the option to choose "Other" if what they want is not
already in that list.
If they choose "Other" I would like them to describe what they want in
an adjacent field on the form. I can manage this by using say an
AfterUpdate event on each of the lists.
Give there are 10 such combo lists to choose from, when they choose
"other" I need the description.
Combo List Material01 has an associated text box MaterialDesc01.
Combo List Material02 has an associated text box MaterialDesc02 and so
on to be used when "Other is selected.
What I am trying to achieve is, for example
If Material01 = "Other" then MaterialDescr01 is "whatever the operator
enters in a pop-up form for the purpose"
I am using the small pop-up form, rather than allowing direct entry
into the MaterialDescr01 text box because I need to run some
compliance rules against the operator entry.
So after the description data string has been accepted I wondered
rather than running a separate call for each combo list could I run a
public function that queried the Combo Box to determine its number
(01,02,03 etc) and then put the resultant description into the
associated description text box.
If Material01 then reference MaterialDescr01
If Material04 then reference MaterialDescr04 etc etc
and so on, by recycling the same code?

At the end of the data entry in the main form I will squirt the data
into the record table.
I am struggling to correctly reference the text control on the form.
I can identify the active Combo list box - say Material01 - and from
that derive it as "01" - how do I then reference the associated
description text box in the form - MaterialDesc - that ends with 01
(or whatever ending number of the Combo list control)?

Hope that explains it.

Cheers,
Piri
 
Back
Top