Solved: VBA delete SQL record with null value

[Movian]

Hey,

I have a listbox that displays a user editable list of options taken from a table. I have this code as part of a delete function no this works find when there is text in the value field for the record. However if the value field is an empty string or null it is unable to locate the record to delete.

Any suggestions ?

For Each varItm In Me.DropList.ItemsSelected
myrs.Open "SELECT * FROM tblLookupValues WHERE Form1 = '" & txtForm & "' AND Control = '" & txtControl & "' AND Value = '" & Me.DropList.ItemData(varItm) & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
MsgBox myrs.RecordCount
myrs.delete
myrs.Close
Next

As usual any help is appreciated.

[OBP]

Movian, I don't quite get the problem, if the search has text and the table record doesn't then you can't get a match.
So is the Search Text also NULL and you are trying to find Null Records?
If so then surely you need an If then to change the SQL to look for a null value?

[Movian]

you know i think your right.... i was not having a good day that day.
Let me change that and get back to you.

[Movian]

Solved

Final solution
For Each varItm In Me.DropList.ItemsSelected
If IsNull(Me.DropList.ItemData(varItm)) Or Me.DropList.ItemData(varItm) = "" Then
myrs.Open "SELECT * FROM tblLookupValues WHERE Form1 = '" & txtForm & "' AND Control = '" & txtControl & "' AND (Value IS NULL OR Value = '' OR Value = ' ')", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Else
myrs.Open "SELECT * FROM tblLookupValues WHERE Form1 = '" & txtForm & "' AND Control = '" & txtControl & "' AND Value = '" & Me.DropList.ItemData(varItm) & "'", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
End If
'MsgBox myrs.RecordCount
myrs.delete
myrs.Close
Next

I guess sometimes when you have been staring at the code too long you just need a second pair of eyes to spot the blindingly obvious.