ACCESS FEED: RE: [MS_AccessPros] Re: Populating an unbound textbox with multi item field

Kamis, 06 Oktober 2011

RE: [MS_AccessPros] Re: Populating an unbound textbox with multi item field

Walter-

Then I don't understand the purpose of your code. As noted earlier (and as you
have discovered), a combo box doesn't allow multi-select. You can assign only
ONE value to a combo box. If the box is bound to a multi-value field, it should
show all the currently selected values when you drop it down - plus show blank
check boxes next to rows that aren't already included in the list.

If you haven't figured out, multi-value fields are a REALLY REALLY BAD idea.
It's a kludge invented in 2007 to "help" novice users deal with unnormalized
data. I recommend you get rid of the multi-value field and build a real
many-many table that you edit with a subform or a popup form.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
Sent: Thursday, October 06, 2011 9:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item field

John;
The rowsource is my "tblStations". if I limit the list to only the currently
selected stations/towns I won't be able to add or remove stations to the field.
The purpose of this form is to "Define Trains"
(Name,symbol,type,origin,termination,stations_served,color_code).

The original bound form allows multiselect on "Stations_Served" field because I
am using a combobox lookup in the table "tblTrain" with multiselect set to true
(however no such property is available for a combobox on a form!?)The multiple
stations are displayed in one row with a ";" between them and the dropdown
displays the station list with a checkbox next to each to denote selection.

The faux continuous record form was an attempt to do the color box thing (works
splendid) but it seems at the expense of the multi item field

Walter

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Walter-
>
> A Combo Box doesn't allow multiple selections, but a List Box does. It sounds
> like you need to use your list to apply a filter to the Row Source to show
only
> the stations in the current record. What is the Row Source of the combo box?
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> Sent: Thursday, October 06, 2011 6:29 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field
>
> John;
> The row source is another table (tblStations) that populates the dropdown, the
> tblTrains has a field "stations_Served" that is a list of all the
stations/towns
> that the train does work, so by definition it is potentially a multiple item
> field (some trains only work one station, however). The field contains the
"ID"s
> for the stations in a comma delimited list of numbers. I can't get it to
display
> anything if the field has more than one item in it and I can't get the drop
down
> to allow multiple entries,
> The code generates "SList" perfectly but it won't assign to "SSxx" combo box
but
> I get no error messages.
> Walter
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Walter-
> >
> > If the initial value of Counter is 0, then you need to do:
> >
> > For i = 0 To Counter - 1
> > SList = SList & StationList(i) & ","
> > Next i
> > ' Strip off the last comma
> > SList = Left(SList, Len(SList) - 1)
> >
> > If the SSxxx control is a combo box, perhaps you mean to set the RowSource,
> not
> > the value of the control?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of nkpberk
> > Sent: Thursday, October 06, 2011 1:21 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item
> field
> >
> >
> >
> >
> > Hi All;
> > I did some more research on the problem below and was able to come up with
> this;
> > 'deal with multi valued field
> > Set ChildRS = rst!Stations_Serviced.Value
> > Do Until ChildRS.EOF
> > ChildRS.MoveFirst
> > Do Until ChildRS.EOF
> > StationList(Counter) = ChildRS!Value.Value
> > ChildRS.MoveNext
> > Counter = Counter + 1
> > Loop
> > SList = StationList(0)
> > For i = 1 To Counter - 1
> > SList = SList + "," & StationList(i)
> > Next i
> > Me("SS" & Format(Cnt, "00")) = SList
> > Loop
> >
> > Where SList is a comma delimited string of the ChildRS!Value.Value's, if
SList
> > contains only one item it works fine, more than that and I get a blank
> combobox
> > on the form. I found that a lookup combobox in a table allows for multiple
> > selections one on a form does not (What is with that?)
> > Is there a way to make this work?
> >
> > Walter Griffin
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "nkpberk" <wgriffin48@>
> > wrote:
> > >
> > > Hi Group;
> > > Back with more near impossibilities :-)
> > > Expanding on A.D.'s faux continuous form (the color box thing) I also have
a
> > field in my "trains" table that has a multiple entry field called
> > "Stations_Serviced" indicating what towns/station car pickup and setout
takes
> > place, Some trains have more than one location to work.
> > >
> > > Anyway, the control is "SSxx" a combobox populated by my "Stations" table
> and
> > the "IDStation" times how many, is saved to the tblTrain!Stations_Served
> > > field. Is there a way to make this work with an unbound combobox?
> > >
> > > I get an error message "64224 Method 'Collect' of object 'recordset2'
> failed"
> > >
> > > I cannot find any reference to this in "Help" so this is cryptic at best.
> > >
> > > Walter Griffin
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links