Controls and Update Tables

#1 (**permalink**) 28-Jul-2006, 08:11 AM

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.

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10270-controls-and-update-tables.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
just wanted to arm you with as much info as possible.

Can anyone help?

Kindest regards

Kevin

#2 (**permalink**) 28-Jul-2006, 08:11 AM

"KeffinJ" wrote:

> Hi all,
>
> I am trying to create an interactive report but I need a little help. I
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
> have a form with a drop down menu.

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

#3 (**permalink**) 28-Jul-2006, 08:11 AM

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" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
>
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
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

#4 (**permalink**) 28-Jul-2006, 08:11 AM

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

Apologies for my poor terminology - my exposure to the world of Access has
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270
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.

#5 (**permalink**) 28-Jul-2006, 08:12 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10270

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

Thanks again.