public sub sos

[thewiseguy]

i'm new to VBA so please go easy on me...

what am i doing wrong with the below code? it keeps bringing up an error message saying "Invalid use of Null".

Private Sub check1_BeforeUpdate(Cancel As Integer)

Call CheckBox(Me.check1, Me.category1, Me.variable1, Me.crit1a, _
Me.crit1b, Me.crit1c, Me.op1)

End Sub

followed by:

Public Sub CheckBox(check As String, category As String, variable As String, _
crita As String, critb As String, critc As String, op As String)

    If (check = True) Then
        category.Enabled = True
        variable.Enabled = True
        crita.Enabled = True
        critb.Enabled = True
        critc.Enabled = True
        op.Enabled = True
    Else
        variable.Enabled = False
        category.Enabled = False
        crita.Enabled = False
        critb.Enabled = False
        critc.Enabled = False
        op.Enabled = False
    End If

End Sub

 

[Rich]

Your code is in the wrong place and rather convoluted

Form_Current
If (check = True) Then
category.Enabled = True
variable.Enabled = True
crita.Enabled = True
critb.Enabled = True
critc.Enabled = True
op.Enabled = True
Else
variable.Enabled = False
category.Enabled = False
crita.Enabled = False
critb.Enabled = False
critc.Enabled = False
op.Enabled = False
End If
End Sub


in the After Update of check
Call Form_Current

 

[thewiseguy]

I need to call on this "Public Sub check" routine several times and in each case it will refer to a new set of fields:

for example, in the code above, it must operate for Me.check1, Me.category1 ... Me.op1

then later, I would like it to work on Me.check2, Me.category2 ... Me.op2

and so on...

I could just repeat the code individually for the 1's, 2's etc but this will start mounting up to a lot and I would like to keep it neat.

Hope that makes sense.

The help so far is much appreciated.

 

[Rich]

I'm not sure I follow exactly what you're trying to do, but if the deciding factor for the enablement of any of the fields is only decided by the check box then a simple
For each Ctrl in Me.Controls is much easier, either way you cannot use the BeforeUpdate event to change text box properties

 

[thewiseguy]

i must apologise - my wording/explanation is confusing even me
i hope this will make sense and you may be able to help me.

i have a form with many rows of unbound fields (each row being a mixture of check/combo/textboxes)

for row 1 i have fields called check1, category1 ... op1
for row 2 i have fields called check2, category2 ... op2
and so on...

now everytime a checkbox is checked, i would like to call upon a subroutine that will only operate to all fields in that row (i.e. if check5 is checked, the subroutine only opperates upon all fields ending with '5')

is this possible and was i on the right path or barking up the wrong tree?

 

[Rich]

My first question would be to ask why they are Unbound?

 

[thewiseguy]

they are unbound for now
(it will eventually/hopefully form the front end of a generic query engine)
i was gonna get onto that later...

 

[thewiseguy]

crumbs - figured out what i needed to do...i think.

initially:

Public qn As Integer, check As String, category As String, variable As String, crita As String, critb As String, _
critc As String, op As String

then:

Public Sub CheckBox(ByVal qn)

    check = "check" & CStr(qn)
    category = "category" & CStr(qn)
    variable = "variable" & CStr(qn)
    crita = "crit" & CStr(qn) & "a"
    critb = "crit" & CStr(qn) & "b"
    critc = "crit" & CStr(qn) & "c"
    op = "op" & CStr(qn)

    If (Me(check) = True) Then
        Me(category).Enabled = True
        Me(variable).Enabled = True
        Me(crita).Enabled = True
        Me(critb).Enabled = True
        Me(critc).Enabled = True
        Me(op).Enabled = True
    Else
        Me(category).Enabled = False
        Me(variable).Enabled = False
        Me(crita).Enabled = False
        Me(critb).Enabled = False
        Me(critc).Enabled = False
        Me(op).Enabled = False
    End If

End Sub

and then call on Public Sub with Call CheckBox(**stick in row number here**)

this is probably obvious to many of you but i'm still learning

thank you for the replies!!!