Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-04-2008, 04:41 PM   #1
Mike375
Guest
 
Posts: n/a
Can a memo field in different records be combined without SendKeys

I have a problem that is Vista related. My business partner has just purchased a laptop with Vista and one of the problem areas is SendKeys. I don't have Vista and he is away at the moment so we only have part time communication so I am trying a few solutions in the hope of getting the problem fixed.

He is getting a message along the line of.....Access needs to be reinstalled because SendKeys is not available.....he does not has his MSOffice with him so that can't be tried at the moment, although everything else is working OK.

There are a few macros involved but I have converted then into VB Module and that works OK for me on XP, although it does not carry out all instructions but it basically works. But it will be a few days before he can try what I have sent him.

Note: I just notice that my conversion to VB module is not doing the SendKeys. No messages etc it is if the section covering SendKeys was not there. The example below was done with XP and macro. The SendKeys and the various other actions are all in the module. Edit...All is OK....my conversion of the macros to a module worked

So I would like to try another possible solution and that being to avoid SendKeys.

The situation involves a file note entry system. Each file note creates a different record and in each record there is a memo field with the file note that was made and of course time and date.

There is another table that holds one record for each person that has a file note made. Whether one file note is made or a 100 file notes there is only record.......The record is created when the first file note is made and the table will not accept duplicates on the persons ID number.

When a file note is made the system then opens the record in the other table and adds with SendKeys to the entry in the memo field on the "one record only table"

A form opened on the "one record" looks like this and as you can see the contents of the memo field from the "many records" have been combined into one. Can I do this without using SendKeys

5/01/2008 11:34:51 AM Test 1 The quick brown fox jumped over the lazy dog

5/01/2008 11:35:21 AM Now is the time for all good men to come to the aid of the party

5/01/2008 11:35:50 AM Why did John have to buy a computer with Vista?

Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

5/01/2008 11:37:38 AM zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz zzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
xxxxxxxxxxxxxxxxxxxxxxxxxxxx


Last edited by Mike375; 01-04-2008 at 05:17 PM.
  Reply With Quote
Old 01-05-2008, 02:03 AM   #2
RoyVidar
Registered User
 
Join Date: Sep 2000
Location: Norway
Posts: 805
Thanks: 0
Thanked 4 Times in 4 Posts
RoyVidar is on a distinguished road
Sendkeys has always has it's issues, and many developers recommend against it - sometimes focus has changed, and the keystrokes ends up somewhere else... I've read somewhere that Sendkeys with Access 2003 isn't supported by Vista, but I don't know, as I don't use it.

I'd be rude, or bold enough to suggest that your immediate challenge isn't Vista related, but related to non optimized date/table structure. What you, in my opinion, should have use, is one record per each of these "Person file notes", i e, create a true 1:M relationship.

Your sample data, as I see it, lends itself to that idea

ThePersonFileNote
PersonID, Date/Time, Text (or memo)

If you do so, then you can use straight forward methods of manipulating it - I mean - it is a database, why shouldn't you be able to insert, update, delete or aggregate the information you store through simple SQL? With your current system, you need some creative programming to manipulate information that seems simple and relational enough to be stored relationally.
__________________
Roy-Vidar
RoyVidar is offline   Reply With Quote
Old 01-05-2008, 02:23 AM   #3
Mike375
Guest
 
Posts: n/a
Currently I have

Client..1 record

File Notes....lots of records for client record. Thus a One to Many

Without SendKeys I have no idea how to get the contents of the memo field from several records into a single record/memo field.

In the FileNotes table each record will have writing in the memo field but if there are six records I want all the note writing to show as a single entity, which I currently do with the one record in the other table and by SendKeys. The memo field in the "one record per file note" table has the contents of all the memo fields from the related records.

In other words, if there are 6 file note records for the client and each record has one line of writing in the memo field, then I want those six lines of writing to be in one record and with the time/date before each line.

I could have the FileNotes records open together and make the memo fields look like it is one thing but that would be messy because the height of the field would be either too little or too much.

The outlay below is from 4 records and by SendKeys put into one record in another table

5/01/2008 11:34:51 AM Test 1 The quick brown fox jumped over the lazy dog

5/01/2008 11:35:21 AM Now is the time for all good men to come to the aid of the party

5/01/2008 11:35:50 AM Why did John have to buy a computer with Vista?

Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

5/01/2008 11:37:38 AM zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz zzzzzzzzzzzzzzz
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
xxxxxxxxxxxxxxxxxxxxxxxxxxxx

  Reply With Quote
Old 01-05-2008, 02:38 AM   #4
stopher
AWF VIP
 
stopher's Avatar
 
