Hey all. Here's my problem: I ha 2

[TrevorAaron]

Hey all. Here's my problem:

I have a medical patient database and I want to create memos for each patient. Right now I have a Patient Table with a Memo Field and have a Form to enter patient information as well as customized notes.

I've been reading on the internet that it's better to have a seperate Notes Table for all the notes that I'll be entering for each patient, including a time stamp and a created by stamp. How would I go about this?

Here's the quote and link of what I'm talking about:

"Instead, I'd really recommend having a separate Notes table related
one-to-many to your main table. It should have a timestamp field CommentTime,
with a default value of Now(); a CommentBy field which you set to CurrentUser
in the form's BeforeInsert event; and a field for the comment. You'ld have a
simple continuous subform on the main form, with only the comment field
enabled so you can see who and when but cannot edit those fields."

http://www.pcreview.co.uk/forums/datstamp-timestamp-user-stamp-memo-box-entry-t3189424.html

Thanks for the help everyone.

-Trevor

 

[dhookom]

This is a basic parent child relationship. The parent table (Patients) should have a primary key field. The value from this field is used in the child table (PatientNotes) in the foreign key field.

Duane
Hook'D on Access
MS Access MVP

 

[TrevorAaron]

Thanks Duane,

Your post will help me. Would you mind helping me out with another tip?

Here's what I have:

Patient Table
- PK is an automated ID number
- I can enter in a patients last name, and first name, and a lot of other information.

Memo Table
- PK is also an automated ID number (not sure if this should be FK from patient table?)
- I have a place where someone can make memo notes.

Am I supposed to go into the relationships table and link ID number from Patient Table to my Memo Table and that will be the foreign key? Then in the property sheet of the Memo Table am I supposed to adjust the "Link Child Fields" and "Link Master Fields"?

Thanks for the help and sorry for the rookie questions.

 

[Lilliabeth]

tblPatients
PatientID -pk
FirstName
LastName
etc

tblMemo
MemoID -pk
MemoField
PatientID (this is known as the Foreign Key)

In the Relationships window, join PatientID to PatientID

--Lilliabeth

 

[TrevorAaron]

Hey thanks Lilliabeth. I know this is a little risky but this is really helpful for me. I've successfully linked the two tables and understand the relationship.

Here's my new question:

The ID from "tblPatients" links seamlessly with ID from "tblMemo". So any notes made for Patient ID#1 go into "tblMemo" under the same ID#. This is great. But I realized I wanted to add a TimeStamp and a Comment Author for each Memo addition per patient. In other words, when someone opens a patient form in access I want to have a notes section where they can make ongoing notes. Everytime they enter new notes I want an auto-timestamp and auto-comment made by stamp. I'm fairly confident I can find coding in VB or some macro where I can implement the auto stamps but what I'm having trouble with is making this work with a seperate notes table.

So the way my "tblMemo" is set up now is such that Memo ID's are linked with Patient ID's but I was thinking that seperate notes would have their own ID with their own time stamp and comment author stamp. Does this make sense? Or am I overthinking what the solution is?


Here's the quote:
"Instead, I'd really recommend having a separate Notes table related
one-to-many to your main table. It should have a timestamp field CommentTime,
with a default value of Now(); a CommentBy field which you set to CurrentUser
in the form's BeforeInsert event; and a field for the comment. You'ld have a
simple continuous subform on the main form, with only the comment field
enabled so you can see who and when but cannot edit those fields."

Again here is where the idea came from:

http://www.pcreview.co.uk/forums/datstamp-timestamp-user-stamp-memo-box-entry-t3189424.html

Thanks so much for the help everyone.

 

[Lilliabeth]

Memo IDs are not linked to PatientIDs.
The memo table has 3 fields: MemoID, Memofield, and PatientID.

PatientID is linked to PatientID.

The primary key in the memo table is MemoID.

To automatically record who and when a record was created and last modified, you can add these fields to the memo table: CreatedBy, CreatedDate, LastModifiedBy, and LastModifiedDate, and then have Access fill them in automatically on the form.

I use something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
    txtCreatedDate.Value = Now()
    txtCreatedBy.Value = Trim(Environ("USERDOMAIN")) & "\" & Trim(Environ("USERNAME"))
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    txtLastModifiedDate.Value = Now()
    txtLastModifiedBy.Value = Trim(Environ("USERDOMAIN")) & "\" & Trim(Environ("USERNAME"))
   End Sub

--Lilliabeth

 

[TrevorAaron]

Hey Lilliabeth, thanks for clarifying. Your suggesstion really helped. I understand for the most part whats going on with your code. How do you have that set up on your form? Do you have a text box that shows who last updated the information? Could you upload an image of your form or another explanation? Thanks Lilliabeth.

 

[Lilliabeth]

Add the textbox controls for the fields just like you do other fields in your table.
Rename each textbox so they match the names referenced in the code... txtCreatedDate, txtCreatedBy, txtLastModifiedDate, txtLastModifiedBy.
Set tab stop property to No for these textboxes. I always set Back style and Border style to Transparent.


--Lilliabeth

 

[TrevorAaron]

Wow this is really cool! Lilliabeth thanks for the help. Do you know of any way to save these "LastModifiedBy" and "LastModifiedDate" changes so they become permanent? In other words when someone enters some more information in the Memo field and they create a time stamp and created by stamp is there a way to record those stamps? Any advice?

 

[Lilliabeth]

In the table, set the Append Only property of the Memo field to Yes. This won't record who made changes, but will record a running history of the contents of the field and when it was changed. To see the running history, right-click in the memo field for the record you're interested in, and choose Show Column History.

But keep in mind, every time a note is added, a new record is added to the Memos table. so "when someone enters some more information in the Memo field" they should usually create a new note, not a change an existing note.

--Lilliabeth

 

[TrevorAaron]

Cool Lilliabeth. I'll implement that suggestion. How do they create a new note in the form section? Right now I just have a memo field that they can "edit" in a sense by entering into the field and change it over and over again. Would you recommend creating a new form dedicated to entering in notes on each patient so that a new note is created each and every time? I think I have an idea of how to do that and what code to implement.

Thanks again for all your help.

 

[Lilliabeth]

This is a simple one-to-many relationship and requires a standard form with subform.

--Lilliabeth

 

[TrevorAaron]

Thanks Lilliabeth! I got it figured out. Sorry for the rookie questions. I really appreciate all the help.

 

[Lilliabeth]

No apology necessary. Glad to hear you figured it out. It is always better that way! Glad to have been helpful.

--Lilliabeth