Hey all. Here's my problem: I ha
Hey all. Here's my problem: I ha
(OP)
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-timestam...
Thanks for the help everyone.
-Trevor
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-timestam...
Thanks for the help everyone.
-Trevor
RE: Hey all. Here's my problem: I ha
Duane
Hook'D on Access
MS Access MVP
RE: Hey all. Here's my problem: I ha
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.
RE: Hey all. Here's my problem: I ha
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
RE: Hey all. Here's my problem: I ha
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-timestam...
Thanks so much for the help everyone.
RE: Hey all. Here's my problem: I ha
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:
CODE
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
RE: Hey all. Here's my problem: I ha
RE: Hey all. Here's my problem: I ha
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
RE: Hey all. Here's my problem: I ha
RE: Hey all. Here's my problem: I ha
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
RE: Hey all. Here's my problem: I ha
Thanks again for all your help.
RE: Hey all. Here's my problem: I ha
--Lilliabeth
RE: Hey all. Here's my problem: I ha
RE: Hey all. Here's my problem: I ha
--Lilliabeth