Changing Control Sources and Visibility in a Subform

[mcclurm]

I have been trying to use VBA to edit the controlsource and visibility of text boxes in a subform. I haven't been able to come up with anything that works. Can someone give me an example of how to change the controlsource of a textbox in a subform?

I'm guessing setting the visibility to false would work pretty much the same way.

Also, does the subform need to be in Form view or will that work in datasheet view. I've never done this kind of dynamic change to a subform, so any help is appreciated.

 

[kjv1611]

I have been trying to use VBA to...

So, what have you tried?

Give us some examples of what you tried? Did you get any errors with your code? Did you get results different than what you were expecting? What did you try, and what were the results?

--

"If to err is human, then I must be some kind of human!" -Me

 

[GKChesterton]

Here are some snips.

When form opens, use OpenArgs to decide if a button should be visible:

Private Sub Form_Load()
    If Me.OpenArgs Like "" Then
        Me!btnMakeMeFake.Visible = True
        Me!coloring.ControlSource = "real_color"
    Else
        Me!btnMakeMeFake.Visible = False
        Me!coloring.ControlSource = "fake_color"
    End If
End Sub

Make a bunch of fields invisible:
This doesn't go in a form. But it's fun to play with. Let's say you have forms and some fields are named "boxMeerkat ..." Run this and they will all be turned invisible. Run it again with the code changed to "Visible=True," and they're visible again.

Private Sub FormControls_boxID_Visible_or_Not()
    ''  VBA Help information for ControlType is useful here
    Dim obj As AccessObject
    Dim ctl As Control
    Dim varType As Variant
    For Each obj In CurrentProject.AllForms
        DoCmd.OpenForm obj.Name, acDesign, , , , acHidden
        For Each ctl In Forms(obj.Name).Controls
            varType = ctl.ControlType
            If varType = acTextBox Then
              If ctl.Name Like "boxMeerkat*" Then
               Debug.Print ctl.ControlSource & "   " & ctl.Name & "  " & obj.Name
                ctl.Visible = False
              End If
            End If
        Next ctl
        
        DoCmd.Close acForm, obj.Name, acSaveYes
    Next obj
End Sub

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​