time stamped notes 1

[officemanager2]

Access beginner

I am trying to develop a text box that will retain the notes with a time stamp once the field has something put into it.

Ideally the list of notes, stretching back however far, would be visible on the same form (perhaps in a different note) when new information is being added.

Any assistance is appreciated.

 

[Ed2020]

You should store the notes in a separate table, which is joined to your main table on a common field. This is because each record in your current table can have multiple notes related to it.

Within your second table you should have fields something like this:

NoteID (primary key, autonumber field)
ParentRecordID (stores the primary key of the corresponding record from your current table - this is what you join on).
DateTimeStamp (a date field with the default value set to Now() - this will automatically store the date and time each note record was created).

You could then display this information on your current form in a sub-form.

If this doesn't make sense I can mock up an example for you.

Ed Metcalfe

Please do not feed the trolls.....

 

[officemanager2]

Hi Ed: I guess I need the mock up. I created the table as you suggested and I believe the relationship was also created successfully but I have missed a step or not done something because the notes are not transferring over.

Perhaps the issue is the relationship which I don't have the best grasp upon. In the main table I've used the primary key, which I called 'ID' (datatype=autonumber), and created a relationship with the 'ParentRecoredID' (datatype=text).

thanks

 

[Ed2020]

ParentRecordID in your Notes table needs be a number field and set to Long Integer.

Which version of Access are you using? I'll need to create you an example in the same version...

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

This is a very basic example:

http://rapidshare.com/files/366836248/Notes_Example.mdb.html

If you look at the data properties of the subform control on frmMain you will see a Link Child Fields property - this is how the form syncs the main form and the Notes subform, and ensures your foreign key (tblNotes.ParentRecordID) is populated correctly.

HTH,

Ed Metcalfe.

Please do not feed the trolls.....

 

[officemanager2]

I'm using 2003

 

[Ed2020]

Lucky guess on my part then - the example I posted is 2002/2003.

Ed Metcalfe.

Please do not feed the trolls.....

 

[officemanager2]

Hi Ed: I'm trying what you have sent over, but the 'new' notes I type in don't enter? They remain in Field 1,2, or 3 but do not go into the subform. I've tried to enter, tab, or start a new record. I can see your notes, but cannot enter any on my side.

 

[officemanager2]

Hi Ed: I believe I have things close to working. The final issue is that each new note over rides the previous one. The number of notes will still increase but the most recent note over rides the other ones. The time stamp remains the same though. I'll keep working on it.

thanks

 

[Ed2020]

Aha, I think there is a bit of a misunderstanding.

In my example Field1, Field2 and Field3 are just dummy fields that I put in my main table because I don't know what your main table contains (i.e. what the notes are actually about). They are not supposed to be for entering notes into.

The notes can be entered directly into the sub-form.

As an example, if this was a system for a doctor's surgery Field1, Field2, and Field3 (in tblMain) could be PatientName, PatientDateOfBirth and PatientGender. These details would be filled in once per patient (unless somebody has a sex change I suppose!).

The sub-form could be used for capturing notes after a patient has attended an appointment. As each patient can have multiple appointments this is entered into a datasheet view sub-form and stored in a separate table. To enter a new note just start typing in the blank row at the bottom of the sub-form (the one with the asterisk next to it).

BTW - frmMain is the one you want to open. sfrmNotes would probably never be opened directly.

Does that make more sense?

Ed Metcalfe.

Please do not feed the trolls.....

 

[officemanager2]

Hi Ed: It's all making much more sense! Thanks for your help. I'm still finding that what has been created is not quite up to the task though. I still cannot figure out why each new note over writes the last note, but the time stamp remains the same. Any suggestions?

Also, given our conversation to date do you have any recommendations for books or other sites to learn access. I initially learned the basics some years ago but never kept up with it. Now I find I'm having to get up to speed quickly so any suggestions would be appreciated.

thanks

 

[officemanager2]

Hi again Ed. Thought I would sneak this in under my last post in case you have some more time.

