How do I SendKeys Keytrokes in Macro or VBA to Enter Date and Time in Text Field with Label?

[Cookie]

I have a cat rescue welfare database project in Access (Office 365 subscription) as a work in progress. Two of the text fields I have on each record for each cat are CatName and ClinicalNotes.

Both these fields are on the same form. The ClinicalNotes field retains the observation notes and has a date and time stamp which the user can punch with a button on the form.

I use a SendKeys macro datetimestamp which, when initiated by the user, basically copies the existing text in the ClinicalNotes field to the clipboard, stamps the date and time and then repastes the copied text with the cursor blinking under the date and time stamp for further text entry by the user. The date and time stamp can be punched as often as required.

The date and time stamp output looks like this: 05-Sep-20@10:04:11 PM using SendKeys and the Control Name ClinicalNotes as the Go To Control.


The field may populate, after repasting, like this:

05-Sep-20@11:00:00 PM

(cursor is now here)

05-Sep-20@10:04:11 PM

Focal seizure, 15 minutes apart.

05-Sep-20@09:10:10 PM

Focal seizure, 5 minutes apart. Given 2mg nutrients.




Whilst it is possible to get the CatName by copying it into the clipboard fairly easily, it's presence in the clipboard means that the copied text from the ClinicalNotes, due for repasting, is no longer available for immediate pasting withe Ctrl v.

This then creates the challenge question. How do I add the catname to the end of the date and time stamp? e.g. 05-Sep-20@10:04:11 PM Cookie whilst the clipboard is holding the pastable ClinicalNotes text?

[moke123]

Not sure I'm following what it is your doing. Why are you using the clipboard?
I think we may need to see your table, form, and code.

For the date stamp cant you just concatenate - Now() & me.YourCatNameField?

Are you using one record for the clinical notes and appending to it?
You should probably be using seperate records for each time stamp and note.

[ranman256]

TxtBox= now()

[Cookie]

Thank you.

The clipboard became a necessity when I found SendKeys, in applying the Date and Time stamp, erased the existing text in the ClinicalNotes field. Please see database attached.

Note this is a work in progress experimental database, see attached.

I am not sure how to apply your suggestion Now() & me.YourCatName and keep the present format of the text box. Can you help me with that please?

The ClinicalNotes field is attached to the Record. So one of the risks with this is still through the MSAccess clipboard where it is possible the 1st record notes could accidentally be pasted to the 2nd record.

I've made a Start button just to make sure the MSAccess clipboard is cleared, it's obviously a manual process. The Windows 10 clipboard may still be holding something, or it can be cleared through the MSAccess ribbon expansion.

At the moment, with the attached database, if you go to the CatRecord form, cut any text from the record and delete, then click on the Get Cat Name First button, then click on Punch, the CatName will be recorded on the record, then the date and time stamp above it. Then click on punch as often as necessary to record next date and time.



Attachment 42923

[Cookie]

Thank you. I am not sure how to apply that though. Can you elaborate please?

[moke123]

Cookie,
Looks like you need to work on normalizing your data first. Google Access database normalization and other like terms.
Judging from the fact your table is named Sheet1 I'm guessing you had this in excel before.
Tables should be tall and thin. Break out similiar items into their own tables and relate them to each other with primary and foreign keys.
For instance a table that just describes the cat - name, gender, coat type, dob,color, photo path, etc.
A table for Foster Parents - name address, phone, etc. Likewise a table for purchasers, medicines, treatments, etc.
Each table should have a primary key. You should also be careful in choosing datatypes. you appear to have a lot of memo datatypes that appear to be overkill.

There really shouldn't be any reason for you to be using send keys. I believe they were deprecated as of access 2010. Also no reason to use the clipboard.

As far as notes go you should have a table tblClinicalNotes with a Pkey of CNotesID, and fields- EntryDate, CatID (foreign key from your cats table), txtNotes, StaffID (foreign key from Staff Table to indicate who made the entry), etc.
You can use a subform or subreport to display the notes in your form. Make each entry a seperate record, trying to append a datestamp and notes to an existing record is an invitation for disaster. One mistake could wipe out your data.

Heres a quick sloppy example how you would break down tables and relate them to each other. Also a sub report to show how notes could be done.

[Cookie]

Thank you,

All good advice. Road Map for the future.

But.......

Micrcosoft issued me with a licence at cost to use MS Access (Office 365) which featured SendKeys, which is a feature I like (even if many MVPs don't). It's a bit like cuisine, just because it doesn't taste nice to one person, doesn't mean it cant be liked by others.

So far the crazy little ClinicalNotes field is withstanding testing but I agree there is room for improvement.

[moke123]

Well just be sure to make a lot of backups. Memo fields are known to cause corruption and having 24 memo fields in one table is unusual.

Good luck with your project.

[Cookie]

Thank you Moke123.