make field name in code variable for vb code loop

Posted on 2005-04-07
Last Modified: 2012-05-05
I have a form with 20 fields on it with the following names:  COL_1, COL_2...COL_20  
I need to check each one of these dropdown fields to see if a predefined text has been selected.  Problem is I can't figure out how to make the number part of the field name a variable or what to Dim it as.

    Dim ColumnNum As ??????, intCount As Integer
    intCount = 1
    Do Until intCount = 20
        ColumnNum = COL_ & intCount
        If ColumnNum = "CompToRcvd" Then
            MsgBox "yes"
            MsgBox "no"
        End If
        intCount = intCount + 1
Question by:woodan1
    LVL 50

    Accepted Solution

    Are these fields in the underlying recordset, or are they controls on the form?  It makes a difference.  Here's a basic template:

    For x = 1 to 20
        Me.Controls("COL_" & x) '<--- references a control COL_x
        Me.Recordset.Fields("COL_" & x)  '<--- references a field in the underlying data
    LVL 4

    Expert Comment

    Dim c As Control
    For Each c In Me.Controls
    If c.ControlType = acTextBox Then
        MsgBox & " " & c.Value
    End If
    Next c

    if combo use:

    if list box

    Author Comment

    That's the answer.  Thanks.

        Dim x As Integer
        x = 1
        For x = 1 To 20
            If Me.Controls("COL_" & x) = "CompToRcvd" Then
                Debug.Print "Yes " & "COL_" & x
                Debug.Print "No " & "COL_" & x
            End If

    Author Comment

    Sorry McDougall, didn't catch your response until after I accepted.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now