Automate (by VBA) scanning controlnames and saving in a table.

[Johan]

Hello,

Trying to find a way saving the controlnames on a form into a table
automaticly by vba-code.. I want to be able setting up the permissions onto
forms-level, but also by controls (if needed) into a table. Of course it's
possible to set controlpermissions form by form in vba code, but want the
administrator give the possibility to have full access to this permissions by
administring this controltable based on usergroups.

Can someone tell me how to search for all controls onto a form and save them
automaticly into a tabel??

Thanks for a helpfull tip.

Johan

 

[Tom van Stiphout]

On Sun, 28 Jun 2009 12:46:02 -0700, Johan

You can iterate over the Controls collection of the Form object.
Then use an append query or recordset object to save to a table.

-Tom.
Microsoft Access MVP

 

[Arvin Meyer MVP]

The following code finds and locks the controls on a form. Put it in a
standard module and name the module something other than the function name.:

http://www.datastrat.com/Code/LockIt.txt

With this code you can dynamically lock the controls on a form based upon
the Windows Login Name or any other criteria,

 

[Johan]

Hello Arvin,

Your solution i can understaend, also found some more solutions to do that,
but the i would like to 'scan' the form for the controls and save them in a
database-tabel.

Do you have a suggestion for that also.

Thanks for you replay and have a nice day.

Johan (Netherlands)

 

[Arvin Meyer MVP]

Sure. Build a recordset and as you iterate through the controls write them
to the table like:

rst.AddNew
rst!FieldName = ctl.fieldname
rst.update

I have another utility that iterates through form controls as well and may
also be helpful to you:

http://www.datastrat.com/Download/FixNames2K.zip

This one allows you to choose a form or report, then fix the names of each
control to a standard naming convention. It too can be altered to write to a
table using a recordset.

 

[Johan]

Hello Arvn,

That's great. Will try it out and let you now...
Thanks for helping...

Johan

 

[Johan]

Hi Arvin,

Tested your FixNames2K. Works just fine. Small question. Is it also possible
to
change the code-behins subname as well, becuase changing the controlname
causes that controlname en subcode does not fit...

Best regards,
Johan

 

[Arvin Meyer MVP]

You can use the Find & Replace function within the Edit menu of a code
Window. What I usually do those is to build the base form first by using the
Field List, then I run FixNames, then I add any other controls as necessary
naming them correctly. Coding is usually started after the FixNames is run.
There are other 3rd party utilities (not free though) which can name a
control properly everywhere it exists. The problem there is that they only
do them one at a time, and that's generally much slower than running
FixNames and getting them all.