Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Controls and Update Tables

Discussion in 'Information Technology' started by KeffinJ, Jul 28, 2006.

  1. KeffinJ

    KeffinJ
    Expand Collapse
    Guest

    Hi all,

    I am trying to create an interactive report but I need a little help. I
    have a form with a drop down menu. I select an item from the menu and hit a
    button (linked to a Macro) which then takes my selection, relates that
    selection to apply to a different table (through the macro) and then outputs
    this info to the report.

    The problem is, I want to do this with 2 menus, i.e. I select from menu 1
    and menu 2 and it produces a) that output side by side onto the report and b)
    totals that output.

    A little more info about how I currently have this working:

    The form record source is an empty table with one field ('selection' is the
    name of the table and the field). The drop down (combo box) control source
    is also 'selection'). The row source for the combo box is a another table
    in my database containing 3 names. So, when I select a name from the combo
    box, it is then dumped into 'selection' (so now this table is no longer
    empty).

    I have a macro linked to a button. So, after I have made my selection from
    the combo box I hit the button. The macro is used to open a report. My
    macro contains a Where Condition:

    [County]=[Forms]![FormDropdown]![selection]

    County is the name of a field in a table that the report draws info from.
    FormDropdown is the name of my simple combobox form, and selection is
    explained above.

    So, my report uses a different table to source data but the fields that are
    found in my simple drop down menu (sourced from a table with 3 fields) are
    also found in this different table. There is a filter on the report which
    reads the same as the Where Condition shown above.

    That seems to work fine! But adding another combobox is driving me mad.

    I am sorry that this is such a long post, especially for my first one, but I
    just wanted to arm you with as much info as possible.

    Can anyone help?

    Kindest regards

    Kevin
     
  2. Loading...


  3. KeffinJ

    KeffinJ
    Expand Collapse
    Guest

    "KeffinJ" wrote:

    > Hi all,
    >
    > I am trying to create an interactive report but I need a little help. I
    > have a form with a drop down menu.


    Sorry, just wanted to clarify, when I say 'menu' I mean 'combo box'.
     
  4. Larry Daugherty

    Larry Daugherty
    Expand Collapse
    Guest

    Some guidance, maybe.

    First, you don't need a table to store your "selection", the first
    combobox is doing that nicely. Just use an unbound form. The
    comboboxes will each have their own query. Apply the same thinking
    you used in the query for your report in the query for the second
    combobox. You can open the report with a where condition from the
    AfterUpdate event of the 2nd combobox.

    When someone using Access says they're using macros, I wince in pain.
    Don't DO that! What's called a macro in the rest of the MS Office
    platforms is really VBA code. Referring to that code in the Access
    world, it's called VBA.

    You might need an Autoexec macro to cause something to absolutely,
    positively happen at application initiation. Don't use the Access
    macro paradigm to create executable code. Use VBA procedures instead:
    Subs and Functions. The macros eventually become more difficult to
    use and they're impossible to troubleshoot without interactively
    re-writing the macro. Using procedures you can cause your code to
    Halt on Error or at one or many breakpoints. From a halt you can
    execute a single statement at a time and check every variable's value
    to understand why a particular code path executes, etc.

    One more word to the wise: take the terminology seriously and use it
    properly.

    HTH
    --
    -Larry-
    --

    "KeffinJ" <KeffinJ@discussions.microsoft.com> wrote in message
    news:956F7C32-CCF0-481E-B42A-B957DE9E53CE@microsoft.com...
    > Hi all,
    >
    > I am trying to create an interactive report but I need a little

    help. I
    > have a form with a drop down menu. I select an item from the menu

    and hit a
    > button (linked to a Macro) which then takes my selection, relates

    that
    > selection to apply to a different table (through the macro) and then

    outputs
    > this info to the report.
    >
    > The problem is, I want to do this with 2 menus, i.e. I select from

    menu 1
    > and menu 2 and it produces a) that output side by side onto the

    report and b)
    > totals that output.
    >
    > A little more info about how I currently have this working:
    >
    > The form record source is an empty table with one field ('selection'

    is the
    > name of the table and the field). The drop down (combo box) control

    source
    > is also 'selection'). The row source for the combo box is a

    another table
    > in my database containing 3 names. So, when I select a name from

    the combo
    > box, it is then dumped into 'selection' (so now this table is no

    longer
    > empty).
    >
    > I have a macro linked to a button. So, after I have made my

    selection from
    > the combo box I hit the button. The macro is used to open a report.

    My
    > macro contains a Where Condition:
    >
    > [County]=[Forms]![FormDropdown]![selection]
    >
    > County is the name of a field in a table that the report draws info

    from.
    > FormDropdown is the name of my simple combobox form, and selection

    is
    > explained above.
    >
    > So, my report uses a different table to source data but the fields

    that are
    > found in my simple drop down menu (sourced from a table with 3

    fields) are
    > also found in this different table. There is a filter on the report

    which
    > reads the same as the Where Condition shown above.
    >
    > That seems to work fine! But adding another combobox is driving me

    mad.
    >
    > I am sorry that this is such a long post, especially for my first

    one, but I
    > just wanted to arm you with as much info as possible.
    >
    > Can anyone help?
    >
    > Kindest regards
    >
    > Kevin
     
  5. KeffinJ

    KeffinJ
    Expand Collapse
    Guest

    Thank you, Larry, I will try this approach shortly.

    Apologies for my poor terminology - my exposure to the world of Access has
    been through books only...no real people...and yes, I do take this very
    seriously indeed.

    It looks like Access can do one function many different ways, which is
    confusing to me, but your suggested way seems to be very logical. I'll let
    you know how it goes.

    Once again, thank you.
     
  6. KeffinJ

    KeffinJ
    Expand Collapse
    Guest

    I've tried and tried and I seem to be at a complete loss - it looks like I am
    missing something crucial here...a step that I am not aware of.

    I think I'll start a new post (a much simpler one!) to better verbalize the
    issue.

    Thanks again.
     

Share This Page