Repost: multiselect listbox

[Guest]

I posted this last week and didn't receive a reply.

I am using John Vinson's sample code (the one about the animal shelter) but
I'm
having some issues applying it to my form. I have a table of taxi companies.
I have a subtable of cities they serve (a junction table). I want the feel
of a multiselect listbox for the user to select the cities the taxi serve.
(instead of having a combo box where they have to manually selecta record for
each city). So after they selected a few cities, they click on a button and
it will add new cities to the junction table, delete any cities that weren't
selected and keep any existing ones.

I was able to get the code to work but I'm having problems displaying what
was selected when a user goes to a taxi company record. So, when a user goes
from one taxi company record to the next, I want the listbox to be updated
with the cities they served highlighted. I assume the code will have to be
in the Oncurrent function of the form. But I'm not sure how to code it.

Below is the modified code to add/delete cities:

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("tblAreaServe", dbOpenDynaset, dbSeeChanges)
With Me!lstCity
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this City is currently in the table
rs.FindFirst "[OrganizationID] = " & Me.OrganizationID & " AND " _
& "[CityID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.addnew
rs!OrganizationID = Me.OrganizationID
rs!CityID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.close
Set rs = Nothing
Set db = Nothing

Thanks.
Ngan

 

[John Vinson]

I posted this last week and didn't receive a reply.

My apologies, Ngan. That code isn't in my current database, and I had
to dig through some old backup CD's to get it!

I was able to get the code to work but I'm having problems displaying what
was selected when a user goes to a taxi company record. So, when a user goes
from one taxi company record to the next, I want the listbox to be updated
with the cities they served highlighted. I assume the code will have to be
in the Oncurrent function of the form. But I'm not sure how to code it.

Here's the code that I had:

Private Sub Form_Current()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iItem As Integer
If Me.NewRecord = True Or Me.RecordSource = "" Then
Exit Sub
End If
Me.lstBehaviorIssues.Requery
Me.lstHealthIssues.Requery
Me!subAnimalCondition.Form!cboCondition.Requery
Me.cboBreedID.Requery
Set db = CurrentDb
strSQL = "SELECT SoftSlip, HealthIssueID FROM AnimalCondition " _
& "WHERE SoftSlip = '" & Me.[SoftSlip] & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With Me!lstHealthIssues
For iItem = 0 To .ListCount - 1
rs.FindFirst "[HealthIssueID] = " & .Column(0, iItem)
.Selected(iItem) = Not rs.NoMatch
Next iItem
End With
With Me!lstBehaviorIssues
For iItem = 0 To .ListCount - 1
rs.FindFirst "[HealthIssueID] = " & .Column(0, iItem)
.Selected(iItem) = Not rs.NoMatch
Next iItem
End With
rs.Close
Set rs = Nothing

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub


This uses two listboxes not one, but I think you can see how it works.
Essentially, it tries to find each HealthIssueID in the listbox, and
checks to see if it exists in the recordset for the current AnimalID
(SoftSlip is the ID field). The .NoMatch propertyof the recordset is
TRUE if there is no match, FALSE if there is a match, so Not
rs.NoMatch means "set this to true if there is a match".


John W. Vinson[MVP]

 

[Guest]

Thanks so much for the code! It works well...except when I go to a new
record. It still highlights the cities from the previous record. I want it
to highlight nothing.

Ngan

I posted this last week and didn't receive a reply.

My apologies, Ngan. That code isn't in my current database, and I had
to dig through some old backup CD's to get it!

I was able to get the code to work but I'm having problems displaying what
was selected when a user goes to a taxi company record. So, when a user goes
from one taxi company record to the next, I want the listbox to be updated
with the cities they served highlighted. I assume the code will have to be
in the Oncurrent function of the form. But I'm not sure how to code it.

Here's the code that I had:

Private Sub Form_Current()
' Comments :
' Parameters: -
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
On Error GoTo PROC_ERR


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim iItem As Integer
If Me.NewRecord = True Or Me.RecordSource = "" Then
Exit Sub
End If
Me.lstBehaviorIssues.Requery
Me.lstHealthIssues.Requery
Me!subAnimalCondition.Form!cboCondition.Requery
Me.cboBreedID.Requery
Set db = CurrentDb
strSQL = "SELECT SoftSlip, HealthIssueID FROM AnimalCondition " _
& "WHERE SoftSlip = '" & Me.[SoftSlip] & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
With Me!lstHealthIssues
For iItem = 0 To .ListCount - 1
rs.FindFirst "[HealthIssueID] = " & .Column(0, iItem)
.Selected(iItem) = Not rs.NoMatch
Next iItem
End With
With Me!lstBehaviorIssues
For iItem = 0 To .ListCount - 1
rs.FindFirst "[HealthIssueID] = " & .Column(0, iItem)
.Selected(iItem) = Not rs.NoMatch
Next iItem
End With
rs.Close
Set rs = Nothing

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in Form_Current:" _
& vbCrLf & Err.Description
Resume PROC_EXIT


End Sub


This uses two listboxes not one, but I think you can see how it works.
Essentially, it tries to find each HealthIssueID in the listbox, and
checks to see if it exists in the recordset for the current AnimalID
(SoftSlip is the ID field). The .NoMatch propertyof the recordset is
TRUE if there is no match, FALSE if there is a match, so Not
rs.NoMatch means "set this to true if there is a match".


John W. Vinson[MVP]

 

[John Vinson]

Thanks so much for the code! It works well...except when I go to a new
record. It still highlights the cities from the previous record. I want it
to highlight nothing.

Ah. Add a block:

If Me.NewRecord Then
<loop through the listbox setting Selected to False>
End If

John W. Vinson[MVP]

 

[Guest]

A sidenote: I have my tables linked to the SQL server via a DSNless connection.

Seems the newrecord function doesn't work. Even if I did a simple new
record check, nothing comes up. For instance, I have if me.newrecord then
msgbox "new record". When I go to a new record, no message box.

Did I miss something?

 

[Guest]

never mind. I actually had "exit sub" in the if me.record is true statement.
stupid me.