Change form design via VBA

[Carl Colijn]

Hi all,

I want to change the design of some of my forms via a VBA routine; to be
specific: I want to clear the RecordSource property. Running this routine
will be part of my roll-out procedure list (I need the RecordSource to be
empty for distribution and later set dynamically in OnLoad, but sometimes
the actual used runtime setting might get saved in when I switch to form
design when modifying the form).

I'm currently using the following technique:
Call DoCmd.OpenForm(sFormName, acDesign)
Forms(sFormName).RecordSource = ""
Call DoCmd.Save(acForm, sFormName)
Call DoCmd.OpenForm(sFormName)
Call DoCmd.Close(acForm, sFormName)

but this invariantly crashes Access with the following error:
Microsoft Visual C++ Runtime Library

Runtime Error!
Program D:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE
abnormal program termination

I'm using Access 2003.

Is there an alternative way to set a form's recordsource via VBA code? If I
have to process each form manually before each roll-out, I fear some of them
might slip through from time to time...

Thanks in advance,
Carl Colijn

--
TwoLogs - IT Services and Product Development
A natural choice!
http://www.twologs.com
ShellDispenser: restarting Windows Explorer couldn't be easier!
http://www.twologs.com/en/products/shelldispenser.asp

 

[TC]

Carl, this is off the top of my head, but I've written tons of code
like this in the past, and I'm sure that it would work for you.

(untested)

' close all forms before running this code.
dim con as container, doc as document
set con = dbengine(0)(0).containers![forms]
for each doc in con.documents
' doc.name is the name of the next form in the database.
docmd.openform doc.name, acDesign '<<< (1)
forms(doc.name).recordsource = "" '<<< (2)
docmd.close acform, doc.name, acSave '<<< (1)
next
set con=nothing

Note:
(1) Check the parameters: I don't have Access here to check.
(2) Try NULL, if the empty string ("") doesn't work. I can't remember
whether the recordsource property is a variant, or a string.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Carl Colijn]

In

Carl, this is off the top of my head, but I've written tons of code
like this in the past, and I'm sure that it would work for you.

(untested)

' close all forms before running this code.
dim con as container, doc as document
set con = dbengine(0)(0).containers![forms]
for each doc in con.documents
' doc.name is the name of the next form in the database.
docmd.openform doc.name, acDesign '<<< (1)
forms(doc.name).recordsource = "" '<<< (2)
docmd.close acform, doc.name, acSave '<<< (1)
next
set con=nothing

Note:
(1) Check the parameters: I don't have Access here to check.
(2) Try NULL, if the empty string ("") doesn't work. I can't remember
whether the recordsource property is a variant, or a string.

Hi TC,

Thanks, this code works without crashing Access! The difference with the
code fragment I used was that I first performed a DoCmd.Save to save the
changes, then a normal DoCmd.Open to get the form out of design mode, and
then the final DoCmd.Close.

Don't know why I took these particular steps; I tried all sort of things
while the code kept crashing Access... But just a simple DoCmd.Close(...
Save) did the trick

Thanks again and have a nice weekend,
Carl

btw: an empty string works OK (at least, so far for me), and it's acSaveYes
or acSavePrompt; I used acSaveYes

 

[TC]

Ok, well done

TC (MVP Access)
http://tc2.atspace.com