multi selection list fields

  • Thread starter Thread starter stan
  • Start date Start date
S

stan

I have an Access form with 5 list boxes that allow multiple selections.
When I try to feed selections to a table field (with multiple selections) I
get a null value. Does anyone have code I can attach to a button on each box
to feed multiple selections into a fields in a table. (I know this is a
violation of normalizaiton rules but I need to do it anyway).
 
Hi Stan,

Try something like this air code to copy the selected entries to your table.
Obviously you need to change table and field names to real ones

Regards

Kevin

Sub StoreSelections()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblProjectNames")
With Forms!frmAccounting!lstAccountantNames
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Copy Selected name to the tblProjectNames
'Add a new record
rs.AddNew
'Copy the name which is in Column 0 to the Name field of the
recordset
rs!Name = .Column(0)
'Save the new record
rs.Update
End If
Next 'Item selected
End With
End Sub
 
If the speed limit is 65 mph and a police officer pulls you over for doing
90 mph, do you think you can tell him you know you were speeding but you
needed to do it anyway?

Your tables are not designed correctly and you need to fix that problem.
Then you won't need to do it anyway!!! Teel us what the fields are in your
database and we will help you come up with a normalized set of tables.

Steve
(e-mail address removed)
 
Stan,

Another way might be to make it appear that way, as it is not normal to
store more then one value per field. Have a look at
http://allenbrowne.com/func-concat.html and see if something like this
might work for you.

Might also help to tell us what you are tyring to accomplish and we can
offer some alternative solutions that does not include unnormalizing your
database.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi Gena

I do not understand your comment. I don't understand what "as it is not
normal
to store more then one value per field" have to do with anything posted
here?

I am always trying to learn. Help me out. I obviously missed something.

Thx

Kevin
 
Kevin,

I could have misread what you are saying but it *sounded* like you where
trying to take multiple values selected from a list box (which would be from
several records) and place them into one field in one record. Is that
correct or did I miss something?

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

kc-mass said:
Hi Gena

I do not understand your comment. I don't understand what "as it is not
normal
to store more then one value per field" have to do with anything posted
here?

I am always trying to learn. Help me out. I obviously missed something.

Thx

Kevin
Click to expand...
 
Hi Gina,

You misread. It is placing the data for one field selection into one
distinct field in one record of a table.
Successive actions are to other, new records.

Thx

Kevin
 
Good point.

my database is for an outfit involved with poltting land parcels. They are
designated in the usual way: township(n,s), range(e,w) and section. One
record can contain numerous parcels--each with their own designations.

The 5 multi list boxes were designed to accomdate multiple survey points,
which are often (usually) the case with individual records.

I've toyed with the idea of a subreport for the points but I'd probably need
5 separate ones (?)
 
<One record can contain numerous parcels--each with their own designations.>

This raises a red flag!! It sounds like you have a one-to-many relationship
and are trying to stuff it all into one table.

It sounds like you need tables something like ....
TblLandBaron
LandBaronID
etc

TblTownship
TownshipID
Township

TblRange
RangeID
Range

TblSection
SectionID
Section

TblParcel
ParcelID
ParcelName
TownshipID
RangeID
SectionID

TblLandBaronParcel
LandBaronParcelID
LandBaronID
ParcelID

Steve
(e-mail address removed)
 
Back
Top