Access VBA put a form element name as an argument

Let's see if anyone knows how to solve this problem:

I have a form with several elements: Some of them are textboxes called A1, A2, A3, A4...

Now, their AfterUpdate SubProcedure is extremely long but barely similar for each of them: A1_AfterUpdate, A2_AfterUpdate, A3_AfterUpdate...etc... are very similar but for the names of the textboxes they change.

My idea was to gather all that was equal in a subprocedure defined this way:

Private Sub Update(Box As String, Menu As Boolean)

   If Menu=True{ 

   Me!Box.Text = "This is the text that is going to change"

   }

End Sub

So, the only thing I must do is to call it this way, for instance:

Update(A1, True)

But it doesn't seems to work. Any idea on how to reach this objective?

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

1

Box as TextBox not as string. You should concider public generic function or Class with eventhanders

– krish KM
Nov 22 '18 at 9:48

1

there are no { in VBA. Your syntax does not look like VBA at all. It's If Then End If

– Patrick Honorez
Nov 22 '18 at 9:57

add a comment |

Let's see if anyone knows how to solve this problem:

I have a form with several elements: Some of them are textboxes called A1, A2, A3, A4...

My idea was to gather all that was equal in a subprocedure defined this way:

Private Sub Update(Box As String, Menu As Boolean)

   If Menu=True{ 

   Me!Box.Text = "This is the text that is going to change"

   }

End Sub

So, the only thing I must do is to call it this way, for instance:

Update(A1, True)

But it doesn't seems to work. Any idea on how to reach this objective?

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

1

Box as TextBox not as string. You should concider public generic function or Class with eventhanders

– krish KM
Nov 22 '18 at 9:48

1

there are no { in VBA. Your syntax does not look like VBA at all. It's If Then End If

– Patrick Honorez
Nov 22 '18 at 9:57

add a comment |

Let's see if anyone knows how to solve this problem:

I have a form with several elements: Some of them are textboxes called A1, A2, A3, A4...

My idea was to gather all that was equal in a subprocedure defined this way:

Private Sub Update(Box As String, Menu As Boolean)

   If Menu=True{ 

   Me!Box.Text = "This is the text that is going to change"

   }

End Sub

So, the only thing I must do is to call it this way, for instance:

Update(A1, True)

But it doesn't seems to work. Any idea on how to reach this objective?

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

Let's see if anyone knows how to solve this problem:

I have a form with several elements: Some of them are textboxes called A1, A2, A3, A4...

My idea was to gather all that was equal in a subprocedure defined this way:

Private Sub Update(Box As String, Menu As Boolean)

   If Menu=True{ 

   Me!Box.Text = "This is the text that is going to change"

   }

End Sub

So, the only thing I must do is to call it this way, for instance:

Update(A1, True)

But it doesn't seems to work. Any idea on how to reach this objective?

vba ms-access

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

edited Nov 22 '18 at 9:58

krish KM

4,4121727

edited Nov 22 '18 at 9:58

krish KM

4,4121727

edited Nov 22 '18 at 9:58

krish KM

4,4121727

asked Nov 22 '18 at 9:40

AccOUCH

205

asked Nov 22 '18 at 9:40

AccOUCH

205

asked Nov 22 '18 at 9:40

AccOUCH

205

1

Box as TextBox not as string. You should concider public generic function or Class with eventhanders

– krish KM
Nov 22 '18 at 9:48

1

there are no { in VBA. Your syntax does not look like VBA at all. It's If Then End If

– Patrick Honorez
Nov 22 '18 at 9:57

add a comment |

1

Box as TextBox not as string. You should concider public generic function or Class with eventhanders

– krish KM
Nov 22 '18 at 9:48

1

there are no { in VBA. Your syntax does not look like VBA at all. It's If Then End If

– Patrick Honorez
Nov 22 '18 at 9:57

Box as TextBox not as string. You should concider public generic function or Class with eventhanders

– krish KM
Nov 22 '18 at 9:48

there are no { in VBA. Your syntax does not look like VBA at all. It's If Then End If

– Patrick Honorez
Nov 22 '18 at 9:57

add a comment |

2 Answers
2

active

oldest

votes

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class:

Public WithEvents txt As Access.TextBox



Private Sub txt_AfterUpdate()

    MsgBox txt.Name & " has been updated."

End Sub

In your form module add this code:

Public MyTextBoxes As New Collection



Private Sub Form_Open(Cancel As Integer)



    Dim ctl As Control

    Dim txtBoxEvent As clsTextBoxEvents



    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then

            Set txtBoxEvent = New clsTextBoxEvents

            Set txtBoxEvent.txt = ctl

            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"

            MyTextBoxes.Add txtBoxEvent

        End If

    Next ctl



End Sub

The MyTextBoxes declaration must be at the very top of the module.

This just adds the AfterUpdate event to all textboxes on the form. You'll probably want to refine that a bit to textboxes with specific text in the name, or controls that are in a specific frame on the form.

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

|
show 1 more comment

If you use a function instead of a sub:

Private Function UpdateCtl(Menu As Boolean)

   If Menu Then 

       activecontrol = "This is the text that is going to change"

   End If

End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).

Simple and fast

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53427898%2faccess-vba-put-a-form-element-name-as-an-argument%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class:

Public WithEvents txt As Access.TextBox



Private Sub txt_AfterUpdate()

    MsgBox txt.Name & " has been updated."

End Sub

In your form module add this code:

Public MyTextBoxes As New Collection



Private Sub Form_Open(Cancel As Integer)



    Dim ctl As Control

    Dim txtBoxEvent As clsTextBoxEvents



    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then

            Set txtBoxEvent = New clsTextBoxEvents

            Set txtBoxEvent.txt = ctl

            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"

            MyTextBoxes.Add txtBoxEvent

        End If

    Next ctl



End Sub

The MyTextBoxes declaration must be at the very top of the module.

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

|
show 1 more comment

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class:

Public WithEvents txt As Access.TextBox



Private Sub txt_AfterUpdate()

    MsgBox txt.Name & " has been updated."

End Sub

In your form module add this code:

Public MyTextBoxes As New Collection



Private Sub Form_Open(Cancel As Integer)



    Dim ctl As Control

    Dim txtBoxEvent As clsTextBoxEvents



    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then

            Set txtBoxEvent = New clsTextBoxEvents

            Set txtBoxEvent.txt = ctl

            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"

            MyTextBoxes.Add txtBoxEvent

        End If

    Next ctl



End Sub

The MyTextBoxes declaration must be at the very top of the module.

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

|
show 1 more comment

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class:

Public WithEvents txt As Access.TextBox



Private Sub txt_AfterUpdate()

    MsgBox txt.Name & " has been updated."

End Sub

In your form module add this code:

Public MyTextBoxes As New Collection



Private Sub Form_Open(Cancel As Integer)



    Dim ctl As Control

    Dim txtBoxEvent As clsTextBoxEvents



    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then

            Set txtBoxEvent = New clsTextBoxEvents

            Set txtBoxEvent.txt = ctl

            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"

            MyTextBoxes.Add txtBoxEvent

        End If

    Next ctl



End Sub

The MyTextBoxes declaration must be at the very top of the module.

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

Add a class module - I've called it clsTextBoxEvents.

Add this code to the class:

Public WithEvents txt As Access.TextBox



Private Sub txt_AfterUpdate()

    MsgBox txt.Name & " has been updated."

End Sub

In your form module add this code:

Public MyTextBoxes As New Collection



Private Sub Form_Open(Cancel As Integer)



    Dim ctl As Control

    Dim txtBoxEvent As clsTextBoxEvents



    For Each ctl In Me.Controls

        If TypeName(ctl) = "TextBox" Then

            Set txtBoxEvent = New clsTextBoxEvents

            Set txtBoxEvent.txt = ctl

            txtBoxEvent.txt.AfterUpdate = "[Event Procedure]"

            MyTextBoxes.Add txtBoxEvent

        End If

    Next ctl



End Sub

The MyTextBoxes declaration must be at the very top of the module.

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

answered Nov 22 '18 at 10:10

Darren Bartrup-Cook

13.9k11432

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

|
show 1 more comment

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

Impressive, but seems complex. Did you ever actually use such construct ? A use case perhaps ? Thx

– Patrick Honorez
Nov 22 '18 at 13:18

I don't understand the role of the MyTextBoxes collection. By reading the code with my knowledge it seems that nothing is done with it. What do I miss ?

– Patrick Honorez
Nov 22 '18 at 13:23

The MyTextBoxes collection should be Private really as it's only used in the form. The collection is used to store each instance of the class - one for each textbox. Without the MyTextBoxes.Add txtBoxEvent at the end none of the events fire.

– Darren Bartrup-Cook
Nov 22 '18 at 13:53

@PatrickHonorez I have to say I've not used such a construct in Access as I'd usually use a Continuous Form set up if I had lots of controls doing the same thing, but that would need changes to the tables as well. I've used similar code in Excel when creating controls at runtime. Now you've got me trying to think of a useful use case and all I'm thinking is "are very similar but for the names of the textboxes they change" from the OPs question.

– Darren Bartrup-Cook
Nov 22 '18 at 14:01

@PatrickHonorez I use this type of construct all the time for validating or restricting input - i.e. a group of TextBoxes that only accept numeric input. If you think this is "complex", you haven't seen the code-behind with 5 or 10 duplicate copies of a KeyPress event. This is much cleaner.

– Comintern
Nov 22 '18 at 14:09

|
show 1 more comment

If you use a function instead of a sub:

Private Function UpdateCtl(Menu As Boolean)

   If Menu Then 

       activecontrol = "This is the text that is going to change"

   End If

End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).

Simple and fast

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

add a comment |

If you use a function instead of a sub:

Private Function UpdateCtl(Menu As Boolean)

   If Menu Then 

       activecontrol = "This is the text that is going to change"

   End If

End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).

Simple and fast

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

add a comment |

If you use a function instead of a sub:

Private Function UpdateCtl(Menu As Boolean)

   If Menu Then 

       activecontrol = "This is the text that is going to change"

   End If

End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).

Simple and fast

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

If you use a function instead of a sub:

Private Function UpdateCtl(Menu As Boolean)

   If Menu Then 

       activecontrol = "This is the text that is going to change"

   End If

End Sub

then you can call it directly from the control's AfterUpdate property: =UpdateCtl(True).

Simple and fast

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

edited Nov 22 '18 at 10:45

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

answered Nov 22 '18 at 9:58

Patrick Honorez

18.9k564118

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

add a comment |

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

Please consider adding a little explanation to your answer. Code-only answers automatically get flagged as low quality, yours is currently in review

– Erik A
Nov 22 '18 at 10:29

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Argthtjtr