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

Walter-

Post the name of the form that's giving you trouble. It's late here, so I won't
get to it until tomorrow, but maybe someone else will pick it up. If not, I'll
have a look at it in the morning.

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: Friday, October 07, 2011 10:13 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item field

John & Group

I have uploaded a zip file of the form and related objects that should make it
clearer what is happening with the multiselect field I am attempting to make
work.

A.D. may be interested in some of the changes I made to his sample
Not everything works as to data updating, adding records and such but I wanted
to try to get past the problem field first.

Walter Griffin

--- In MS_Access_Professionals@yahoogroups.com, "nkpberk" <wgriffin48@...>
wrote:
>
> John;
> The form is unbound and the combo box is trying to display a multi value field
from the "tblTrain" In VBA code. The combo dropdown is populated by
"tblStations" as the rowsource.
> Apparently, as the form is not bound to any table I can't get the combobox to
display as a multiselect as the field is defined in its table
"tblTrain!Stations_Served"
> This evening I will try to put a subset of this app together with this form
and supporting tables/queries/code and upload it to this group, maybe that will
help.
> Walter
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Walter-
> >
> > I'm having a hard time visualizing what it is you're trying to do. Is the
combo
> > box bound? If so, what is the data type of the bound field - multi-value?
What
> > is the Record Source of the 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 11:20 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field
> >
> > John:
> > The code is to apply the existing values stored in the
> > "tblTrain!Stations_Served", other code would be required to update the data.
> >
> > The kludge is more difficult to deal with for novices than otherwise, I
don't
> > know how M$ figured it would help if they were not going to support the
creative
> > data structure throughout the application
> >
> > I don't like attachments either but I could not make OLE containers work for
the
> > life of me in 2010. Worked OK in 2K!
> >
> > As to normalizing data, I am applying the station "ID"s to the "tblTrain"
not a
> > bunch of duplicate text.
> >
> > I have been resisting the subform because each train record is only on one
line
> > on a highly preformated form .
> >
> > if I want to make the "define trains" form single record I could keep the
multi
> > select and I think I could display the color code box
> >
> > D****d if you do and d****d if you don't situation here.
> >
> > Walter
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@>
wrote:
> > >
> > > 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
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>

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

Yahoo! Groups Links