Code for using Checkbox value to write to associative table

It works! I must've had something in there wrong. Thanks for the assistance everyone! Last step for me is to clear out the values when someone looks at a new record. Is there a quick way to do that?
 
Use the loop again and set the values to false.
 
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
 
Try

Code:
Private Sub GoToNextRecordButton_Click90()
Dim x As Integer
For x = 1 To 2
   If Me.Controls("Chk" & x) = True Then
      Me.Controls("Chk" & x) = False
   End If
Next x
Me.Requery
End Sub
 
On the other hand you don't care it they are true. Why not just:

Code:
Private Sub GoToNextRecordButton_Click90()
Dim x As Integer
For x = 1 To 2
      Me.Controls("Chk" & x) = False
 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
 
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
 
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:

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 Sub

Note that the Me.Requery was taken out.
 
If you go to new record why would you need to requery ?
 
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 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!
 
also, is it possible to build in the functionality to update the values in the checkboxes when switching records? or does that code need to be separate from the code to delete records from the table?
 
also, is it possible to build in the functionality to update the values in the checkboxes when switching records? or does that code need to be separate from the code to delete records from the table?

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 Sub
 
Okay thanks! I will add in the additional check boxes so that it works smoothly with no errors.
 
Okay thanks! I will add in the additional check boxes so that it works smoothly with no errors.

Hold 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.
 

Users who are viewing this thread

Back
Top Bottom