Sabtu, 08 Oktober 2011

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

 

John;
The only way I can think of is to open a bound single record form from the add/delete/( add an edit button) using the record IDs .
Kind of a kludge to use another kludge.
Walter

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Walter-
>
> The problem is a Combo Box acts in a "special" way only when it is bound to a
> multi-value field. When bound to a normal field or unbound, it's just a plain
> Combo Box. To do what you want, you would have to use a plain text box and use
> your code to assign the list of comma-separated values to show the underlying
> multi-values. I'm not sure how you would handle letting the user update the
> values to add or remove a Station.
>
> 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:53 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Populating an unbound textbox with multi item field
>
> John;
> It's in the file root directory (my bad) named "TrainDB" it's a zip
> and has only one form and all the objects to make it work.
> I don't have all the update/add record stuff working yet, wanted to see if I
> could get past this multiselect thing
> Walter
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > 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
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar