Solved Creating a tracking notes field in database.

[JamieRhysEdwards]

Hi all,

Hopefully this is where this goes as it might not fit into a single chat area. In my current database, I have a notes field which is a Long Text in a contact table. However, this is what I would consider a dumb method in that it does not state who created the note, date and time and then the message. Instead, it just displays a message which can be deleted at any time.

So, I'm looking to have an area that allows the user to enter a note. Once they save the contact, this is then saved and in the contact form, there would be a subform which then displays all notes relating to this contact (which they won't be able to delete either as this is for auditing purposes).

Here's what I think how it should go so far:

1) The note should be in its own Table - Let's call it TableContactNotes. This table should consist of the following entities:

ID - AutoNumber - Replication ID - Primary Key
ContactID - Number - Lookup with TableContacts
UserID - Number - Lookup with TableUsers
Timestamp - Date/Time
Note - Long Text

As mentioned above when the user enters the note in the Contacts Note TextBox and then saves the Contact, I guess it would then be saved into TableContactNotes. I would then need to create a SubForm which then grabs the information for a specific Contact from that table and then display the information in a tab on the Contacts Form.

Would this be the most efficient way of doing this? This would also prevent deletion by those without access to the actual table correct?

Thanks

 

[June7]

Just displaying notes on a BOUND subform won't prevent deletion. Set form AllowDelete to No. If you use VBA to pull one Note record's data and populate UNBOUND controls, yes, record could not be deleted

 

[isladogs]

You could consider using the Column History feature of long text (memo) fields:

Column History in long text fields

This explains how to use the Column History property to store historical data in long text (memo) fields

www.isladogs.co.uk

This would fit your requirements very closely. However do consider the pitfalls as well (discussed in the same article)

 

[June7]

By using Column History, there would not be a separate field for UserID. User would have included that as part of their comment.

 

[isladogs]

Not sure I understand your point.
The OP states that the UserID will be stored as a separate field. Providing users log in to the database in some way, the UserID can be stored automatically.

 

[June7]

Yes, but not with each note. I presume multiple users can add notes to each Contact record.

 

[strive4peace]

hi JamieRhysEdwards

your proposed structure looks ok, basically ... but there will probably be multiple notes for each record and although Access can add date/time, it doesn't add attribution -- and it's hard to parse

Contacts and notes pertaining to records are common ... perhaps you might want to see how I chose to handle it? I made a video about that here (link to download datbases with source code in video description):

 

[SHANEMAC51]

I have a notes field which is a Long Text in a contact table. However, this is what I would consider a dumb method in that it does not state who created the note, date and time and then the message.

I would use a subordinate table to the fields
- note ID
- contact ID
- who created the note
- date and time
- message

and a continuous form for viewing/entering..

you can also enter it in the free fields on the contact form with further entry in the notes table

 

[JamieRhysEdwards]

I've decided to implement the table, however, I'm getting the following error:

Run-time error '3134':

Syntax error in INSERT INTO Statement.

This is my statement:

If (Me.TextNotes.Value <> "") Then
        CurrentDb.Execute "INSERT INTO TableContactNotes (ContactID, UserID, Note) Values('" & Me.ID.Value & "', '" & TempVars("CurrentUserID").Value & "', '" & Me.TextNotes & "')"
    End If

I'm not sure what I've done wrong to get the syntax error... I changed the structure of the table slightly:

ID - AutoNumber - Replication ID - Primary Key
Timestamp - Date/Time
ContactID - Number - Lookup with TableContacts
UserID - Number - Lookup with TableUsers
Note - Long Text

 

[Minty]

Remove the single quotes around the UserID and Contact ID , they are numbers not text.

 

[Gasman]

Put all the sql into a string variable and debug.print it before using it. That should show you the errors.

 

[June7]

Or instead use a bound form for the notes and make it a subform with Contacts on main form, then just use Notes form BeforeUpdate event to simply set value of UserID field:

Me!UserID = TempVars(CurrentUserID)

Or maybe instead set DefaultValue property of a textbox bound to UserID field to TempVars(CurrentUserID):

 

[JamieRhysEdwards]

Remove the single quotes around the UserID and Contact ID , they are numbers not text.

I tried that and it looks to be correct (not that I'm a sql expert in any stretch of the imagination.

This is what I get when I get when I remove the single quotes:

CurrentDb.Execute "INSERT INTO TableContactNotes (ContactID, UserID, Note) Values(" & Me.ID.Value & ", " & TempVars("CurrentUserID").Value & ", '" & Me.TextNotes & "')"

copy it and print it as suggested by @Gasman:

INSERT INTO TableContactNotes (ContactID, UserID, Note) Values(1, 3, 'Skimp')

However, it still fails with the same error.

Or instead use a bound form for the notes and make it a subform with Contacts on main form, then just use Notes form BeforeUpdate event to simply set value of UserID field:

Me!UserID = TempVars(CurrentUserID)

Or maybe instead set DefaultValue property of a textbox bound to UserID field to TempVars(CurrentUserID):

This sounds a little more complicated than I can do at this stage.... Plus this doesn't need anything too overly fancy.

 

[Gasman]

I cannot see anything wrong with that.

INSERT INTO TableContactNotes (ContactID, UserID, Note) Values(1, 3, 'Skimp')

Edit now I pasted I can, put a space after values

 

[June7]

Space after Values is not necessary and won't make a difference.

Also don't see anything wrong with the SQL. But since it fails, why not try the alternatives?

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[isladogs]

Just out of interest, try pasting this into a query in SQL view and then run it

INSERT INTO TableContactNotes (ContactID, UserID, Note) VALUES (1, 3, 'Skimp');

 

[SHANEMAC51]

ContactID, UserID

maybe the coupling of these fields is declared in the tables as unique, because the author did not post an example of a comment table and/or there is a quotation mark/apostrophe in the comment text

 

[Gasman]

maybe the coupling of these fields is declared in the tables as unique, because the author did not post an example of a comment table and/or there is a quotation mark/apostrophe in the comment text

That would not gemerate a syntax error though, would it?

 

[SHANEMAC51]

 s1="INSERT INTO TableContactNotes (ContactID, UserID, [Note])
s1=s1 & " Values(" & Me.ID.Value
s1=s1 & ", " & TempVars("CurrentUserID").Value
s1=s1 & ", '" & Me.TextNotes & "')"
debug.print s1
CurrentDb.Execute s1

and/or there is a quotation mark/apostrophe in the comment text
it is possible that the [note] is a reserved word

 

[isladogs]

Note isn't a reserved word

SQL Reserved Words - Microsoft Support

support.microsoft.com