change query or filter from inside a form?

  • Thread starter Thread starter rexHowell
  • Start date Start date
R

rexHowell

I am writing a dB for a heating & air-conditioning company ..
dispatching technicians is a key part of this.

My dB has a customer table & a order table. In the order tabl
there is a field for the scheduled date of the order.

I have a form for dispatching ... and its easy to setup a form t
show only ScheduledDate=date() ... but I would need to have a separat
form for each query. ie. ScheduledDate=date()+1 for Tomorrows orders
or ScheduledDate=date()-1 for yesterdays orders. I assume having
separate form for each of these querys is not the correct way to dea
with this problem.

If I could just have a button on my form that would change the query
that would be awesome … button one could change ScheduledDate=date()+
then refresh the form, button two could change ScheduledDate=date()-
then refresh.

This would allow me to have one form … instead of having to create
new one for each query. Is this possible?

Thanks,

Re
 
rexHowell said:
I am writing a dB for a heating & air-conditioning company ...
dispatching technicians is a key part of this.

My dB has a customer table & a order table. In the order table
there is a field for the scheduled date of the order.

I have a form for dispatching ... and its easy to setup a form to
show only ScheduledDate=date() ... but I would need to have a separate
form for each query. ie. ScheduledDate=date()+1 for Tomorrows orders,
or ScheduledDate=date()-1 for yesterdays orders. I assume having a
separate form for each of these querys is not the correct way to deal
with this problem.

If I could just have a button on my form that would change the query,
that would be awesome . button one could change ScheduledDate=date()+1
then refresh the form, button two could change ScheduledDate=date()-1
then refresh.

This would allow me to have one form . instead of having to create a
new one for each query. Is this possible?
Click to expand...

I think the easiest thing would be to have an unbound option group in
the form header, containing toggle buttons for "Today's Orders",
"Tomorrow's Orders", "Yesterday's Orders", "All Orders". Those options
would be assigned the numeric values from 1 to 4. You might set the
option group's default value to be 1 (today's orders) or 4 (all
orders) -- or whatever you want. Create an AfterUpdate event procedure
for the option group, like this:

'----- start of example code -----
Private Sub frmDateSelect_AfterUpdate()

Select Case Me!frmDateSelect
Case 1 ' today's orders
Me.Filter = "ScheduleDate = Date()"
Me.FilterOn = True
Case 2 ' tomorrow's orders
Me.Filter = "ScheduleDate = Date() + 1"
Me.FilterOn = True
Case 3 ' yesterday's orders
Me.Filter = "ScheduleDate = Date() - 1"
Me.FilterOn = True
Case 4 ' all orders
Me.Filter = vbNullString
Me.FilterOn = False
End Select

End Sub
'----- end of example code -----

The form's recordsource, in this scheme, would remain the same -- always
set to select all orders. The filter applied would determine which
orders are actually displayed. Unless you want the option group to
default to "All Orders", you'll need to force the filter to be applied
when the form is opened. You can do this by calling the option group's
AfterUpdate procedure in the form's Open event (or maybe the Load event;
try that if the Open event doesn't work):

'----- start code for Open event -----
Private Sub Form_Open(Cancel As Integer)

Call frmDateSelect_AfterUpdate

End Sub
'----- end code for Open event -----

Side note: It would also be possible to do this by simply changing the
form's recordsource directly, rather than by filtering it.
 
Back
Top