Access - Form Checkbox to Edit certain records opened in a second Form

Hello all,

I have a Form1 that has a series of 10 checkboxes.  That form is opened by a lead FormA where the user will have 3 control buttons to choose from.  If One Certain control button on FormA is selected this Particular Form1 opens and hides the boxes 10, 9, 8 etc... down to the number of checkboxes required by a value that comes from FormA...    That part is working fine.

Now the user selects the appropriate Checkboxes named CkBox1, CkBox2, 3, 4, 5, etc... to 10 but they can obviously only select from the Visible ones as explained above.  

On that Form1 after selecting one, two, three or however many Checkboxes is appropriate I have a Control Button that Opens a Form2.  When that Form2 opens one particular field needs to be checked for each of the corresponding Checkboxes that got selected on Form1

My inept attempt was CurrentDb.Execute but that adds records... I need to Edit records that are created during the execution of the FormA.

The Particular Form where I am having my error starts with frm_LVLReportingTypeSelect  with the control button selected there as "Lottery Clear Chip" which opens frm_ClearChipSelectMachines  ...  

The Code I attempted to alter instead of INSERT INTO to EDIT was (but as you know won't work)
Private Sub cmdSelectClearChipMachines_Click()
Dim t As Integer
t = 0

    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"

Do Until t = Val(Me.txtLocNbrMachines)
    CurrentDb.Execute "EDIT ShiftReportingLVL(MachineClearChipped) VALUES  (Yes), WHERE ShiftReportingLVL (RptgSeqID, LVLPositionNbr) =(" & Me.txtNewSeqID & "," & t & ")", dbFailOnError
    t = t + 1
    
Loop
    
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Open in new window



I have attached the stripped down db just in case
CkBoxFlowToForm.accdb
wlwebbAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
sorry, did not understand your intention, try this one

Private Sub cmdSelectClearChipMachines_Click()
Dim t As Integer, chkBoxes As String
t = 1


For t = 1 To 10
    If Me("CkBox" & t) = -1 Then
        chkBoxes = chkBoxes & "," & t
    End If
Next
If chkBoxes & "" <> "" Then
    chkBoxes = Mid(chkBoxes, 2)
    Else
    MsgBox "No Checkbox was selected"
    Exit Sub
End If
'
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"
CurrentDb.Execute "Update ShiftReportingLVL set MachineClearChipped=Yes" _
        & " WHERE RptgSeqID=" & Me.txtNewSeqID & " And LVLPositionNbr In(" & chkBoxes & ")", dbFailOnError

    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Open in new window

0
 
Rey Obrero (Capricorn1)Commented:
try this codes

Private Sub cmdSelectClearChipMachines_Click()
Dim t As Integer
t = 0

    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"

Do Until t = Val(Me.txtLocNbrMachines)
'    CurrentDb.Execute "EDIT ShiftReportingLVL(MachineClearChipped) VALUES  (Yes), WHERE ShiftReportingLVL (RptgSeqID, LVLPositionNbr) =(" & Me.txtNewSeqID & "," & t & ")", dbFailOnError
    
    CurrentDb.Execute "Update ShiftReportingLVL set MachineClearChipped=Yes" _
        & " WHERE RptgSeqID=" & Me.txtNewSeqID & " And LVLPositionNbr =" & t, dbFailOnError
    t = t + 1
    
Loop
    
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Open in new window

0
 
wlwebbAuthor Commented:
Well, it doesn't error out like mine.... but I tried various combinations of checked and unchecked boxes on the frm_ClearChipSelectMachines and when it opens the frm_ShiftReportingLVL form it always has the records for 1, 2, 3 & 4 checked regardless of what combination of boxes got checked on the frm_ClearChipSelectMachines Form.

I don't know but maybe it is getting the wrong checkboxes from the original coding that originally inserted the records........

Here is the two subs that make up that procedure just in case:
Private Sub cmdClearChip_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("You selected Clear Chip Reporting.  Are you sure this is a WV Lottery Clear Chip reporting?" & vbNewLine & vbNewLine & _
    "PLEASE NOTE: You must select the particular Machine or Machines that are being Clear Chipped.  Check the box of any Machine that the Lottery is performing a Clear Chip!", vbYesNo, "CLEAR CHIP REPORTING")

    DoCmd.OpenForm "frm_ClearChipSelectMachines", acNormal, , , acFormEdit, acWindowNormal, Me.txtNbrMachines
    Call GenerateLVLMachineLines3(LResponse = vbYes)
    Forms!frm_ClearChipSelectMachines.txtNewSeqID = Me.txtNewSeqID
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub


Private Sub GenerateLVLMachineLines3(IsClearChip As Boolean)
Dim bytCounter As Byte
Dim lngNextSeqID As Long

 lngNextSeqID = Nz(DMax("RptgSeqID", "ShiftReportingLVL") + 1)
 Forms!frm_LVLReportingTypeSelect.txtNewSeqID = Val(lngNextSeqID)
 

Do Until bytCounter = Me.txtNbrMachines
'    CurrentDb.Execute "INSERT INTO ShiftReportingLVL (LVLPositionNbr, RptgSeqID, ClearChipReporting) VALUES (" & bytCounter + 1 & "," & lngNextSeqID & "," & IsClearChip & ")", dbFailOnError
    CurrentDb.Execute "INSERT INTO ShiftReportingLVL (LVLPositionNbr, RptgSeqID) VALUES (" & bytCounter + 1 & "," & lngNextSeqID & ")", dbFailOnError
    
    bytCounter = bytCounter + 1
Loop

End Sub

Open in new window

0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
wlwebbAuthor Commented:
HOLD ON... I was looking at the field ClearChippedReporting for the Seq NOT the MachineClearChipped which I had hidden.
0
 
wlwebbAuthor Commented:
Yep.... still acts as I described above... sorry for the HOLD...
0
 
Rey Obrero (Capricorn1)Commented:
try this codes


Private Sub cmdSelectClearChipMachines_Click()
Dim t As Integer
t = 1

    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"
Do
'   CurrentDb.Execute "EDIT ShiftReportingLVL(MachineClearChipped) VALUES  (Yes), WHERE ShiftReportingLVL (RptgSeqID, LVLPositionNbr) =(" & Me.txtNewSeqID & "," & t & ")", dbFailOnError
CurrentDb.Execute "Update ShiftReportingLVL set MachineClearChipped=Yes" _
        & " WHERE RptgSeqID=" & Me.txtNewSeqID & " And LVLPositionNbr =" & t, dbFailOnError
    t = t + 1
    If t = Val(Me.txtLocNbrMachines) + 1 Then Exit Do
Loop
    
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Open in new window

0
 
wlwebbAuthor Commented:
Well, now all machines (in this case 5) have a check in the MachineClearChipped field instead of just the ones I checked in the frm_ClearChipSelectMachines..........

I stepped though the code a couple of times and It is reading the right LVLPositionNbr, it's just not doing anything with it......  

Hey, That appears to be it.......  How is it getting which boxes on the frm_ClearChipSelectMachines were checked vs ignoring the ones not checked????


JUST A NOTE:
I have two field for Clear Chip information.  1 field, ClearChipReporting is to be set to Yes for any and all LVLPositionNbr records in that RptgSeqID...  a 2nd field MachineClearChipped gets set to yes for ONLY those particular Machines that actually got clear chipped within that RptgSeqID........

Hope that makes sense......
0
 
wlwebbAuthor Commented:
Well, you beat me to it.... not surprising......  I went to get coffee and thought "hey, I need an If loop for the if ckbox# = Yes.... then ...

I'll give that a whirl... back in a moment
0
 
wlwebbAuthor Commented:
ALMOST!!!!!!!!  It now selects the right Checkbox for the MachineClearChipped.... but it is only checking that same box for the ClearChipReporting box.....  
That ClearChipReportingBox should be check for all those records in the same RptgSeqID for LVLPositionNbr t = 1 thru t = Val(Me.txtLocNbrMachines).  I am attempting to modify.......
0
 
wlwebbConnect With a Mentor Author Commented:
OK, I modified to this........Look right to you.... seems to work......
Private Sub cmdSelectClearChipMachines_Click()
Dim t As Integer, chkBoxes As String, z As Integer

t = 1
z = 1

    Do
        CurrentDb.Execute "Update ShiftReportingLVL set ClearChipReporting=Yes" _
            & " WHERE RptgSeqID=" & Me.txtNewSeqID & " And LVLPositionNbr =" & z, dbFailOnError
        z = z + 1
        If z = Val(Me.txtLocNbrMachines) + 1 Then Exit Do
    Loop
    

For t = 1 To 10
    If Me("CkBox" & t) = -1 Then
        chkBoxes = chkBoxes & "," & t
    End If
Next
If chkBoxes & "" <> "" Then
    chkBoxes = Mid(chkBoxes, 2)
    Else
    MsgBox "No Checkbox was selected"
    Exit Sub
End If
'
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"
    CurrentDb.Execute "Update ShiftReportingLVL set MachineClearChipped=Yes" _
        & " WHERE RptgSeqID=" & Me.txtNewSeqID & " And LVLPositionNbr In(" & chkBoxes & ")", dbFailOnError


    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    DoCmd.Close acForm, "frm_ClearChipSelectMachines", acSaveNo

End Sub

Open in new window

0
 
wlwebbAuthor Commented:
The only problem I see with this is it gives the opportunity for someone to sit there and click on each of these Control Buttons and add a bunch of zero entries RptgSeqIDs....  Ultimately what I have to end up doing is computing the difference between those LVL reportings between one shiftID and the next shiftID.... If there are invalid inputs in between it will mess up a difference calculation.  

Thinking out Loud (or typed) It would appear then that I would need some sort of Voiding update function that leaves the trail but sets a Yes/No [Voided?] field to Yes........  But of course that may/would just end up being a whole other question.
0
 
wlwebbAuthor Commented:
Tried to give myself 5 points for the final change...but as usual what I attempt doesn't work......... ;-)))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.