Requery subform from afterupdate event on another subform.

[Juan Correa]

Access 2003

OK... Here's what I have.

I have one form and three subforms. Each subform is based on a parameter
query.

The first subform shows data by coutry in datasheet mode based on a cbo box
selection in the same subform.

The second subform shows a grand total for revenue based on three controls
in the same subform.

The third subform shows a Chart displaying the total revenue.

The second and third subforms are based on the same parameter query.
The query's parameters ask for the choices listed on the cbo boxes.

What I'd like to do is have the chart refresh the information shown as the
controls on the second subform are changed.

Here are the naming conventions that I've followed:

Main Form: frm_MainReports
SubForm 1: frm_revenuebycountry
SubForm 2: frm_report
SubForm 3: frm_chart
Query for SubForm 1: qry_revenuebycountry
Query for SubForms 2 and 3: qry_chart

In Subform 2 I have three controls:
cbo_area
cbo_year
cbo_activity
these three cbo boxes serve as the parameters for qry_chart.

All three subforms work fine, but I can't for the life of me get the third
one to refresh its data based on the selections from the cbo boxes above.

I'm wondering if I have overcomplicated my desing a bit here.
Any help would be appreciated.

 

[Juan Correa]

Never mind people. I've figured it out.

Thanks anyway

 

[Arpit.Deeksha, Fiji]

Hello Juan

Sorry to bother you but I am also facing some similar problem. I have
created a Subform Add and Drop Course, when I operate through this form as
main form I am able to update a table through an Add or Delete query based on
the Add or Drop field test (a simple VB code on Insert event). It accepts
parameters but if I use this ADD and Drop as subform to another change of
course subform, the update query start looking for parameters. Please put
solution of your problem, probably, I will be able to solve mine after
looking at your solution.

Regards
Arpit.Deeksha, Fiji

 

[Juan Correa]

Arpit,

This is what I had:
Main Form: frm_MainReports
SubForm 1: frm_revenuebycountry
SubForm 2: frm_report
SubForm 3: frm_chart
Query for SubForm 1: qry_revenuebycountry
Query for SubForms 2 and 3: qry_chart

I wanted the data on frm_chart to refresh whenever any of the controls in
frm_revenuebycountry were changed.

After a lot of trying with that setup, I decided that the entire thing was
just too complicated.
I eliminated frm_report and frm_revenuebycountry from the Main Form. I then
created controls in the main form that duplicated the functions that the two
subforms were performing. This left me with only 1 main form and 1 sub form.

Main Form: frm_MainReports:
- Has 6 controls and 4 command buttons.
- txt_cntryName:
Unbound
Linked to a query that brings me a list of all the available countires.

- txt_totalbycountry:
Unbound
Control Source =DSum("[totalUS]", "[qry_revenuebycountry]", "[country= '"
&[cntryName]"'")

The above controls and simple buttons for Excel export and query display
replaced frm_revenuebycountry

- txt_area:
Unbound
Linked to a query that brings me the area related to each of the countries.

- txt_year:
Unbound
Linked to a query that brings the years that we have data for.

txt_activity:
Unbound
Linked to a query that brings all the different activities that are
performed at each area. txt_area is a parameter of the query for this
control, that way it only brings the activities pertinent to the specific
area.

- txt_revenuebyareayearactivity (I know... long name)
Unbound
Control Source ==DSum("[totalUS]","[qry_revenuebycountry]","[area]='" &
[cbo_area] & "' and [activity] ='" & [cbo_activity] & "' and
[year]=[cbo_year]")

These 4 controls and 2 other buttons replaced frm_report.

After making these changes, I was able to add this code to the AfterUpdate
event on the txt_area, txt_year, txt_activity controls

Me.frm_chart.ReQuery and not get the referencing errors that I was getting
with my previous layout.

This worked for me... I don't know if it is something that you could or even
want to implement, but it was a solution to my problem.

Juan Correa