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

Syntax to reference variables in field names

P: 2
Access 2007
I have a group of button called BUTT01, BUTT02, BUTT03... BUTT99. Instead of writing code over and over for each of their On_Click events I would like to use the variable in the field name. I imagine the code would be something like:

Expand|Select|Wrap|Line Numbers
  1. private sub Butt01_click
  2. aaa=1
  3. cshort(aaa)
  4. end sub
  6. private sub Butt02_click
  7. aaa=2
  8. cshort(aaa)
  9. end sub
  11. private sub short(aaa)
  12. Fld="fieldname" &aaa
  13. if forms![Main]![Fld]=true then xxxx else yyyy
  14. more code options referencing combinations of aaa in field names
  15. more code options referencing aaa
  16. aaa=aaa+1
  17. more code options referencing aaa
  18. .
  19. .
  20. end sub
I'm not really explaining this very well so fingers crossed. I have tried loads of combinations to crack this but fail everytime. please help if you can.
Sep 9 '12 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 5K+
P: 5,397
no, not very clear.

You are stuck writing some code for every button's onclick event... at least to call a function or sub. Why you have so many buttons... wow... that may be another topic.

However, from the example code, I think you are on the right path. As I think that you've tried to show a function/sub that could parse a passed value and either thru a select case or a series of ifthens determine what actions to take. The Function/sub could be within the form or as a standalone; however, in each case be aware of the scope of the values/field/recordsets etc...

You should have no need to record the passed value anywhere within the form controls unless you are going to do something with a bound record or a calculation etc... it is not possible to determine that from what you have provided.

Sep 9 '12 #2

P: 2
So how if my field name is M01BUTT01 how do I reference it.
I'm trying to do something like:

Expand|Select|Wrap|Line Numbers
  1. ccc="M" &aaa &"BUTT" &bbb
  2. me.ccc.visible=true
the ccc bit works if I want a string but when I try and use it to reference a field name it just doesnt happen for me...

I think what I am trying to do is Concantenate a number of variables to make up field names. I just need to know the syntax
Sep 9 '12 #3

Expert Mod 5K+
P: 5,397
You're not helping us help you.

Controls either are bound or unbound.
The name of the control is usually either the same as the bound field in the recordset or as assigned by the programmer.
I've never seen a command button bound to a record source.

Concantation for VBA is the same as for any BASIC language... use the plus or ampersign.

The "code" you've shown so far is not helpful... show us your actual code.

Sep 9 '12 #4

Expert Mod 10K+
P: 12,365
You can use the Me.Controls() collection to access a control by its name. Or you can just pass the control directly to the sub.
Sep 10 '12 #5

Expert Mod 2.5K+
P: 3,282

On the button's On Click event, you can also set it to (to use your example)

Expand|Select|Wrap|Line Numbers
  1. = cshort(aaa)
and the function will execute based on the value you send to it. I have a form with 60 buttons on it, and this is how I save tons of code........
Sep 10 '12 #6

Post your reply

Sign in to post your reply or Sign up for a free account.