Referring to text control in a form

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
0
Piri
5/22/2010 10:49:25 AM
access.formscoding 7494 articles. 0 followers. Follow

3 Replies
672 Views

Similar Articles

[PageSpeed] 16

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@hotmail.com> wrote in message
news:62cef924-d439-4c19-9169-028061816a9f@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



0
Al
5/22/2010 1:23:34 PM
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 =3D "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=A0am, "Al Campagna" <newsgro...@comcast.net> wrote:
> Piri,
> =A0 =A0 First, it would appear that your table design may have some probl=
ems.
> =A0 =A0 Sounds like Materials should be in a separate table, and related =
to
> something, One to Many. =A0For example a ONE Assembly and
> MANY Materials, or a ONE Job and MANY Materials, etc... =A0etc...
> =A0 =A0 What happens if the Job needed more than 10 Materials? =A0Even if=
 you
> think you never will, you should always design it so that will never be a=
n
> issue.
> =A0 =A0 As you can see... you're already running into "repetitive - mutip=
le
> coding" issues with this design.
>
> =A0 =A0 This solution should work for your current design... but I would
> recommend that you seriously consider the above suggestion...
>
> =A0 =A0 Make your combos 2 columns, with Material in the first column, an=
d the
> Description in the second column. =A0Set your combo for No Of Cols =3D 2,=
 and
> set widths for 2 columns.
> =A0 =A0 For the first combo, add an unbound text control, named Descripti=
on01,
> with a Control Source of...
> =A0 =A0 =A0 =A0 =3D Material01.Column(1)
> =A0 =A0 (combo columns are numbered left to right 1, 2, 3, 4, etc...)
> =A0 =A0 Whenever a material is selected in Material01, Description01 will
> "display" the associated Description.
> =A0 =A0 Do the same for all ten combos.
> =A0 =A0 Note: It's not necessary to "capture" description, since it can b=
e
> re-related to it's associated Material in any subsequent form, query, or
> report.
> --
> =A0 =A0 hth
> =A0 =A0 Al Campagna
> =A0 =A0 Microsoft Access MVP 2007-2009
> =A0 =A0http://home.comcast.net/~cccsolutions/index.html
>
> =A0 =A0 "Find a job that you love... and you'll never work a day in your =
life."
>
> "Piri" <wiremu.pare...@hotmail.com> wrote in message
>
> news:62cef924-d439-4c19-9169-028061816a9f@a39g2000prb.googlegroups.com...
>
> > Access 2007
> > I have a series of unbound combo list boxes on a data input form:
> > Material01
> > Material02
> > Material03 =A0etc - 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 =3D Screen.ActiveControl
> > strControlName =3D ctlCurrentControl.Name =A0 =A0' this gives me the ac=
tive
> > control okay
> > ControlIDNo =3D Right(strControlName, 2) =A0 =A0'this gives me the acti=
ve
> > combo number - =A0ControlIDNo 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




0
Piri
5/23/2010 11:10:04 AM
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

0
PieterLinden
5/23/2010 8:51:07 PM
Reply:

Similar Artilces:

Cell Value as Named Range Reference
Little bit of a quirky question... Trying to use a cell value as a reference in a formula, where that cell value is the name of a named range. So, assume A1:A3 is a named range: Rng1. And, B1:B3 is Rng2. I want to get the correlation vale for A1:A3 and B1:B3 So, cell D1 contains "Rng1" and D2 has "Rng2"; and cell D3 has the formula: =correl(D1,D2). But I get an error. Have also tried using Indirect to no avail. Any help would be hugely appreciated. Thank you. =CORREL(INDIRECT(D1),INDIRECT(D2)) -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "ste...

symbols won't work in form fields
I have Access 2002. We write a lot of letters in French and as I have a QWERTY keyboard, we have shortcuts for the french letters which are missing from the keyboard. For example Ctrl + e is e with an accute accent, Alt + e is e with a grave accent, and so on. It all works just fine in WORD. But in my forms in my ACCESS database, in my fill-in fields, the shortcuts simply don't work. And there is no "symbol" option available in the tool bar to insert a symbol or French character. Is there any way I can add this function in to the database ? thanks .. Roger ...

