Solved: Concatenate text strings to reference objects and controls in Access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Alaric_

Thread Starter
Joined
Dec 18, 2005
Messages
17
Okay I did a search and found a couple of threads that might have my answer, but I couldn't understand it well enough to figure it out.

What I need to do is to concatenate some text strings and then use the result to reference objects and controls in my database.

For example:

I have 9 possible text values for a combo box. lets call the combo box CategoryCmbo. Then I have a fixed piece of text, "Report", that I want to tack on the end. So, if the contents of CategoryCmbo are "Carpenter", the end result of the two stings being added together should be "CarpenterReport".

How would I use that end result in a reference if it was the name of a control on a form?

How would I use that end result in a reference if it was the name of a query?

-Thanks for any help...
-Ray
 

Alaric_

Thread Starter
Joined
Dec 18, 2005
Messages
17
Figured out how to do it for a control on a form... Don't know yet if it is the same for referencing a Query or Table. In the below example I have concatenated a string variable by the name of CategoryCmbo and the word "Report". So, if the string variable is equal to "Carpenter", I have just gone out and set the "CarpenterReport" control on the form to Enabled = True.


Forms![YourFormNameFrm]!(CategoryCmbo & "Report") .Enabled = True
 
Joined
Aug 5, 2005
Messages
3,086
The only way to use strings as references to objects is to use the Index argument of the object's collection. The index may be an integer index or a named (string) index. For example, you may refer to forms in your database like so:

Forms(0)
Forms(1)
Forms(2)
Forms(n)

Or, more commonly, you may refer to forms like so:

Forms("frmSwitchboard")
Forms("frmDataEntry")
Forms("frmDataView")
Forms("frmClose")

So the code you use will be somewhat dependent on the collection involved. Every collection in VBA has an Index property accessible through the Collection(Index) model.

Let's take, for example, a control on a form. I should say here that this is an abnormal way of achieving this effect because it is relatively inefficient. Generally to make a collection of controls on a form, one would either give them identical names with an incrementing integer trailing (e.g., cboComboBox1, cboComboBox2, cboComboBox3, ... cboComboBoxn) or use the control's Tag property to give every control in the collection an identical tag. Anyway.

Say you have four controls on a form: a combo box cboList and three text boxes txtNameLast, txtNameFirst, and txtNameMiddle. If the text boxes were disabled by default, and you wanted to enable a text box depending on the value of the combo box (First, Middle, or Last), you could do something like this:

Code:
Private Sub cboList_AfterUpdate()
  Const TEXTBOX_BASE As String = "txtName"

  With Me
    .Controls(TEXTBOX_BASE & .cboList.Value).Enabled = True
  End With
End Sub
In this very limited sample, we use the Controls collection to reference a specific control based on its string name.

There is also a Reports collection much like the Forms collection, a QueryDefs collection, and a TableDefs collection, if you're interested. You must use the QueryDefs collection if you wish to manipulate a QueryDef object using its name as the identifier (there is no Queries collection).

HTH, post back if you need more info.

chris.
 
Joined
Aug 5, 2005
Messages
3,086
Wow, I didn't know you could do that using a bang and parentheses. I don't know how portable that is. I'm pretty sure good practice is to use the collection and index.

Anyway.

chris.
 

Alaric_

Thread Starter
Joined
Dec 18, 2005
Messages
17
I am sure your method is the only way to make the variable reference scheme global. My method will only work within the module that contains the code. So mine will work for forms and the like, but yours could be used from information gathered anywhere in the Database.

For now I am going to mark this as solved even though I want to work on the referencing of queries and tables and see what I can come up with.

:)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top