changes not saved on form_unload event 2

[jjd100]

I am trying to clear all of the rowsources for the controls in a form upon exiting the form, and I found this short piece of code to do just that. The code seems to run OK when I close the form, but when I open it again the rowsource values have not been cleared. I would appreciate any assistance.


Private Sub Form_Unload(Cancel As Integer)
Dim ctl As Control

Me.RecordSource = ""

For Each ctl In Me.Controls
Select Case ctl.Properties("ControlType")
Case acComboBox, acListBox
ctl.RowSource = ""
Case acSubform
ctl.Form.RecordSource = ""
Case Else
'do nothing
End Select
Next ctl

Set ctl = Nothing
End Sub

 

[Trevil]

The reason your changes are not still in effect is because of the difference between "design time" and "run time" scope. Your changes are being made at "run time", but your changes are not going to be saved.

Not sure why you want/need to do this, but you can flip your logic: open the form in design view and clear all of the row source settings, then change your code on OPEN to set the rowsource to whatever you want.

"Hmmm, it worked when I tested it....

 

[PHV]

You may try to add this line after the loop:
DoCmd.RunCommand acCmdSave

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jjd100]

Thanks for replying.

The reason that I am trying this is to speed up form open time for a network MSAccess application, where the frontend is on the users c: drive and the backend is on the network. I found this tip in

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

According to the author the form will open up much more quickly when there is a rowsource which is blank vs a rowsource that is set to a query - I confirmed this to be true.

I am changing the rowsouce for the various controls throughout the application, I don't know why it won't clear them on the unload event, it seems to be "too late" for some reason.

It won't let me open the form in design mode when I am in the unload event "you can't switch to a different view at this time" (Trevil tip), or the save command does not work "the command or action Save isn't available now" (PHV tip).

Perhaps this is completely frivolous as I found I got big performance improvement by moving the back-end database on the network up the hierarchy two levels, as well as populating the rowsource with a "value list" instead of a "query".

 

[Eupher]

jjd100,

I think you may have misunderstood Trevil's intent. He's not suggesting you programatically open your form(s) in design view at Unload to remove the RecordSource and RowSource entries. He's suggesting you build your forms (in design view, obviously) with blank RecordSource and RowSource, then programatically set the RecordSource and RowSource properties in the form's Open event - that way you should gain your speed increase and no worries about having to change those properties at Unload.

Ken S.

 

[jjd100]

Thanks Ken, but I still have a problem that I set the rowsources in the application, and these rowsources are automatically SAVED when I exit. Is there some way to keep these changes in the runtime scope so that when I quit the aplication they would be not be saved in the form?

 

[Trevil]

When you close the form is your syntax like:
DoCmd.Close acForm, "frmMain", acSaveYes

If so, that's where the changes are being saved.

"Hmmm, it worked when I tested it....

 

[jjd100]

Trevil, I can see where I have been confused, and that the rowsource is remaining blank once the form is closed, I have been viewing the properties of it while the form is running, my mistake.
Thank you so much for your help.