Need to reference a block of cells from another sheet.
Dear All, apologies if this is probably a mundane question, but I didn't know what 'search terms' to put in google to return any results.. I've got a Spreadsheet with 12 tabs (sheets), I need part of tab 12 (sheet) to appear on tabs 1-11 (sheets), so that if I change data in tab 12 (sheet), tabs 1-11 (sheets) are changed too.. the cells themselves will never move location, just the data within them.. Is it possible? Regards Paul. On your sheet1 select the cell that should receive the data from Sheet12; Hold down the Shift key and Click on Sheet11 << this shoul...

set a single click event for a large group of buttons on a form?
Lets say I have 50 panels on a form and a button on each panel. Rather than write 50 click events (one for each button) I would like to write one event which would be triggered by any of the 50 buttons. Each panel displays different graphical information. When I click a button on a respective panel I want to retrieve the underlying graph data for that panel. I am thinking I could create a button array and set the same click event to each button in the array and then determine which button was clicked by checking the sender object (event arg e ...). Is there a more correct or eff...

refering to a name range area as a reference value multiple times on a worksheet at different locations
i was wondering if i had posted this in the right section as it could apply to both please could you take a look at the following post http://groups.google.com/group/microsoft.public.excel.misc/browse_thread/thread/f088a5737dd26570 thank you Is there some reason you can't post your question here instead of asking us to look elsewhere? -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Blinds Nottingham" <bradrail.blinds.awnings@googlemail.com> wrote in message news:d8768e43-e035-45eb-856a-0ccdc843441d@g1g2000yqi.googlegroup...

Need function to return numbers at beginning of text
I have a column of cells that contain an id and description in one column. I need a formula that will give me only the id. The id can be either 4 or 5 numbers. Some can be followed by a letter and some can be followed by a dash and then a number. What they all have in common is that the id is followed by a space and then the description. So, the =LEFT formula will not work. I need on that can give me all characters before the space regardless of whether that's after the first 5 characters or after 7 or 8 or however many there are before the space. Anyone know of such...

What is IVA# in Distribution Reference and Reference?
We did an inventory adjustment and now we get a unknown amount in Debit under the Inventory account and Credit for COGS account in the Transaction Entry Zoom. We're trying to figure out where that amount came from but we couldn't zoom more than what it's currently shown. In both fields, Reference and Distribution Reference, it's shown IVA#######... that is unknown to or setup by us in anywhere, i.e. Audit Trail, Source Document. Does this amount affect the dollar in the accounts mentioned? And can anyone please help to where we could drill down the IVA details?...

macro button refers to prev. workbook?
Hi, When I create a macro in Excel, and assign a button to it, it works fine the first time, but when I re-open the workbook for a second time, and try to run the macro again, the macro seems to refer to the previous workbook...For some reason, the macro saves the name of the previous workbook... Sub test() ' Dim name As String Dim adress As String Sheets("sheet1").Select Range("A1").Select name = ActiveSheet.Range("B6").Value adress = ActiveSheet.Range("B4").Value ActiveWorkbook.SaveAs Filename:="C:\WINDOWS\Desktop\Temp...

Column and Row reference
How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top and right hand side references Dave Tools>Options>View. Uncheck "row and column headers". Good idea to spend a few minutes browsing through the various Tools>Options tabs to see what else is available to toggle on/off. Gord Dibben Excel MVP On Mon, 19 Jul 2004 15:53:02 -0700, "Dave" <Dave@discussions.microsoft.com> wrote: >How do i hide the column and row reference number in a sheet, when viewing the worksheet i do not to show the top an...

