Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Unanswered: Dynamic Function to simplify form

    Hi peeps,

    Bit of an issue here...

    I have a form that has room for 10 numbers to be input.

    Each of these numbers is unique, and each box they're entered into produces paired information in labels in the same row...

    It looks something like:

    Code:
    Input 1:  [       ]  someInfo  someOtherInfo
    Input 2:  [       ]  someInfo  someOtherInfo
    Input 3:  [       ]  someInfo  someOtherInfo
    Input 4:  [       ]  someInfo  someOtherInfo
    Input 5:  [       ]  someInfo  someOtherInfo
    Where the input is put between the [ ]'s, and then someInfo and someOtherInfo are updated with the paired information.


    Now, the issue is, there are 10 of these inputs on the form, and the code in the _Change() event for each is a couple of hundred lines long, for the checks and all the necessary lookups, etc.

    Because of this, I don't really want to have a hugely bloated form full of effectively repeating code, only with a different number in the control name to represent a different row.

    So, I set about designing a function that can simply be written once, dynamically, and have it called in each _Change() event, and that's it.

    The major issue I'm facing is that when attempting to do this dynamically from a function, I can't build a control name up like I can on the form itself. What I mean by this is I would usually use:

    Code:
    Me("input" & rowNum).Value = "BLAH!!"
    Where rowNum is the number in "Input x:". However, this can't be used with:

    Code:
    Dim f As Form
    
    Set f = Forms!aForm
    
    f!("input" & rowNum).Value = "BLAH!!"

    My question is really, is there a simple way to achieve this? Or can a control, on a form, be called dynamically from a function?

    Should I just bite the bullet and copy and paste, modifying as I go for all 10 inputs' _Change() events? I'm loathed to do that as it requires a lot of tiny changes, and will create incredibly sluggish save times when editing the rest of the forms code, as it'll have to recompile everytime I notice a full-stop missing, or whatever.

    Any other ideas/suggestions welcome!


    Thanks all!
    Looking for the perfect beer...

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I would say a function is the way to go. What problem are you having with what you tried? I just did a brief test and this worked fine:

    Forms!form1("text" & 12).Value = 777
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Oh, I just noticed the "!" there. Get rid of that.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •