ACCESS FEED: [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
>