simple delete SQL statement not working

[markjkubicki]

I've got this quirk that occassionally creates a blank record, so to clean them up, I've put in a snippet that should delete all of those blanks on the next form current event, but it's not working...



the records are in a table related to the form's source table and populate a subform; the user can add new records on the subform
Public Sub DoSQLDeleteBlankInstallationNotes(frm As Access.Form)
Dim sSQL As String
sSQL = "Delete from tbeInstallationNotes" & _
" WHERE len(nz([InstallationNote],"")) =0;"
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Sub

so to check myself, on the sub form right next to the text box with the value [InstallationNote], I added another text box whose value is:
=len(nz([InstallationNote],""))
low and behold, blank records have a zero value

and then another text box
=len(nz([InstallationNote],"")) = 0
and I get -1's

dispite this, the blank records do not delete (?!)

preplexed
(but with thanks in advance),
mark

[Beetle]

I've got this quirk that occassionally creates a blank record,

Sort of wondering why you don't fix the "quirk" rather than continually deleting records after the fact. However, be that as it may...

the records are in a table related to the form's source table and populate a subform

What's the nature of the relationship? If you have RI enforced and you do not have Cascade Delete Related Records set, then you cannot delete a record from the parent table because it would leave orphaned records in the child table. Normally you would get a message informing you of this but you have turned off warnings in the code so you have no idea that something went wrong. I recommend you use -

CurrentDb.Execute sSQL, dbFailOnError

instead of

DoCmd.RunSQL sSQL

- because it will bypass the unnecessary (and annoying) standard warnings about "you are about to delete x records in a table" but will still inform you if there is an error during the process (provided that you make sure and include the dbFailOnError argument). So you don't need to worry about turning the warnings off and on, which is a potential problem in and of itself because if you are not careful how you structure the code then the warnings could get turned off but not get turned back on again. So the code would just be;

Public Sub DoSQLDeleteBlankInstallationNotes(frm As Access.Form)

    Dim sSQL As String

    sSQL = "Delete from tbeInstallationNotes" & _
    " WHERE len(nz([InstallationNote],"")) =0;"

    CurentDb.Execute sSQL, dbFailOnError

End Sub

[markjkubicki]

(pardonte pause... my attentiosn on this is, unfortunately, intermittent)

to your ?'s

why you don't fix the "quirk"

I'd like to, and have tried; haven give up for the time being.... I thought a work-around was worth venturing

the problem is that on a subform, I have a command button that flips the background color of the sfrm (generally to yellow) as a user triggered alert that they need to do something to the information on the form (finish editing, correct, verify... something); if the subform has no current records, when the background color is changed (by VBA), a blank record is autamatically added ?!?!?

What's the nature of the relationship?

IR is not reinforced; the table that I am deleting the records from has a many-to-one retaionship to the table datasource for the parent table; i am deleting from the many table ONLY

CurrentDb.Execute sSQL, dbFailOnError

good point, I'll correct here, and adheare to your suggestion in all instances forthcoming (THANX);
having changed the code, no errors were flagged; however, adding msg boxes as a crude debugger: the first (sSQL) showed... the second ("no error") does not

Dim sSQL As String
sSQL = "Delete from tbeInstallationNotes" & _
" WHERE len(nz([InstallationNote],"")) = 0;"
MsgBox (sSQL)
CurrentDb.Execute sSQL, dbFailOnError
MsgBox "no error"

---------------------
curiously, a similar block of code executes correctly:
Dim sSQL As String
sSQL = "Delete from tbeInstallationNotes" & _
" WHERE [BaseInstallationNote] = true and [Type] = '" & frm.Type.Value & "';"
CurrentDb.Execute sSQL, dbFailOnError

[ssanfu]

Have you tried setting a breakpoint on the line

sqL = "Delete........

then stepped (F8 key) through the code to see if you reach the 2nd message box line? A little debugging goes a long way...
Do you have an error handler in this code?

[markjkubicki]

as has been said before:
"when nothing else works... check the obvious"

syntax error in string query expression 'len(nz([InstallationNote],"")) = 0;'.

-now I have no idea what that could be, but at least I know more than I did before

[ssanfu]

Dim sSQL As String
    sSQL = "Delete from tbeInstallationNotes" & _
        " WHERE len(nz([InstallationNote],"")) = 0;"
MsgBox (sSQL)

I must be brain dead today....

Try this:

    Dim sSQL As String

    sSQL = "DELETE * FROM tbeInstallationNotes" & _
           " WHERE (((Len(nz([InstallationNote],"")))=0));"
    
    MsgBox (sSQL)
    CurrentDb.Execute sSQL, dbFailOnError

[markjkubicki]

nope... sorry... same error

[ssanfu]

The code worked in my test dB.....

Do you have a reference set for Microsoft DAO 3.6 Object Library?

Since the records in the many table that are created have no data, how about this:

    Dim sSQL As String

    sSQL = "DELETE *"
    sSQL = sSQL & " FROM tbeInstallationNotes"
    sSQL = sSQL & " WHERE (((tbeInstallationNotes.InstallationNote) Is Null));"

    MsgBox (sSQL)
    CurrentDb.Execute sSQL, dbFailOnError

[Missinglinq]

I wondered about this, too:

...Do you have a reference set for Microsoft DAO 3.6 Object Library...

because of

...syntax error in string query expression 'len(nz([InstallationNote],"")) = 0;'

The 'syntax error,' plus the fact that Intellisense apparently didn't recognize Len and Nz as valid functions (if it had, it would have capitalized the first letter of each one) tends to support the possibility of a Missing Reference.

If you haven't checked for this kind of thing before, here are Doug Steele's detailed instructions on how to troubleshoot the problem:

Access Reference Problems

Linq ;0)>

[markjkubicki]

trying to enable Microsoft DAO 3.6 Object Library results in the error: " Name conflicts with existing module, project, or object library "