asked on

Question about Deleting records using VBA

I am writing some code to delete records from a table, but I am pretty sure I am going about it wrong.

The thing that is hanging the code up (I think) is that sometimes there will be a null value in one of the fields. when I run this code and there is no null value, it works fine. When there is a null value, it tells me I have a syntax error.

I am also thinking that I am using Len wrong. It is for string length, and I am using it here to check to see if there is a value in the PersonID field. Is that appropriate?

Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strSQL As String
Dim i As Variant

Set frm = Forms("f_SubTaskAssignments")
Set ctl = frm![lstExistingAssignments]
Set db = CurrentDb

For Each i In ctl.ItemsSelected

'***For future reference - Add chr(34) around text!

If Len(" & ctl.Column(7, i) & ") > 0 Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
End If


If Len(" & ctl.Column(7, i) & ") = Null Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & "  And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
        
End If
db.Execute strSQL, dbFailOnError



Next i

ctl.Requery
End Sub

Open in new window

I know this is lots of wrong and I look forward to getting some advice on how to make it right.

Thank you!

Jeffrey Coachman

Not sure I understand your code...
This line:
For Each i In ctl.ItemsSelected
...will commonly refer to controls selected while the form is in design View...
(so you might want to comment out that loop...)

In any event, ...
I have sometimes had bad luck when using LEN() to check for nulls
Try IsNull instead, something roughly like this:

If IsNull(XXX) then...
Your Delete Code
End If

Lets see if another expert spots something more specific...

JeffCoachman

Beartlaoi

Checking for NULL in SQL requires different syntax. Rather than "= NULL" you must use "IS NULL", this is a pain with ad-hoc queries but there is a way.

"[TaskOrderID]" & IIF(ISNULL(ctl.Column(0, i)), " = " & ctl.Column(0, i) , " IS NULL" ) &

Open in new window

although to make your code more readable use this

Function MyEq(Expr As String) As String
     MyEq = IIf(IsNull(Expr), " IS NULL", " = """ & Expr & """")
End Function

"[TaskOrderID]" & MyEq(ctl.Column(0, i)) &

Open in new window

Megin

ASKER

Beartlaoi: I am a bit confused on how to use the IIF as opposed to the If statement. I didn't get very far with that. But I am sure that I my ignorance.

I also tried using IsNull and it didn't help.

I am getting a syntax error. It states that there is a missing operator in my query expression.

I went and made PersonID = null in the second sql statement because I thought that it might be looking for that, but nothing changed when I added it. I also tried PersonID IsNull.

Can you guys take another look or explain to me again?

If Len(" & ctl.Column(7, i) & ") > 0 Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
End If



If IsNull(" & ctl.Column(7, i) & ") Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " AND [PersonID]= Null And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
        
End If
db.Execute strSQL, dbFailOnError

Open in new window

Megin

ASKER

This still isn't working. I don't think my first If statement is right. I have tried it a few different ways.

If ctl.Column(7, i) = Null Then
Debug.Print " & ctl.Column(7, i) & "

strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " AND [PersonID]= Null And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
Else
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""


        
End If
db.Execute strSQL, dbFailOnError



Next i

ctl.Requery
End Sub

Open in new window

The debug.print isn't working. I am not sure what I am doing wrong here.

ASKER CERTIFIED SOLUTION

Beartlaoi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Beartlaoi

The problem with your original line 15

If Len(" & ctl.Column(7, i) & ") > 0 Then

Open in new window

is that it is a constant, that is it is getting the length of & ctl.Column(7, i) & Everything between the quotes is taken exactly, not evaluated, so it is always 22 characters.

and the other If on line 21, Len will never return null, it should look like

If IsNull(ctl.Column(7, i)) Then

Open in new window

Do you expect that other columns on the form will be null?
What is the output of

Debug.Print IIf(IsNull(ctl.Column(7, i)), "(null)", """" & ctl.Column(7, i) & """")

Open in new window

* Explanation of IIf, it takes three inputs, a boolean and two variants
IIf( TrueOrFalse, TruePart, FalsePart)
If the first parameter is true then it returns the true part, if the first parameter is false then it returns the false part
It is literally this function

Function IIf(bWhich as boolean, vTruePart as variant, vFalsePart as variant) as Variant
  if bWhich = True Then
    IIf = vTruePart
  else
    IIf = vFalsePart
End If

Open in new window

Megin

ASKER

Setting it equal to "" rather than Null worked!!!!!!!

THANK YOU!!!!!!