Unload RecordSource from Form

[alex]

Hello,

Using Access ’03…

I’m taking the advice of some in this group to try and speed up the
performance of my db. I’ve set (begun setting) the record source of
my forms/subForms at run-time.

Everything is working well…it’s when I attempt to set the record
source to “” in the unload event of the form, that I run into
problems.

When the form unloads, I get this error:
The expression you entered has a field, control, or property name that
the database can’t find.

The error takes me to this code in the same module as the unload
event:

Private Sub ckName_AfterUpdate()

Dim sfrm As SubForm
Set sfrm = Me.sfrmA

'show particular column(s)
sfrm.Form.Controls("tbCompany").ColumnHidden = _
Not Me.ckName.value

End Sub

For some reason, Access is trying to do something in the after update
event of ckName when the form unloads. This happens even when the
form is not dirty; i.e., I only open and immediately close the form.

I’ve tried using the OnClose event and get the same error.

Any thoughts?
alex

 

[James A. Fortune]

Hello,

Using Access ’03…

I’m taking the advice of some in this group to try and speed up the
performance of my db. I’ve set (begun setting) the record source of
my forms/subForms at run-time.

Everything is working well…it’s when I attempt to set the record
source to “” in the unload event of the form, that I run into
problems.

When the form unloads, I get this error:
The expression you entered has a field, control, or property name that
the database can’t find.

The error takes me to this code in the same module as the unload
event:

Private Sub ckName_AfterUpdate()

Dim sfrm As SubForm
Set sfrm = Me.sfrmA

'show particular column(s)
sfrm.Form.Controls("tbCompany").ColumnHidden = _
Not Me.ckName.value

End Sub

For some reason, Access is trying to do something in the after update
event of ckName when the form unloads. This happens even when the
form is not dirty; i.e., I only open and immediately close the form.

I’ve tried using the OnClose event and get the same error.

Any thoughts?
alex

That does sound odd. What does putting the following in
ckName_AfterUpdate() do:

If sfrm.Form.RecordSource = "" Then Exit Sub

?

James A. Fortune
(e-mail address removed)

 

[alex]

That does sound odd.  What does putting the following in
ckName_AfterUpdate() do:

     If sfrm.Form.RecordSource = "" Then Exit Sub

?

James A. Fortune
(e-mail address removed)- Hide quoted text -

- Show quoted text -

James, that worked! I really appreciate the help. It was actually
the control on the form, so I changed the code to me.RecordSource = ""
Then Exit Sub.
alex

 

[Dirk Goldgar]

Hello,

Using Access ’03…

I’m taking the advice of some in this group to try and speed up the
performance of my db. I’ve set (begun setting) the record source of
my forms/subForms at run-time.

Everything is working well…it’s when I attempt to set the record
source to “” in the unload event of the form, that I run into
problems.

It's not clear to me why you would want to clear the form's RecordSource in
its Unload event. The form's recordset is going to be unloaded anyway. Is
it for fear that the recordsource you set at run-time will be saved with the
form?

 

[alex]

Hello,

Using Access ’03…

I’m taking the advice of some in this group to try and speed up the
performance of my db.  I’ve set (begun setting) the record source of
my forms/subForms at run-time.

Everything is working well…it’s when I attempt to set the record
source to “” in the unload event of the form, that I run into
problems.

It's not clear to me why you would want to clear the form's RecordSource in
its Unload event.  The form's recordset is going to be unloaded anyway. Is
it for fear that the recordsource you set at run-time will be saved with the
form?

Dirk,

Yes, I'm clearing the record source in fear that it will be saved. I
was following the outline in <http://www.granite.ab.ca/access/
performancefaq.htm> that says to clear the record/row source in the
unload event. It mentions Access 2000, but I assumed '03 was no
different.
alex

 

[Dirk Goldgar]

Yes, I'm clearing the record source in fear that it will be saved. I was
following the outline in
<http://www.granite.ab.ca/access/performancefaq.htm> that says to clear
the record/row source in the unload event. It mentions Access 2000, but I
assumed '03 was no different.

I presume you mean this one:

http://www.granite.ab.ca/access/performanceforms.htm

I have, upon occasion, set subform recordsources at runtime, but I've never
found the need to clear them on closing. However, I don't believe I've done
this in Access 2000. So it could be that the saving of the recordsouces was
fixed in later versions, or it could be that it never did happen
automatically, but rather was caused by users clicking the Save button on
the toolbar (thinking mistakenly that it would save the current record). I
wouldn't normally give the users that button.

I'd be interested to try benchmarking the techniques shown in that article
using later versions of Access, but don't have the time right now.

Anyway, thank you for the explanation.