Using a Query To Fill a FlexGrid

[MikeG]

I have a form with a flexgrid. I use a query to get the data to fill the
flexgrid. I t works great as long as I enter the date range in the query like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate] and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required parameters.

I am using code to fill the flexboard. I could cut and paste it here, but it
would take up a lot of space. This is the begging of it that identifies where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open the query
in design mode and entering it?

 

[tina]

try adding parameters for those referenced controls to the SQL statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design view
by selecting Query | Parameters on the menu bar. in the Parameters dialog,
enter the complete start date control reference on the first line and then
choose Date/Time from the droplist in the next column; then go to the next
line and do the same for the end date.

hth

 

[MikeG]

Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message. Any
other ideas?

try adding parameters for those referenced controls to the SQL statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design view
by selecting Query | Parameters on the menu bar. in the Parameters dialog,
enter the complete start date control reference on the first line and then
choose Date/Time from the droplist in the next column; then go to the next
line and do the same for the end date.

hth

I have a form with a flexgrid. I use a query to get the data to fill the
flexgrid. I t works great as long as I enter the date range in the query like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate] and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required parameters.

I am using code to fill the flexboard. I could cut and paste it here, but it
would take up a lot of space. This is the begging of it that identifies where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open the query
in design mode and entering it?

 

[tina]

what happens if you run the query from the database window, with the form
references in the criteria? do input boxes for the parameters pop up? if so,
does the query run successfully after entering the dates in the parameter
boxes? also, this is obvious of course, but have you doublechecked that the
form name and control names are spelled correctly in the query criteria (and
the parameters clause)?

hth

Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message. Any
other ideas?

try adding parameters for those referenced controls to the SQL statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design view
by selecting Query | Parameters on the menu bar. in the Parameters dialog,
enter the complete start date control reference on the first line and then
choose Date/Time from the droplist in the next column; then go to the next
line and do the same for the end date.

hth

I have a form with a flexgrid. I use a query to get the data to fill the
flexgrid. I t works great as long as I enter the date range in the

query
like

this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate] and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required parameters.

I am using code to fill the flexboard. I could cut and paste it here,

but
it

would take up a lot of space. This is the begging of it that

identifies
where

the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open the query
in design mode and entering it?

 

[MikeG]

I typed the Parameters in SQL view. When I run the form, I get the parameter
prompts for the [StartDate] and [EndDate] but still get the same error. If I
just run the query, I get all of the records instead of the ones from the
date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the query in
design mode and run just the query, it works fine. If I run the form, I get
the error. By the way, the word Report should have been Activity in the
search criteria.

Another thing which may be affecting this is the fact that there are 2 forms
involved: frmMonthlyActivity used to get the date ranges and frmEEPBilling
where the flexgrid is located.

what happens if you run the query from the database window, with the form
references in the criteria? do input boxes for the parameters pop up? if so,
does the query run successfully after entering the dates in the parameter
boxes? also, this is obvious of course, but have you doublechecked that the
form name and control names are spelled correctly in the query criteria (and
the parameters clause)?

hth

Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message. Any
other ideas?

try adding parameters for those referenced controls to the SQL statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design view
by selecting Query | Parameters on the menu bar. in the Parameters dialog,
enter the complete start date control reference on the first line and then
choose Date/Time from the droplist in the next column; then go to the next
line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the data to fill the
flexgrid. I t works great as long as I enter the date range in the query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the
actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate] and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required parameters.

I am using code to fill the flexboard. I could cut and paste it here, but
it
would take up a lot of space. This is the begging of it that identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open the query
in design mode and entering it?

 

[tina]

okay. is frmMonthlyActivity open, when you run the code to fill the
flexgrid?

hth

I typed the Parameters in SQL view. When I run the form, I get the parameter
prompts for the [StartDate] and [EndDate] but still get the same error. If I
just run the query, I get all of the records instead of the ones from the
date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the query in
design mode and run just the query, it works fine. If I run the form, I get
the error. By the way, the word Report should have been Activity in the
search criteria.

Another thing which may be affecting this is the fact that there are 2 forms
involved: frmMonthlyActivity used to get the date ranges and frmEEPBilling
where the flexgrid is located.

what happens if you run the query from the database window, with the form
references in the criteria? do input boxes for the parameters pop up? if so,
does the query run successfully after entering the dates in the parameter
boxes? also, this is obvious of course, but have you doublechecked that the
form name and control names are spelled correctly in the query criteria (and
the parameters clause)?

hth

Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message. Any
other ideas?

:

try adding parameters for those referenced controls to the SQL statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in

Design
view

by selecting Query | Parameters on the menu bar. in the Parameters dialog,
enter the complete start date control reference on the first line

and
then

choose Date/Time from the droplist in the next column; then go to

the
next

line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the data to

fill
the

flexgrid. I t works great as long as I enter the date range in the query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the
actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range:

[StartDate]
and

