strSQL type mismatch

[corner40]

hi guys
so i'm getting an run-time error 13, type mismatch, error message.
I have a command button that is supposed to delete the selected record in a listbox based on the activityID(autonum).
when the command button is clicked this is the code being run:

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
If Me.lstCurrentlyBooked.ItemsSelected.Count > 0 Then
Set db = CurrentDb()
strSQL = "SELECT * FROM [ActivitiesBooked] WHERE [ActivityID] = " & Me.lstCurrentlyBooked.Column(0) & " ; "
MsgBox strSQL
Set rs = db.OpenRecordset(strSQL)
rs.Delete
rs.Close
Set rs = Nothing
End If
Me.lstCurrentlyBooked.Requery

is bombs on set rs = db.openrecordset(strSQL)
the correct values is coming across. ex. activityid = 55
any suggestions would be great.
thanks
Jeremy

 

[BobAtHome]

Instead of opening a recordset, try deleting using DoCmd and RunSql, as follows:

Private Sub cmdDelete_Click()
Dim strSql As String
Dim intActivityId As Integer
intActivityId = lstCurrentlyBooked
strSql = "Delete from ActivitiesBooked where ActivityId = " & intActivityId & ";"
DoCmd.RunSQL strSql
lstCurrentlyBooked.Requery
End Sub

Of course you still have to check that something is selected in the dropdown.

I am also assuming the ActivityId is numeric.
If it is text then the strSql would be as follows:
strSql = "Delete from ActivitiesBooked where ActivityId = '" & intActivityId & "';"

Using the single quote around the ActivityId.

 

[TheAceMan1]

How are ya corner40 . . . . .

ListBoxes return [blue]Text Values[/blue], so instead of:

[blue]Me.lstCurrentlyBooked.Column(0)[/blue]

Try:

[blue][purple][b]Val([/b][/purple]Me.lstCurrentlyBooked[purple][b])[/b][/purple][/blue]

In any case [blue]BobAtHome[/blue] is right. [blue]DoCmd.RunSQL[/blue] is the better way to go.

See Ya! . . . . . .