By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Completely delete a record inan event

imrosie
100+
P: 222
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
May 18 '07 #1
Share this Question
Share on Google+
13 Replies


ADezii
Expert 5K+
P: 8,619
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
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Dim strSQL As String
  3.   'DELETES the Current Record
  4.   MySQL = "DELETE * FROM <your table> Where <your table>.<Primary Key> = " & Me![Primary Key Field]
  5.   DoCmd .RunSQL strSQL
  6.   'Move the Record Pointer in some manner
  7. DoCmd.SetWarnings True
May 18 '07 #2

imrosie
100+
P: 222
Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. Dim strSQL As String
  3.   'DELETES the Current Record
  4.   MySQL = "DELETE * FROM <your table> Where <your table>.<Primary Key> = " & Me![Primary Key Field]
  5.   DoCmd .RunSQL strSQL
  6.   'Move the Record Pointer in some manner
  7. 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
May 18 '07 #3

imrosie
100+
P: 222
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
May 18 '07 #4

ADezii
Expert 5K+
P: 8,619
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?
May 18 '07 #5

imrosie
100+
P: 222
I'm running Access 2002. thanks
May 20 '07 #6

ADezii
Expert 5K+
P: 8,619
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd .RunSQL mysql
May 20 '07 #7

imrosie
100+
P: 222
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.
May 21 '07 #8

imrosie
100+
P: 222
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.
May 21 '07 #9

imrosie
100+
P: 222
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
May 21 '07 #10

imrosie
100+
P: 222
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
May 21 '07 #11

ADezii
Expert 5K+
P: 8,619
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:
Expand|Select|Wrap|Line Numbers
  1. Dim mysql As String       'Declare the Variable
  2.  
  3. 'assign SQL String to the Variable mysql
  4. mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  5.  
  6. 'Execute the SQL Statement
  7. 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.
May 21 '07 #12

imrosie
100+
P: 222
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:
Expand|Select|Wrap|Line Numbers
  1. Dim mysql As String       'Declare the Variable
  2.  
  3. 'assign SQL String to the Variable mysql
  4. mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
  5.  
  6. 'Execute the SQL Statement
  7. 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.
May 21 '07 #13

P: 19
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
May 21 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.