Link to home
Start Free TrialLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

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

Avatar of wlwebb

ASKER

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

Avatar of wlwebb

ASKER

HOLD ON... I was looking at the field ClearChippedReporting for the Seq NOT the MachineClearChipped which I had hidden.
Avatar of wlwebb

ASKER

Yep.... still acts as I described above... sorry for the HOLD...
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

Avatar of wlwebb

ASKER

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......
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

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
Avatar of wlwebb

ASKER

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.......
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of wlwebb

ASKER

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.
Avatar of wlwebb

ASKER

Tried to give myself 5 points for the final change...but as usual what I attempt doesn't work......... ;-)))