Completely delete a record inan event

[imrosie]

Hello,

I have a form that provides a command button for an erase of a image record. However, it only removes the link (image description) to the image, leaving behind full record all the other fields associated with that image. This becomes a problem when a user is searching hrough all records. They get a Null image screen, but the description (auther and date) are still there. How can I completely remove that entire record instead. Here's the code below. thanks in advance

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
Call Form_Current
End If
End If
End Sub

[ADezii]

Hello,

I have a form that provides a command button for an erase of a image record. However, it only removes the link (image description) to the image, leaving behind full record all the other fields associated with that image. This becomes a problem when a user is searching hrough all records. They get a Null image screen, but the description (auther and date) are still there. How can I completely remove that entire record instead. Here's the code below. thanks in advance

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
Call Form_Current
End If
End If
End Sub

DoCmd.SetWarnings False
Dim strSQL As String
  'DELETES the Current Record
  MySQL = "DELETE * FROM <your table> Where <your table>.<Primary Key> = " & Me![Primary Key Field]
  DoCmd .RunSQL strSQL
  'Move the Record Pointer in some manner
DoCmd.SetWarnings True

[imrosie]

DoCmd.SetWarnings False
Dim strSQL As String
  'DELETES the Current Record
  MySQL = "DELETE * FROM <your table> Where <your table>.<Primary Key> = " & Me![Primary Key Field]
  DoCmd .RunSQL strSQL
  'Move the Record Pointer in some manner
DoCmd.SetWarnings True

Thanks so much for your quick reply. Do I put this code into the event for the command after my code? thanks

[imrosie]

Hello,

I tried putting the sql into the event as follows:

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("The image will be removed permanantly, are you sure?", vbYesNo + vbQuestion) = vbYes Then
'Me!frmimagesubform.Form![imgPicture].Picture = ""
'Me![imageFile] = Null
'SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
DoCmd .RunSQL strSQL
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub

The module tells me it doesn't like the 'Docmd.' please help. thanks

[ADezii]

Hello,

I tried putting the sql into the event as follows:

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("The image will be removed permanantly, are you sure?", vbYesNo + vbQuestion) = vbYes Then
'Me!frmimagesubform.Form![imgPicture].Picture = ""
'Me![imageFile] = Null
'SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
DoCmd .RunSQL strSQL
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub

The module tells me it doesn't like the 'Docmd.' please help. thanks

What Version of Access are you running?

[imrosie]

I'm running Access 2002. thanks

[ADezii]

Hello,

I tried putting the sql into the event as follows:

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("The image will be removed permanantly, are you sure?", vbYesNo + vbQuestion) = vbYes Then
'Me!frmimagesubform.Form![imgPicture].Picture = ""
'Me![imageFile] = Null
'SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
DoCmd .RunSQL strSQL
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub

The module tells me it doesn't like the 'Docmd.' please help. thanks

DoCmd .RunSQL strSQL should be:

DoCmd .RunSQL mysql

[imrosie]

Hello,

Thanks for your help, I appreciate. I changed the vb code as you suggested (and recompiled). Now the code tells me that it doesn't understand the line (as follows):

mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]

In fact, the 'mysql' in that line is now covered with a grey bar. May I ask also what is the 'Dim strSQL As String' used for in this scenario? Because once I switched it out and changed that line from:

DoCmd.RunSQL strSQL
TO
DoCmd.RunSQL mysql

I don't see where it going to be used. thanks again.

[imrosie]

Hi, just tried another way of putting the complete path to mysql; it give me a problem with the "C:\"Program Files..ect. I've tried "C:"\Program Files, etc. still get the problem that "mysql" variable isn't defined.

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
"C:\"Program Files\MySQL\MySQL Server 4.1\bin\mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
DoCmd.RunSQL mysql
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub
Call Form_Current
End If
End If
End Sub

thanks for your time.

[imrosie]

I've tried several ways and nothing works...do I need to put the word execute preceding the mysql?