[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required parameters.

I am using code to fill the flexboard. I could cut and paste it

here,
but

it
would take up a lot of space. This is the begging of it that identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open

the
query

in design mode and entering it?

 

[MikeG]

Yes, frmMonthlyActivity is open with the StartDate and EndDate entered.

okay. is frmMonthlyActivity open, when you run the code to fill the
flexgrid?

hth

I typed the Parameters in SQL view. When I run the form, I get the parameter
prompts for the [StartDate] and [EndDate] but still get the same error. If I
just run the query, I get all of the records instead of the ones from the
date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the query in
design mode and run just the query, it works fine. If I run the form, I get
the error. By the way, the word Report should have been Activity in the
search criteria.

Another thing which may be affecting this is the fact that there are 2 forms
involved: frmMonthlyActivity used to get the date ranges and frmEEPBilling
where the flexgrid is located.

what happens if you run the query from the database window, with the form
references in the criteria? do input boxes for the parameters pop up? if so,
does the query run successfully after entering the dates in the parameter
boxes? also, this is obvious of course, but have you doublechecked that the
form name and control names are spelled correctly in the query criteria (and
the parameters clause)?

hth


Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message. Any
other ideas?

:

try adding parameters for those referenced controls to the SQL
statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design
view
by selecting Query | Parameters on the menu bar. in the Parameters
dialog,
enter the complete start date control reference on the first line and
then
choose Date/Time from the droplist in the next column; then go to the
next
line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the data to fill
the
flexgrid. I t works great as long as I enter the date range in the
query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with the
actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate]
and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required
parameters.

I am using code to fill the flexboard. I could cut and paste it here,
but
it
would take up a lot of space. This is the begging of it that
identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM
qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open the
query
in design mode and entering it?

 

[tina]

well, i'm sorry to say i'm stumped, Mike. i've no idea what avenue to
explore next. suggest you start a new thread, with a synopsis of what we
covered in this one (you may want to include a link to this thread, but
suggest you don't *expect* people to go to it to find out where you are at
this point). cross-posting isn't often necessary, but you may want to
cross-post to this ng and also microsoft.public.access.queries - and make
sure you cross-post rather than multi-posting. hopefully somebody else will
have a bright idea that's escaping us; good luck with it.

hth

Yes, frmMonthlyActivity is open with the StartDate and EndDate entered.

okay. is frmMonthlyActivity open, when you run the code to fill the
flexgrid?

hth

I typed the Parameters in SQL view. When I run the form, I get the parameter
prompts for the [StartDate] and [EndDate] but still get the same

error. If
I

just run the query, I get all of the records instead of the ones from the
date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the

query
in

design mode and run just the query, it works fine. If I run the form,

I
get

the error. By the way, the word Report should have been Activity in the
search criteria.

Another thing which may be affecting this is the fact that there are 2 forms
involved: frmMonthlyActivity used to get the date ranges and frmEEPBilling
where the flexgrid is located.

:

what happens if you run the query from the database window, with the form
references in the criteria? do input boxes for the parameters pop

up? if
so,

does the query run successfully after entering the dates in the parameter
boxes? also, this is obvious of course, but have you doublechecked

that
the

form name and control names are spelled correctly in the query

criteria
(and

the parameters clause)?

hth


Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error

message.
Any

other ideas?

:

try adding parameters for those referenced controls to the SQL
statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in Design
view
by selecting Query | Parameters on the menu bar. in the Parameters
dialog,
enter the complete start date control reference on the first

line
and

then
choose Date/Time from the droplist in the next column; then go

to
the

next
line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the data

to
fill

the
flexgrid. I t works great as long as I enter the date range in the
query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's

with
the

actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range: [StartDate]
and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required
parameters.

I am using code to fill the flexboard. I could cut and paste

it
here,

but
it
would take up a lot of space. This is the begging of it that
identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM
qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to

open
the

query
in design mode and entering it?

 

[MikeG]

Thank you tina for your effort. The fact that the form works when I add the
date range directly in the query kind of points towards frmMonthlyActivity as
being the culprit, though I can't see why. I'll play around with it a little
more before I start a new thread. How do you cross-post?

well, i'm sorry to say i'm stumped, Mike. i've no idea what avenue to
explore next. suggest you start a new thread, with a synopsis of what we
covered in this one (you may want to include a link to this thread, but
suggest you don't *expect* people to go to it to find out where you are at
this point). cross-posting isn't often necessary, but you may want to
cross-post to this ng and also microsoft.public.access.queries - and make
sure you cross-post rather than multi-posting. hopefully somebody else will
have a bright idea that's escaping us; good luck with it.

hth

Yes, frmMonthlyActivity is open with the StartDate and EndDate entered.

okay. is frmMonthlyActivity open, when you run the code to fill the
flexgrid?

hth


I typed the Parameters in SQL view. When I run the form, I get the
parameter
prompts for the [StartDate] and [EndDate] but still get the same error. If
I
just run the query, I get all of the records instead of the ones from the
date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the query
in
design mode and run just the query, it works fine. If I run the form, I
get
the error. By the way, the word Report should have been Activity in the
search criteria.

