Message Box

[jjsaw5 via AccessMonster.com]

Hello again,

I'd like to prompt my users with a " are you sure you want to delete" message.
...or somethings along those lines when they attempt to delete a record. I've
never doen a pop up message before so any help will be greatly apprichiated!!!


Thanks in advance!!

 

[Guest]

If MsgBox("Are you sure you want to delete", vbQuestion + vbYesNo,
"Confirm Delete") = vbYes Then
' Do the delete
Else
' Don't Do the Delete
End If

 

[jjsaw5 via AccessMonster.com]

I'm not really sure where to put it in my code

Here is the code for my delete


Private Sub Command37_Click()
On Error GoTo Err_Command37_Click



Dim strSQL
Dim strSQLCols As String
Dim strSQLWhere As String
Dim index As Integer



'**********************************************************
'build a sql statement to update all of the columns to null
'**********************************************************
strSQL = "UPDATE " & Me.RecordSource & " SET "


'*************************************************************************************************
'spin through all of the active record columns and set the Columns clause
and Where clause critera

'*************************************************************************************************
For index = 0 To Me.Recordset.fields.Count - 1

strSQLCols = strSQLCols & "[" & Me.Recordset.fields(index).name & "]
" & " = NULL" & ","

'***********************************************
'enclose the field value in quotes if applicable
'***********************************************
If Len(Trim(Me.Recordset.fields(index))) > 0 Then
Select Case Me.Recordset.fields(index).name
Case "SR Num", "Tracker Item"
strSQLWhere = strSQLWhere & "[" & Me.Recordset.fields
(index).name & "]" & "=" & Me.Recordset.fields(index).Value & " AND "
Case Else
strSQLWhere = strSQLWhere & "[" & Me.Recordset.fields
(index).name & "]" & "='" & Me.Recordset.fields(index).Value & "' AND "
End Select
End If

Next index

'**********************************************************************
'remove the trailing comma delimiters from the columns and where clause
'**********************************************************************
If Right(strSQLCols, 1) = "," Then strSQLCols = Left(strSQLCols, Len
(strSQLCols) - 1)
If Right(strSQLWhere, 5) = " AND " Then strSQLWhere = Left(strSQLWhere,
Len(strSQLWhere) - 5)

'***********************************
'build the update SQL and execute it
'***********************************
If Len(strSQLWhere) > 0 Then
strSQL = strSQL & strSQLCols & " WHERE " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
cmbSRNum.Requery
cmbSRNum = Null
End If

'****************
'refresh the form
'****************
Me.Refresh


Exit_Command37_Click:
Exit Sub

Err_Command37_Click:
MsgBox Err.Description
Resume Exit_Command37_Click

End Sub

 

[Guest]

This is a very strange thing to do. Why are you filling existing records
with Null values? Why not delete the records?
But, in any case, see below for location of code

Also, instead of:
DoCmd.RunSQL strSQL
If you use:
CurrentDb.Execute(strSQL), dbFailOnError

It will execute faster and you will not have to set Warnings on or off.

--
Dave Hargis, Microsoft Access MVP

I'm not really sure where to put it in my code

Here is the code for my delete


Private Sub Command37_Click()
On Error GoTo Err_Command37_Click



Dim strSQL
Dim strSQLCols As String
Dim strSQLWhere As String
Dim index As Integer

CODE SHOULD BE HERE

If MsgBox("This is not a real Delete, but what the heck", vbQuestion
+ vbYesNo, "Confirm Psuedo Delete") = vbNo Then
Exit Sub
End If

'**********************************************************
'build a sql statement to update all of the columns to null
'**********************************************************
strSQL = "UPDATE " & Me.RecordSource & " SET "


'*************************************************************************************************
'spin through all of the active record columns and set the Columns clause
and Where clause critera

'*************************************************************************************************
For index = 0 To Me.Recordset.fields.Count - 1

strSQLCols = strSQLCols & "[" & Me.Recordset.fields(index).name & "]
" & " = NULL" & ","

'***********************************************
'enclose the field value in quotes if applicable
'***********************************************
If Len(Trim(Me.Recordset.fields(index))) > 0 Then
Select Case Me.Recordset.fields(index).name
Case "SR Num", "Tracker Item"
strSQLWhere = strSQLWhere & "[" & Me.Recordset.fields
(index).name & "]" & "=" & Me.Recordset.fields(index).Value & " AND "
Case Else
strSQLWhere = strSQLWhere & "[" & Me.Recordset.fields
(index).name & "]" & "='" & Me.Recordset.fields(index).Value & "' AND "
End Select
End If

Next index

'**********************************************************************
'remove the trailing comma delimiters from the columns and where clause
'**********************************************************************
If Right(strSQLCols, 1) = "," Then strSQLCols = Left(strSQLCols, Len
(strSQLCols) - 1)
If Right(strSQLWhere, 5) = " AND " Then strSQLWhere = Left(strSQLWhere,
Len(strSQLWhere) - 5)

'***********************************
'build the update SQL and execute it
'***********************************
If Len(strSQLWhere) > 0 Then
strSQL = strSQL & strSQLCols & " WHERE " & strSQLWhere
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
cmbSRNum.Requery
cmbSRNum = Null
End If

'****************
'refresh the form
'****************
Me.Refresh


Exit_Command37_Click:
Exit Sub

Err_Command37_Click:
MsgBox Err.Description
Resume Exit_Command37_Click

End Sub