Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access - Continuous Form open to selected records - Set a Yes/No field to Yes

Posted on 2012-09-03
9
Medium Priority
?
563 Views
Last Modified: 2012-09-04
Hello all

I have 2 Forms. Form1 has three control buttons.  All the Control Buttons open the same Form2 and closes the Form1.

The 3 Control Buttons are being used to set various field Yes/No on Form2 boxes based upon which button got selected on Form1.

When Form2 opens, the clerk is going to input a set number of new records of information (ie:5 records).

When Form2 opens I have an unbound textbox on Form1 that looks to the Table and finds the DMax of RptgSeqID and adds 1 to the DMax.  Then when it opens Form2 it INSERTS to my Table a certain number of records and assigns the field with that DMax value.  It then opens Form2 with just those records selected using a Docmd.openform where command.

The Table that Form2 comes from has a field named [RptgSeqID].  So when the clerk inputs the 5 records of info each record has the same [RptgSeqID]

Following is the code for one of the command buttons from Form1
Private Sub GenerateLVLMachineLines()
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) VALUES (" & bytCounter + 1 & "," & lngNextSeqID & ")", dbFailOnError
    bytCounter = bytCounter + 1
Loop

' Me.txtNbrMachines = ""
'    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit

End Sub

Private Sub cmdShiftEnd_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("Is this LVL Reporting for END OF DAY?", vbYesNo, "REPORTING END OF DAY?")
    
    If LResponse = vbYes Then
        Call GenerateLVLMachineLines
        DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
        Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
        Forms!frm_ShiftReportingLVL.EndOfDay = True
        DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    Else
        Call GenerateLVLMachineLines
        DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
        Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
        Forms!frm_ShiftReportingLVL.EndOfDay = False
        DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    End If
    

    
End Sub

Open in new window

Now then, what is happening is it opens the form correctly and selects the right records based on the RptgSeqID correctly BUT two things are wrong.

1) it is only setting the First Yes/No box to Yes of the 5 records it is pulling and
2) it is showing the ability to "add" a new record which I do not want the clerk to be able to do.

Any help?
0
Comment
Question by:wlwebb
  • 4
  • 4
9 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38362519
You can disable additions in form's properties or with command
Me.AllowAdditions = False (for current form in on load event, for example)
Can you upload sample DB with these forms and some dummy data?
0
 

Author Comment

by:wlwebb
ID: 38363804
Als315
I tried setting the Form setting Allow Additions to "No"  but the form still showed a "New" record.  I didn't understand why but thought maybe I was doing something wrong.

As for the other, here's and upload of the db sample
The first form that should be loaded is Frm_LVLReportingTypeSelect.
AddRecords.accdb
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38363984
Try these changes:
Private Sub cmdShiftEnd_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("Is this LVL Reporting for END OF DAY?", vbYesNo, "REPORTING END OF DAY?")
        
    Call GenerateLVLMachineLines(LResponse = vbYes)
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    
End Sub

Private Sub GenerateLVLMachineLines(IsEndOfDay 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
        bytCounter = bytCounter + 1
        CurrentDb.Execute "INSERT INTO ShiftReportingLVL (LVLPositionNbr, RptgSeqID, EndOfDay) VALUES (" & bytCounter & "," & lngNextSeqID & "," & IsEndOfDay & ")", dbFailOnError
    Loop
    
End Sub

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 

Author Closing Comment

by:wlwebb
ID: 38364298
That was it...... THANK YOU Irog!

One Problem ....

I now get an error on the Other two control buttons......  

Do I have 2 Private Subs called GenerateLVLMachineLines One that has the IsEndOfDay as Boolean and one without?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38364639
Can you post the code for the other buttons?
0
 

Author Comment

by:wlwebb
ID: 38364774
It was in the db attached...  I will post the first being my old code... and the second being what I have "attempted" to rewrite based on what you did.....  Trouble is, mine are working backwards.... Ie: I select yes in the Y/N VB and it isn't checking the box for either the Machine Money Pull Button or the Lottery Clear Chip button....  I guess I'm a failure
Original Before Your Change:
Private Sub cmdClearChip_Click()
Dim intSeqID As Integer
intSeqID = Nz(DMax("RptgSeqID", "ShiftReportingLVL"))


    Call GenerateLVLMachineLines
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Machine Pull"
    Forms!frm_ShiftReportingLVL.ClearChipReporting = True
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Private Sub cmdMachPull_Click()
Dim intSeqID As Integer
intSeqID = Nz(DMax("RptgSeqID", "ShiftReportingLVL"))
    
    Call GenerateLVLMachineLines
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"
    Forms!frm_ShiftReportingLVL.MachinePulled = True
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    

End Sub

Private Sub cmdShiftEnd_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("Is this LVL Reporting for END OF DAY?", vbYesNo, "REPORTING END OF DAY?")
    
    If LResponse = vbYes Then
        Call GenerateLVLMachineLines
        DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
        Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
        Forms!frm_ShiftReportingLVL.EndOfDay = True
        DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    Else
        Call GenerateLVLMachineLines
        DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
        Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
        Forms!frm_ShiftReportingLVL.EndOfDay = False
        DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    End If
    

    
End Sub

Private Sub Form_Load()
    Me.cboLocationNbrMachines.RowSource = "SELECT DISTINCT LocationID, LocationName, CurrentLocation, LocationNbrLVLMachines  FROM qry_Company_Location_DBAs WHERE (((qry_Company_Location_DBAs.CurrentLocation)=-1));"
    Me.cboLocationNbrMachines = Me.cboLocationNbrMachines.ItemData(0)

End Sub

Private Sub GenerateLVLMachineLines()
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) VALUES (" & bytCounter + 1 & "," & lngNextSeqID & ")", dbFailOnError
    bytCounter = bytCounter + 1
