[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access - Problem with vbYesNo...

Posted on 2012-09-12
4
Medium Priority
?
410 Views
Last Modified: 2012-09-12
Good Eve,

Based on a question earlier today regarding vbYesNo I started testing a vbYesNo in my code and found that it in fact was processing a Sub even though I had selected No.

The LResponse vbYesNo is where I'm trying to fix. Line 9.  Lines 23 - 29 is where I attempted to add If stmts to run based on Selecting Yes vs No
 
Private Sub cmdShiftEnd_Click()
    Dim cResponse As Integer, LResponse As Integer, lngMyRptgSeqID As Integer, lngMyShiftRptgLVLCtlID As Integer, strCriteria As String, strCriteria2 As String
    
    cResponse = MsgBox("You selected END OF SHIFT reporting.  Are you reporting Your End of Shift Information?", vbYesNo, "IS THIS YOUR END OF SHIFT REPORTING?")
        If cResponse = vbNo Then
            Exit Sub
        End If
    
    LResponse = MsgBox("Is this LVL Reporting for END OF DAY?", vbYesNo, "REPORTING END OF DAY?")
        
        Me.cboSelectedLVLRptgType.RowSource = "SELECT LVLRptgTypeID, LVLRptgType FROM LVLReportingType WHERE (((LVLRptgType)=" & "'Shift End'" & "))" & ";"
        Me.cboSelectedLVLRptgType = Me.cboSelectedLVLRptgType.ItemData(0)

' Application.Echo False
    
    Call NewLVLControl
    
    lngMyRptgSeqID = GetMyShiftSeqID()
    lngMyShiftRptgLVLCtlID = GetMyShiftRptgLVLCtlID()
    
    CurrentDb.Execute "INSERT INTO ShiftReportingEndCountCtl (ShiftRptgLVLCtlID, CountType) VALUES (" & lngMyShiftRptgLVLCtlID & "," & 1 & ")", dbFailOnError
    
    If LResponse = vbYes Then
        Call GenerateLVLMachineLines1(LResponse = vbYes)
    End If
    
    If LResponse = vbNo Then
        Call GenerateLVLMachineLines1No(LResponse = vbNo)
    End If
    
    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.Page6.SetFocus
    Parent.Page6.Visible = True
    strCriteria2 = "[ShiftRptgLVLCtlID] = " & lngMyShiftRptgLVLCtlID
    Forms![frm_DataReporting]![ShiftEndCashCount].Form.Filter = strCriteria2
    Forms![frm_DataReporting]![ShiftEndCashCount].Form.FilterOn = True
    Forms![frm_DataReporting]![ShiftEndCashCount]![ShiftRprgEndCountDetails_Coins].Form![Amount].SetFocus
    
    Parent.Page2.SetFocus
    Forms![frm_DataReporting]![WVLVLControlTotals].Form![txtCtlAmtIn].SetFocus
    
Application.Echo True

Parent.Page1.Visible = False
    
    
' ????    Forms!frm_ShiftReportingLVL.txtLVLRptgTypeNbr = Val(Me.cboSelectedLVLRptgType)
' ????    Forms!frm_ShiftReportingLVL.txtLocationNbrMachines = Val(Me.txtNbrMachines)
' ????    Forms!frm_ShiftReportingLVL.cboLVLRptgType = "Shift End"
    
End Sub

Open in new window


The one that I thought was working was the first sub.   but at the end was My "Attempt" to add a coding for VBNo's If...
Private Sub GenerateLVLMachineLines1(IsEndOfDay As Boolean)
    Dim bytCounter As Byte, i As Integer, z As Integer, lngMyRptgSeqID As Integer, lngMyShiftRptgLVLCtlID 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 EndOfDay=" & 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
' *************************** BELOW was my Guess *********
 Private Sub GenerateLVLMachineLines1No(IsEndOfDay As Boolean)

    Dim bytCounter As Byte, i As Integer, z As Integer, lngMyRptgSeqID As Integer, lngMyShiftRptgLVLCtlID 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")
    
    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
  • 3
4 Comments
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 2000 total points
ID: 38393710
You don't need a separate subroutine for a NO response.  All you need is GenerateLVLMachineLines1 which was designed to handle both responses.

Change line 11 in that subroutine to:
CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET EndOfDay=" & IsEndOfDay & " WHERE ShiftRptgLVLCtlID= " & lngMyShiftRptgLVLCtlID, dbFailOnError

Open in new window

Then in your other routine, remove lines 23 to 29 and replace with:
Call GenerateLVLMachineLines1(LResponse = vbYes)

Open in new window

0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38393721
Or was your intent to not update EndOfDay field in the table ShiftReportingLVLCtl to False if the response was NO?  If that's the case then line 11 should be:
If IsEndOfDay Then CurrentDb.Execute "UPDATE ShiftReportingLVLCtl SET EndOfDay=TRUE WHERE ShiftRptgLVLCtlID= " & lngMyShiftRptgLVLCtlID, dbFailOnError

Open in new window

0
 

Author Closing Comment

by:wlwebb
ID: 38393730
Yep, that did the trick......  Your assumption was correct, that the code either set the Endofday checkbox to Yes or no depending on the answer to the vbYesNo

Thank You!
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38393741
I still don't understand why you use a 3rd parameter for your DMax function since you are not specifying a criteria.  You really only need the field to get the maximum from (1st parameter) and the table or query name (2nd parameter).

z = DMax("[ShiftEndCountCtlID]", "ShiftReportingEndCountCtl")
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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

826 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