Referencing a field caption via VBA

[jpstroud]

Is it possible to reference a field caption via VBA? I have a series of checkboxes with captions that I would like to compile into 3 different text boxes. Sample output would be similar to "Academic, Public, Special" when the options are Academic, Government, Public, and Special. There may be an easier way to do this, but for now, since I want three sets of checkboxes output the same way, I thought to write one procedure:

    Dim ctlFieldsArray As Variant, i As Variant, db As Database, rst As DAO.Recordset, ctlField As Field
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblProjects", dbOpenDynaset)
    
    CheckList = ""
    
    Select Case ListType
        Case "Benetype"
            ctlFieldsArray = Array("projBeneTypeAcad", "projBeneTypePub", "projBeneTypeSchool", "projBeneTypeSpec", "projBeneTypeOther")
        Case "Bene"
            ctlFieldsArray = Array("projBeneLibStaff") ', rst![projBeneLibUsers], rst![projBeneSpecific], rst![projBeneOther])
        Case "Activities"
            ctlFieldsArray = Array("projActStaffTraining") ', rst![projActUserTraining], rst![projActGrants], rst![proActDev], rst![proActDemo], rst![proActOther], rst![proActDetail])
    End Select
    
    For Each i In ctlFieldsArray
        Set ctlField = rst.Fields(i)
        If ctlField = True Then
            If CheckList = "" Then
                CheckList = ctlField.Caption
            Else
                CheckList = "; " & ctlField.Caption
            End If
        End If
    Next i
    
    rst.Close
    db.Close

Now, I'm used to working with forms, not reports, so some of my declarations may be off. Then again, maybe I just can't refer to the Caption property of a field (though the documentation points to the fact that I can).

The specific error I get is a "Method or Data member not found" on the CheckList = ctlField.Caption line.

Any thoughts? TIA

 

[cghoga]

Make sure to reference the checkbox "label" rather than the fieldname itself.

hope this helps.

 

[jpstroud]

There is no label, the checkboxes won't appear on the report (unless I drop them in and hide them). I have the code opening a similar dataset to pull the info I need.

 

[dhookom]

I don't understand entirely what you are attempting to accomplish with the rs etc. However, if you want to reference the caption of a label control and that label control is a child of another control such as a text box or check box, try:

ctlField.Controls(0).Caption
or
ctlField.Controls(1).Caption

One of the above should work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpstroud]

Essentially, I want to compile a list of options that were checked into one text box instead of having to list out each and every check box. Instead of having:

___ Academic
_X_ Public
_X_ Government
___ Special

on the report, I want one field that says:

Public; Government

Any thoughts on how to go about this? I'm willing to start from scratch on the coding if that's what's necessary. However, I have three sets of checkboxes, so if I do use code, I'd like a generic one that I can apply to the other sets as well.

 

[dhookom]

I believe your issue is one of normalization. You have data contained in field names. What happens if you need to add another "category"? Do you add another field and then modify your reports, forms, queries, code,...?

I would create a union query of these fields:
SELECT ID, "Academic" as Category
FROM tblYourTable
WHERE Academic = -1
UNION ALL
SELECT ID, "Public"
FROM tblYourTable
WHERE Public = -1
UNION ALL
--ect---

You then create an expression using the generic concatenate function found at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The best solution though is to first normalize your table structure if possible.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpstroud]

The data that I have has certain options. I've normalized everything as much as I can while still staying in the guidelines that have been set forth. Since I'm not the end user on this, I have to stick with what they will be able to understand and utilize. The form that sets the data requires checkboxes for these options to be set (requires as in my boss wants them). I've tried setting up alternative ways, but they wouldn't have it.

As for the data as field names... they aren't. It was just convenient that the captions equaled what I wanted as the final result. They need to be able to select each option they want for the particular project, and as a display on the report, have the options selected earlier show as a semicolon delimited list.

I could do a Select Case for each field which has been checked, and then assign the value based on the current field name being evaluated, but I was hoping for a more elegant solution.

 

[dhookom]

It doesn't matter what your field names are, I still find the structure un-normalized. As I suggested earlier, I hope you don't have to add any more options. If you can't change the structure then I would still consider using the union query for normalizing and the concatenate function for displaying the "checked" fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jpstroud]

I've normalized as much as I'm able given the constraints.

The issue itself is merely a matter of attempting to concantinate stings together. What would you suggest that would help me accomplish this?

 

[dhookom]

My two most recent posts suggest using a union query and the generic concatenate function from a supplied url.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]