Arrgh Help, Error 3163

[Ian]

I have a form with amongst other controls on it, two combo boxes (A) and (B)
that use as their RowSources two SELECT DISTINCT queries based on two
individual tables. The tables have 3 fields, an ID (auto), text and memo. The
combo box displays the text field. After selecting one of these fields the
accompanying memo field data is displayed in a separate textbox on my form.
These combo boxes are only made visible to the user when a particular open is
selected from another combo box(Z).

This has worked fine for me in the version of my app and the previous 5
versions. Today however, when I select one of the key options in combo box(Z)
and then click on the arrowhead of the particular combo box(A), I get the
error 3163, telling me I'm trying to enter or paste too much information. If
I choose the other key field in combo box(Z) and then click the arrowhead in
combo box(B), it gives me the correct set of drop downs and will populate the
textbox with the contents of the underlying tables memo field as intended.

The only thing I have done is Compile the code and Compact and Repair the
database to reduce it's size (53Mb > 5Mb) when compared to the original
version I copied it from earlier today. Has this caused the problem and if so
why has it only affected one combo box and secondly doesn't making an MDE
compact and repair as well? Previous versions have been made into MDEs.

I am at a complete loss to understand what has heppened and more importantly
how to reverse it.

Any help would most, most appreciated, thank you,
Ian.

 

[Ian]

Okay, a bit more information, after some head scratching and trying different
things, it appears the problem seems to be in displaying the contents of the
memo field in the textbox on the form when the memo field for a particular
record exceeds the 255 size limit for a textbox. However, when the app was
working all it did was merely truncate the memo field and didn't give me the
error message.

How can I display my large memo field on the form with out Error 3163 being
invoked? Is it possible in the textbox i currently have? I'm sure I've done
this beofer but can't remember how i got around it.

I appreciate your help,
Ian.

 

[Dale Fye]

Ian,

I usually leave the memo field out of the queries that populate the combo
boxes. Then, in the after update of the combo, go back and retrieve the data
for the text box that is in the memo field. Something like:

me.txt_Memo = DLOOKUP("Memo_Field", "MyTable", "ID = " & me.cbo_ID)

This way, you get the entire text that is in the memo field rather than just
the first 255 characters.

Regarding todays problem. Have you made sure that all of your references
are selected properly?

 

[Ian]

Dale,

You are a hero!! I have tormented myself for hours with this one. Your
suggestion on keeping the memo field out of the query has done the trick. I'm
at a loss to explain "today's" events, as all refernces seem intack, but
everything works and I'm happy again.

Thanks again for your prompt and insightful help,
Ian.

 

[Dale Fye]

Ian,

Jet doesn't look kindly on trying to do DISTINCT or DISTINCTROW queries that
include memo fields. I use a lot of memo fields, but when I do, I generally
leave them out of queries that support combo or list boxes.

Glad to help

Dale

 

[John W. Vinson]

Ian,

Jet doesn't look kindly on trying to do DISTINCT or DISTINCTROW queries that
include memo fields. I use a lot of memo fields, but when I do, I generally
leave them out of queries that support combo or list boxes.

No matter how you do it, a Combo Box will truncate a memo field anyway. Memos
simply don't belong in combo or listboxes.

John W. Vinson [MVP]