Another thing which may be affecting this is the fact that there are 2
forms
involved: frmMonthlyActivity used to get the date ranges and
frmEEPBilling
where the flexgrid is located.

:

what happens if you run the query from the database window, with the
form
references in the criteria? do input boxes for the parameters pop up? if
so,
does the query run successfully after entering the dates in the
parameter
boxes? also, this is obvious of course, but have you doublechecked that
the
form name and control names are spelled correctly in the query criteria
(and
the parameters clause)?

hth


Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message.
Any
other ideas?

:

try adding parameters for those referenced controls to the SQL
statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in
Design
view
by selecting Query | Parameters on the menu bar. in the Parameters
dialog,
enter the complete start date control reference on the first line
and
then
choose Date/Time from the droplist in the next column; then go to
the
next
line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the data to
fill
the
flexgrid. I t works great as long as I enter the date range in the
query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the 9's with
the
actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range:
[StartDate]
and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required
parameters.

I am using code to fill the flexboard. I could cut and paste it
here,
but
it
would take up a lot of space. This is the begging of it that
identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM
qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having to open
the
query
in design mode and entering it?

 

[tina]

you're welcome Mike, sorry i wasn't able to be of more help.
to cross-post, put both newsgroup "addresses" in a single message (just like
emailing one msg to both Tom and Mary at the same time). multi-posting would
be to post a separate yet essentially identical record to each newsgroup (or
multiple times to the same newsgroup, especially within an unreasonably
short time period).

hth

Thank you tina for your effort. The fact that the form works when I add the
date range directly in the query kind of points towards frmMonthlyActivity as
being the culprit, though I can't see why. I'll play around with it a little
more before I start a new thread. How do you cross-post?

well, i'm sorry to say i'm stumped, Mike. i've no idea what avenue to
explore next. suggest you start a new thread, with a synopsis of what we
covered in this one (you may want to include a link to this thread, but
suggest you don't *expect* people to go to it to find out where you are at
this point). cross-posting isn't often necessary, but you may want to
cross-post to this ng and also microsoft.public.access.queries - and make
sure you cross-post rather than multi-posting. hopefully somebody else will
have a bright idea that's escaping us; good luck with it.

hth

Yes, frmMonthlyActivity is open with the StartDate and EndDate entered.

:

okay. is frmMonthlyActivity open, when you run the code to fill the
flexgrid?

hth


I typed the Parameters in SQL view. When I run the form, I get the
parameter
prompts for the [StartDate] and [EndDate] but still get the same error. If
I
just run the query, I get all of the records instead of the ones

from
the

date range.

If I type "Between Forms!frmMonthlyActivity!StartDate And
Forms!MonthlyActivity!EndDate" under the field [DateWorked] in the query
in
design mode and run just the query, it works fine. If I run the

form,
I

get
the error. By the way, the word Report should have been Activity

in
the

search criteria.

Another thing which may be affecting this is the fact that there are 2
forms
involved: frmMonthlyActivity used to get the date ranges and
frmEEPBilling
where the flexgrid is located.

:

what happens if you run the query from the database window, with the
form
references in the criteria? do input boxes for the parameters

pop
up? if

so,
does the query run successfully after entering the dates in the
parameter
boxes? also, this is obvious of course, but have you

doublechecked
that

the
form name and control names are spelled correctly in the query criteria
(and
the parameters clause)?

hth


Thanks Tina for your reply!
I tried your suggestion, but I'm still getting the same error message.
Any
other ideas?

:

try adding parameters for those referenced controls to the SQL
statement, as

PARAMETERS [Forms]![frmMonthlyReport]![StartDate] DateTime,
[Forms]![frmMonthlyReport]![EndDate] DateTime;
SELECT...

you can type this manually in the query's SQL view, or do it in
Design
view
by selecting Query | Parameters on the menu bar. in the Parameters
dialog,
enter the complete start date control reference on the first line
and
then
choose Date/Time from the droplist in the next column; then

go
to

the
next
line and do the same for the end date.

hth


I have a form with a flexgrid. I use a query to get the

data
to

fill
the
flexgrid. I t works great as long as I enter the date

range in
the

query
like
this:

Between #99/99/9999# And #99/99/9999# (substituting the

9's
with

the
actual
dates) in the [DateWorked] field of the query.

However, when I use another form to enter the date range:
[StartDate]
and
[EndDate] in the query like this:

Between Forms!frmMonthlyReport!StartDate And
Forms!frmMonthlyReport!EndDate

I get this message: No value given for one or more required
parameters.

I am using code to fill the flexboard. I could cut and

paste
it

here,
but
it
would take up a lot of space. This is the begging of it that
identifies
where
the data comes from:

strSQL = "SELECT EEP1, CalDay, ConsumerName, DateWorked FROM
qryEEP_Cal"
With rstHIRE
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open strSQL
End With
'Assign rstHIRE as the Recordset for the form
Set Me.Recordset = rstHIRE
nRecords = rstHIRE.RecordCount

Is there a way to enter the date range other than having

to
open

the
query
in design mode and entering it?