Comment History

[Pauly78]

I apologise if this is covered elsewhere, but i cant seem to find anything on this issue (maybe because im not sure what im looking for - im a complete novice!)

Im attempting to create a customer database for my customers which also keeps a record of when my customers contact me. This feature is implemented on the access 2013 customer call centre desktop template, which ive trid o copy to my own without success.

What i would like is a comments box where i can type a brief note of any conversation with my customer, then say press enter, te comments appears in another box with a time and date stamp. The first box becomes empty ready for the next comment etc etc

I currently have a field on my tables called 'Comments' but im not sure if i need another for the history - i cant find one on the sample database, and the ode ive used from there is this

=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

But i just get an error on form view.

Ive attached an image to show what i mean.
Can anyone help? or suggest anther way i can do this?

Thanks in advance

 

[CJ_London]

I think you are mixing up the new history feature for memo fields with what you are trying to do.

Take a look at this link

http://www.fmsinc.com/MicrosoftAccess/2007/ColumnHistory/Index.asp

Using this method, you are recording all the edits to a single memo field, but only showing the latest one - or view the history to see corrections to typo's, meaning etc.

but you want to do this

What i would like is a comments box where i can type a brief note of any conversation with my customer

Using the above method, you edit the previous conversation to make it the latest conversation - is that what you really want?

I think your comments should be stored in a separate table with a separate record for each conversation and then your comments part of the form would be a subform based on that table.

 

[Pauly78]

i have created another table called History as i thought this would be better organisaion, but im baffled with the whole table relationships thing. Ive got a field named Comments in my main table but i dont understand how it works to make what i type into get stored in the history table

 

[Pauly78]

Ive tried following the instructions on the link you have given me and it still wont work, i works if i press ctrl + G and type in the code and press enter, but it wont work fo me to put it on the form, i just get the error, its so frustrating! lol

 

[CJ_London]

you would have a table called comments, typically with the following fields

CommentPK autonumber
CustomerFK long (links to customer table PK, set it up in the relationships window)
Comment memo
TimeStamp date/time default now()

In addition, you may want these fields as well. Note long/text means one or the other - which one depends on how your db is set up

User long/text - if you want to know who made the comment
NextAction - long/text - if you want to set up an action to be undertaken as a result of the comment
NextActionBy date - if using next action, when this needs to be completed by
NextActionUser long/text - if using next action, who is to take the next action

You do not need a field called comments in your customer table

Create a form from this table and use it as a subform on your customer form and place it in replacement of your existing comment and history controls

 

[Pauly78]

Thanks ill try this.... what does FK and PK mean?

 

[bob fitz]

Thanks ill try this.... what does FK and PK mean?

Perhaps this will help: http://docs.embarcadero.com/product...ted_Part_II/Primary_Keys_and_Foreign_Keys.htm

 

[Pauly78]

am i able to place the subform in my main form and have it only show history records for the current customer in the main form only? because right now it shows up but i can scroll through all other customers history enries too, i only want to be able to look through history for that one particular record?

 

[CJ_London]

you need to populate your linkchild and linkmaster properties for the subform control

 

[Pauly78]

This is getting very complicated! :/

 

[CJ_London]

They are not that complicated! here are some links to explain about subforms

https://support.office.com/en-gb/ar...835-aa3d-cd62281cb85d&ui=en-US&rs=en-GB&ad=GB

https://www.youtube.com/watch?v=NQm_fNSwk9o

Or google 'access subforms' to find plenty of other links

 

[Pauly78]

yay, ive done it! Thanks for your help.