Truncated Memo field displayed on form

[Miss Teacher]

Hi,

I know this is probably a rather tiresome query, but I've surfed the net
endlessly and not found a solution.

I'm creating a classroom planning/assessment database application (the new
Learning Essentials for those involved in Australian Education). The system
I'm creating, database-wise, is a little complicated (I am an absolute
self-taught beginner, so please be kind and precise with your terminology,
explaining where I should find the tools you mention). We have 7 "KLAs",
which are each chosen with an "Assessable Element" (depending on the KLA) for
each grade, which each act as an umbrella for a number of "Knowledge and
Understandings" (each K & U has a descriptor of more than 255 characters.
Under each "Knolwedge and Understanding" is a dozen or so "Detailed Elements"
(that the kids get assessed on).

So I have the following heirachy of tables:

KLAs
Assessable Elements
Year (or Grade in America-speak)
Ways of Working (differing and dependent on previous choices)
Knowledge and Understandings (includes the descriptor)
Detailed Elements

Each table is linked in heirachy to the one above via foreign keys (and
sometimes to more than one table above (my description is the simplified
version - it's more complicated than that). So you can imaging that the KLAs
table is quite small (only 7 records), but the tables get larger as each one
lower in the chain is increased to account for grades, categories and more
detailed categories towards the bottom.

My fields down to "Knowledge and Understandings" are fine. The problem
occurs with the "descriptor" field being truncated on my form. The "Knowledge
and Understandings" combobox provides the user with options based on the
previous 4 choices, but it is only really a 'title' field (because I
certainly couldn't include the descriptor for it within a combo box). Next to
this combo box is a text box that displays the contents on a memo field (from
the same table) depending on this choice. It is this descriptor field that is
being truncated.

All the fields are 'unbound' as they all depend on choice of the previous
field (and there are 'duplicate' records as some grades have identical
choices), then a search is carried out on the next table using the Primary
and Foreign Keys to retrieve 'after_update':

Private Sub cboKnowledgeAndUnderstanding_AfterUpdate()
Me.txtKnowledgeDescriptor = Null
Me.txtKnowledgeDescriptor = Me.cboKnowledgeAndUnderstanding.Column(4)
Me.cboDetailedElement.Requery
End Sub

My Row Source Type is Table/Query
My Row Source says:

SELECT [Knowledge And Understandings].KnowledgeAndUnderstandingsID,
[Knowledge And Understandings].KnowledgeAndUnderstandingsName, [Knowledge And
Understandings].KLAIDF, [Knowledge And Understandings].ByEndOfYear,
[Knowledge And Understandings].KnowledgeDescriptor
FROM [Knowledge And Understandings]
WHERE ((([Knowledge And Understandings].KLAIDF)=[Forms]![Planning
Development]![cboKLA]) AND (([Knowledge And
Understandings].ByEndOfYear)=[Forms]![Planning
Development]![cboByEndofYear]));

My format fields (both on the form and table) are empty. I can't find where
there is any property called "Unique Value" either on my form or table (I
read that this could be a problem). As you can see I have not the word
DISTINCT anywhere within my query. And I don't have GROUP BY in my SQL
statement, nor am I sorting the results.

These tables will not require editing in the finished product - they are
simply to be stored as ID numbers in a new database as the pillars upon which
to create planning. The descriptor, in fact is disabled, and for desplay
purposes only. In fact these tables need only ever be read only.

What is the problem with my descriptor field? Why is it truncated?

 

[Linq Adams via AccessMonster.com]

The Wonder from Down Under, Allen Browne, has a page on the woes of using
memo fields in queries, along with some work arounds:

http://allenbrowne.com/ser-63.html

 

[Miss Teacher]

I have been through this list:

As already mentioned, I have no Group By Statement in my query, nor do I use
DISTINCT. I cannot find any "Unique Values" property. Both my table and form
"Format" propertie are empty. I have not used UNION. I have not concaternated
anything. Perhaps I should have mentioned the actual page, rather than the
information contained upon it... Unless I am misunderstanding any details - I
am an absolute beginner (of only a few days).

 

[Ken Snell \(MVP\)]

You're wanting to display the memo field in a combo box's dropdown list, is
that right? A combo box (and a list box) cannot display more than 255
characters for any column in the combo box's Row Source query. That is a
built-in design, and cannot be changed.

What you can do is to display the memo field's contents for the selected
item from the combo box in a textbox on the form. You can use the code in
this article for doing that:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi,

I know this is probably a rather tiresome query, but I've surfed the net
endlessly and not found a solution.

I'm creating a classroom planning/assessment database application (the new
Learning Essentials for those involved in Australian Education). The
system
I'm creating, database-wise, is a little complicated (I am an absolute
self-taught beginner, so please be kind and precise with your terminology,
explaining where I should find the tools you mention). We have 7 "KLAs",
which are each chosen with an "Assessable Element" (depending on the KLA)
for
each grade, which each act as an umbrella for a number of "Knowledge and
Understandings" (each K & U has a descriptor of more than 255 characters.
Under each "Knolwedge and Understanding" is a dozen or so "Detailed
Elements"
(that the kids get assessed on).

So I have the following heirachy of tables:

KLAs
Assessable Elements
Year (or Grade in America-speak)
Ways of Working (differing and dependent on previous choices)
Knowledge and Understandings (includes the descriptor)
Detailed Elements

Each table is linked in heirachy to the one above via foreign keys (and
sometimes to more than one table above (my description is the simplified
version - it's more complicated than that). So you can imaging that the
KLAs
table is quite small (only 7 records), but the tables get larger as each
one
lower in the chain is increased to account for grades, categories and more
detailed categories towards the bottom.

My fields down to "Knowledge and Understandings" are fine. The problem
occurs with the "descriptor" field being truncated on my form. The
"Knowledge
and Understandings" combobox provides the user with options based on the
previous 4 choices, but it is only really a 'title' field (because I
certainly couldn't include the descriptor for it within a combo box). Next
to
this combo box is a text box that displays the contents on a memo field
(from
the same table) depending on this choice. It is this descriptor field that
is
being truncated.

All the fields are 'unbound' as they all depend on choice of the previous
field (and there are 'duplicate' records as some grades have identical
choices), then a search is carried out on the next table using the Primary
and Foreign Keys to retrieve 'after_update':

Private Sub cboKnowledgeAndUnderstanding_AfterUpdate()
Me.txtKnowledgeDescriptor = Null
Me.txtKnowledgeDescriptor = Me.cboKnowledgeAndUnderstanding.Column(4)
Me.cboDetailedElement.Requery
End Sub

My Row Source Type is Table/Query
My Row Source says:

SELECT [Knowledge And Understandings].KnowledgeAndUnderstandingsID,
[Knowledge And Understandings].KnowledgeAndUnderstandingsName, [Knowledge
And
Understandings].KLAIDF, [Knowledge And Understandings].ByEndOfYear,
[Knowledge And Understandings].KnowledgeDescriptor
FROM [Knowledge And Understandings]
WHERE ((([Knowledge And Understandings].KLAIDF)=[Forms]![Planning
Development]![cboKLA]) AND (([Knowledge And
Understandings].ByEndOfYear)=[Forms]![Planning
Development]![cboByEndofYear]));

My format fields (both on the form and table) are empty. I can't find
where
there is any property called "Unique Value" either on my form or table (I
read that this could be a problem). As you can see I have not the word
DISTINCT anywhere within my query. And I don't have GROUP BY in my SQL
statement, nor am I sorting the results.

These tables will not require editing in the finished product - they are
simply to be stored as ID numbers in a new database as the pillars upon
which
to create planning. The descriptor, in fact is disabled, and for desplay
purposes only. In fact these tables need only ever be read only.

What is the problem with my descriptor field? Why is it truncated?

 

[Miss Teacher]

No . I would like to populate a memo field, but have it depend on the user
input from the previous combo box (I realise that a combo box can hold more
that 255 characters. Basicaly the combo box displays a list of titles and,
upon choosing one of these titles, the form displays the full description in
the next text box - the combo box needs to update the text box, so I've been
using the "Update_After" event on the combo box.

You're wanting to display the memo field in a combo box's dropdown list, is
that right? A combo box (and a list box) cannot display more than 255
characters for any column in the combo box's Row Source query. That is a
built-in design, and cannot be changed.

What you can do is to display the memo field's contents for the selected
item from the combo box in a textbox on the form. You can use the code in
this article for doing that:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi,

I know this is probably a rather tiresome query, but I've surfed the net
endlessly and not found a solution.

I'm creating a classroom planning/assessment database application (the new
Learning Essentials for those involved in Australian Education). The
system
I'm creating, database-wise, is a little complicated (I am an absolute
self-taught beginner, so please be kind and precise with your terminology,
explaining where I should find the tools you mention). We have 7 "KLAs",
which are each chosen with an "Assessable Element" (depending on the KLA)
for
each grade, which each act as an umbrella for a number of "Knowledge and
Understandings" (each K & U has a descriptor of more than 255 characters.
Under each "Knolwedge and Understanding" is a dozen or so "Detailed
Elements"
(that the kids get assessed on).

So I have the following heirachy of tables:

KLAs
Assessable Elements
Year (or Grade in America-speak)
Ways of Working (differing and dependent on previous choices)
Knowledge and Understandings (includes the descriptor)
Detailed Elements

Each table is linked in heirachy to the one above via foreign keys (and
sometimes to more than one table above (my description is the simplified
version - it's more complicated than that). So you can imaging that the
KLAs
table is quite small (only 7 records), but the tables get larger as each
one
lower in the chain is increased to account for grades, categories and more
detailed categories towards the bottom.

My fields down to "Knowledge and Understandings" are fine. The problem
occurs with the "descriptor" field being truncated on my form. The
"Knowledge
and Understandings" combobox provides the user with options based on the
previous 4 choices, but it is only really a 'title' field (because I
certainly couldn't include the descriptor for it within a combo box). Next
to
this combo box is a text box that displays the contents on a memo field
(from
the same table) depending on this choice. It is this descriptor field that
is
being truncated.

All the fields are 'unbound' as they all depend on choice of the previous
field (and there are 'duplicate' records as some grades have identical
choices), then a search is carried out on the next table using the Primary
and Foreign Keys to retrieve 'after_update':

Private Sub cboKnowledgeAndUnderstanding_AfterUpdate()
Me.txtKnowledgeDescriptor = Null
Me.txtKnowledgeDescriptor = Me.cboKnowledgeAndUnderstanding.Column(4)
Me.cboDetailedElement.Requery
End Sub

My Row Source Type is Table/Query
My Row Source says:

SELECT [Knowledge And Understandings].KnowledgeAndUnderstandingsID,
[Knowledge And Understandings].KnowledgeAndUnderstandingsName, [Knowledge
And
Understandings].KLAIDF, [Knowledge And Understandings].ByEndOfYear,
[Knowledge And Understandings].KnowledgeDescriptor
FROM [Knowledge And Understandings]
WHERE ((([Knowledge And Understandings].KLAIDF)=[Forms]![Planning
Development]![cboKLA]) AND (([Knowledge And
Understandings].ByEndOfYear)=[Forms]![Planning
Development]![cboByEndofYear]));

My format fields (both on the form and table) are empty. I can't find
where
there is any property called "Unique Value" either on my form or table (I
read that this could be a problem). As you can see I have not the word
DISTINCT anywhere within my query. And I don't have GROUP BY in my SQL
statement, nor am I sorting the results.

These tables will not require editing in the finished product - they are
simply to be stored as ID numbers in a new database as the pillars upon
which
to create planning. The descriptor, in fact is disabled, and for desplay
purposes only. In fact these tables need only ever be read only.

What is the problem with my descriptor field? Why is it truncated?

 

[Ken Snell \(MVP\)]

I think you misunderstood what I said. A combo box CANNOT contain more than
255 characters in any column that is in the combo box's RowSource query. if
you are setting the value of a textbox using the value of a column from the
combo box, you CANNOT get more than 255 characters period from the combo
box's column.

Instead, use the primary key value from the combo box's RowSource query as a
criterion in a DLookup function to retrieve the full memo field's contents:

Private Sub MyComboBox_AfterUpdate()
Me.TextBoxName.Value = DLookup("MemoFieldName", _
"QueryName", "PrimaryKey =" & Me.MyComboBox.Column(0))
End Sub

The above assumes that the primary key value is in the first column of the
combo box, and that the primary key is a numeric data type.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

No . I would like to populate a memo field, but have it depend on the
user
input from the previous combo box (I realise that a combo box can hold
more
that 255 characters. Basicaly the combo box displays a list of titles and,
upon choosing one of these titles, the form displays the full description
in
the next text box - the combo box needs to update the text box, so I've
been
using the "Update_After" event on the combo box.

You're wanting to display the memo field in a combo box's dropdown list,
is
that right? A combo box (and a list box) cannot display more than 255
characters for any column in the combo box's Row Source query. That is a
built-in design, and cannot be changed.

What you can do is to display the memo field's contents for the selected
item from the combo box in a textbox on the form. You can use the code in
this article for doing that:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi,

I know this is probably a rather tiresome query, but I've surfed the
net
endlessly and not found a solution.

I'm creating a classroom planning/assessment database application (the
new
Learning Essentials for those involved in Australian Education). The
system
I'm creating, database-wise, is a little complicated (I am an absolute
self-taught beginner, so please be kind and precise with your
terminology,
explaining where I should find the tools you mention). We have 7
"KLAs",
which are each chosen with an "Assessable Element" (depending on the
KLA)
for
each grade, which each act as an umbrella for a number of "Knowledge
and
Understandings" (each K & U has a descriptor of more than 255
characters.
Under each "Knolwedge and Understanding" is a dozen or so "Detailed
Elements"
(that the kids get assessed on).

So I have the following heirachy of tables:

KLAs
Assessable Elements
Year (or Grade in America-speak)
Ways of Working (differing and dependent on previous choices)
Knowledge and Understandings (includes the descriptor)
Detailed Elements

Each table is linked in heirachy to the one above via foreign keys (and
sometimes to more than one table above (my description is the
simplified
version - it's more complicated than that). So you can imaging that the
KLAs
table is quite small (only 7 records), but the tables get larger as
each
one
lower in the chain is increased to account for grades, categories and
more
detailed categories towards the bottom.

My fields down to "Knowledge and Understandings" are fine. The problem
occurs with the "descriptor" field being truncated on my form. The
"Knowledge
and Understandings" combobox provides the user with options based on
the
previous 4 choices, but it is only really a 'title' field (because I
certainly couldn't include the descriptor for it within a combo box).
Next
to
this combo box is a text box that displays the contents on a memo field
(from
the same table) depending on this choice. It is this descriptor field
that
is
being truncated.

All the fields are 'unbound' as they all depend on choice of the
previous
field (and there are 'duplicate' records as some grades have identical
choices), then a search is carried out on the next table using the
Primary
and Foreign Keys to retrieve 'after_update':

Private Sub cboKnowledgeAndUnderstanding_AfterUpdate()
Me.txtKnowledgeDescriptor = Null
Me.txtKnowledgeDescriptor = Me.cboKnowledgeAndUnderstanding.Column(4)
Me.cboDetailedElement.Requery
End Sub

My Row Source Type is Table/Query
My Row Source says:

SELECT [Knowledge And Understandings].KnowledgeAndUnderstandingsID,
[Knowledge And Understandings].KnowledgeAndUnderstandingsName,
[Knowledge
And
Understandings].KLAIDF, [Knowledge And Understandings].ByEndOfYear,
[Knowledge And Understandings].KnowledgeDescriptor
FROM [Knowledge And Understandings]
WHERE ((([Knowledge And Understandings].KLAIDF)=[Forms]![Planning
Development]![cboKLA]) AND (([Knowledge And
Understandings].ByEndOfYear)=[Forms]![Planning
Development]![cboByEndofYear]));

My format fields (both on the form and table) are empty. I can't find
where
there is any property called "Unique Value" either on my form or table
(I
read that this could be a problem). As you can see I have not the word
DISTINCT anywhere within my query. And I don't have GROUP BY in my SQL
statement, nor am I sorting the results.

These tables will not require editing in the finished product - they
are
simply to be stored as ID numbers in a new database as the pillars upon
which
to create planning. The descriptor, in fact is disabled, and for
desplay
purposes only. In fact these tables need only ever be read only.

What is the problem with my descriptor field? Why is it truncated?

 

[Miss Teacher]

Ah! The problem is that the query is still a COMBO BOX RowSource query and is
searched for by Access as such? Regardless of the fact that it is sent to a
text box? Got it! So I have to have a fresh query that is not designed to
place data into a combo box formerly.

Thanks for your help - I'm learning, slowly...

I think you misunderstood what I said. A combo box CANNOT contain more than
255 characters in any column that is in the combo box's RowSource query. if
you are setting the value of a textbox using the value of a column from the
combo box, you CANNOT get more than 255 characters period from the combo
box's column.

Instead, use the primary key value from the combo box's RowSource query as a
criterion in a DLookup function to retrieve the full memo field's contents:

Private Sub MyComboBox_AfterUpdate()
Me.TextBoxName.Value = DLookup("MemoFieldName", _
"QueryName", "PrimaryKey =" & Me.MyComboBox.Column(0))
End Sub

The above assumes that the primary key value is in the first column of the
combo box, and that the primary key is a numeric data type.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

No . I would like to populate a memo field, but have it depend on the
user
input from the previous combo box (I realise that a combo box can hold
more
that 255 characters. Basicaly the combo box displays a list of titles and,
upon choosing one of these titles, the form displays the full description
in
the next text box - the combo box needs to update the text box, so I've
been
using the "Update_After" event on the combo box.

You're wanting to display the memo field in a combo box's dropdown list,
is
that right? A combo box (and a list box) cannot display more than 255
characters for any column in the combo box's Row Source query. That is a
built-in design, and cannot be changed.

What you can do is to display the memo field's contents for the selected
item from the combo box in a textbox on the form. You can use the code in
this article for doing that:
http://www.mvps.org/access/forms/frm0058.htm

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




Hi,

I know this is probably a rather tiresome query, but I've surfed the
net
endlessly and not found a solution.

I'm creating a classroom planning/assessment database application (the
new
Learning Essentials for those involved in Australian Education). The
system
I'm creating, database-wise, is a little complicated (I am an absolute
self-taught beginner, so please be kind and precise with your
terminology,
explaining where I should find the tools you mention). We have 7
"KLAs",
which are each chosen with an "Assessable Element" (depending on the
KLA)
for
each grade, which each act as an umbrella for a number of "Knowledge
and
Understandings" (each K & U has a descriptor of more than 255
characters.
Under each "Knolwedge and Understanding" is a dozen or so "Detailed
Elements"
(that the kids get assessed on).

So I have the following heirachy of tables:

KLAs
Assessable Elements
Year (or Grade in America-speak)
Ways of Working (differing and dependent on previous choices)
Knowledge and Understandings (includes the descriptor)
Detailed Elements

Each table is linked in heirachy to the one above via foreign keys (and
sometimes to more than one table above (my description is the
simplified
version - it's more complicated than that). So you can imaging that the
KLAs
table is quite small (only 7 records), but the tables get larger as
each
one
lower in the chain is increased to account for grades, categories and
more
detailed categories towards the bottom.

My fields down to "Knowledge and Understandings" are fine. The problem
occurs with the "descriptor" field being truncated on my form. The
"Knowledge
and Understandings" combobox provides the user with options based on
the
previous 4 choices, but it is only really a 'title' field (because I
certainly couldn't include the descriptor for it within a combo box).
Next
to
this combo box is a text box that displays the contents on a memo field
(from
the same table) depending on this choice. It is this descriptor field
that
is
being truncated.

All the fields are 'unbound' as they all depend on choice of the
previous
field (and there are 'duplicate' records as some grades have identical
choices), then a search is carried out on the next table using the
Primary
and Foreign Keys to retrieve 'after_update':

Private Sub cboKnowledgeAndUnderstanding_AfterUpdate()
Me.txtKnowledgeDescriptor = Null
Me.txtKnowledgeDescriptor = Me.cboKnowledgeAndUnderstanding.Column(4)
Me.cboDetailedElement.Requery
End Sub

My Row Source Type is Table/Query
My Row Source says:

SELECT [Knowledge And Understandings].KnowledgeAndUnderstandingsID,
[Knowledge And Understandings].KnowledgeAndUnderstandingsName,
[Knowledge
And
Understandings].KLAIDF, [Knowledge And Understandings].ByEndOfYear,
[Knowledge And Understandings].KnowledgeDescriptor
FROM [Knowledge And Understandings]
WHERE ((([Knowledge And Understandings].KLAIDF)=[Forms]![Planning
Development]![cboKLA]) AND (([Knowledge And
Understandings].ByEndOfYear)=[Forms]![Planning
Development]![cboByEndofYear]));

My format fields (both on the form and table) are empty. I can't find
where
there is any property called "Unique Value" either on my form or table
(I
read that this could be a problem). As you can see I have not the word
DISTINCT anywhere within my query. And I don't have GROUP BY in my SQL
statement, nor am I sorting the results.

These tables will not require editing in the finished product - they
are
simply to be stored as ID numbers in a new database as the pillars upon
which
to create planning. The descriptor, in fact is disabled, and for
desplay
purposes only. In fact these tables need only ever be read only.

What is the problem with my descriptor field? Why is it truncated?

 

[Ken Snell \(MVP\)]

Ah! The problem is that the query is still a COMBO BOX RowSource query and
is
searched for by Access as such? Regardless of the fact that it is sent to
a
text box? Got it! So I have to have a fresh query that is not designed to
place data into a combo box formerly.

Well, not quite. The code that gets the value from a .Column(i) of a combo
box is actually reading a value from the query's results returned to the
combo box. It's not rereading the original query at all. That is why you
need to use a DLookup function to reread the query. You can use the same
query that was in the RowSource property of the combobox (e.g., if you're
using a stored query named "qryComboBoxQuery", you can use that same query
name in the DLookup function as the second argument). However, if you're
using an SQL statement as the RowSource query for the combo box, then you'll
need to save that query and use the saved query in the DLookup function.