Join Date: Feb 2006
Location: Southampton, UK
Posts: 2,396
Thanks: 20
Thanked 311 Times in 292 Posts
stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough stopher is a jewel in the rough
Quote:
Originally Posted by RoyVidar View Post
Your sample data, as I see it, lends itself to that idea

ThePersonFileNote
PersonID, Date/Time, Text (or memo)
I completely agree with Roy. You can easily make it look seemless on screen or on a report. Also you can set the shrink/grow options so that your lines will automatically be the right height according to how much text they have.

One question? DO you permanently want all the lines store in one field or are they to be entered in seperate fields then combined only for display/reporting purposes?
Chris
stopher is offline   Reply With Quote
Old 01-05-2008, 02:42 AM   #5
RoyVidar
Registered User
 
Join Date: Sep 2000
Location: Norway
Posts: 805
Thanks: 0
Thanked 4 Times in 4 Posts
RoyVidar is on a distinguished road
> Without SendKeys I have no idea how to get the contents of the memo field from several records into a single record/memo field.

Why would you do that? If you have it properly and relationally stored in one table, why on earth would you "calculate" it and store the same data it in a complete redundant and non relational way? Why not just keep it where it is, and be happy with it?

You are mixing how you wish to display something with how it is stored. If you store it correctly, you will always be able to recalculate it on the fly when you wish to display it.

If you store it incorrectly - then you're in dodo - you have multiple versions of the thruth

To get the result you're wanting, I think you could do it quite relationally by doing a select.

If you really wish it to look garbled as you showe it, and not an ordinary report, where it through a clickety-wizardly-click would look like this
Code:
5/01/2008 11:34:51 AM Test 1 The quick brown fox jumped over the lazy dog

5/01/2008 11:35:21 AM Now is the time for all good men to come to the aid
                      of the party

5/01/2008 11:35:50 AM Why did John have to buy a computer with Vista?

                      Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

                      bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

5/01/2008 11:37:38 AM zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz 
                      zzzzzzzzzzzzzzz
                      zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
                      xxxxxxxxxxxxxxxxxxxxxxxxxxxx
then there's lot of code out there to do such - it would probably involve opening a recordset, and simply concatenate data - in ADO, it could look something like this:

SQLString = "SELECT TheDate, TheComment FROM TheTable WHERE PersonID = 42"
TheString = CurrentProject.Connection.Execute(SQLString, , adcmdtext).GetString(adClipString, , " ", vbCrLf)
__________________
Roy-Vidar
RoyVidar is offline   Reply With Quote
Old 01-05-2008, 03:27 AM   #6
Mike375
Guest
 
Posts: n/a
Chris

The separate records per file note are kept as they are the records and of course allow for retrieval by time/date etc.

The One to One record with the content of all the client's file notes in one record is for the following purposes:

1) The display. The form opens to the same size/postion and the gov't appointed auditors think it is wonderful because it looks just like hand written notes.

2) Easy for a copy/paste to Word.

3) There is a document I must provide to each prospect/client and this is a Word Doc done by bookmarks. One of the bookmarks gets the contents of the memo field with all the notes. The whole document copies back to another table that is a Many to the client/prospect
  Reply With Quote
Old 01-05-2008, 03:36 AM   #7
Mike375
Guest
 
Posts: n/a
It is only garbled if the individual file note is that way. I did that example to show that it can be like "freehand"

I am 100% happy with what is done, the gov't auditors are 100% happy. However, I now have a problem with SendKeys/Vista and would like to be able to it without SendKeys but at the same time not lose on the final result.

5/01/2008 10:31:24 PM I take the view that the most important aspects of disability insurance are the contractual rights provided by the policy document with regards to the definitions of disability and in particular how those definitions impact on a long term claim.

5/01/2008 10:33:40 PM The majority of disability policies available also provide benefits known as “ancillary” benefits.

In most cases these benefits have no bearing on a long term claim and as such I do not give a great deal of credit for having these benefits. Other people may disagree with me as to the most important features of a disability policy.

5/01/2008 10:34:10 PM However, from our first discussion it did seem that you were of the opinion that the definitions of total and partial disability were the critical parts of a disability policy, hence this type of comparison. It should also be noted that some ancillary policy benefits are only paid when the insured has met the definition of total disability.


Last edited by Mike375; 01-05-2008 at 03:49 AM.
  Reply With Quote
Old 01-05-2008, 03:42 AM   #8
Mike375
Guest
 
Posts: n/a
and simply concatenate data

That crossed my mind today.

However, I can't think of a way to that where I get the same display of

Date Time Notes

Also, I have no I idea how to that from data that is in a column. Is there any way to it from multiple records? Actually, that would be easy as it is done as each file note is made so a query could join the last record with the "one record. However the display that looks like notes going down the page with each note preceded by the date and time might be the issue.

