persistence of form recordsource

[Christopher Glaeser]

I implemented command buttons like "Open" and "All" on frmWorkOrders so
users could view the open or all workorders. This is done in VBA code by
setting the form recordsource to the queries qryWorkOrdersOpen and
qryWorkOrdersAll. This is my first Access project, and I did not realize
these assignments would persist for the next time the form was open. There
are command buttons on other forms that open frmWorkOrders to a specific
workorder, but frmWorkOrders will open blank if that specific workorder is
closed and the last recordsource was qryWorkOrdersOpen.

Now that I better understand this persistence issue, I need to take a step
back and review my design. Is it good practice to change a form's
recordsource to different queries? If so, should I always set the
recordsource to a known state when opening a form? Or, should I set the
recordsource to one and only one state (all), and use some other method to
filter the records?

Best,
Christopher

 

[Marshall Barton]

I implemented command buttons like "Open" and "All" on frmWorkOrders so
users could view the open or all workorders. This is done in VBA code by
setting the form recordsource to the queries qryWorkOrdersOpen and
qryWorkOrdersAll. This is my first Access project, and I did not realize
these assignments would persist for the next time the form was open. There
are command buttons on other forms that open frmWorkOrders to a specific
workorder, but frmWorkOrders will open blank if that specific workorder is
closed and the last recordsource was qryWorkOrdersOpen.

Now that I better understand this persistence issue, I need to take a step
back and review my design. Is it good practice to change a form's
recordsource to different queries? If so, should I always set the
recordsource to a known state when opening a form? Or, should I set the
recordsource to one and only one state (all), and use some other method to
filter the records?

Setting the form's RecordSource property in it's Open event
procedure should NOT persist after the form is closed. If
it is, then I strongly suspect that something else is going
on.

In general, using an event in the form to (re)set the record
source is a fairly normal way to do some things, especially
to (re)filter the form's dataset.

If all you want to do is open the form to either a specific
record or all records, then using the OpenForm method's
WhereCondition argument is the usual way.

 

[Christopher Glaeser]

Setting the form's RecordSource property in it's Open event

procedure should NOT persist after the form is closed. If
it is, then I strongly suspect that something else is going
on.

I don't think it's an Open event procedure that is causing me problems, it's
the couple of command buttons that I added to frmWorkOrder to change the
record source. frmWorkOder includes two command buttons "Open" and "All".
Here is the code fragment for OpenWorkOrders_Click.

If Me.Dirty Then Me.Dirty = False
Me.RecordSource = "qryWorkOrdersOpen"
Me.OpenWorkOrders.Enabled = False
Me.AllWorkOrders.Enabled = True

Apparently, this assignment to RecordSource persists. Then, a command
button on another form does the following:

Private Sub WorkOrderID_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmWorkOrder", , , "WorkOrderID=" & Me.WorkOrderID
End Sub

The subroutine WorkOrderID_DblClick opens an empty record (not the behavior
I want) if Me.WorkOrderID is a closed workorder and RecordSource has been
set to a query for open WorkOrders. Should I edit the DoCmd.OpenForm
statement to reset the recordsource? Or, am I misunderstanding this
persistent behavior?

Best,
Christopher

 

[Christopher Glaeser]

Setting the form's RecordSource property in it's Open event

procedure should NOT persist after the form is closed. If
it is, then I strongly suspect that something else is going
on.

I did some more testing, and I think you are right, something else is going
on. Now, I'm not really sure how the form RecordSource was set. Perhaps I
set it incorrectly as the default and assumed it was changed. Thanks for
the feedback, it has helped me to dig deeper.

Best,
Christopher

 

[Marshall Barton]

I don't think it's an Open event procedure that is causing me problems, it's
the couple of command buttons that I added to frmWorkOrder to change the
record source. frmWorkOder includes two command buttons "Open" and "All".
Here is the code fragment for OpenWorkOrders_Click.

If Me.Dirty Then Me.Dirty = False
Me.RecordSource = "qryWorkOrdersOpen"
Me.OpenWorkOrders.Enabled = False
Me.AllWorkOrders.Enabled = True

Apparently, this assignment to RecordSource persists. Then, a command
button on another form does the following:

Private Sub WorkOrderID_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmWorkOrder", , , "WorkOrderID=" & Me.WorkOrderID
End Sub

The subroutine WorkOrderID_DblClick opens an empty record (not the behavior
I want) if Me.WorkOrderID is a closed workorder and RecordSource has been
set to a query for open WorkOrders. Should I edit the DoCmd.OpenForm
statement to reset the recordsource? Or, am I misunderstanding this
persistent behavior?

I have never seen the modified record source persist after
the form is closed. Are sure that it closes and you're not
just making it invisible or covering it up with another
form?

What is the form's RecordSource query when you open the form
in design view? Also check the form's Filter property and
make sure that it is empty.

 

[Christopher Glaeser]

I have never seen the modified record source persist after

the form is closed.

Thanks!!! This is very helpful. I was trying to work around a problem that
does not exist. I've set the RecordSource in design view, and I think
everything is going to work the way I want.

Best,
Christopher