Variable Not Defined Issue 2

[cpsqlrwn]

Can someone please tell me why the following code is producing a "variable not defined" message on the word Cancel? Thank you for any help!

Dim Answer As Integer
    
    Answer = MsgBox("Yes to Delete this Batch, No to Exit Without Deleting, or Cancel to Continue Editing?", vbYesNoCancel + vbQuestion _
    + vbDefaultButton3, "Delete Unprocessed Batch?")
If Answer = vbCancel Then
    Cancel = True
    Exit Sub
Else
  If Answer = vbNo Then
      DoCmd.Close acForm, "UnprocessedBatchDeleteForm", acSaveNo
      Exit Sub
  End If
End If

 

[traingamer]

Because your variable 'Cancel' is not defined (in this code snippet).

Cancel = True

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[cpsqlrwn]

I don't understand. I didn't declare Cancel in this code and it works fine.

If Me![QuantityReceived].Value <= 0 Then
    MsgBox "You must enter a positive number in Quantity Received!", vbOKOnly _
    + vbCritical, "Positive Quantity Required!"
    Cancel = True
    Me.QuantityReceived.SetFocus
    Exit Sub
End If

Dim ZeroResponse As Integer
If Me![TotalCostReceived].Value = 0 Then
    ZeroResponse = MsgBox("You have entered Total Cost Received of ZERO!" _
    & vbCrLf & vbCrLf & "Are you sure this is correct?", vbYesNo _
    + vbQuestion + vbDefaultButton2, "Zero Cost Entered")
  If ZeroResponse = vbNo Then
    Cancel = True
    Exit Sub
  End If
End If

If Me![TotalCostReceived].Value < 0 Then
    ZeroResponse = MsgBox("You cannot enter a negative value in Total Cost Received!", _
    vbOKOnly + vbCritical, "Negative Cost Entered!")
    Cancel = True
    Exit Sub
End If

 

[TheAceMan1]

How are ya cpsqlrwn . . .

You probably not using your code in an event that has the [blue]Cancel[/blue] arguement. Example would be any forms [blue]On Open[/blue] event:

[blue]Private Sub Form_Open([purple][b]Cancel[/b][/purple] As Integer)
   [green]'Your Code Here[/green]
End Sub[/blue]

See Ya! . . . . . .

 

[cpsqlrwn]

Ace Man:

You're exactly right! I was able to figure that out, but now I am trying to resolve the following.

My command button performs mathematical modifications to fields in certain tables and I wanted to provide a warning message and an exit option before the updates. I am using RunSQL commands to perform the modifications in my command button.

If I am using a command button which runs specific code, can I still stop those actions under the form's Before Update event or will the actions already be done? Can you help me with this? Thank you.

 

[TheAceMan1]

cpsqlrwn . . .

[blue]Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
 
   Msg = "Delete this batch?" & DL & _
         "Click 'Yes to delete." & DL & _
         "Click 'No' to abort! . . ."
   Style = vbQuestion + vbYesNo
   Title = "User Response required! . . ."
   
   If MsgBox(Msg, Style, Title) = vbYes Then
      [green]'Delete batch code here![/green]
   Else
      [purple][b]Cancel[/b][/purple] = True
   End If

End Sub[/blue]

See Ya! . . . . . .

 

[cpsqlrwn]

Thank you TheAceMan1:

We're getting there. Just 2 more questions.

My code is just like yours now, except that I reversed the If...Then sequence, putting the vbNo scenario first. Does it matter at all if I have the vbNo first with an exit option followed by the Delete code if the response is vbYes?

My second question is what I am stumped on right now. I am not actually deleting these records. I am marking an IsDeleted boolean field and time stamping a field call DateDeleted. In this way I am preserving an audit trail while making the records inactive for processing purposes. I am doing all this with a command button. this is all working fine. Now I am trying to provide a confirmation message box at the end of the command button code telling the user that the record has been deleted and would you like to delete another receipt. I am getting an invalid procedure call or argument error and I can't seem to figure it out. Here is the code.

Private Sub DeleteReceiptCommand_Click()
On Error GoTo Err_DeleteReceiptCommand_Click

Dim Conf As Integer
Dim Response As Integer
    
    Response = MsgBox("Yes to Delete this Receipt, No to Exit Without Deleting", vbYesNo + vbQuestion _
    + vbDefaultButton2, "Are You Sure?")
    
If Response = vbNo Then
'    DoCmd.Close acForm, "ReceiptDeleteForm", acSaveNo
    Exit Sub
End If

DoCmd.RunSQL "UPDATE ReceiptTable SET IsDeleted = -1" & _
" WHERE (([ReceiptTable]![ID])=[Forms]![ReceiptDeleteForm]![ID])"

DoCmd.RunSQL "UPDATE ReceiptTable SET DeletedDate = Now()" & _
" WHERE (([ReceiptTable]![ID])=[Forms]![ReceiptDeleteForm]![ID])"

DoCmd.RunSQL "UPDATE ItemTable SET LastQuantityOnHand = QuantityOnHand" & _
" WHERE (([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem])"

DoCmd.RunSQL "UPDATE ItemTable SET LastTotalCostOnHand = TotalCostOnHand" & _
" WHERE (([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem])"

DoCmd.RunSQL "UPDATE ItemTable SET QuantityOnHand = LastQuantityOnHand - [Forms]![ReceiptDeleteForm]!QuantityReceived" & _
" WHERE (([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem])"

DoCmd.RunSQL "UPDATE ItemTable SET TotalCostOnHand = LastTotalCostOnHand - [Forms]![ReceiptDeleteForm]!TotalCostReceived" & _
" WHERE (([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem])"

DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost = 0" & _
" WHERE ((([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem]) and ([ItemTable]![QuantityOnHand]=0))"

DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost = TotalCostOnHand/QuantityOnHand" & _
" WHERE ((([ItemTable]![Item])=[Forms]![ReceiptDeleteForm]![ReceivedItem]) and ([ItemTable]![QuantityOnHand]<>0))"

Forms!ReceiptDeleteForm.Requery
Forms!ReceiptDeleteForm!Combo0.Requery


Conf = MsgBox("Receipt Has Been Deleted!" & vbCrLf _
       & vbCrLf & "Do You Want to Delete Another Receipt?", _
       vbYesNo, vbQuestion, "Delete Another Receipt?")

If Conf = vbNo Then
    DoCmd.Close
    Exit Sub
End If

Exit_DeleteReceiptCommand_Click:
    Exit Sub

Err_DeleteReceiptCommand_Click:
    MsgBox Err.Description
    Resume Exit_DeleteReceiptCommand_Click
End Sub

 

[danvlas]

Replace
vbYesNo, vbQuestion

with
vbYesNo +vbQuestion

And...be aware that Response is an argument name used sometimes by Access. You could try to develop a naming convention to avoid such cases.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TheAceMan1]

[blue] Does it matter at all if I have the vbNo first with an exit option followed by the Delete code if the response is vbYes?[/blue]

Not at all. I am curiuous though as to [blue]what happened to the infamous cancel arguement?[/blue]

In any case it looks like your set . . .

See Ya! . . . . . .

 

[cpsqlrwn]

I eliminated the Cancel argument because the event (a Command Box_Click) does not have the Cancel option. Is there a way to implement the Cancel option in an event that does not offer the Cancel argument? Thanks!

 

[TheAceMan1]

cpsqlrwn . . .

The answer is No! . . .

See Ya! . . . . . .

 

[cpsqlrwn]

Thanks TheAceMan1 for all your help on these issues.