Code for using Checkbox value to write to associative table (1 Viewer)
- Thread starter tbbrown32
- Start date
Below is the code I am using for clearing out the check boxes. It doesn't seem to work though, and the checkboxes stay filled.
Private Sub GoToNextRecordButton_Click90()
Dim x As Integer
For x = 1 To 2
If Me.Controls("Chk" & x) = True Then
Me.x = False
End If
Next x
Me.Requery
End Sub
Private Sub GoToNextRecordButton_Click90()
Dim x As Integer
For x = 1 To 2
If Me.Controls("Chk" & x) = True Then
Me.x = False
End If
Next x
Me.Requery
End Sub
Hmmm... I put both codes in to test out both, and it should update, but it still doesn't clear out the check boxes when I go to a different record. And actually, as I'm thinking about it, I probably want to query the table instead to pull in any existing values that may be present when I switch to a record that has already been filled out.
You're right. For editing you are going to have to set the checkboxes to true for the records that exist in the table, but this clearing of the record was for new records and this code should be ok for that. Where did you put this code? I think it could go in the form's current event surrounded by
If Me.NewRecord Then
' the code
End If
If Me.NewRecord Then
' the code
End If
Right now, I've just got the code set to run when a button is clicked, but I think I'm still doing this incorrectly as it isn't having the desired effect.
Private Sub NewRecordButton_Click()
If Me.NewRecord Then
Dim x As Integer
For x = 1 To 2
Me.Controls("Chk" & x) = False
Next x
Me.Requery
End If
End Sub
Private Sub NewRecordButton_Click()
If Me.NewRecord Then
Dim x As Integer
For x = 1 To 2
Me.Controls("Chk" & x) = False
Next x
Me.Requery
End If
End Sub
Right. That's not working; not sure why not. You can put it in the New Buttons event which after converting to VBA and inserting the code looks like:
Note that the Me.Requery was taken out.
Code:
Private Sub Command100_Click()
On Error GoTo Command100_Click_Err
' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
' <UserInterfaceMacro For="Command221" Event="OnClick" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application" xmlns:a="http://schemas.microsoft.com/office/accessservices
' _AXL:/2009/11/forms"><Statements><Action Name="ApplyFilter"><Argument Name="WhereCondition">[DiscoveryDate]=[Forms]![frmNewMain]![Text218]</Argument></Action></Statements></UserInterfaceMacro>
Dim x As Integer
For x = 1 To 2
Me.Controls("Chk" & x) = False
Next x
On Error Resume Next
DoCmd.GoToRecord , "", acNewRec
If (MacroError <> 0) Then
Beep
MsgBox MacroError.Description, vbOKOnly, ""
End If
Command100_Click_Exit:
Exit Sub
Command100_Click_Err:
MsgBox Error$
Resume Command100_Click_Exit
End SubNote that the Me.Requery was taken out.
You don't. That's why I took it out.
That code works perfectly! Thanks for the help! If I still need help with unchecking the check boxes and getting the record to delete out of the table as a result, should I post that in a totally separate thread or just finish it out in this one?
You'll need to open a recordset of the tblIncidentClassifications based on the current InternalIncidentID, loop through the records and set the checkboxes accordingly. DO you want to give that a shot or should I give you some code?
I think this one needs to work in the current event.
I think this one needs to work in the current event.
I attempted it, but I was trying to as an add on to an existing loop so that if the value was false, it would delete the record from the table. The problem with that was that there might not be a corresponding record in the table if the box was never checked in the first place, so my method of doing it isn't really working. If you have a minute to send code, that'd be great as I'm very much over my head with some of this. Thanks!
I'm working on that. The trick is going to be to get this to work in the form's current event. I'll try to get you something within an hour.
The delete is a different issue, but should be the easy one.
Here some code shown in the form's current event and it works except for where the check boxes don't exist. So you'll have to add more check box or modify the data in the tblIncidentClassifications to see this work. We still need to figure out the big picture here. In the end this need to update the table automatically some how. I'm thinking about that.
Code:
Private Sub Form_Current()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Dim x As Integer
For x = 1 To 2
Me.Controls("Chk" & x) = False
Next x
Set db = CurrentDb
strSQL = "SELECT ClassificationID FROM tblIncidentClassifications WHERE tblIncidentClassifications.InternalIncidentID = '" & Me.InternalIncidentID & "'"
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
Me.Controls("Chk" & rs!ClassificationID) = True
rs.MoveNext
Loop
rs.Close
db.Close
End SubHold off on the adding of check boxes. I got an idea about another approach that could avoid that. I'll get it to you soon.
Okay thanks! Also I don't know if I mentioned this before or not, I'm going to be doing the same thing for other tabs on the main page. For example, this tab is for classifications, another tab is for Actions and another for Referalls. Basically whatever approach we use I was just planning to duplicate and adjust to code so I can add check boxes to other tabs on the home page as well. I'm not sure if this is pertinent to the solution, but I was just going to adjust the code accordingly later on.
