Moving memo fields from main table in to seperate table

[munkee]

Hi all,

I have 3 memo fields on my main table. I understand that they are susceptible to corruption over network environments. I am looking to move these memo fields in to seperate tables and I assume create a 1:1 relationship with my main table.

I have created the tables with a primary key and a foreign key and joined them to the primary key of the main table with integrity applied.

My issue is, I have a number of forms where the memo fields in the main table show up. How do I go about replacing these with the memo fields I have produced in the 1 to 1 relationship? My form recordsources are based on the main table tblLog and I use sub forms to normally link to my other relationships outside of this.

Any help is appreciated.

[NeoPa]

Base your forms instead on the query that links your [tblLog] with your new Memo fields table.

[munkee]

That is the answer I figured I would be doing. However this then caused a whole heap of problems. One major problem is that my data access pages no longer allowed adding.editing.deleting records so for now I have had to revert back to my memo fields being sat on the main table.

I keep getting errors about not being able to update the record unless another record is present ( as you can tell I have forgot the exact one ). But I think the issue is around that I need to save the record as soon as the page opens to create a key between the main table and the memo table before I can start adding in any data. Coupling this with a query for a record set and the data access pages were crapping out. Weird how it is happening since I have the pages set to data entry = true

[NeoPa]

There seems to be some confusion here :

1. Firstly, and most importantly, I can be of very limited help if you cannot remember the exact details.
2. If you are using a query then it is important to know that it is an updatable query before binding a form to it. See Reasons for a Query to be Non-Updatable.
3. If you set a form to Data Entry then that allows showing only of the new record. Once a record exists in the table it cannot be shown in Data Entry mode.

I hope this is some help. If not, we'd need to look at some more precise and practical information.

[munkee]

No worries NeoPa I am now revisiting whether there is a need for these fields to be memo type.

We have been using an excel spreadsheet to keep a log of issues. One of the fields in the spreadsheet asks to describe the issue. It appears the average is a lot less than 255chars (with 1.7k records in the spreadsheet) and around 20 entries of the 1700 ever being over 255. So I think it might be easier just to set the fields as text and put the limit in place with a char counter. It might even help users be more accurate with their descriptions instead of waffling on.

[NeoPa]

Munkee: No worries NeoPa I am now revisiting whether there is a need for these fields to be memo type.

Isn't it funny how sometimes problems can trigger us to look at things in a different way and come up with a better solution than if we hadn't fallen over the problem in the first place.

It is a bit of a shame that Text fields have such a low limit though. 255 chars is not much in today's world.

Anyway, good luck with it.

[munkee]

Just thought I would finalise the topic with my solution.

Since the primary initial input for the database will be coming from a data access page (submission form) I have chosen to move all of the memo fields to text (255 char limit).

In the text areas where the 255 char limits apply I have used javascript to show a message under the box when it gains focus. This message informs the user that they have 255 chars left to enter their description and this decreases as they type.

The box locks for any further input when they reach their limit of 255 characters. On losing focus the message disappears to keep the submission form nice and tidy. Atleast this way I can now ensure users are aware of the limit and actively work towards not exceeding it in a quite user friendly way.

[NeoPa]

Sounds like a neat solution :)