This might prove to be my greatest "work around" challenge......something I specialise in

Last edited by Mike375; 01-05-2008 at 04:21 AM.
  Reply With Quote
Old 01-05-2008, 04:14 AM   #9
Rich
Guest
 
Posts: n/a
Still enthralled with the spreadsheet mindset there Mike, eh
  Reply With Quote
Old 01-05-2008, 04:20 AM   #10
Mike375
Guest
 
Posts: n/a
This is what I have just done. A bit late in Australia for this stuff, after 11pm

I just mae a rough setup to get the last file note onto a row with the field that has the build up of the notes/writing.

This & " " & worked OK except of course for the postion.

5/01/2008 10:34:10 PM However, from our first discussion it did seem that you were of the opinion that the definitions of total and partial disability were the critical parts of a ence this type of comparison. 5/01/2008 11:12:10 PM Experiment for SendKeys replacement

The spacing between end of time and writing is from the current system and easily changed. But I need to get like below.

5/01/2008 10:34:10 PM However, from our first discussion it did seem that you were of the opinion that the definitions of total and partial disability were the critical parts of a ence this type of comparison.

5/01/2008 11:12:10 PM Experiment for SendKeys replacement
  Reply With Quote
Old 01-05-2008, 04:25 AM   #11
Mike375
Guest
 
Posts: n/a
Quote:
Originally Posted by Rich View Post
Still enthralled with the spreadsheet mindset there Mike, eh
Rich,

How about a suggestion. I need a result.
  Reply With Quote
Old 01-05-2008, 05:24 AM   #12
Mike375
Guest
 
Posts: n/a
I think I might have a way, a bit rough though, so if someone has better then HELP

Put two bookmarks in a Word Doc, one under the other with space

[]

[]

Table1

The field with all the writing from all the notes....One to One with client

Table2

The record for each file note made......Many to One with client

Assume the first file note is to be made for a client.

The contents of the memo field from Table 1 are inserted into the first bookmark. Thus if the first record for the client nothing will go to the bookmark.

The contents of the memo field where the note is made is inserted into the second book mark

The Word Doc is copied and the contents is stuck into the memo field that will hold all the notes.

For the sencond file note the first note will be inserted into the first book mark and the new file note into the second bookmark and when copied and sent back to the memo field holding all th writing the existing note will be replaced by the 1st and 2nd note.

That should work but there is a bug. Since there will be nothing in the first book mark when the first file note is made that will mean the notes in the memo field will start a line or two down. However, that will rectify itself from the second and subsequent file notes.

Perhaps I can make something whereby if the memo field that holds all the writing is null, then the first bookmark can be used when the first file note for a client is made.

I am thinking out loud

[First client file note entry goes here] Bookmark 1

[Nothing is sent here for first client file note] Bookmark 2

Above is done on basis that memo field of "notes holding" is null.

Word Doc is copied and and sent to the Access memo field

Second file note is made

[The contents of the "notes holding" memo field inserted here] Bookmark 1

[The contents of the memo field of the new note inserted here] Bookmark 2

Word Doc is copied and sent to memo field and thus both notes replace the one note.

Sounds OK
  Reply With Quote
Old 01-05-2008, 07:22 AM   #13
Rich
Guest
 
Posts: n/a
Why do you even need Word?
  Reply With Quote
Old 01-05-2008, 07:44 AM   #14
Mike375
Guest
 
Posts: n/a
Quote:
Originally Posted by Rich View Post
Why do you even need Word?
Because without SendKeys I don't know how to get the contents of more than one memo field record into a single record and with the spacing I want, unless I do the above with Word. I could make the above without too much trouble by adapting what I do with letters as they even have the bit with copying back to Access memo fields.

I can change what I have so it will add the contents of each additional file note to the previous ones with concatenation but I finish with one huge paragragh with date/time at the start of each sentence. When the file notes are put together as a single display they can be a few pages long so it is totally unacceptable to have that as one paragraph.

I have searched Google and the problem with SendKeys and Vista is everywhere. We only have one computer with Vista but obviously that is a temporary situation.
  Reply With Quote
Old 01-05-2008, 08:26 AM   #15
Rich
Guest
 
Posts: n/a
I'm with Roy and Stopher, best of luck

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Having a records calculated field get info from previous record theSizz Modules & VBA 2 01-21-2007 08:46 AM
Querying a Memo field Only returns part of info commandolomo General 5 12-06-2005 07:38 AM
Need Help: Memo Field and Aggregate Functions Yahweh Queries 4 06-21-2005 02:53 AM
Selecting Memo Field from ComboBox kappa General 4 05-29-2005 06:42 AM
Grouping/stepped format? green_dot Reports 7 01-27-2003 10:49 AM




All times are GMT -8. The time now is 08:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World