Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

ADO DELETE 2

Status
Not open for further replies.

timhans

Programmer
Jun 24, 2009
75
Hello, have a form with navigation button's, user's want to delete records the code below work well, but want to give the chance to back out, trying msgbox "xyz",vbOKCancel but after canceling deletes anyway. Any insight is apriciated. Thanks


Private Sub CmdDelete_Click()
On Error GoTo cnnError

If vbOKCancel = True Then GoTo AAA 'Here

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection

Dim rsDelete As New ADODB.Recordset
rsDelete.ActiveConnection = cnn

MsgBox "Continue", vbOKCancel 'Here

rsDelete.Open "SELECT * FROM Equipment WHERE DemoID = '" & Me.txtbox1.Value & "'", , adOpenDynamic, adLockOptimistic, adCmdText

If rsDelete.EOF = False Then
With rsDelete
.Delete
.Update
.Close

End With

End If

MsgBox "Record Deleted", vbInformation
AAA: 'Here
Set rsDelete = Nothing
Set cnn = Nothing

Exit Sub

cnnError:
MsgBox "There was an Error Connecting to the DataBase." & Chr(13) _
& Err.Number & ", " & Err.Description
 
A starting point:
If MsgBox("Continue the delete ?", vbOKCancel) <> vbOK Then Exit Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
timhans . . .

Using PHV's method for the msgbox at the beginning of your code and adding some formatting:
Code:
[blue]Private Sub CmdDelete_Click()
   
On Error GoTo cnnError
   Dim cnn As ADODB.Connection, rsDelete As New ADODB.Recordset, SQL As String, DL As String

   If [purple]MsgBox("Are you sure you want to continue with deletion?", _
             vbQuestion + vbOKCancel, _
             "User Confirmation Required! . . .")[/purple] = vbCancel Then Exit Sub

   Set cnn = CurrentProject.Connection
   Set rsDelete.ActiveConnection = cnn
   DL = vbNewLine & vbNewLine
   
   SQL = "SELECT * " & _
         "FROM Equipment " & _
         "WHERE DemoID = '" & Me.txtbox1.Value & "'"
   rsDelete.Open SQL, , adOpenDynamic, adLockOptimistic, adCmdText

   If rsDelete.EOF = False Then
      With rsDelete
         .Delete
         .Update
         .Close
      End With
   End If
 
   MsgBox "Record Deleted", vbInformation
   
   Set rsDelete = Nothing
   Set cnn = Nothing
Exit Sub

cnnError:
   [purple]MsgBox "There was an Error Connecting to the DataBase!" & DL & _
          "ErrNum:" & Err.Number & ", " & Err.Description[/purple]

End Sub[/blue]
Get into the habit of formatting your code better. Its makes for easier and quicker reading. Mainly for you!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
timhans said:
If vbOKCancel = True Then GoTo AAA

translates as
Code:
If 1 = -1 Then Goto AAA

timhans said:
MsgBox "Continue", vbOKCancel

All that does is show a message box with the OK and Cancel buttons.
 
PHP, Acemane,JoeAtWork

Clearly my understanding of canceling a event was equivalent to 1 = -1
But WOW the canceling via msgbox was most helpful, I have retro fitted a number of events with this method.
DL = vbNewLine & vbNewLine
is completely new to me, I googled it, has to do with validation ?. I'll keep looking for better description of it.
Aceman, point taken on formatting, not only dose it make it easier for me to read buy if I am going to ask others to help me with my code it is only respectful and polite to to format so it is readable. Thanks every one
 
timhans said:
DL = vbNewLine & vbNewLine
is completely new to me, I googled it, has to do with validation ?

vbNewLine simply adds a "new line" to a string, whatever is appropriate to the platform the program is running on. For me it would be the equivalent of Carriage Return + Line Feed.

In Aceman's code it's not actually used as far as I can see, as the DL variable does not get used.
 
timhans said:
[blue]DL = [purple]vbNewLine[/purple] & [purple]vbNewLine[/purple]
is completely new to me, ...[/blue]
[purple]vbNewLine[/purple] represents Chr(13) & Chr(10) as an an intrinsic constant (thats a carriage return + linefeed). It saves from having to type Chr(13) & Chr(10). Another constant is [purple]vbCrLf[/purple]. All it does is goto a new line, like hitting enter in an editor. Since I've concatenated the constant twice, two new lines are entered, putting a line space between lines.

Examples:
Code:
[blue]   "1st line here" & [purple][b]vbNewLine[/b][/purple] & "2nd line here" ... we get:
   [purple]1st line here
   2nd line here[/purple]

   "1st line here" & [purple][b]vbNewLine[/b][/purple] & [purple][b]vbNewLine[/b][/purple] & "2nd line here" ... we get:
   [purple]1st line here
   
   2nd line here[/purple]
   
   "1st line here" & [purple][b]DL[/b][/purple] & "2nd line here" ... we get:
   [purple]1st line here
   
   2nd line here[/purple][/blue]


[blue]JoeAtWork[/blue]: its in the error handler portion:
Code:
[blue]   MsgBox "There was an Error Connecting to the DataBase!" & [purple][b]DL[/b][/purple] & _
          "ErrNum:" & Err.Number & ", " & Err.Description[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue] [thumbsup2]
Also faq181-2886 [blue]Worthy Reading![/blue] [thumbsup2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top