Sub asking for parameter when it shouldn't

  • Thread starter Thread starter Joe Harman
  • Start date Start date
J

Joe Harman

I have a form with three controls.

One control is a date field whose source is a query. The
query simply gets all the different dates from the source
table, i.e., the query result is one field. This control
is on the Form Header.

One control is a subform (datasheet) whose source is also
a query. The query extracts several fields from a
table. There is one related table to translate a code
but no calculations or groupings. This control is
located in the Detail Section of the main form.

The last control is also a subform (datasheet) whose
source is also a query. The query groups on the date and
totals several numeric fields. This control is also
located in the Detail Section of the main form.

All three queries are separate queries but all are
queries on the same table.

I have the following code in the OnLoad event of the main
form:

Private Sub Form_Load()
Stop
Me.subformDisplayDiary.Form.Filter
= "((qryDisplayDiary.Date_Eaten=#" & Date & "#))"
Me.subformDisplayDiary.Form.FilterOn = True
Me.subformDiaryTotalsByDate.Form.Filter
= "((qryDiaryTotalsByDate.Date_Eaten=#" & Date & "#))"
Me.subformDiaryTotalsByDate.Form.FilterOn = True
Me.cboDate = Date
Me.Refresh

End Sub

The purpose of this code is to filter the subforms to the
current date and also select the current date in the
combo box which is the control on the Form Header.

The Problem. The first two lines of code (after the
debug stop) work as planned, however, when the 3rd line
executes I get a "parameter box" with a prompt
of "qryDiaryTotalsByDate.Date_Eaten". If I enter today's
date, it goes on to the next line of code and seemingly
processes it correctly, i.e., it places the string in the
filter property. The next line (turning the filter on)
also seems to work. The combo box is updated properly.
When it executes "Me.Refresh" the "parameter box" pops up
again. When I enter today's date, it goes on and exits
the routine. When I look at the form, however,
subformDisplayDiary is properly filtered and
subformDiaryTotalsByDate isn't although in the area where
it shows the number of records it says filtered. I have
no idea why it is prompting for a parameter.

Can anyone help? You may reply directly to
(e-mail address removed) if you'd like.
 
Hi Joe,

Any time I have seen a Parameter prompt related to a query it almost
always is due to typo when referencing a field.
Check qryDiaryTotalsByDate.Date_Eaten and make sure its valid field name.
As another way to do this you may also consider just placing the Date
parameter in the query itself do away with the code.

HTH

"This posting is provided "AS IS" with no warranties, and confers no
rights. You assume all risk for your use. © 2003 Microsoft Corporation. All
rights reserved."

Richard Marr
Microsoft IIS Support Professional
 
Back
Top