By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

A97 update all Command Button and Textbox Caption properties?

P: n/a
MLH
This is one for you gurus. Someone has undoubtedly already done this.
How difficult would it be to recurse all command buttons and textbox
controls on all forms, appending an incrementing number to the end of
their Caption property strings? Say I have 10 forms with 10 such
controls each, I would like to have " (001)" - " (100)" appended to
the end of each of their Caption property strings.

Of course, I know about the manual brute force technique. I would
like to automate the job with a procedure in a class module.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
The trick to doing this would be to do them in the order you desire.

To loop through the controls, open the form in design view, loop through the
controls, see what type of control each is, and concatenate the number onto
the current caption.

Untested, very general, example:

Dim ctl As Control, strForm As String, intButton As Integer, intTextbox As
Integer
'create a loop here to go through all of the forms
strForm = "frmFormName"
DoCmd.OpenForm strForm,acDesign,,,,acHidden 'hidden is optional
For Each ctl In Forms(strForm).Controls
If ctl.ControlType = acCommandButton Then
intButton = intButton + 1
ctl.Caption = ctl.Caption & intButton
End If
If ctl.ControlType = acLabel Then
'get the associated control, is it a textbox?
If ctl.Parent.ControlType = acTextbox Then
intTextbox = intTextbox + 1
ctl.Caption = ctl.Caption & intTextbox
End If
End If
Next
DoCmd.Close acForm, strForm, acSaveYes

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:l7********************************@4ax.com...
This is one for you gurus. Someone has undoubtedly already done this.
How difficult would it be to recurse all command buttons and textbox
controls on all forms, appending an incrementing number to the end of
their Caption property strings? Say I have 10 forms with 10 such
controls each, I would like to have " (001)" - " (100)" appended to
the end of each of their Caption property strings.

Of course, I know about the manual brute force technique. I would
like to automate the job with a procedure in a class module.

Nov 13 '05 #2

P: n/a
PS.

One thing I thought of after doing that though is that Access 97 doesn't
have an AllForms Collection. To loop through the forms in Access 97 and open
them you'll need to open a recordset on the system table MSysObjects. Open
the recordset as a Snapshot so that you don't accidentally make changes to
this table. You will need to get the value from 2 fields. The Type field
will tell you if the object is a form. The Type value for a form is -32768.
The Name field will then give you the name of the form so that you can open
it.

--
Wayne Morgan
MS Access MVP
"MLH" <CR**@NorthState.net> wrote in message
news:l7********************************@4ax.com...
This is one for you gurus. Someone has undoubtedly already done this.
How difficult would it be to recurse all command buttons and textbox
controls on all forms, appending an incrementing number to the end of
their Caption property strings? Say I have 10 forms with 10 such
controls each, I would like to have " (001)" - " (100)" appended to
the end of each of their Caption property strings.

Of course, I know about the manual brute force technique. I would
like to automate the job with a procedure in a class module.

Nov 13 '05 #3

P: n/a
Wayne Morgan wrote:
PS.

One thing I thought of after doing that though is that Access 97 doesn't
have an AllForms Collection. To loop through the forms in Access 97 and open
them you'll need to open a recordset on the system table MSysObjects. Open
the recordset as a Snapshot so that you don't accidentally make changes to
this table. You will need to get the value from 2 fields. The Type field
will tell you if the object is a form. The Type value for a form is -32768.
The Name field will then give you the name of the form so that you can open
it.


Wayne, I disagree that you need to open a recordset on MSysObjects. The
following code will list the forms in the mdb. It's easy enough to
expand it to open the forms like to you did in the prior post. I can do
the same with Reports and Modules.

Sub ListFormNames
Dim dbs As Database
Dim doc As Document
Dim i As Integer

Set dbs = CurrentDb

'change the word Forms to Reports or Modules for their list of names
With dbs.Containers!Forms
For Each doc In .Documents
i = i + 1
Debug.Print i & " " & doc.name
Next doc
End With

MsgBox "Done"
End Sub
Nov 13 '05 #4

P: n/a
Thanks Salad,

I guess I'm going to need to reload Access 97. Trying to just remember what
it had and didn't have doesn't cut it.

--
Wayne Morgan
MS Access MVP
"Salad" <oi*@vinegar.com> wrote in message
news:mi*****************@newsread2.news.pas.earthl ink.net...
Wayne Morgan wrote:

Wayne, I disagree that you need to open a recordset on MSysObjects. The
following code will list the forms in the mdb. It's easy enough to expand
it to open the forms like to you did in the prior post. I can do the same
with Reports and Modules.

Sub ListFormNames
Dim dbs As Database
Dim doc As Document
Dim i As Integer

Set dbs = CurrentDb

'change the word Forms to Reports or Modules for their list of names
With dbs.Containers!Forms
For Each doc In .Documents
i = i + 1
Debug.Print i & " " & doc.name
Next doc
End With

MsgBox "Done"
End Sub

Nov 13 '05 #5

P: n/a
Wayne Morgan wrote:
Thanks Salad,

I guess I'm going to need to reload Access 97. Trying to just remember what
it had and didn't have doesn't cut it.

:-)

I have Office2003 sitting in a folder, not installed. Someday I guess
I'm going to have to get with the game and move to it.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.