Re: Truncated Memo field displayed on form

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/




"Miss Teacher" <MissTeacher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:36EEF85A-D7C2-4474-BD9A-90C167A5FD28@xxxxxxxxxxxxxxxx
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.

"Ken Snell (MVP)" wrote:

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/




"Miss Teacher" <MissTeacher@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B3C39713-08BD-47EC-9C12-4C972E6D6184@xxxxxxxxxxxxxxxx
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?





.

Relevant Pages

  • Re: Truncated Memo field displayed on form
    ... The problem is that the query is still a COMBO BOX RowSource query and is ... The above assumes that the primary key value is in the first column of the ... My fields down to "Knowledge and Understandings" are fine. ... occurs with the "descriptor" field being truncated on my form. ...
    (microsoft.public.access.forms)
  • Re: Using part of a field
    ... See the article "Finding and replacing characters using wildcards" at: ... AS400 SQL may have a different way of doing ... called i.dsn, containing the followiing text: ... sort of thing in a query. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Export to Ascii
    ... characters in a calculated field when you export a query. ... Modify your query to append its output to the temporary table; ... "John Nurick" wrote: ... that instead of concatenating with a delimiter it concatenates a fixed ...
    (microsoft.public.access.externaldata)
  • Re: SQL too long?
    ... I haven't done a thorough analysis here, but I suspect that the number of characters needed to express the SQL may not be as important as the number of aggregate functions like Sumand Countthat you invoke along the way. ... Although what you now have may be correct, assuming no simian behavior on the part of Access, splitting up the Query may also make it easier to debug if you later encounter any questions concerning its correctness, or if you need to revise it in some way. ... For example, in my copy of Access 2000, it says that the "Number of characters in an SQL statement" has a maximum value of "approximately 64,000", so you probably have some wiggle room there, even without using abbreviated aliases for some of the names. ... tblARTrn01.invno) INNER JOIN tblARMst01 ON tblDUPSFreight.invno = tblARMst01. ...
    (microsoft.public.access.queries)
  • Re: Subquery Confusion
    ... Then I got this crazy idea that an Array can only contain a maximum ... number of characters, ... Then I decide that maybe I'm completely wrong with my query, ... it out of Excel VBA and spit it into Microsoft SQL Server Management ...
    (microsoft.public.excel.programming)