Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2006
    Posts
    4

    Unanswered: Simple VBA question - form/subform addressing

    This is probably a seriously n00b question, but I don't work in Access (I took a course once but immediately went headfirst into SQL Server afterwards so I remember little).

    How do I address objects in an Access form and subform, so that if the value in a form field is changed (via a drop-down selection), a new record is created in the subform and the key field is set to a value dependent on the selection made in the form field, like so:

    switch (formFieldValue)
    case "ABC" : lowest = 10000;highest = 19999;break;
    case "DEF" : lowest = 20000;highest = 29999;break;
    case "GHI" : lowest = 30000;highest = 39999;break;
    default : lowest = null;highest;break;
    endswitch;

    subFormKeyValue = (select max(select tableFieldKey from table where tableFieldKey >= lowest and tableFieldKey <= highest)) +1;

    This way I can have an autonumber effect within a table key, but dependent on the value of the drop-down, which as far as I can tell is not possible to build into the table's autonumber property.

    ~ Mister Mistoffeles

    P.S.: Even a point in the right direction would help, I don't expect my research to be done for me.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Whilst in the form in question? Me.MyControl or just ControlName(.Property - if desired) or thru the Controls collection

    For the control on a subform: Me.SubFormControlName.SubFormControlName ...

    From elsewhere: Forms!MyFormName!MyControlName ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2006
    Posts
    4
    Cool, thanks. Is there somewhere that the object hierarchy in Access is detailed that I could use as a reference?

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    For Forms: the Forms collection

    For Reports: the Reports collection

    For Controls: the Controls collection

    Is this what your asking? Or are you just asking for Help?
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Oct 2006
    Posts
    4
    Quote Originally Posted by M Owen
    For Forms: the Forms collection

    For Reports: the Reports collection

    For Controls: the Controls collection

    Is this what your asking? Or are you just asking for Help?
    I don't know what "the Reports collection", "the Forms collection" or "the Controls collection" are, I am looking for documentation of the object hierarchy model accessible in an Access database file using VBA, if that makes any sense.

    All of my programming using Microsoft products has been using C#/C#.NET and SQL Server, except for a very small project near the beginning of my web programming college program, and it's quite limited even at that. The book I have, Microsoft Office Access 2003 Step by Step, doesn't cover VBA at all, it just throws some code at you to stick in a few places and expects you to just accept it.

    So I guess, yes, I am asking for help, with grokking the way form objects are accessed in code in Access/VBA. I don't want this task done for me, I just need an understanding of how to do the "generic" things so that I am able to write the specific code for this function.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    any .MDB
    Alt-F11 (=open code window)
    in the code-window magi-buttons tool bar there is something that looks like a box with three stupid coloured squares floating above it... hey, you found the object browser.
    it takes some getting used to, but it really is very valuable for the abstract task of browsing the 'object hierarchy model'.

    otherwise, a very specific question will get an answer here.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Oct 2006
    Posts
    4
    Quote Originally Posted by izyrider
    any .MDB
    Alt-F11 (=open code window)
    in the code-window magi-buttons tool bar there is something that looks like a box with three stupid coloured squares floating above it... hey, you found the object browser.
    it takes some getting used to, but it really is very valuable for the abstract task of browsing the 'object hierarchy model'.

    otherwise, a very specific question will get an answer here.

    izy
    Thanks, I'll have a look at it.

Posting Permissions

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