UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Vbcancel Not Working, Access 2010    
 
   
guitarsweety
post Aug 19 2017, 07:47 AM
Post#1



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I thought selecting the cancel button would stop this delete query from running, but it's not working. Can anyone help me with this.

CODE
Case "btdeleteattendance"
MsgBox "Are you sure you want to permanently remove all attendance records?", vbCritical + vbOKCancel + vbApplicationModal, "Insteel Wire Products"
DoCmd.OpenQuery "DeleteAttendance"
Dim err_hndl As Integer
If err_hndl = vbCancel Then
Exit Sub
End If
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 07:53 AM
Post#2


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Your code, as written, executes the query REGARDLESS of how the user responds.

You need to make execution conditional on that response.

Omitted code guessed at --
CODE
Select case WhateverYourCaseSelectCriteriaIs
Case one
-----
Case "btdeleteattendance"
    If MsgBox(Prompt:="Are you sure you want to permanently remove all attendance records?", Buttons:= vbCritical + vbOKCancel + vbApplicationModal, Title:="Insteel Wire Products") = vbOk Then
    DoCmd.OpenQuery "DeleteAttendance"
    End IF
Case OtherCasestoCheck
End Select

--------------------
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 08:07 AM
Post#3



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I don't understand what I should do iconfused.gif


CODE
Public Sub fncOnAction(control As IRibbonControl)
On Error GoTo trataerror
Select Case control.ID
Case "btcontact"
Dim oApp As Outlook.Application
Dim oMail As MailItem
Set oApp = CreateObject("Outlook.application")

Set oMail = oApp.CreateItem(olMailItem)
oMail.Body = ""
oMail.Subject = "Assistance Needed With Kingman Labels"
oMail.To = "dwood@insteel.com"
oMail.Display
Set oMail = Nothing
Set oApp = Nothing

Case "btinstructions"
Application.FollowHyperlink "N:\0-Rolling Attendance Reporting\Instructions-Formatting Files\Attendance Report Instructions.docx"

Case "btexit"
DoCmd.Quit

Case "btdeleteattendance"
  If MsgBox(Prompt:="Are you sure you want to permanently remove all attendance records?", Buttons:=vbCritical + vbOKCancel + vbApplicationModal, Title:="Insteel Wire Products") = vbOK Then
    DoCmd.OpenQuery "DeleteAttendance"
    End If

Case "btdeletecarryover"
MsgBox "Are you sure you want to permanently remove all employee carry over hours?", vbCritical + vbOKCancel + vbApplicationModal, "Insteel Wire Products"
DoCmd.OpenQuery "DeleteCarryOverHours"


Case "btopenemployee"
DoCmd.OpenForm "Employee List"

Case "btEmpDetails"
DoCmd.OpenForm "Employee Details"
Case "btlabel"
DoCmd.OpenForm "frmReports"
Case "btopenattendance"
DoCmd.OpenTable "tblAttendance"

Case "btcloseattendance"
DoCmd.Close acTable, "tblAttendance", acSaveYes

Case "btopenreport"
DoCmd.OpenForm "frmReporting"

Case "btimport"
MsgBox "To import your hours browse to the location of the excel workbook named Calculate Sick Hours and double-click to select!", vbInformation + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
  Dim strImportFileName As String
    strImportFileName = "Calculate Sick Hours.xlsm"
    
    DoCmd.SetWarnings False
    On Error GoTo err_hndl
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "tbl_TempForImporting", selectFile, True
  
    DoCmd.SetWarnings True
      
    DoCmd.OpenQuery "Find duplicates for tbl_TempForImporting"

    
    DoCmd.Close acQuery, "Find duplicates for tbl_TempForImporting"
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Set db = CurrentDb

    Set rs = CurrentDb.OpenRecordset("tbl_TempForImporting")
    db.Execute "tbl_TempForImporting Query", dbFailOnError

    If db.RecordsAffected = 0 Then
      MsgBox "Attendance hours already exist for " & rs.Fields(1) & "." & " To prevent duplication, no records have been imported!", vbCritical + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
    Else
    MsgBox "A Total of " & db.RecordsAffected & " Records were imported for the month of " & rs.Fields(1), vbInformation + vbOKOnly + vbApplicationModal, "Insteel Wire Products"
    End If
    Set db = Nothing
    DoCmd.OpenQuery "tbl_TempForImporting Query"
    DoCmd.OpenQuery "DeleteTempTable"
    Exit Sub
err_hndl:
MsgBox ("This is not the correct file! Click cancel and Import the Calculate Sick Hours Excel Workbook"), vbExclamation + vbOKCancel + vbApplicationModal, "Insteel Wire Products"

If err_hndl = vbCancel Then
Exit Sub
End If
Case Else
    MsgBox "button:" & control.ID, vbInformation, "Warning"
End Select
exiterror:
Exit Sub
trataerror:
MsgBox "Error:" & Err.Number & vbCrLf & Err.Description, vbCritical, "Warning", Err.HelpFile, Err.HelpContext
Resume exiterror:
End Sub
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 08:35 AM
Post#4


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


hmm.... I showed you what to do.

Make each of the choices CONDITIONAL on the results of the message box.

If the user clicks "Ok", run the query. If the user clicks "Cancel" don't run the query. Do that within each part of the Case statement where you need to decide whether to run the query or not run it.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 08:37 AM
Post#5


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


I don't see that your "error handler" is adding anything useful.

