Change form design via VBA

  • Thread starter Thread starter Carl Colijn
  • Start date Start date
C

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
 
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
 
In
TC said:
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.
Click to expand...

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
 
Back
Top