what text files are supported in publisher 2007
"just ducky" wrote: > what text files are supported in publisher 2007 ============================== Please....always include your question in the body of your message. Have a look at the following article: Microsoft Publisher 2007 Import text from another file into a publication http://office.microsoft.com/en-us/publisher/HP100643891033.aspx -- John Inzer MS-MVP Digital Media Experience Notice This is not tech support I am a volunteer Solutions that work for me may not work for you Proceed at your own risk "John Inzer" wrote: >...

greyed out options in forms toolbar
hi people, i have a grayed out option in my view-toolbars-forms called 'cobination drop down edit' any ideas how i can get to use? or waht it is? cheers. You can only use that tool on a dialog sheet, which was used in earlier versions of Excel, to create custom dialog boxes. To see one, right-click on a sheet tab, and choose Insert. Choose Excel 5.0 Dialog, click OK. These have been replaced by UserForms, that you can create in the Visual Basic Editor. (Alt + F11) shaun wrote: > i have a grayed out option in my view-toolbars-forms > called 'cobination drop down edi...

Missing References
I had a problem today with a missing reference - Microsoft Forms 2 on a colleague's machine at work (Office 2003; VBA 6.5; XP). I've read Chip Pearson's "Missing References in VBA" which is a geat explanation about how to fix problems like this. Oddly the library didn't seem to be flagged as "missing" and was in fact present in the system32 directory but didn't show up in the list of available references. Now to the questions: I'm not clear on whether if I write code in a project that references a library which is not part of the &qu...

My Publisher is "out of control writing 'gibberish' code .........
How do i fix this problem. When I open Publisher 2003, it loads and then begins to write what appears to be code. It is out of control, writong this 'gibberish',and then at the bottom, where the pages are shown, it loads page after page of this dang crap?!?!? Does anyone know what is happening to me??????? M. Slowruner R. Has this always happened with Publisher? Have you tried to uninstall and reinstall it? -- JoAnn Paules MVP Microsoft [Publisher] "slowrunner" <anonymous@discussions.microsoft.com> wrote in message news:010801c49383$d0ded5c0$a401280a@phx.gb...

Creating text files to import
I'm a newbie here, and a programmer who needs to convert a few existing reports from our application into a format that can be converted to Excel. I tried simply outputting a report to a .txt file, but the import was so completely random. These reports have headings and subheadings, then a body with different types of text (numbers, dates, etc.). The reports create spaces between each word. Example: 3/29/05 Inventory Sold Report Page 1 Item# Quantity Date Sold 9874779948 6 03/19/05 7367632737 15 03/07/0...

Reference
Is it possible to create a reference to another object such as: int & x = i; but using an image list, and referencing different objects depending on a condition? such as : CImageList & imageList; if (thumbnail == 1) imageList = m_ImageListThumb; else imageList = m_ImageListFullSize; Hope this makes sense! Ben You're better off using pointers for this. I believe, depending on the class, that what you're trying to will make a copy of the object instead of referencing it. "Ben Williamson" <oakdaleclose@googlemail.com> wrote in message news:43ee4603...

Tab Control 02-27-10
On a form. is there a way to get a tab control with a vertical scrollbar? If not, I'll have to use a sub-form (but I'd rather not). Thanks. bob In a word, "No." Sorry. One of the purposes of Tabbed Controls is to avoid having to scroll down a form. You could use a subform on the Tabbed page. -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/201002/1 Thanks. I want to have a small area of the form scroll-able...

How to convert text-cells
Hi, I've problems to import Excel-spreadsheet-data into our database. I'm using Borland-Delphi-ADO, but a test with MS-Access importing feature leeds me into the same problems. The spreadsheet contains a col with only 9-digit values, but they have got the text-attribut except of two that are numbers. I see this when I delete the aligning-property, all cells are left justified, but the two right. I know I can give a col the text-attribute while importing data from a textfile. But I dont know how to delete the text-attribute or set it for a complete col. Best regards Dietmar "...

Unwanted blank line in cell with wrapped text
Unwanted blank line sometimes appears in cell with wrapped text. Editing, Autofit height and width, and justify do not eliminate it. Forcing column width to be excessively wide makes it go away, as does reducing the font size (neither of which I want to do). What's going on? This only happens in some wrapped text cells. bbl If you uncheck "wrap text" do you see a little square box in the text where the blank line is? If so, someone entered a new line using ALT + ENTER. In that case, you can get rid of these boxes by Edit>Replace what: hit ALT + 0010(you won't se...

