Query, Form, Sub-Form

[accessquestion]

form1 adds a row at a time to table1.

table2 has 2 fields, a date field: date-submitted; and a text field
describing the date

query1 averages values from table1, within a 1 year date range.

subform1 is in form1 and shows the average values for previous year along
side the new values being entered.

subform1 is tied to form1 by the field "store" (as in retail store number)

query1 has:

SELECT Avg([table2].[Period-End]) AS [AvgOfPeriod-End], [table1].[Store],
Avg([table1].[field3]) AS [AvgOffield3],.... endless list of fields....
GROUP BY [table1].[Store]
HAVING (((Avg(Periods.[Period-End])) Between
CDate([Forms]![form1]![Date-Submitted]) And
DateAdd("yyyy",-1,CDate([Forms]![form1]![Date-Submitted]))));

where date-submitted is the name of a TEXT BOX bound to that field in table2

when form1 is opened, a dialog pops up demanding
"[Forms]![form1]![Date-Submitted]"
when the date is entered, nothing happens until the store is selected from
the dropdown on form1 (correctly so) and subform1 is then populated with the
correct averages. (I'm only displaying, not updating, the averages)

however, when a date is entered in form1.date-submitted, the dialog pops up
again.

I'd like to have the dialog not ever pop-up, but wait until I enter all of:
the store number, date-submitted and the new data
before query1 runs - I've a button for "CALC" which does nothing yet, I'd
like to use it to run query1 after data entry, but before saving and going to
a new (blank) row.

Help is greatly appreciated!