Ideally the most recent time stamped note would be at the top of all the other notes, currently all new notes show up at the bottom. Using the sort on the time stamp field I can get the latest note to the top but inputting the new note happens at the bottom. It may seem like a small detail but I'm trying to make this as easy as possible for the end users, some of which rarely use a computer, or have had little to no exposure to computers.

The current layout has a field on the frmMain to input notes. This way the end user would create the new note, and then when saving it the note would be transferred over to the subform which can be viewed on the main form. This will enable the end user to glance once at the notes and see what was said last. I think it would be best to have a 'save' button on the main form as opposed to a 'new' button on the subform.

Some notes may go several lines, but the current capture does not increase as the note increases. I've looked into this and it appears to me that the properties window for the specific items should be able to allow this but to date I've not had success in increasing the size of the field of view as the note increases in size (not the data type which I have set to Memo), for example a note that is four sentences long is awkward to read.

Finally I'm trying to get more out of the properties boxes. I found once I created the subform it was difficult to alter it. Not the actually look which is easy to do in design view but the guts of the thing that allow it to perform. I've figure out a few things through trial and error and it strikes me that most alterations could be performed in the properties box instead of deleting and starting the thing over, but to date my adventures in the properties box have been mixed at best.

I hope you or no one on the forum minds this direction of the thread which is a lot of open questions.

thanks

 

[Ed2020]

When you say each new note overwrites the last note, do you mean in my example database or in yours? I have not managed to replicate this behaviour in mine... If it's only occurring in your database I'd need to have a look at it to find out why that's happening.

In terms of Access books I'd recommend the Access Developers' Handbook by Paul Litwin, Ken Getz and Mike Gilbert. I'd also strongle recommend reading up on database normalisation first - it is vital that anyone designing relational databases understands this properly.

If I were you I'd set the sub-form as read only and set the sort order to place the most recent notes at the top. I'd then create a new command button on the main form "Add Note" which opens a new pop-up form to create a new note record.

Ed Metcalfe.

Please do not feed the trolls.....

 

[officemanager2]

Hi Ed: Your last line is exactly what I'd like to do but I am not having any luck with it.

The reason for the over write is because I had set things up in the subform Control Source with the following '=Forms![Mailing List]!Notes'

I've dropped the 'Notes' field on the main from and am just using the subform for notes, but I think something like you have discussed is far better. I'm trying to figure out how to set the subform as read only but cannot find this button.

Hopefully this link

http://rapidshare.com/files/367663785/SEGO_contact.mdb.html

will allow you to see what I've done (which doesn't seem like much, basically I've attempted to replicate what you put together).

 

[Ed2020]

I'll take a look this evening to see what else needs doing. It sounds like you've got the majority of it working perfectly, so there shouldn't be much more to do.

I'll upload a new version later today.

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Hi,

Sorry it's taken me a little longer than I expected to take a look at this.

There's a new version available here:

http://rapidshare.com/files/368527475/SEGO_contact.mdb.html

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

How are ya officemanager2 . . .

[blue]Ideally the most recent time stamped note would be at the top of all the other notes, currently all new notes show up at the bottom. Using the sort on the time stamp field [purple]I can get the latest note to the top but inputting the new note happens at the bottom.[/purple][/blue]

See my post here thread702-1309854 which has to do with [blue]Putting the Form Input Line at the Top![/blue] ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Ed2020]

Nice suggestion! I hadn't thought of that. Have a star!

Ed Metcalfe.

Please do not feed the trolls.....

 

[TheAceMan1]

officemanager2 . . .

Please post your table structure in your next post:
[ol][li]PrimaryKeyNames & Datatype[/li]
[li]ForeignKeyNames & Datatype[/li]
[li]OtherFieldNames & Datatype[/li]
[li]Any Relationships between tables[/li][/ol]
We really need to see what you have.



See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[officemanager2]

Hi Ed: Sorry about dropping off the there, a few things came up that needed to be dealt with. The book you recommended arrived the other day and it is THICK. Should I start from page one or are there chapters that you recommend?

Now that this thing is coming together the next step will be to figure out how to network it to three other computers.

Aceman: What is the best way to post the information you are asking for? Ed and I have just exchanged the database as a whole file. I'm open to what ever is most efficient.

thanks