Trying to clean up null on close

  • Thread starter Thread starter RobUCSD via AccessMonster.com
  • Start date Start date
R

RobUCSD via AccessMonster.com

With the code below I'm trying to clean up nulls on close (via cmd button),
its propting for a parameter:

"Forms!frmPtDemographicNew!frmVisitNewEdit!fsubRNnotes.Form!fldRNnotes."

DoCmd.RunSQL "DELETE * FROM tblRNnotes WHERE [fldRNnotes] = [Forms]!
[frmPtDemographicNew]![frmVisitNewEdit]![fsubRNnotes].Form![fldRNnotes] Is
Null"
DoCmd.Close

I'm not sure what its asking for. Thanks for your help in advance, Rob
 
What it is saying it that SQL doesn't understand what

"[Forms]![frmPtDemographicNew]![frmVisitNewEdit]![fsubRNnotes].Form![fldRNnotes]"

means.

It is a reference to a control on a page, but you have it *inside* the
quotes. To get the value of the control, it needs to be outside the quotes.
You need to concatenate the form reference to the string. (see below)


Also, the forms reference looks wrong. Is this a nested subform?


Try rewriting it like this:

'-----------------------------------
Dim strSQL as String


strSQL = "DELETE * FROM tblRNnotes
strSQL = strSQL & " WHERE [fldRNnotes] = "
strSQL = strSQL &
[Forms]![frmPtDemographicNew]![frmVisitNewEdit]![fsubRNnotes].Form![fldRNnotes]
strSQL = strSQL & " Is Null"

'for debugging-----
DoCmd.Print strSQL
' or
'MsgBox strSQL
'------------------

DoCmd.RunSQL
DoCmd.Close


'------end-----------------------------

In looking at the SQL statement, I question the WHERE clause.
You have (I am going to shorten the reference to the form)

"WHERE [fldRNnotes] = " & Me.[fldRNnotes] & " Is Null"

This should (I didn't test) result is TRUE or FALSE

So it would eval to ..."WHERE TRUE Is Null" or ..."WHERE FALSE Is Null"

It would never be true and no records would ever be deleted.


Would "...WHERE [fldRNnotes] Is Null" work to delete the records you want
deleted?

Or you will have to write a delete query that has a WHERE clause that is the
conditions of the sub-sub form to select the records to be deleted.


One last thing. If you don't want the warnings that "DoCmd.RunSQL" gives,
change

DoCmd.RunSQL to Currentdb.Execute strSQL


HTH
 
Back
Top