HELP!!! Is there a multi-select list box code for data entry?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a database using Access 2003. The database contains a data
entry form which contains a list box. I have chosen "extended" so the user
can select many choices in which to enter the data in a certain field of a
table. Unfortunately, I don't know where to go from here. I don't know a
code that uses a multi-select list box for data entry.

Does anyone know a code or where I can find a code, to program my
multi-select list box for data entry? I am VERY NEW to programming, so be as
detailed as possible.

Thanks in advance.
 
It would be nice if you could do this. Unfortunately, Access doesn't make
this one aspect of data entry very easy. Look at continuous forms instead.

HTH;

Amy
 
I have created a database using Access 2003. The database contains a data
entry form which contains a list box. I have chosen "extended" so the user
can select many choices in which to enter the data in a certain field of a
table. Unfortunately, I don't know where to go from here. I don't know a
code that uses a multi-select list box for data entry.
Click to expand...

Amy's quite right; a continuous Subform is simpler both for you and
(with appropriate training) for the user. However, if you DO want to
use a listbox, here's some sample code. You'll need to *understand*
the code well enough to figure out what it's doing in order to modify
the field, table, and control names; post back if it's a) necessary to
do this and b) the code is unclear. It's code for the Click event of a
command button named cmdProcess on the form containing the listbox
lstHealthIssues.

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,
' 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 currently
' 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]
 
One of my Tables named "Micro Descriptors" contains a field "Margin
Description" with a list box value list: Smooth, Undulating, Semilunar,
Serrate, etc. I want the user to be able to select more than one item in
that field on a data entry form.

Given this, do you think it's best to use a multi-select list box or a
continuous form? For some reason I was under the impression that continuous
subforms were for retrieving or filtering data not data entry.

I'm sorry if these are dumb questions, I'm just learning about programming.

John Vinson said:
I have created a database using Access 2003. The database contains a data
entry form which contains a list box. I have chosen "extended" so the user
can select many choices in which to enter the data in a certain field of a
table. Unfortunately, I don't know where to go from here. I don't know a
code that uses a multi-select list box for data entry.
Click to expand...

Amy's quite right; a continuous Subform is simpler both for you and
(with appropriate training) for the user. However, if you DO want to
use a listbox, here's some sample code. You'll need to *understand*
the code well enough to figure out what it's doing in order to modify
the field, table, and control names; post back if it's a) necessary to
do this and b) the code is unclear. It's code for the Click event of a
command button named cmdProcess on the form containing the listbox
lstHealthIssues.

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,
' 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 currently
' 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]
Click to expand...
 
One of my Tables named "Micro Descriptors" contains a field "Margin
Description" with a list box value list: Smooth, Undulating, Semilunar,
Serrate, etc. I want the user to be able to select more than one item in
that field on a data entry form.

Given this, do you think it's best to use a multi-select list box or a
continuous form? For some reason I was under the impression that continuous
subforms were for retrieving or filtering data not data entry.
Click to expand...

Stop.

A field *CANNOT* contain multiple values. Your margin description
field in the Micro Descriptors table can have one, and only one,
value.

If you have a one to many relationship you must use *two tables* in a
one to many relationship.

You can indeed use a subform for data entry - perfectly normal and
routine, in fact, and much easier than using one for filtering! You
can also use the (rather advanced, as you saw) VBA code I posted if
you have a strong preference of the look and feel of a multiselect
listbox.

But it sounds like you need to get your table structure right *first*
before wasting time on form design!

John W. Vinson[MVP]
 
Chantel,

May I suggest you look in the Help file for the "ItemsSelected Property".
This may guide you in making your wish come true, as it provides code as well
as explanation.

Sam

John said:
One of my Tables named "Micro Descriptors" contains a field "Margin
Description" with a list box value list: Smooth, Undulating, Semilunar,
Click to expand...
[quoted text clipped - 4 lines]
continuous form? For some reason I was under the impression that continuous
subforms were for retrieving or filtering data not data entry.
Click to expand...

Stop.

A field *CANNOT* contain multiple values. Your margin description
field in the Micro Descriptors table can have one, and only one,
value.

If you have a one to many relationship you must use *two tables* in a
one to many relationship.

You can indeed use a subform for data entry - perfectly normal and
routine, in fact, and much easier than using one for filtering! You
can also use the (rather advanced, as you saw) VBA code I posted if
you have a strong preference of the look and feel of a multiselect
listbox.

But it sounds like you need to get your table structure right *first*
before wasting time on form design!

John W. Vinson[MVP]
Click to expand...
 
Thanks!!! I looked back at my table structure and it was all wrong. I had a
lot of redundancy, which I have solved.

Are there any examples illustrating continuous subforms for data entry? I
just need to see it visually.

Thanks again.
Chantel.
 
Check this out about multiselect in a listbox
http://allenbrowne.com/ser-50.html
For how you can view the contious subform, I hope you have a subform alread,
if you do, in the design view, look at the properties of the form. Change
the default view to contious subform....
 
Are there any examples illustrating continuous subforms for data entry? I
just need to see it visually.
Click to expand...

Almost ANY of the sample databases have at least one, often several -
it's *the* universal technique for entering one-to-many data.

The Orders form in the NORTHWIND sample database is just one example.

John W. Vinson[MVP]
 
Back
Top