asked on

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

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?

als315

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?

wlwebb

ASKER

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

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

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?

IrogSinta

Can you post the code for the other buttons?

wlwebb

ASKER

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

IrogSinta

I see an error here:
Call GenerateLVLMachineLines3(LResponse - vbYes)

It should be:
Call GenerateLVLMachineLines3(LResponse = vbYes)

wlwebb

ASKER

Duuuh, that would make a difference.......

IrogSinta

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.