Solved Creating a tracking notes field in database. (1 Viewer)

JamieRhysEdwards

New member
Local time
Today, 22:00
Joined
Mar 26, 2022
Messages
27
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:

Code:
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

AWF VIP
Local time
Today, 14:00
Joined
Mar 9, 2014
Messages
5,423
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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 22:00
Joined
Jan 14, 2017
Messages
18,186
You could consider using the Column History feature of long text (memo) fields:

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

June7

AWF VIP
Local time
Today, 14:00
Joined
Mar 9, 2014
Messages
5,423
By using Column History, there would not be a separate field for UserID. User would have included that as part of their comment.
 

isladogs

MVP / VIP
Local time
Today, 22:00
Joined
Jan 14, 2017
Messages
18,186
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

AWF VIP
Local time
Today, 14:00
Joined
Mar 9, 2014
Messages
5,423
Yes, but not with each note. I presume multiple users can add notes to each Contact record.
 
Last edited:

strive4peace

AWF VIP
Local time
Today, 17:00
Joined
Apr 3, 2020
Messages
1,003
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):

 
Last edited:

SHANEMAC51

Active member
Local time
Tomorrow, 01:00
Joined
Jan 28, 2022
Messages
310
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
 
Last edited:

JamieRhysEdwards

New member
Local time
Today, 22:00
Joined
Mar 26, 2022
Messages
27
I've decided to implement the table, however, I'm getting the following error:

Code:
Run-time error '3134':

Syntax error in INSERT INTO Statement.

This is my statement:

Code:
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:

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

Minty

AWF VIP
Local time
Today, 22:00
Joined
Jul 26, 2013
Messages
10,355
Remove the single quotes around the UserID and Contact ID , they are numbers not text.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:00
Joined
Sep 21, 2011
Messages
14,047
Put all the sql into a string variable and debug.print it before using it. That should show you the errors.
 

June7

AWF VIP
Local time
Today, 14:00
Joined
Mar 9, 2014
Messages
5,423
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

New member
Local time
Today, 22:00
Joined
Mar 26, 2022
Messages
27
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:

Code:
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:

Code:
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

Enthusiastic Amateur
Local time
Today, 22:00
Joined
Sep 21, 2011
Messages
14,047
I cannot see anything wrong with that. :(

Code:
INSERT INTO TableContactNotes (ContactID, UserID, Note) Values(1, 3, 'Skimp')
Edit now I pasted I can, put a space after values
 

June7

AWF VIP
Local time
Today, 14:00
Joined
Mar 9, 2014
Messages
5,423
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

MVP / VIP
Local time
Today, 22:00
Joined
Jan 14, 2017
Messages
18,186
Just out of interest, try pasting this into a query in SQL view and then run it

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

SHANEMAC51

Active member
Local time
Tomorrow, 01:00
Joined
Jan 28, 2022
Messages
310
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

Enthusiastic Amateur
Local time
Today, 22:00
Joined
Sep 21, 2011
Messages
14,047
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

Active member
Local time
Tomorrow, 01:00
Joined
Jan 28, 2022
Messages
310
Code:
 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
 

Users who are viewing this thread

Top Bottom