GingGangGoo
Registered User.
- Local time
- Today, 13:16
- Joined
- Dec 14, 2010
- Messages
- 121
I have a set of labels and checkboxes set up to work like a list box. Was working fine, until I needed to add a couple more checkboxes. I got everything set, but now the afterupdate is throwing an error message.
Here is the event that fires oncurrent:
-----------------------------------------
Private Sub Form_Current()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim X As Integer
Dim ctl As Control
'enable or disable all the checkboxes
'if a new record, Not Me.NewRecord = False
'if not a new record, Not Me.NewRecord = True
EnableDisableCheckBoxes Not Me.NewRecord
'Hide all checkboxes so that only those needed will be displayed
HideAll
'Check if it's a new record
'create the SQL statement for all the roles in tblRole
strSQL = "SELECT tblRole.RoleID, tblRole.RoleName FROM tblRole ORDER BY "
strSQL = strSQL & "tblRole.RoleName"
Set rs = CurrentDb.OpenRecordset(strSQL)
'loop through all the records, populating the Tag and Caption as needed and make
'the necessary controls visible
With rs
Do While Not .EOF
X = X + 1
'only 3 checkboxes available. This is the biggest problem with this method.
'The checkboxes and labels must be created ahead of time
If X > 5 Then
MsgBox "Too many items in the list. The form must be modified" & _
Chr(13) & Chr(10) & "to allow more than 5 items."
Exit Do
End If
'populate the Tag property with the RoleID so it can be used later
'to add/remove records
With Me("chk" & X)
.Tag = rs!RoleID
.Visible = True
End With
'populate the caption of the label so that the user will know which item the
'checkbox applies to
With Me("lbl" & X)
.Caption = rs!RoleName
.Visible = True
End With
.MoveNext
Loop
'always Close open recordsets...
.Close
End With
'Now populate the check boxes accordingly
'Open a recordset of all items for this person
If Not Me.NewRecord Then
strSQL = "Select RoleID From tblConRole Where ConID = " & Me.ConID
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .EOF And Not .BOF Then
.MoveFirst
Do While Not .EOF
'loop through all the controls to find the correct one to mark 'True'
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If CLng(ctl.Tag) = rs!RoleID Then
ctl.Value = True
'exit the loop to save time once it is found
Exit For
End If
End If
Next ctl
.MoveNext
Loop
End If
'always Close open recordsets...
.Close
End With
'and set them to Nothing when you are completely through with them to avoid memory leaks
Set rs = Nothing
End If
End Sub
Private Sub txtSentry_GotFocus()
'save the record if it is 'dirty'
If Me.Dirty Then Me.Dirty = False
'call form_current to display the listboxes
Call Form_Current
End Sub
Private Function EnableDisableCheckBoxes(EnableIt As Boolean)
Dim X As Integer
For X = 1 To 5
Me("chk" & X).Enabled = EnableIt
Next X
End Function
Private Sub HideAll()
Dim X As Integer
For X = 1 To 5
Me("chk" & X).Visible = False
Me("chk" & X).Value = False
Me("lbl" & X).Visible = False
Next X
End Sub
Public Function AddRemoveRecord()
Dim X As String
Dim strSQL As String
'determine the 'number' of the check box that was updated
X = Right(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 5)
If Me("chk" & X) Then
'if true, add it
strSQL = "INSERT INTO tblConRole ( ConID, RoleID ) SELECT " & Me.ConID
strSQL = strSQL & " AS Expr1, " & Me("chk" & X).Tag & " AS Expr2"
Else
'if false, delete it
strSQL = "Delete tblConRole.* From tblConRole WHERE ConID = " & Me.ConID
strSQL = strSQL & " AND RoleID = " & Me("chk" & X).Tag
End If
CurrentDb.Execute strSQL
End Function
------------------------------
The original code was exactly the same except where it says 5, or >5 it said 3. I updated tblRole with the new roles as well.
In the afterupdate of each checkbox I have: =AddRemoveRecord()
When I click on a checkbox I get this message:
The expression Afterupdate you entered as the event property setting produced the following error: Invalid call or argument.
I just can't figure out how to fix this. And I know that though it's unlikely, if I need to add a role in the future, I'll have to go back in an change the code again, so I'd like to not make the same mistake.
Thanks
Here is the event that fires oncurrent:
-----------------------------------------
Private Sub Form_Current()
Dim strSQL As String
Dim rs As DAO.Recordset
Dim X As Integer
Dim ctl As Control
'enable or disable all the checkboxes
'if a new record, Not Me.NewRecord = False
'if not a new record, Not Me.NewRecord = True
EnableDisableCheckBoxes Not Me.NewRecord
'Hide all checkboxes so that only those needed will be displayed
HideAll
'Check if it's a new record
'create the SQL statement for all the roles in tblRole
strSQL = "SELECT tblRole.RoleID, tblRole.RoleName FROM tblRole ORDER BY "
strSQL = strSQL & "tblRole.RoleName"
Set rs = CurrentDb.OpenRecordset(strSQL)
'loop through all the records, populating the Tag and Caption as needed and make
'the necessary controls visible
With rs
Do While Not .EOF
X = X + 1
'only 3 checkboxes available. This is the biggest problem with this method.
'The checkboxes and labels must be created ahead of time
If X > 5 Then
MsgBox "Too many items in the list. The form must be modified" & _
Chr(13) & Chr(10) & "to allow more than 5 items."
Exit Do
End If
'populate the Tag property with the RoleID so it can be used later
'to add/remove records
With Me("chk" & X)
.Tag = rs!RoleID
.Visible = True
End With
'populate the caption of the label so that the user will know which item the
'checkbox applies to
With Me("lbl" & X)
.Caption = rs!RoleName
.Visible = True
End With
.MoveNext
Loop
'always Close open recordsets...
.Close
End With
'Now populate the check boxes accordingly
'Open a recordset of all items for this person
If Not Me.NewRecord Then
strSQL = "Select RoleID From tblConRole Where ConID = " & Me.ConID
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .EOF And Not .BOF Then
.MoveFirst
Do While Not .EOF
'loop through all the controls to find the correct one to mark 'True'
For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox Then
If CLng(ctl.Tag) = rs!RoleID Then
ctl.Value = True
'exit the loop to save time once it is found
Exit For
End If
End If
Next ctl
.MoveNext
Loop
End If
'always Close open recordsets...
.Close
End With
'and set them to Nothing when you are completely through with them to avoid memory leaks
Set rs = Nothing
End If
End Sub
Private Sub txtSentry_GotFocus()
'save the record if it is 'dirty'
If Me.Dirty Then Me.Dirty = False
'call form_current to display the listboxes
Call Form_Current
End Sub
Private Function EnableDisableCheckBoxes(EnableIt As Boolean)
Dim X As Integer
For X = 1 To 5
Me("chk" & X).Enabled = EnableIt
Next X
End Function
Private Sub HideAll()
Dim X As Integer
For X = 1 To 5
Me("chk" & X).Visible = False
Me("chk" & X).Value = False
Me("lbl" & X).Visible = False
Next X
End Sub
Public Function AddRemoveRecord()
Dim X As String
Dim strSQL As String
'determine the 'number' of the check box that was updated
X = Right(Screen.ActiveControl.Name, Len(Screen.ActiveControl.Name) - 5)
If Me("chk" & X) Then
'if true, add it
strSQL = "INSERT INTO tblConRole ( ConID, RoleID ) SELECT " & Me.ConID
strSQL = strSQL & " AS Expr1, " & Me("chk" & X).Tag & " AS Expr2"
Else
'if false, delete it
strSQL = "Delete tblConRole.* From tblConRole WHERE ConID = " & Me.ConID
strSQL = strSQL & " AND RoleID = " & Me("chk" & X).Tag
End If
CurrentDb.Execute strSQL
End Function
------------------------------
The original code was exactly the same except where it says 5, or >5 it said 3. I updated tblRole with the new roles as well.
In the afterupdate of each checkbox I have: =AddRemoveRecord()
When I click on a checkbox I get this message:
The expression Afterupdate you entered as the event property setting produced the following error: Invalid call or argument.
I just can't figure out how to fix this. And I know that though it's unlikely, if I need to add a role in the future, I'll have to go back in an change the code again, so I'd like to not make the same mistake.
Thanks