--------------------
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 08:42 AM
Post#6



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I'm trying but I'm getting a case without select case compile error
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 09:09 AM
Post#7


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


You have one or more unbalanced conditionals between the Select Case control.ID statement and the End Select statement.

Try compiling the code to see where the error is.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 09:10 AM
Post#8


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


BTW:

I am pretty sure placing the error handler label INSIDE the SELECT CASE statement is not a good idea. Move it to the end, please.

--------------------
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 09:40 AM
Post#9



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


You're speaking in terms way beyond my understanding as this is all still so new to me. I appreciate your help with this, but I'll just leave as is and hope that the user really intends on deleting everything when they click.
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 10:01 AM
Post#10


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


We can fix this, but you have to be willing to learn how VBA works. It's not necessarily the way we would normally think, so you have to learn the structures.

Here's the outline of a typical approach to error handling.

Public Sub fncOnAction(control As IRibbonControl)

Dim variableone as variabletype
Dim variabletwo as anothervariabletype

On Error GoTo err_hndl



Select Case xxxxx
Case one
Case two
case else
End Select

ExitError:

Exit Sub

err_hndl:

Your Error handlers go here
Resume ExitError

End Sub

Now, notice that you only go into the error handler if the code causes a real error. A "wrong" button click (i.e. the user clicks "Cancel" instead of "OK") is NOT an error. It's conditional choice. You're trying to use that button click as "an error", which it isn't.

I don't know where that particular structure came from, but it's not one I would use or recommend.

--------------------
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 10:11 AM
Post#11



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


Thank you GroverParkGeorge, I will study this and see if I can figure out the correct way to make this work. I've been using this same code for a few years now with no issues but I've added two query commands which now changes everything. Again thank you.
Go to the top of the page
 
GroverParkGeorge
post Aug 19 2017, 10:52 AM
Post#12


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Out of curiosity, did you develop that structure, or was it copied from somewhere?

--------------------
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 10:55 AM
Post#13



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


No I did not develop it at all. I found it years ago online when I was trying to learn about custom ribbons.
Go to the top of the page
 
guitarsweety
post Aug 19 2017, 11:21 AM
Post#14



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


For the sake of time I've created a form with command buttons for the queries.
Go to the top of the page
 
guitarsweety
post Aug 20 2017, 08:21 AM
Post#15



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


uarulez2.gif I figured it out


CODE
Case "btdeleteattendance"
Dim prompt As Integer
    If MsgBox(prompt:="Are you sure you want to permanently remove all attendance records?", Buttons:=vbCritical + vbYesNo + vbApplicationModal, Title:="Insteel Wire Products") = vbYes Then DoCmd.OpenQuery "DeleteAttendance"
    
   If prompt = vbNo Then
    Exit Sub
    End If
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 12:03 PM
Post#16


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


That should also work.

Congratulations on resolving the problem.

Continued success with your project.

--------------------
Go to the top of the page
 
guitarsweety
post Aug 20 2017, 12:09 PM
Post#17



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


I couldn't have done it without your help. Thank you so very much
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 12:33 PM
Post#18


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Actually, I'm not sure this syntax is fully correct as I look at it more carefully.

Do you have Option Explicit declared in all modules?

Attached File  optionsforVBA.png ( 6.47K )Number of downloads: 0

Attached File  optionsforModule.png ( 2.44K )Number of downloads: 0


You are using a variable called "Prompt", but I do not see you setting it to any value.

This code "works" because, if you click on the "Yes" button, the query is run.

If you click on the "No" button, nothing actually happens because you are checking the value of the Prompt variable, which is not actually set to anything. It may be coincidentally "working" as you want, though, because the default value of the variable could be evaluated as "false" or "No".

Therefore I suggest this modification:

CODE
...previous code
Case "btdeleteattendance"
    Dim prompt As Integer ' although it is okay to dim variables just before using them, I prefer to put them all at the top of the procedure. Suit yourself.
    prompt = MsgBox(prompt:="Are you sure you want to permanently remove all attendance records?", Buttons:=vbCritical + vbYesNo + vbApplicationModal, Title:="Insteel Wire Products")
    If prompt = vbYes Then
          DoCmd.OpenQuery "DeleteAttendance"
    ElseIf  prompt = vbNo Then
        Exit Sub
    End If
..ensuing code


It makes explicit what you want to have happen.

Also, it might be good to observe that there is another alternative to using DoCmd.OpenQuery. That pops up a message box asking the user to confirm that they want to run the query. Fine, but there is an alternative that can execute the query silently.

CurrentDB.Execute "DeleteAttendance", dbFailOnerror

That expression will run the query without asking for confimation. However, if an error occurs, it will send processing to your error handler.
Attached File(s)
Attached File  compile.png ( 28.05K )Number of downloads: 0
 

--------------------
Go to the top of the page
 
guitarsweety
post Aug 20 2017, 12:42 PM
Post#19



Posts: 189
Joined: 1-December 11
From: Mt Airy, NC


i don't understand. Everything seems to be working perfectly
Go to the top of the page
 
GroverParkGeorge
post Aug 20 2017, 12:49 PM
Post#20


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


And my suggestion is that it seems to "work" because it is a coincidence.

You are using a variable named "prompt", which is unfortunately the same as the name of one of the arguments in the MsgBox() function.

It is NOT being set, at least not in the code snippet posted. Change it to something else and try again. Say, "booDelete" to indicated that it is a boolean (yes or no) and that it pertains to the deletion of records.

Does it still "work"?

--------------------
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 05:25 PM