Selasa, 11 Oktober 2011

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

 

Thanks John! Glad you liked it.

Incidentally, this exercise has been helpful in acquiring an understanding of multi-value fields.

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: John Viescas
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, October 11, 2011 21:13
Subject: RE: [MS_AccessPros] Re: Populating an unbound textbox with multi item field

A.D.-

Well, I saw the post of your file, so I downloaded it and took a look before I
read your explanation below. I'm torn between "why didn't *I* think of that"
and "that's just f#@$ing brilliant!" Nice job.

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 A.D. Tejpal
Sent: Tuesday, October 11, 2011 4:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field

John,

Interestingly, it is found that the functionality of normal multi-value
field (MVF) combo boxes on a simulated continuous form having rows of unbound
controls, as sought by Walter, can be realized through the concept of reverse
crosstab.

In this specific case, it involves a single record dummy table as record
source for the form, with 12 multi-value fields (MV01 to MV12) catering to the
12 rows of MVF combo boxes (SS01 to SS12). The dummy MVF's serve somewhat like
transposed front ends for the real MVF's in main data source. As an abundant
precaution, form's cycle property is set to single record. AllowAdditions &
AllowDeletions properties are also set to False (This does not interfere with
the main data source as the form is not bound to that).

It is ensured that at all stages, contents of these MVF combos keep in synch
with those of matching MVF fields on matching records in the data source. This
arrangement is found more robust than the other alternative of having a single
MVF field in the dummy table and then dynamically shifting the control source of
MVF combos on the form as required.

Walter's file, duly modified so as to demonstrate the above approach,
renamed as A2K10_Form_MultiValueContSimulated.zip, has been uploaded to Samples
folder in Files section. In the course of this exercise, certain interesting
functions and subroutines for handling MVF fields & combos were developed at my
end. These are also included as module M_MultiValuedFields.

Walter: You might like to try it out and let me know whether your problem
stands resolved and whether anything needs further attention.

Note:
Value property of multi-value combo box returns an array while similar
property of multi-value field returns a recordset.

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: John Viescas
To: MS_Access_Professionals@yahoogroups.com
Sent: Tuesday, October 11, 2011 00:32
Subject: RE: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field

A.D.-

I even wrote a bit of code to run through all the properties of the combo
bound
to the MVF to see if there is a "hidden" AllowMultipleValues. Here's the
list:

EventProcPrefix Supplier_IDs
Name Supplier IDs
ControlType 111
ControlSource Supplier IDs
Format
DecimalPlaces 255
Visible True
DatasheetCaption
ColumnCount 2
ColumnWidths 0;1440
ColumnHeads False
ListRows 16
ListWidth 1440
SeparatorCharacters 0
Width 3450
Height 359
Top 2636
Left 2354
BackStyle 1
BackColor 16777215
BorderStyle 1
OldBorderStyle 1
BorderLineStyle 0
BorderWidth 0
BorderColor 12632256
SpecialEffect 0
FontName Calibri
FontSize 11
TextAlign 0
FontWeight 400
FontUnderline False
FontItalic False
FontBold 0
ForeColor 0
IsHyperlink False
DisplayAsHyperlink 0
GridlineStyleTop 0
GridlineStyleBottom 0
GridlineStyleLeft 0
GridlineStyleRight 0
GridlineColor 0
GridlineWidthTop 1
GridlineWidthBottom 1
GridlineWidthLeft 1
GridlineWidthRight 1
TopMargin 22
BottomMargin 22
LeftMargin 22
RightMargin 22
TopPadding 30
BottomPadding 30
LeftPadding 30
RightPadding 30
HorizontalAnchor 0
VerticalAnchor 0
CanGrow False
CanShrink False
DisplayWhen 0
ReadingOrder 0
ScrollBarAlign 0
NumeralShapes 0
KeyboardLanguage 0
RowSource SELECT [ID], [Company] FROM [Suppliers Extended]
ORDER BY [Company];
RowSourceType Table/Query
BoundColumn 1
LimitToList True
AllowValueListEdits True
ListItemsEditForm
InheritValueList True
ShowOnlyRowSourceValues False
InputMask
DefaultValue
ValidationRule
ValidationText
Enabled True
Locked False
AutoExpand True
OnClick
OnClickEmMacro
BeforeUpdate
BeforeUpdateEmMacro
AfterUpdate
AfterUpdateEmMacro
OnDirty
OnDirtyEmMacro
OnChange
OnChangeEmMacro
OnNotInList
OnNotInListEmMacro
OnGotFocus
OnGotFocusEmMacro
OnLostFocus
OnLostFocusEmMacro
OnDblClick
OnDblClickEmMacro
OnMouseDown
OnMouseDownEmMacro
OnMouseUp
OnMouseUpEmMacro
OnMouseMove
OnMouseMoveEmMacro
OnKeyDown
OnKeyDownEmMacro
OnKeyUp
OnKeyUpEmMacro
OnKeyPress
OnKeyPressEmMacro
OnEnter
OnEnterEmMacro
OnExit
OnExitEmMacro
OnUndo
OnUndoEmMacro
ControlTipText
TabIndex 9
TabStop True
StatusBarText
ShortcutMenuBar
HelpContextId 0
AllowAutoCorrect True
IMEHold False
IMEMode 0
IMESentenceMode 3
SmartTags
Tag
TextFontCharSet 0
ConditionalFormat Null
ColumnWidth -1
ColumnOrder 0
ColumnHidden False
Section 0
Text Null
SelText Null
SelStart Null
SelLength Null
ListCount 10
ListIndex 3
InSelection Null
AggregateType -1
Layout 0
LayoutID 0

I don't see one, do you?

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 John Viescas
Sent: Monday, October 10, 2011 7:44 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field

A.D.-

Take a look at "Supplier IDs" in the Products table in Northwind. It is MVF,
and you'll find a property "Allow Multiple Values" on the Lookup tab in Table
Design. If you open the Product Details form, you'll see that the combo box
exhibits the special characteristics needed to support the MVF. But if you
look
at the form in Design view and select the combo box, that property is nowhere
to
be found. It's ONLY when a combo is bound to an MVF that it takes on the
special characteristics. I don't think there's any way to make this happen
for
an unbound 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 A.D. Tejpal
Sent: Monday, October 10, 2011 7:07 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Populating an unbound textbox with multi item
field

Walter,

I have not had any experience with multi-valued fields (MVF).

Considering your keen-ness and the situation explained by you, preliminary
study with Access 2010 seems to indicate that there could be an interesting
way
to implement the functionality of MVF on simulated continuous form made up of
unbound controls.

It might perhaps become feasible for you to have the best of both worlds,
in
a seamless manner, complete with MVF style drop down list having check boxes.

After some pertinent tests, I would try to post further details.

Best wishes,
A.D. Tejpal
------------

----- Original Message -----
From: nkpberk
To: MS_Access_Professionals@yahoogroups.com
Sent: Sunday, October 09, 2011 00:00
Subject: [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

[Non-text portions of this message have been removed]

__._,_.___
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