Query with parameter taken from a user-updated form control.

[Rayo K]

I have a form with a control that defaults to the current date. The user can
also change the date once the form is open. The underlying query uses the
control's value as a parameter to run a crosstab query. The form works fine
when first opened, but the data does not update when the control's value is
changed. Please help.




My interpretation of the help doc is that the Requery method allows a refresh
of the results, assuming the underlying data has changed. I'm guessing the
form creates the cursor, using the default date when it opens. The Requery
just executes the same query again. I suggest you make another post asking
about a query on a form with a parameter coming from a field on the form.

That doesn't seem to help. The underlying query has a filter based on a
parameter that comes from the form's text box. If I change that value, then
shouldn't "Requery" run the query again with the new value?

I'm not that familiar with queries on forms. Here's a hint from VB Help on
.Requery:

"Use the Requery method to refresh the entire contents of a Recordset object
from the data source by reissuing the original command "...
"Thus, the Requery method can only refresh the current cursor. To change any
of the cursor properties and view the results, you must use the Close method
so that the properties become read/write again. You can then change the
property settings and call the Open method to reopen the cursor."

Thanks. This works great. Now I am trying to filter the records by a user
selected date. I have a form based on the crosstab query with an unbound
field named [EndDate] that defaults to the current date but can be changed by
the user. The intent is to see how many work orders were open per machine on
a given day (to track completion rates). I added an event procedure to the
EndDate field that I copied below. The problem is, the data doesn't change
when the date changes. It still shows what is oepn based on the default date
rather than the date that is entered. Any ideas?

The event procedure:

Private Sub EndDate_AfterUpdate()
Me.Requery
End Sub


Here is the crosstab query as it now stands:

PARAMETERS [Forms]![WorkOrderSummaryFrm]![EndDate] DateTime;
TRANSFORM Count(ReclassWOQRY.[W/O Type]) AS [CountOfW/O Type]
SELECT ReclassWOQRY.[Mach #], Count(ReclassWOQRY.[W/O Type]) AS [Total Of
W/O Type]
FROM ReclassWOQRY
WHERE (((ReclassWOQRY.Date)<[Forms]![WorkOrderSummaryFrm]![EndDate]) AND
((ReclassWOQRY.[Date Completed])>[Forms]![WorkOrderSummaryFrm]![EndDate])) OR
(((ReclassWOQRY.[Date Completed]) Is Null) AND ((ReclassWOQRY.Status)="O"))
GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

 

[kingston via AccessMonster.com]

Is the data in a subform? Otherwise, if the form is bound to the data and
the control's default value is today's date, a refresh will not change the
data as you've found out for yourself. Assuming that the data is in a
subform, try this as the control's After Update event:

Me.Subform.Requery

I have a form with a control that defaults to the current date. The user can
also change the date once the form is open. The underlying query uses the
control's value as a parameter to run a crosstab query. The form works fine
when first opened, but the data does not update when the control's value is
changed. Please help.

My interpretation of the help doc is that the Requery method allows a refresh
of the results, assuming the underlying data has changed. I'm guessing the
form creates the cursor, using the default date when it opens. The Requery
just executes the same query again. I suggest you make another post asking
about a query on a form with a parameter coming from a field on the form.

That doesn't seem to help. The underlying query has a filter based on a
parameter that comes from the form's text box. If I change that value, then

[quoted text clipped - 41 lines]

GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;

 

[Rayo K]

Now it is in a subform. And now it works. Thanks, I had never used subforms
before.

Is the data in a subform? Otherwise, if the form is bound to the data and
the control's default value is today's date, a refresh will not change the
data as you've found out for yourself. Assuming that the data is in a
subform, try this as the control's After Update event:

Me.Subform.Requery

I have a form with a control that defaults to the current date. The user can
also change the date once the form is open. The underlying query uses the
control's value as a parameter to run a crosstab query. The form works fine
when first opened, but the data does not update when the control's value is
changed. Please help.

My interpretation of the help doc is that the Requery method allows a refresh
of the results, assuming the underlying data has changed. I'm guessing the
form creates the cursor, using the default date when it opens. The Requery
just executes the same query again. I suggest you make another post asking
about a query on a form with a parameter coming from a field on the form.

That doesn't seem to help. The underlying query has a filter based on a
parameter that comes from the form's text box. If I change that value, then

[quoted text clipped - 41 lines]

GROUP BY ReclassWOQRY.[Mach #]
PIVOT ReclassWOQRY.safety_WO;