Loop

' Me.txtNbrMachines = ""
'    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID, acFormEdit
 

End Sub

Open in new window

My "Attempt" after your change:
Private Sub cmdClearChip_Click()
'Dim intSeqID As Integer
'intSeqID = Nz(DMax("RptgSeqID", "ShiftReportingLVL"))
    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")
    

    Call GenerateLVLMachineLines3(LResponse - vbYes)
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Clear Chip"
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo

End Sub

Private Sub cmdMachPull_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("You selected Machine Money Pull.  Are you sure this is a Machine Money Pull reporting?" & vbNewLine & vbNewLine & _
    "PLEASE NOTE: All Machines are defaulted to Pulled.  Uncheck the box of any Machine from which money was not pulled!", vbYesNo, "MACHINE MONEY PULL REPORTING")
    
' Dim intSeqID As Integer
' intSeqID = Nz(DMax("RptgSeqID", "ShiftReportingLVL"))
    
    Call GenerateLVLMachineLines2(LResponse = vbYes)
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Machine Pull"
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    

End Sub

Private Sub cmdShiftEnd_Click()
    Dim LResponse As Integer
    
    LResponse = MsgBox("Is this LVL Reporting for END OF DAY?", vbYesNo, "REPORTING END OF DAY?")
        
    Call GenerateLVLMachineLines1(LResponse = vbYes)
    DoCmd.OpenForm "frm_ShiftReportingLVL", acNormal, , "RptgSeqID =" & txtNewSeqID
    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
    DoCmd.Close acForm, "frm_LVLREportingTypeSelect", acSaveNo
    
    
End Sub

Private Sub Form_Load()
    Me.cboLocationNbrMachines.RowSource = "SELECT DISTINCT LocationID, LocationName, CurrentLocation, LocationNbrLVLMachines  FROM qry_Company_Location_DBAs WHERE (((qry_Company_Location_DBAs.CurrentLocation)=-1));"
    Me.cboLocationNbrMachines = Me.cboLocationNbrMachines.ItemData(0)

End Sub
Private Sub GenerateLVLMachineLines1(IsEndOfDay 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
        bytCounter = bytCounter + 1
        CurrentDb.Execute "INSERT INTO ShiftReportingLVL (LVLPositionNbr, RptgSeqID, EndOfDay) VALUES (" & bytCounter & "," & lngNextSeqID & "," & IsEndOfDay & ")", dbFailOnError
    Loop
    
End Sub
Private Sub GenerateLVLMachineLines2(IsMachinePull 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, MachinePulled) VALUES (" & bytCounter + 1 & "," & lngNextSeqID & "," & IsMachinePull & ")", dbFailOnError
    bytCounter = bytCounter + 1
Loop

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
    bytCounter = bytCounter + 1
Loop

End Sub

Open in new window

0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38364997
I see an error here:
Call GenerateLVLMachineLines3(LResponse - vbYes)

It should be:
Call GenerateLVLMachineLines3(LResponse = vbYes)
0
 

Author Comment

by:wlwebb
ID: 38365282
Duuuh, that would make a difference.......
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38365631
May I suggest that you look at what your various procedures have in common and just combine them into 1 routine that you could pass parameters to.  That way, your code is more compact and if you have to make any changes in the future, you could do it all in one procedure rather than three.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 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