Conditional deletion of records

  • Thread starter Thread starter TESA0_4
  • Start date Start date
T

TESA0_4

I have persued many threads and found code that has come close to solving my
problem but not quite all the way.
I have a sub form set to Continuous Form that displays the 'steps' that make
up a larger overall task. Where necessary, 'special conditions' need to be
defined for each 'step'. A 'step' can have none or many 'special conditions'
that are stored in a separate table that includes the 'stepID'. Each 'step'
record is uniquely identified and each 'special condition' record is uniquely
identified.
When users are drafting 'steps' and 'special conditions' they need to be
able to delete records; however, they must be prevented from deleting a
'step' if it has related 'special conditions'.
I have a delete button on the subform for each 'step' but my current coding
only manages to delete EVERY 'step' record that does not have 'special
conditions'. I only want to delete one specific 'step' record if there are no
related 'special conditions'.
Can anyone help me out? Thank you in anticipation.
 
What does the code in your "Delete" button look like?

My guess is that your SQL statement fails to reference the ID value of the
specific Step you are on. I suspect that the SQL should look something like:

DELETE * FROM yourTable
WHERE yourTable.StepID = Forms!yourForm.txt_StepID
AND NOT EXISTS(SELECT *
FROM tbl_SpecialConditions
WHERE StepID = Forms!yourForm.txt_StepID)

HTH
Dale
 
Hi Dale,
Thanks for your advice. You are perfectly correct - I was not referencing
the specific 'step' I was trying to test for deletion and did not know how to
get the SQL and syntax right.

I have worked with your code and find that the first part of the statement
DELETE FROM WHERE works fine. The problem seems to be with the
keywords/syntax of the AND.... portion of the statement. I've tried many
syntax variations without success. For example, the following code results in
the error message detailed below. I guess it has something to do with the
brakets ():

strSQL = "DELETE FROM tblSteps WHERE tblSteps.StepID =" & Me.txtStepID.Value
& "AND NOT EXISTS " & _
"(SELECT StepID FROM tblSpecialConditions WHERE StepID =)" &
Me.txtStepID.Value

The error says:

Syntax error in expression 'tblSteps.StepID =32AND NOT EXISTS (SELECT StepID
FROM tblSpecialConditions WHERE StepID =)32

Needless to say 32, is the StepID I am testing for deletion.

Any further assistance you can provide would be appreciated.

Regards,
Terry
 
If you are going to write it this way, you need to make sure you have spaces
and ( ) in the correct places. In your case there are a couple of problems/

1. With JET SQL you also need to indicate what to delete, in this case
"DELETE *".
2. You need a space between the txtStepID.Value and the "AND"
3. You put the closing ) in front of the value in the Exists clause

Try this.I generally insert spaces at the end of my lines, followed by the "
and the _ to indicate a line wrap. I do this to make it more readable.

strSQL = "DELETE * FROM tblSteps " _
& "WHERE tblSteps.StepID =" & Me.txtStepID.Value & " " _
& "AND NOT EXISTS (SELECT StepID " _
& "FROM tblSpecialConditions "
_
& "WHERE StepID = " &
Me.txtStepID.Value & ")"

Another way to do this would be to eliminate the Exists clause and just
check to see whether the

strCriteria = "[StepID] = " & me.txtStepID.Value
if DCOUNT("StepID", "tblSpecialConditions", strCriteria) = 0 then
strSQL = "DELETE * FROM tblSteps " _
& "WHERE StepID = " & me.txtStepID.Value
Currentdb.execute strSQL
Else
msgbox "Cannot delete a step which contains sub-steps"
END IF


HTH
Dale
 
Hi Dale,

Magic! The function now works a treat. I'm clearly in need of a couple of
good reference texts on SQL to support my various VBA an MS Access texts.
Thanks for your assistance.

Dale Fye said:
If you are going to write it this way, you need to make sure you have spaces
and ( ) in the correct places. In your case there are a couple of problems/

1. With JET SQL you also need to indicate what to delete, in this case
"DELETE *".
2. You need a space between the txtStepID.Value and the "AND"
3. You put the closing ) in front of the value in the Exists clause

Try this.I generally insert spaces at the end of my lines, followed by the "
and the _ to indicate a line wrap. I do this to make it more readable.

strSQL = "DELETE * FROM tblSteps " _
& "WHERE tblSteps.StepID =" & Me.txtStepID.Value & " " _
& "AND NOT EXISTS (SELECT StepID " _
& "FROM tblSpecialConditions "
_
& "WHERE StepID = " &
Me.txtStepID.Value & ")"

Another way to do this would be to eliminate the Exists clause and just
check to see whether the

strCriteria = "[StepID] = " & me.txtStepID.Value
if DCOUNT("StepID", "tblSpecialConditions", strCriteria) = 0 then
strSQL = "DELETE * FROM tblSteps " _
& "WHERE StepID = " & me.txtStepID.Value
Currentdb.execute strSQL
Else
msgbox "Cannot delete a step which contains sub-steps"
END IF


HTH
Dale

TESA0_4 said:
Hi Dale,
Thanks for your advice. You are perfectly correct - I was not referencing
the specific 'step' I was trying to test for deletion and did not know how
to
get the SQL and syntax right.

I have worked with your code and find that the first part of the statement
DELETE FROM WHERE works fine. The problem seems to be with the
keywords/syntax of the AND.... portion of the statement. I've tried many
syntax variations without success. For example, the following code results
in
the error message detailed below. I guess it has something to do with the
brakets ():

strSQL = "DELETE FROM tblSteps WHERE tblSteps.StepID =" &
Me.txtStepID.Value
& "AND NOT EXISTS " & _
"(SELECT StepID FROM tblSpecialConditions WHERE StepID =)" &
Me.txtStepID.Value

The error says:

Syntax error in expression 'tblSteps.StepID =32AND NOT EXISTS (SELECT
StepID
FROM tblSpecialConditions WHERE StepID =)32

Needless to say 32, is the StepID I am testing for deletion.

Any further assistance you can provide would be appreciated.

Regards,
Terry
Click to expand...
Click to expand...
 
Back
Top