How to use a variable string to reference an existing object name on a form

Question

I have an access 2007 form with 18 textbox controls in two columns. They are to add colors and color codes to a vendor table when entering a new style. Column one's controls are named Color1, Color2, etc, and column two's controls are named Code1, Code2, etc.

The code below goes through the textbox controls on the form and gets the control's name and value fine.

I do realize I'll need to put another if/then type statement to only perform the actions if the control selected by the case statement has a name beginning with "color". I'll add that later.

If you see the sql statement, I'm trying to insert a new color/code combination by calling up the control with the name beginning with "code" and ending with the number associated with the "color" textbox currently being evaluated in the case select.
TL;DR, when textbox "color1" is being evaluated, I want the value of what is in "code1" to be a part of the sql statement as well. The number at the end is the link between the two.

I'm sure I'm probably using an incorrect reference for ctrlcode or something like that, but if someone knows what I'm trying to do and could help me get this working, I'd appreciate it.

Dim sql As String, ctrlcode As Control  'Also tried as Object

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox           'Only searching textboxes
    'Debug.Print ctl.Name, ctl.Value
    If Not IsNull(ctl.Value) Then
        Debug.Print ctl.Name, ctl.Value, Right(ctl.Name, 1)
        **Next line fails   Runtime error 424, object required**
        Set ctrlcode = frm.Controls("code" & Right(ctl.Name, 1))
        Debug.Print ctrlcode.Value, "ctrlcode value"

        sql = "INSERT INTO tblVendorColors (Stylecode, color, colorcode, coloravail)" _
            & " VALUES (Styletext, '" & ctl.Value & "', '" & ctrlcode.value & "', true)"
    Debug.Print sql
    End If

Case Else
    ' pass
End Select

Styletext is the name of the form control textbox ( rather than "text1") containing the style name. — RunningWithScissors
– RunningWithScissors, Commented Nov 8, 2015 at 1:56

HansUp · Accepted Answer · 2015-11-08 16:10:34Z

4

Use For i = 1 To 9 to loop through your text box pairs. Then you can reference the text box values as Me.Controls("Color" & i).Value and Me.Controls("Code" & i).Value

Instead of building and executing an INSERT, you can add a row to a recordset and store the values there.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Long

Set db = CurrentDb
Set rs = db.OpenRecordset("tblVendorColors", dbAppendOnly)
With rs
    For i = 1 To 9
        If Not IsNull(Me.Controls("Color" & i).Value) Then
            .AddNew
            !Stylecode.Value = Me!Styletext.Value
            !Color.Value = Me.Controls("Color" & i).Value
            !colorcode.Value = Me.Controls("Code" & i).Value
            !coloravail.Value = True
            .Update
        End If
    Next
    .Close
End With

edited Nov 8, 2015 at 16:10

answered Nov 8, 2015 at 13:42

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andre Over a year ago

+1 That's a lot cleaner. But I wonder: why do you use .Value (on both sides of the assignment)? Force of habit, or does it solve a problem that could come up when relying on .Value being the default property of recordset fields and controls?

HansUp Over a year ago

My preference is to explicitly include .Value when I want .Value. It's not really a benefit here, but doesn't cause harm either. ;-) I find it a useful habit because I'm already prepared for situations where the default property is not what's referenced ... for example TypeName(!Color) would return "Field2" instead of the field's .Value

RunningWithScissors Over a year ago

I love both code answers here. In hindsight, going with the for loop would be cleaner and if building it to more than 9 boxes a for loop avoids having to do more manipulation in finding the number in the text string of the control name. I could also rename the single digit boxes to 01 through 09 then 10 and up.. etc, but even still, the code would just get bloated without need going through the each ctl method. Thanks for the advice.

Andre · Accepted Answer · 2015-11-08 09:32:59Z

2

    **Next line fails   Runtime error 424, object required**
    Set ctrlcode = frm.Controls("code" & Right(ctl.Name, 1))

Instead of frm., use Me..

You should put Option Explicit at the start of all modules, and set the Require Variable Declaration option in the VBA Editor. This forces you to Dim all variables and find such errors already at compile time.

In addition, if Styletext is a control, you need to treat it as ctl and ctrlcode when building your SQL string.

" VALUES ('" & Me.Styletext & "', '" & ctl.Value ...

answered Nov 8, 2015 at 9:32

Andre

27.7k7 gold badges43 silver badges85 bronze badges

1 Comment

RunningWithScissors Over a year ago

Thanks Andre. I did already fix up my Styletext part (thanks for the reminder) and it is the exact answer I was looking for. I'm surprised it was that close... me vs frm. Good call on the explicit/require declarations. That being said, I'll probably go with a for loop like Hans has up there. I was originally going to avoid that type in case I built up the form options at some point, but with the need to check for "color" vs "code" on each box, it now seems less efficient. And changing the number in the for declaration isn't all that hard if I make changes.

Collectives™ on Stack Overflow

How to use a variable string to reference an existing object name on a form

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related