[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-04
12
Medium Priority
?
514 Views
Last Modified: 2012-09-09
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
0
Comment
Question by:wlwebb
  • 9
  • 3
12 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38366567
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
 

Author Comment

by:wlwebb
ID: 38366583
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
 

Author Comment

by:wlwebb
ID: 38366597
HOLD ON... I was looking at the field ClearChippedReporting for the Seq NOT the MachineClearChipped which I had hidden.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:wlwebb
ID: 38366598
Yep.... still acts as I described above... sorry for the HOLD...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38366611
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
 

Author Comment

by:wlwebb
ID: 38366619
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1980 total points
ID: 38366637
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
 

Author Comment

by:wlwebb
ID: 38366647
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
 

Author Comment

by:wlwebb
ID: 38366662
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
 

Assisted Solution

by:wlwebb
wlwebb earned 0 total points
ID: 38366666
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
 

Author Comment

by:wlwebb
ID: 38366672
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
 

Author Closing Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question