How to run mysql within a subroutine from the Windows C: prompt

[imrosie]

Hello,

I'm trying to work the bugs out of my app. At compile time I get an error (grey bar covering the 'mysql')when I put the following code in:

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
    Call Form_Current
    End If
  End If
End Sub

thanks in advance

[nico5038]

I've moved your question to the Access forum, so all experts can have a look.

I'm a bit puzzled why you use "mysql", while you define;
dim strSQL

Both won't be needed when you use:

currentdb.execute ("delete * from X where ID=" & myID)

Nic;o)

[imrosie]

I've moved your question to the Access forum, so all experts can have a look.

I'm a bit puzzled why you use "mysql", while you define;
dim strSQL

Both won't be needed when you use:

currentdb.execute ("delete * from X where ID=" & myID)

Nic;o)

Someone gave this me and I followed it. I'm a newbie. Sorry, what should it be? thanks

[NeoPa]

Someone gave this me and I followed it. I'm a newbie. Sorry, what should it be? thanks

Rosie, try to bit a bit more specific showing which error message you got and on which line of the code (by number).

[imrosie]

Rosie, try to bit a bit more specific showing which error message you got and on which line of the code (by number).

Thanks for your response NeoPa....I finally got it working with help from thescripts, of course. Here's the code that works but has one more issue stated beneath:

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 mysql As String  'Declare the Variable by assigning SQL String to the variable mysql
 mysql = "DELETE * FROM imageInventory Where imageInventory.imageID = " & Me![imageID]
 'Execute the SQL Statement is next
DoCmd.RunSQL mysql
DoCmd.SetWarnings True
    Call Form_Current
    End If
  End If
End Sub

The other issue is that once the delete occurs, immediately the message #Deleted, pops up in each of the form controls. It causes a problem when (on same form) a user clicks the 'next' control or 'previous'...They get a msg saying 'Type mismatch' error. This error goes away after you close form and reopen (which apparently gets rid of the table deletes, so no error). I've tried using Me.Refresh immediately after the delete in the code, but it doesn't clear it out immediately, I still have to close form and re-open. Any ideas?

[NeoPa]

.Refresh won't do it for you. You need .Requery.
The former merely updates the data in records that already were included in your recordset.