Referring to text control in a form
in [Forms]
|
From: Piri on 22 May 2010 06:49 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
From: Al Campagna on 22 May 2010 09:23 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." "Piri" <wiremu.pareiha(a)hotmail.com> wrote in message news:62cef924-d439-4c19-9169-028061816a9f(a)a39g2000prb.googlegroups.com... > 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
From: Piri on 23 May 2010 07:10 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 On May 23, 1:23 am, "Al Campagna" <newsgro...(a)comcast.net> wrote: > 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." > > "Piri" <wiremu.pare...(a)hotmail.com> wrote in message > > news:62cef924-d439-4c19-9169-028061816a9f(a)a39g2000prb.googlegroups.com... > > > 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
From: PieterLinden via AccessMonster.com on 23 May 2010 16:51 I'm confused. Why is a bound form with the data entry property set to True not a viable option? Then you could just use the Not In List event of the combobox to add your new items and you could finish the thing in a matter of minutes. This just seems like a LOT of unnecessary work. Essentially you are trying to emulate control arrays, which are available in VB6 but not in Access. Maybe I'm just all about easy... Pieter -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1
|
Pages: 1 Prev: Bound Form Search Next: How do I call a procedure in Main form from sub-form? |