Assigning Function to multiple Textboxes

[Ian Mac]

All,

I'm fairly new to all this and have hit a wall, I have the following code:

Sub Update()

Dim C1, C2, C3, Col
Dim USeat As Integer
Dim YesNo As Boolean
Dim ctlCurrentControl As Control, strControlName As String, strControl As String
Set ctlCurrentControl = Screen.ActiveControl

Me.comboProgram.SetFocus
If Me.comboProgram.Text = "" Then
MsgBox "You need to select A Program and Campaign before selecting any Seats.", vbCritical, "Select Seats!"
Exit Sub
Else

strControlName = ctlCurrentControl.Value

YesNo = DLookup("[Update]", "tbl_Seat_Update", "[Seat] = " & strControlName)
C1 = DLookup("[Colourindex1]", "tbl_Campaign", "[Campaign_ID] = " & Me!comboProgram.Value)
C2 = DLookup("[Colourindex2]", "tbl_Campaign", "[Campaign_ID] = " & Me!comboProgram.Value)
C3 = DLookup("[Colourindex3]", "tbl_Campaign", "[Campaign_ID] = " & Me!comboProgram.Value)

If YesNo = True Then
    YesNo = False
    ctlCurrentControl.BackColor = RGB(192, 192, 192)
    Else
    YesNo = True
    ctlCurrentControl.BackColor = RGB(C1, C2, C3)
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Seat_Update SET tbl_Seat_Update.[Update] = " & YesNo & " WHERE (((tbl_Seat_Update.Seat)=" & strControlName & "));"
DoCmd.SetWarnings True

End If

End Sub

This is ment to run on the On-Click Event on any of 448 textboxes.
I have it assigned to 8 currently and it works dandy.
The problem is I now have to put

Private Sub T1_Click()
Update
End Sub

448 times and don't fancy or see the need doing.

So I thought I could make it a Function, I changed all the Me! to the form name and now have the following:

Function Update()

Dim C1, C2, C3, Col
Dim USeat As Integer
Dim YesNo As Boolean
Dim ctlCurrentControl As Control, strControlName As String, strControl As String
Set ctlCurrentControl = Screen.ActiveControl
Dim frmInputSeatUsage As Form

[b]frmInputSeatUsage!comboProgram.SetFocus[/b]
If frmInputSeatUsage!comboProgram.Text = "" Then
MsgBox "You need to select A Program and Campaign before selecting any Seats.", vbCritical, "Select Seats!"
Exit Function
Else

strControlName = ctlCurrentControl.Value

YesNo = DLookup("[Update]", "tbl_Seat_Update", "[Seat] = " & strControlName)
C1 = DLookup("[Colourindex1]", "tbl_Campaign", "[Campaign_ID] = " & frmInputSeatUsage!comboProgram.Value)
C2 = DLookup("[Colourindex2]", "tbl_Campaign", "[Campaign_ID] = " & frmInputSeatUsage!comboProgram.Value)
C3 = DLookup("[Colourindex3]", "tbl_Campaign", "[Campaign_ID] = " & frmInputSeatUsage!comboProgram.Value)

If YesNo = True Then
    YesNo = False
    ctlCurrentControl.BackColor = RGB(192, 192, 192)
    Else
    YesNo = True
    ctlCurrentControl.BackColor = RGB(C1, C2, C3)
End If

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tbl_Seat_Update SET tbl_Seat_Update.[Update] = " & YesNo & " WHERE (((tbl_Seat_Update.Seat)=" & strControlName & "));"
DoCmd.SetWarnings True

End If

End Function

And now I get the message, 'Object variable or With block variable not set'. At the line set.

Anyone have any ideas on this , OR is there an easier way round my problem.

Many thanks,

 

[Paul_Bricker]

You Dim your form variable but you don't set it anywhere.
Dim frmInputSeatUsage As Form

You need to add
Set frmInputSeatUsage = Forms!YourFormNameHere

That should clear your error.
Paul

 

[Ian Mac]

You Dim your form variable but you don't set it anywhere.
Dim frmInputSeatUsage As Form

You need to add
Set frmInputSeatUsage = Forms!YourFormNameHere

That should clear your error.
Paul

Excellent Thanks Very Much.
There are just to many things too look up in the help files, made even harder if you don't know what your looking for.

Thanks Again

 

[Paul_Bricker]

Microsoft doesn't go out of their way to make the error messages relevant to the problem either. That particular message covers a multitude of errors that don't bare a lot of resemblance to each other.
Glad it helped.
Paul