This might require some lateral thinking...

I have an OnClick on a label that opens a Word doc and then pastes into Bookmarks the value of various Access fields. The last Bookmark takes data that is from a memo field and the Bookmark is at the end of the Word doc. and it is this area where my question applies.

The memo field data pasted into the Bookmark is edited (sometimes). When complete the Access button at the bottom of the screen is clicked and then another label is clicked and its OnClick action copies the section of writing from the last Bookmark and pastes back into the memo field on the persons record.

In addition a new record is opened on another table and the writing is pasted into a memo field in that table along with the persons ID number etc. Thus everytime we run this we create an additional record in this other table. Now to my problem. Many times when this is run the section of writing in the Bookmark is not changed and of course this results in duplicates in this other table.

Now if Access 95 would let me set a memo field for "no duplicates" then that would solve everything but it won't allow me to do that.

Any ideas?

Mike

No version of Access nor any other RDBMS is going to let you set a memo field to "no duplicates". That would require defining a unique index with a fixed length of 64k for the memo field.

You can solve the problem by moving the section of the macro that creates the duplicate record to the memo field's AfterUpdate event. That way, it will only be executed if the field is actually changed. There is of course a ripple effect and that is that you'll have to figure out how to stop the first part of your macro from arbitrarially updating the memo field. I don't quite understand the purpose of updating the memo via Word when you have the record open in an Access form but I'm sure you have some reason.

Pat Hartman said:

No version of Access nor any other RDBMS is going to let you set a memo field to "no duplicates". That would require defining a unique index with a fixed length of 64k for the memo field.

You can solve the problem by moving the section of the macro that creates the duplicate record to the memo field's AfterUpdate event. That way, it will only be executed if the field is actually changed. There is of course a ripple effect and that is that you'll have to figure out how to stop the first part of your macro from arbitrarially updating the memo field. I don't quite understand the purpose of updating the memo via Word when you have the record open in an Access form but I'm sure you have some reason.

Click to expand...

Pat,

The writing in question is a summary of why a policy or policies were sold..need, pluses, minues etc. The Wod doc is also linked to two queries which display the policy benefits.

The writing in the memo field is often edited when the Word doc opens so that formatting such as bold type etc can be done. By pasting it back to the memo field then it is updated.

The mutli record version is there for a couple of reasons. Firstly it gives us a copy of very single one that was done. Secondly, this is fairly new for us as it relates to gov't legislation introduced in March 2004 and we think by having a copy of every one that is done we will be able to get perhaps 10 or so that are basically the same. The multi record table has a form which we can open and transfer the data from one its records memo field to the one on the persons record.

An easy option is to have pairs of labels whereby one won't store the writing in a new record. The problem with that solution is that I can't have a situation whereby someone "decides" which way to go. In other words I am better off to have 1000 records with 88 duplicates but with every one that was done being recored as opposed to no duplicates but we are missing a few copies of what was done.

I have the same duplicate problem in another muti record table. In that table amemo field has the entire Word doc pasted in. We store every piece of correspondence done in Word in Access memo fields.

One partial solution might be to put a couple of labels on the form along the lines of Did you edit the Word doc Yes No. If No is clicked then a label becomes visible that would store the writing into a memo field in another table or perhaps a second memo field on the table current being used. That would probably knock out most of the duplicates but at the same time not result in a failure to record.

Sometimes I long for the days of boxes of cards

Mike

From your description, I begin to smell a rat of a particular flavor - a database table that is not properly normalized.

The way to prevent duplicates is to build a key and set up "NO DUPS" but, of course, you cannot build a key on a memo field.

Two ways that might help are to expand your thinking about what is in the key. One possibility is to time-tag and username-tag the entry in your auxiliary table, keeping the time tag accurate to at least the second. The odds are that you can slow down your users so that the same user cannot click the button twice within the same second. (Heck, disable the button for a second...).

The other is that if you control the order of doing the Word update vs. a log of updates, you can know (from the log of updates) if the same person is about to update the same field in the same file at the same time-tag as the one you just entered.

Then there are the hashing techniques. You cannot easily tell if the contents of a long memo field are the same before and after an edit - but if you create a hash key from the before and after, and if the hash is long enough, and if they differ from before to after, you can verify that an edit occurred. You might have to do some research into hash keys. If you have a programming package that supports development of WinSock connections over Secure Shell or in an IPSEC environment, you would be required to generate MD4 or MD5 hashes, so the subroutines to do so would be in the resource kit. You might therefore do some web searches on that topic. As I recall, if you do two hashes based on different key sizes then the only way that they BOTH stay the same on the before and after strings is if the string didn't change, though that DOES assume you didn't use really short hash keys. If you find the reference on IPSEC, they will give you more info on the theory of this technique.

BTW, this method also lets you do a form of validation to show no one has been mucking about in your DB - because if you separately store the hashes, you can decide whether something has or has not been changed since the hashes are stored by recomputing and comparing the old and new hashes. I'm pretty sure that for 128-bit hashes, your security level is quite high. But I'm not so sure this is the direction you wanted to go. If not, perhaps I misunderstood what you wrote. But this is what came to mind.

I think I have found a solution or at least a part solution. The Len function works on the memo field. I just entered 12 pages of writing and it counted just fine. Just to check I then went back a couple of times and added and removed a couple of characters and the Len field reflected those changes.

I also tried opening a form with a macro and Where being

[Field]=[Forms]![MyForm]![Field]

That worked but only if character count was held to 250 otherwise message came up Field too long.

The chances of two entries having the same character count but being different writing would have to be extremely small.....I think?

Mike

The_Doc_Man

The duplicates are in the multi record part and they could be created a week or a month apart. Duplicates of the memo field on the main table are not a problem and occur with some regularity as different prospects get exactly the same notes.

The is a duplicate created virtually everytime the final printout is done as that is the same system except it just goes all the way through without breaking mid way with the Word doc opened for editing. I have to have the same set up on the print out system because sometimes only a print out is done, that is, no trial run. The notes which migh be very simple and short are entered into the memo field and then the print out system is run.

Mike