Stuck with sql

[P Zero]

I have an unbound list box with two columns. It lists the records in a table called tblBuilding. I use this list box in a form to add or delete records from the table.

The problem I am having is with deleteing records. I can get it to work fine if I only have one column in my list box, but with two columns it won't work. I'm pretty sure the problem is with the vba behind the 'delete' button;

Private Sub BTNDelete_Click()

On Error GoTo PROC_ERR

strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] = """ & Me.ItemList & """;"

DoCmd.RunSQL strSQL
Me.ItemList.Requery

PROC_EXIT:
Exit Sub
PROC_ERR:
If Err.Number = 2501 Then
Else
End If
Resume PROC_EXIT

End Sub

I'm betting the problem is in the line beginning 'strSQL' in that I haven't defined the fields in the table properly, but to be honest I'm not sure of the correct syntax. Can anyone point me in the right direction?

 

[namliam]

Your delete query in itself is OK. The one thing I think could be wrong...

ID seems to indicate it is a number field. If it is a number field you dont use "s around your field.

"s you use for strings... # for dates, and for number, nothing.
i.e.
Number: "[fldBuildingId] = " & Me.ItemList & ";"
String/text: "[fldBuildingId] = """ & Me.ItemList & """;"
Date: "[fldBuildingId] = #" & Me.ItemList & "#;"

Good luck !

 

[P Zero]

Hi namliam, thanks for your help, that certainly helps me understand whats going on a bit better, but...

fldBuildingID is a user defined field and as such is a text string, so I think the syntax is correct. Any other ideas?

 

[Kiwiman]

Howzit

I believe for text you need a single quote mark, not the double quote. All my SQL scripts in vb, when referencing a text field use the single quote.

"[fldBuildingId] = "'" & Me.ItemList & "'"

 

[Kiwiman]

or possibly may be more accurate

"[fldBuildingId] = '" & Me.ItemList & "'"

 

[P Zero]

Thanks Kiwiman, although still no luck I'm afraid.

strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] =  '" & Me.ItemList & "'";"

 

[Kiwiman]

if you insert the below after you have finished creating the SQL statement the actual statement will show in the immediate window (this may have to be open)

It should show what has been assigned to the variable strSQL. Post the result or see if you can see any errors on the strSQL.

debug.print strSQL

 

[P Zero]

It says there is a syntax error with the code below;

strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] =  '" & Me.ItemList & "'";"

 

[BrunoBB]

I could be wrong, but I think there's an extra double quote just after the last single quote on the second line. I've rewritten your SQL below...

strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] = '" & Me.ItemList & "';"

 

[Kiwiman]

in the immediate window it will show something like

DELETE * FROM [tblBuilding] WHERE [fldBuildingId] =  'A'

where A is the value from the list. The debug.print statement should go after the strSQL = "Delete..." and before the docmd.runsql

I pasted your code in a function of mine to test the syntax building and it did not like the last double quote and the semi colon.

try

strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] =  '" & me.ItemList & "'"

 

[P Zero]

Thanks, there is now no syntax error, but nothing happens when I press my delete button. I'm really confused as this works perfectly when only one field is listed in the list box. When I have 2 fields showing, it just wont work.

 

[Kiwiman]

Sorry not an expert on multi-select list box. But you will need some code to loop through all your selections chosen from the list box and assign to a variable. You will also need to change the "=" in your strSQL to "IN"

I found this in one of the databases I was given but have not pulled it apart to see how it works. I'm not sure where the database came from originally

Something like

' Loop through the selected items in the list box and build a text string
    For Each varItem In Me!ItemList.ItemsSelected
        strCriteria = strCriteria & ",'" & Me!ItemList.ItemData(varItem) & "'"
    Next varItem

' Check that user selected something
    If Len(strCriteria) = 0 Then
        MsgBox "You did not select anything from the list" _
            , vbExclamation, "Nothing to find!"
        Exit Sub
    End If
' Remove the leading comma from the string
    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string
    strSQL = "DELETE * FROM [tblBuilding] WHERE " & _
"[fldBuildingId] IN (" & strCriteria & ")"

 

[Kiwiman]

This is the actual database where the code came from. Again, I don't know where this came from.

 

[namliam]

fldBuildingID is a column in your table, NOT a user defined field at all, yes user defined but... in the table. ID -generaly- indicates a number... but if you are not getting an error on your excisting code, but simply "it is not working with 2 columns in the box"
Then perhaps you need to select the other column? Is your listbox/combobox 'bound' to the right field.

A list/combobox is a 0-based-array.
Me.Listbox.Column(0) will return your first column
Me.Listbox.Column(1) will return your second column

But ONLY of the selected row.

@Kiwiman
In access both single and double quotes work. If you go to SQL (oracle or SQL Server), yes single quotes only...
But in Access this "[fldBuildingId] = '" & Me.ItemList & "'"
is the same as "[fldBuildingId] = """ & Me.ItemList & """"
is the same as "[fldBuildingId] = " & chr(34) & Me.ItemList & chr(34)

 

[Kiwiman]

Thanks Mailman. I've always had problems witht he double quote, but will now look more closely at these alternatives above.

 

[P Zero]

Thanks both of you for all your help. It works great once I specify the column that the data in the listbox is coming from, ie; Me.ItemList.Column(1)

Although I realise that 'ID' is generally used for numerical fields, in this case the user's data is actually called Building ID, and although usually a number, can sometimes contain text or other characters, such as 0-01.

Learning more all the time

Thanks again,

Chris.