I've just put everything back to the following; it just doesn't like the 'mysql' at beginning of line with ( ="DELETE * FROM imageInventory Where imageInventory.[imageID] = " & Me![imageID]

thanks again.


Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
mysql = "DELETE * FROM imageInventory Where imageInventory.[imageID] = " & Me![imageID]
DoCmd.strSQL mysql
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub
Call Form_Current
End If
End If
End Sub

[imrosie]

I've tried several ways and nothing works...do I need to put the word execute preceding the mysql?

I've just put everything back to the following; it just doesn't like the 'mysql' at beginning of line with ( ="DELETE * FROM imageInventory Where imageInventory.[imageID] = " & Me![imageID]

thanks again.


Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
mysql = "DELETE * FROM imageInventory Where imageInventory.[imageID] = " & Me![imageID]
DoCmd.strSQL mysql
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub
Call Form_Current
End If
End If
End Sub

In fact, can't I just remove these 3 lines from my code and get a better result once I get the 'mysql' working? thanks again
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus

[ADezii]

Hi, just tried another way of putting the complete path to mysql; it give me a problem with the "C:\"Program Files..ect. I've tried "C:"\Program Files, etc. still get the problem that "mysql" variable isn't defined.

Private Sub cmdErasePic_Click()
If Not IsNull([imageFile]) Then
If MsgBox("This image will be permanantly removed, Are you sure?", vbYesNo + vbQuestion) = vbYes Then
Me!frmimagesubform.Form![imgPicture].Picture = ""
Me![imageFile] = Null
SysCmd acSysCmdClearStatus
DoCmd.SetWarnings False
Dim strSQL As String
'DELETES the Current Record
"C:\"Program Files\MySQL\MySQL Server 4.1\bin\mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
DoCmd.RunSQL mysql
'Move the Record Pointer IN some manner
DoCmd.SetWarnings True
End If
End If
End Sub
Call Form_Current
End If
End If
End Sub

thanks for your time.

just tried another way of putting the complete path to mysql; it give me a problem with the "C:\"Program Files..ect. I've tried "C:"\Program Files, etc. still get the problem that "mysql" variable isn't defined.

Why are you attempting to set a Path to mysql? it is a String Variable and will contain the SQL String to be executed. Here are your 'Critical' lines of code, do not attempt to modify them in any manner:

Dim mysql As String       'Declare the Variable
 
'assign SQL String to the Variable mysql
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
 
'Execute the SQL Statement
DoCmd.RunSQL mysql

Double check the following:

1. Is the Table Name imageInventory?
2. Are you referring to the proper Field on the Form, namely imageID?
3. If the response to the above question was Yes, is imageID a Numeric Field? If it is not, then the Syntax has to be changed.

[imrosie]

Why are you attempting to set a Path to mysql? it is a String Variable and will contain the SQL String to be executed. Here are your 'Critical' lines of code, do not attempt to modify them in any manner:

Dim mysql As String       'Declare the Variable
 
'assign SQL String to the Variable mysql
mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
 
'Execute the SQL Statement
DoCmd.RunSQL mysql

Double check the following:

1. Is the Table Name imageInventory?
2. Are you referring to the proper Field on the Form, namely imageID?
3. If the response to the above question was Yes, is imageID a Numeric Field? If it is not, then the Syntax has to be changed.

Thanks. Yes the tbl name is imageInventory and the imageID is a numeric field. I was attempting to use the code given me on the first post to this discussion.
Do I need the DoCmd.SetWarnings False and DoCmd.SetWarnings True statements now as suggested in the first post.

[cgrider]

Thanks. Yes the tbl name is imageInventory and the imageID is a numeric field. I was attempting to use the code given me on the first post to this discussion.
Do I need the DoCmd.SetWarnings False and DoCmd.SetWarnings True statements now as suggested in the first post.

you need them only if you dont want a confirmation on the deletion of the record in the table which, in most cases, you do not want.

CG