Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access - vbYesNo

Posted on 2012-09-12
2
Medium Priority
?
354 Views
Last Modified: 2012-09-12
Good evening (or morning wherever you may be)...

Have a bit of code that I've never done much with.  I needed a Msgbox to come up and ask for a Yes/No answer and do some code part of which goes to another tab for some checkbox selections, if it was yes.. that part is working.

However, if I click NO, I want it to cancel and return to the same tabbed form where I started.  It's still opening the other tabbed form.

The command button that starts all of this is on Page1 which is:
Forms![frm_DataReporting]![LVLReportingTypeSelect].Form!
If the Yes/No gets selected as NO then it needs to return to this form.


This is the code for the Command button that starts my code.
Private Sub cmdClearChip_Click()
    Dim LResponse As Integer, lngMyRptgSeqID As Integer, lngMyShiftRptgLVLCtlID As Integer, i As Integer
    
    Dim strCriteria As String
    
    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")
    
    Me.cboSelectedLVLRptgType.RowSource = "SELECT LVLRptgTypeID, LVLRptgType FROM LVLReportingType WHERE (((LVLRptgType)=" & "'Clear Chip'" & "))" & ";"
    Me.cboSelectedLVLRptgType = Me.cboSelectedLVLRptgType.ItemData(0)

Application.Echo False
    
    Call NewLVLControl
    lngMyRptgSeqID = GetMyShiftSeqID()
    lngMyShiftRptgLVLCtlID = GetMyShiftRptgLVLCtlID()
    
    CurrentDb.Execute "INSERT INTO ShiftReportingEndCountCtl (ShiftRptgLVLCtlID, CountType) VALUES (" & lngMyShiftRptgLVLCtlID & "," & 3 & ")", dbFailOnError
    
    Call GenerateLVLMachineLines3(LResponse = vbYes)
        
    Parent.Page4.SetFocus
    Forms![frm_DataReporting]![ShiftReportingLVL].Form![AmtOut].SetFocus
    Forms![frm_DataReporting]![ShiftReportingLVL].Form.Requery
    strCriteria = "[ShiftRptgLVLCtlID] = " & lngMyShiftRptgLVLCtlID
    Forms![frm_DataReporting]![ShiftReportingLVL].Form.Filter = strCriteria
    Forms![frm_DataReporting]![ShiftReportingLVL].Form.FilterOn = True
        
    Parent.Page1a.SetFocus
    Parent.Page1a.Visible = True
        
        i = 10
    
        Do Until i = Val(Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtNbrMachines])
 
        Forms![frm_DataReporting]![ClearChipSelectMachines].Controls("lblCkBox" & i).Visible = False
        Forms![frm_DataReporting]![ClearChipSelectMachines].Controls("CkBox" & i).Visible = False
        i = i - 1
        Loop
    
Application.Echo True

End Sub

Open in new window


AND This is the Yes Answer part (at least as I understand it it's the Yes part)
Private Sub GenerateLVLMachineLines3(IsClearChip As Boolean)
Dim bytCounter As Byte, lngMyRptgSeqID As Long, lngMyShiftRptgLVLCtlID As Long, i As Integer, z As Integer
Dim strSQL As String

lngMyRptgSeqID = GetMyShiftSeqID()
lngMyShiftRptgLVLCtlID = GetMyShiftRptgLVLCtlID()
    
    i = 1   ' This counter is for Inserting Active Currency Seq to End Count tbl
    z = DMax("ShiftEndCountCtlID", "ShiftReportingEndCountCtl", "ShiftEndCountCtlID")
    
    CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET ClearChipReporting=" & True & " WHERE ShiftRptgLVLCtlID= " & lngMyShiftRptgLVLCtlID, dbFailOnError
    
    Do Until bytCounter = Me.txtNbrMachines
        bytCounter = bytCounter + 1
    CurrentDb.Execute "INSERT INTO ShiftReportingLVL (LVLPositionNbr, RptgSeqID) VALUES (" & bytCounter & "," & lngMyRptgSeqID & ")", dbFailOnError
    Loop
    
    Do While i <= Forms![frm_DataReporting]![LVLReportingTypeSelect].Form![txtCntAllActiveDenominations]
        strSQL = "INSERT INTO ShiftReportingEndCountDetails (ShiftEndCountCtlID, DenominationID) SELECT " & z & ", DenominationID FROM [qry_Denomination_All_Active] WHERE [AllActiveDenominationSeq]= " & i
        CurrentDb.Execute strSQL, dbFailOnError
        i = i + 1
    Loop

End Sub

Open in new window

0
Comment
Question by:wlwebb
2 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38393413
You never test the response from the message box, so no matter what is pressed the code will just continue on.

To check the response for No you can use the following code which should go after the message box.

It stops the code if No is pressed.
If LResponse =VbNo Then
      Exit Sub
End If

Open in new window

0
 

Author Closing Comment

by:wlwebb
ID: 38393473
PERFECT!!! Thank you!
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

810 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