Memo fields on form

[swotr]

I am fairly New to access and have a subform within a form for users to enter information. The main form has a combobox where a selection is made and the subform displays all the records that correspond to the selection for the users to edit. There is underlying VB code with a select statement from a table that was used to do this.

There is a text box in the form whose control source is a memo field in the table. I am noticing that when data is displayed on the form (pulling from the table), everytime the text box has a lot of data, other fields on the form are not displaying ANY data. Is this tied to the 255 character restriction?

The select statement is fairly straighforward. Select all from table where field = combobox input orderby (a simple numeric primary key)

I have a deadline to meet tomorrow, so any help provided is greatly appreciated!

-Thanks.

 

[roelandkim]

These other fields, are they on the main form, or the subform? From what I understand, you have a form with a drop down box, when a user makes a selection, the subform is populated with data from an underlying table which feeds the subform. Try putting the other data fields also within the subform. It sounds like this is most likely an issue of how the form/subform is setup. In my experience, what you are trying to do sounds straightforward, as long as the memo field is setup as a memo field in the actual table structure, it shouldn't be a problem.

 

[swotr]

The other data fields are on the subform also.

One thing I discovered is that when i use Microsoft office 2007 (on my home computer, this issue does not exist. It exists only in Office 2003.

Unfortunately all the users only have office 2003.

Any suggestions?

 

[swotr]

I did some more investigation and found that every time the memo field in the table exceeded (250 and something) characters, that is when access 2003 fails to display the other fields in the form.

Oddly enough, the memo field itself displays in its entirety, the other text and numeric fields in the form are not displaying.

If i reduce the number of characters of the memo field to less than 250, the data does display. However i cannot do this in reality just to make the form work.

This is only an issue in Access 2003 and not 2007.

Is there any known fix for the issue? Help please!

 

[missinglinq]

What service pack are you running on the 2003 machine? The SP3 service pack for 2003 came with a boatload of bugs, although this one is new to me.

 

[swotr]

SP2.

Should i install a more recent service pack?

 

[missinglinq]

I don't think that's going to make a difference. If you'd had SP3 installed (which, as I said, is buggy) I'd have suggested installing the latest hotfix for it. I spend about 6-8 hours a day on this and 5 other Access forums, and have done for the past three years, and I've never seen any bug such as this reported. I suspect that your form or subform is corrupt.

I suppose you could try the old "create a blank database and import everything into it" routine. It does do away with corruption in an amazingly large number of cases. Other than that, I'm afraid I have no other guesses. Sorry!

 

[roelandkim]

I'm also running Access 2003 sp2, and tried to recreate the issue you are having. I've made multiple databases in this version, and have yet to come across this issue. I was able to kind of mess up the form by creating a form with a memo box, then chaning the memo box on the form to a text box. It wouldn't display info in the field anymore.

Make sure on the table level the field in question is set to a Memo field

Delete the field on the form, and then add it back, there may have been a formatting problem, where the table has the field as a memo, but the form itself has a different format.

If neither of these seem to work, then I would either follow the suggestion above, or try recreating the form from scratch.

 

[swotr]

Thanks for the suggestions. I have tried the above

1. Deleting the text box and reinserting it (attached to the memo field in the table). However i dont understand what you mean by memo box.
2. Recreating the form from scratch.

Neither seems to work. However i did notice that the subform on its own does not have the issue. All the fields display correctly whether the memo field has 250 plus characters or not.

Which makes me believe that the select statement attached to the 'after update' event of the combo boxes in the main form (that pulls the records filtered per the selection in the combo boxes) is not able to function correctly when the memo field has more than 255 characters.

So my new question is, is there another way to do this.

My subform needs to only display the records filtered per the selection made from the combox(es) (one for Business unit and the next for department) in the main form. Is there a way to do this without using a select statement.


Please let me know if i can attach the database (will cleanse it to remove proprietary into). It is only 2 MB.

Thanks again!

 

[roelandkim]

Sure, post a sample DB, I'll take a look and see if there is another way to do it.

 

[boblarson]

Use a query (no aggregates or expressions) as the basis of the form and NOT the table. We found, on another forum, that doing so made the full memo field available to the form, for some odd reason.

 

[wiklendt]

have you also tried "compact and repair"? and at 2mb, a zipped version should make it within the allowed kb limit for post attachments...

 

[swotr]

Here is the cleansed database.

In the Form selections please use BU1 in the Business Unit selection box and DG1 in the Department/Group selection box to see what i am describing.

The password to the VB code is annualbusinessplan

Thanks!

 

[swotr]

Hello? Anybody been able to view the database to see the issue i was describing?

 

[boblarson]

Hello? Anybody been able to view the database to see the issue i was describing?

Yes, and if you had followed my advice in this post:

http://www.access-programmers.co.uk/forums/showpost.php?p=829931&postcount=11

Use a query (no aggregates or expressions) as the basis of the form and NOT the table. We found, on another forum, that doing so made the full memo field available to the form, for some odd reason.

It works just fine (other than the scroll bars don't seem to work in your textbox for some reason). If you use your down arrow keys in my sample once you are in that field you will see the whole field is there (see the Form's Record Source on the form where I changed it to a query).

 

[swotr]

I still see the issue in the database that you updated where if i select Business unit BU1 and Department DG1, and go to the second activity (where the memo field (Annual activity) is more than 250 or so characters), all the remaining fields on the form are empty.

I feel like i am missing something. I also tried what you suggested, created a query selecting all the fields from the table and changed the record source of both the form and subform to the query instead of the table. That doesnt appear to fix the issue either.

I am not sure if this is a version difference issue that is causing this. I already mentioned before that Access 2007 did not have this issue.

I am ready to give up at this point!

Thanks anyway.