By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,932 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,932 IT Pros & Developers. It's quick & easy.

listbox record count help!

P: n/a
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 , double
click even in my listbox 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
destination, 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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

Oct 3 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"rsbutterfly16 via AccessMonster.com" <u21410@uwewrote in message
news:673a04a543673@uwe...
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 , double
click even in my listbox 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
destination, 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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1
From what I understand you want to make sure the passenger doesn't get
deleted totally form the list. If so make a query that counts how many times
the passenger is on the list and if record count less 2 cancel his action.
Does me.list33.recordset include your whole list? Tip: after running your
query sometimes you need to do a movefirst and movelast (any order) to get
an accurate count using the property recordcount.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----
Oct 3 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.