wlwebb
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_ClearChipSelectMachine s ...
The Code I attempted to alter instead of INSERT INTO to EDIT was (but as you know won't work)
I have attached the stripped down db just in case
CkBoxFlowToForm.accdb
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
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
I have attached the stripped down db just in case
CkBoxFlowToForm.accdb
ASKER
Well, it doesn't error out like mine.... but I tried various combinations of checked and unchecked boxes on the frm_ClearChipSelectMachine s 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_ClearChipSelectMachine s 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:
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
ASKER
HOLD ON... I was looking at the field ClearChippedReporting for the Seq NOT the MachineClearChipped which I had hidden.
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
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_ClearChipSelectMachine s......... .
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_ClearChipSelectMachine s 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......
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_ClearChipSelectMachine
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I'll give that a whirl... back in a moment
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.......
That ClearChipReportingBox should be check for all those records in the same RptgSeqID for LVLPositionNbr t = 1 thru t = Val(Me.txtLocNbrMachines).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Tried to give myself 5 points for the final change...but as usual what I attempt doesn't work......... ;-)))
Open in new window