Controls and Update Tables

[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

 

[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.

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

 

[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.

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

 

[Larry Daugherty]

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-
--

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

 

[Larry Daugherty]

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-
--

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

 

[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.

 

[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.

 

[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.