Access - vbYesNo

Posted on 2012-09-12
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:
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)
    strCriteria = "[ShiftRptgLVLCtlID] = " & lngMyShiftRptgLVLCtlID
    Forms![frm_DataReporting]![ShiftReportingLVL].Form.Filter = strCriteria
    Forms![frm_DataReporting]![ShiftReportingLVL].Form.FilterOn = True
    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
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
    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

End Sub

Open in new window

Question by:wlwebb
    LVL 33

    Accepted Solution

    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


    Author Closing Comment

    PERFECT!!! Thank you!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now