Selecting multiple values

[Guest]

Hello Everyone,

I am trying to capture dependencies between a list of tables (could be one
to many). Users also need the ability to add a new table and specify which
tables this new one depends on.

To capture this information I have created one table with two fields. One
field will store the table, and the other field will store the table the
first field depends on. If it is a one to many relationship then I will just
have several records.

Now my problem is when I create a list box, and enable multi-select, the
records aren't being created. From what I have read, it seems the problem is
it is not possible to bind multi-select list boxes to a single field.

So does anyone have any ideas on how I can address the functionality I need?
Perhaps I need to change my table design...at this point I am really not sure
how to approach this problem.

Any help would be very much appreciated,
Thank you

 

[John W. Vinson]

Hello Everyone,

I am trying to capture dependencies between a list of tables (could be one
to many). Users also need the ability to add a new table and specify which
tables this new one depends on.

To capture this information I have created one table with two fields. One
field will store the table, and the other field will store the table the
first field depends on. If it is a one to many relationship then I will just
have several records.

Whoa... this would be *very* unusual. Creating tables should be an infrequent
operation, and should almost certainly NOT be user-driven. Could you explain
what these tables will contain? If they all have the same structure, it is
very likely that you DON'T need new tables - just another field to indicate
the category of new data.

Now my problem is when I create a list box, and enable multi-select, the
records aren't being created. From what I have read, it seems the problem is
it is not possible to bind multi-select list boxes to a single field.

So does anyone have any ideas on how I can address the functionality I need?
Perhaps I need to change my table design...at this point I am really not sure
how to approach this problem.

I would like to explore your database design further, but here's some code I
use to move data from a Multiselect listbox into a table. It's from an animal
shelter database, for animals' medical conditions, but you should be able to
adapt it to your needs:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

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("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is already
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = 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
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

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

End Sub



John W. Vinson [MVP]

 

[Guest]

Thank you very much John, this should do it!

Hello Everyone,

I am trying to capture dependencies between a list of tables (could be one
to many). Users also need the ability to add a new table and specify which
tables this new one depends on.

To capture this information I have created one table with two fields. One
field will store the table, and the other field will store the table the
first field depends on. If it is a one to many relationship then I will just
have several records.

Whoa... this would be *very* unusual. Creating tables should be an infrequent
operation, and should almost certainly NOT be user-driven. Could you explain
what these tables will contain? If they all have the same structure, it is
very likely that you DON'T need new tables - just another field to indicate
the category of new data.

Now my problem is when I create a list box, and enable multi-select, the
records aren't being created. From what I have read, it seems the problem is
it is not possible to bind multi-select list boxes to a single field.

So does anyone have any ideas on how I can address the functionality I need?
Perhaps I need to change my table design...at this point I am really not sure
how to approach this problem.

I would like to explore your database design further, but here's some code I
use to move data from a Multiselect listbox into a table. It's from an animal
shelter database, for animals' medical conditions, but you should be able to
adapt it to your needs:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

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("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is already
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = 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
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

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

End Sub



John W. Vinson [MVP]