Sheet Reference
Is there a formula for listing the current Sheet. I know about the filename cell("filename",A1) which returns the entire path, but I just want the Sheet name to appear. John, You still use the CELL("filename") function, but you need to trim the result a bit. Like this: =MID(CELL("filename"),FIND("]",CELL("filename"))+1,99) -- Ture Magnusson Microsoft MVP - Excel Karlstad, Sweden "John" <anonymous@discussions.microsoft.com> wrote in message news:0c6001c3a856$291c9a20$a501280a@phx.gbl... > Is there a formula for listing...

Seaching Ex cel for text styles
Using VSTO and .NET 3.5 how does one search an Excel Workbook text for a specific style applied to it. In Word one would do the following: Me.application.Selection.Find.Style = Me.document.Styles(searchStyleName) Me.application.Selection.Find.Replacement.ClearFormatting() Me.application.Selection.Find.Replacement.Style = Me.document.Styles(searchStyleName) With Me.application.Selection.Find ...

I need to add the Change Text Direction Icon to my toolbar.
I found how to do it in the Help section and went to "Tools" then "Options" but I do not have a "Regional tab" in the options box. I have tried some other thing too but no success. Any Ideas? This is in Visio 2007. jcreek -- Thanks for your Help Hi JCreek, I am also getting weird behavior. I can add the "Change Text Direction" toolbar button to a toolbar, but when I exit the Customize... mode, the button disappears. When I re-enter the Customize... mode, the button is there. Also, I can change the direction of the text, via a trick: go to th...

Want a macro to open the Properties Dialog Box and enter text
I want to be able to open the Properties Dialog and then enter information into it, without having to retype or copy and paste each time. Is this doable with VBA macros? -Jessica Which version? "Jessica Dubey" <jdubey13@gmail.com> wrote in message news:1300374a-0fb1-4ca4-8044-3f996af4adef@a38g2000yqc.googlegroups.com... > I want to be able to open the Properties Dialog and then enter > information into it, without having to retype or copy and paste each > time. Is this doable with VBA macros? > > -Jessica > > __________ Information from E...

Macro to Reference Column Next to Current Reference
Hi, I'd like to automate the following procedure using a macro. Please help. 1) i have 2 sheets: Sheet1 & Sheet2 2) In Sheet1, Cells B2,D2,F2 has the formulas "=Sheet2! F2", "=Sheet2!K2", "=Sheet2!P2" correspondingly. 3) Every month when i do my work, i'd have to manually rekey the formulas to reference the subsequent columns, ie, Cell B2,D2,F2 will change from above to "=Sheet2! G2", "=Sheet2!L2", "=Sheet2!Q2" and in the following month, I have to change manually again to "=Sheet2! H2", "=Sheet2!M2&qu...

Special Forms for activities
Hello! I am on the way to deploy MS CRM in our company! Now i have the first big problem. We should have some special form to integrate in the crm contact history. for example: if a sales manager visits a customer, he hast to fill a special form with some additionial informations about the customer. my question: how can i solve the problem to create own forms with my own fields? can someone help me? thank and much christmas greetings from berlin Hi, One way that you can do this is to add another tab in the account and and add fields to the Schema (in Deployment Manager) and then cus...

R1C1 reference #2
Excel keeps changing to the R1C1 reference style, but only in one of my workbooks (I believe in just one of the worksheets). I keep changing it back (Tools - Options - General - R1C1 Reference Style), but when I move or copy cells in the workbook, it changes back to R1C1. Anyone have an idea? Look at the TOOLS / OPTIONS / SETTINGS is the 'R1C1 References Style' box checked? If not, check it then save the workbook. Not sure if this is your issue but it's worth a try. Good Luck, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "Tony S" wrote: > Ex...