help with lisbox count!

[rsbutterfly16]

hi guys i have form with a listbox . The listbox is coming from a query that gets all passengers(column 1 in listbox) and destinations(column 3) . so you have repeting rows of passengers since they could have up to three destinations. Then the user has to select a row in the list box , click opn command delete button and there is a i have to sql query to delete the currect item selected (passenger with destination). this works fine. however i need to put some kind of code so then when there is a recordcount of 1 of the current passenger, then you can't delete. Meaning that each passenger must have at least one destination. I already put in the table not to allow any nulls in for destinations, but still it lets you delete the last destination.

this is what i have so far:


Private Sub cmdDelete_Click()

Dim dbs As DAO.Database
Dim strSQL As String


If Me.List33.Recordset = 1 Then
MsgBox " you must have at least one Destination "
DoCmd.CancelEvent


Set dbs = CurrentDb
If MsgBox("Are you sure you want to delete the selected Destination?", vbYesNo) = vbYes Then

strSQL = "UPDATE tblPassengerDestination Set Dest_Status = ""Inactive"" WHERE ID = " & List33.Column(0)
DoCmd.RunSQL strSQL


MsgBox "Your Destination has been deleted"
Me.List33.Requery



Else
DoCmd.CancelEvent

End If


but nothing happens.....

 

[ansentry]

Just my 2 cents worth but wouldn’t you be better of with Main Form (Customers) subform (Destinations).

That way you would not have duplicate customers.

Another way would be to have 2 list boxes.