multiple selection, list box : NOT saving my selections !?

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

running access 2k;

background info:
--------------------------------------------------------------------
have the following databases: (the 1st field is the key)

cust_data: key,name,selection_id
selections: selection_id, selection_desc

now I'm building a form that shows the name, and a list box next to
the name, which allows simple selections.

the control source points to cust_data.selection_id

The listbox performs the following in row source:

SELECT [selections].[selection_id], [selections].[selection_desc] FROM
selections;


the bound column is 1

in format i have:
column count = 2; column widths = 0";2.6146"
------------------------------------------------------------------------
the problem:
------------------------------------------------------------------------
i currently have several records in my table; however, when I make
selections in the list box, and then go to the next record - I'm still
seeing the selections from my PREVIOUS RECORD !!

in short - my selections don't appear to be saving....

I'm just this must be something really easy; but i'm missing it....

any help?

tia - Bob
 
How can you save multiple values in one field?

What does that one field supposed to looking like when it has more then one
value?

How does ms-access know to save all values into one field?

You can't save multiple values into one field, it is just not possible.

If the user needs to select and add more the one value, then you need to
crate a another table. (you use a relation). You would thus be best to use a
sub-form, and use a combo box. That way, the user can add as many selections
as you want (each new choice would be a new line in the sub form). There is
not a reasonable way to save more then one value into a field.
 
i currently have several records in my table; however, when I make
selections in the list box, and then go to the next record - I'm still
seeing the selections from my PREVIOUS RECORD !!

in short - my selections don't appear to be saving....

I'm just this must be something really easy; but i'm missing it....
Click to expand...

What you are missing is an understanding of how a multiselect listbox
WORKS. It is a *display tool*, not a storage medium. You cannot store
multiple values in a field in a table (well, you could, by storing a
long text string with the values separated by a delimiter, but it
would be very bad design to do so).

If you have a one to many relationship (from the records on your form
to multiple values you are now selecting in a listbox), you should
have *another table* in which to store them, related one to many to
this table; and you should use a Subform to enter them, not a listbox.
 
What you are missing is an understanding of how a multiselect listbox
WORKS. It is a *display tool*, not a storage medium. You cannot store
multiple values in a field in a table (well, you could, by storing a
long text string with the values separated by a delimiter, but it
would be very bad design to do so).

If you have a one to many relationship (from the records on your form
to multiple values you are now selecting in a listbox), you should
have *another table* in which to store them, related one to many to
this table; and you should use a Subform to enter them, not a listbox.
Click to expand...

Thanks to both Albert & John;

of course it makes perfect sense now... I just ASSUMED that access
would in fact, store multiple values in that field, and have a way to
parse them.

I can certainly see how your suggestions would work. However; I would
really like to present users with a list-box-appearance, so they can
see the whole list of choices, and just pick them off.

I'm really having trouble visuallizing how this would be accomplished;
OR with what you've told me IF it can be done....

any direction you could point me would be well appreciated !

tia - Bob
 
I would really like to present users with a list-box-appearance, so they can
see the whole list of choices, and just pick them off.

I'm really having trouble visuallizing how this would be accomplished;
OR with what you've told me IF it can be done....
Click to expand...

It can be done, but as I said, it's a fair bit of work. You're quite
correct, the fact that Form design gives you a multiselect listbox
capability but with no immediate way to *use* the multiselects is both
confusing and irksome!

Here's some code that I have used in such a situation. Adapt the field
and tablenames to your case and see if it helps.

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the
' unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table,
' newly 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
' 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
 
I would really like to present users with a list-box-appearance, so they can
see the whole list of choices, and just pick them off.

I'm really having trouble visuallizing how this would be accomplished;
OR with what you've told me IF it can be done....
Click to expand...

It can be done, but as I said, it's a fair bit of work. You're quite
correct, the fact that Form design gives you a multiselect listbox
capability but with no immediate way to *use* the multiselects is both
confusing and irksome!

Here's some code that I have used in such a situation. Adapt the field
and tablenames to your case and see if it helps.

Private Sub cmdProcess_Click() [snip]
Click to expand...

John;

tx again for your help - I'll check that out.

really appreciate your help!

Bob
 
Back
Top