Comments Box

[Connorbop]

Hello,

This is one that's been hard to search for as it only comes up with the use of a 'Memo' field - this is not what I want.

I need a comments history which allows me to add comments to a specific case and when another comment is added, it is moved to the top. Each comment is marked with the time/date stamp and the user that entered it. This is within the "Issues web database" sample db on Access 2010. So it works along the lines of:

1. Type a comment within a txtbox.
2. Click "Add Comment" button for comment to be displayed within the case.
3. Comment is saved with user, date and time.
4. Any other comment is also displayed but only when viewed in the correct case.

I believe this uses queries and reports, I tried to copy the sample database however it was next to impossible as I'm unable to troubleshoot when it goes wrong. Does anyone know what this is referred to or how I can create this?

Thank you in advance!

 

[Minty]

The correct way to do this would be with a comments table, containing a noteID (Primary Key, autonumber) ,CaseID(Foreign Key) ,employeeID, TimeStamp and comment field.

You then link these in a subform to display within the case In reverse order (Latest last). When a new note is added requery the sub form to display the new note at the top.

 

[Connorbop]

The correct way to do this would be with a comments table, containing a noteID (Primary Key, autonumber) ,CaseID(Foreign Key) ,employeeID, TimeStamp and comment field.

You then link these in a subform to display within the case In reverse order (Latest last). When a new note is added requery the sub form to display the new note at the top.

Thanks for the point in the right direction, this has been helpful and I will give this a go, however I have a couple of questions:

How do I pull the employeeID into a table when they add a comment?

How do I link comment numbers to case numbers?
e.g. How do I only display Case ID "1" within the Case ID "1" form? and then have Case ID "2" displayed on Case ID "2" and so on?

 

[Minty]

I would have a look through these videos http://www.access-programmers.co.uk/microsoft-access-tutorials/
They will assist you with all your questions. If you get stuck then please post back with what you have tried, any code used and any errors.

You basically need a sub-form for your comments. The wizards will certainly get you close to what you need. Design a form for the comments. Then use the sub form wizard as per the videos.

 

[Connorbop]

So I have had a go and have a working comments section as far as manually adding comments via the table. However I would like to have a txtbox with an "AddComment" button in order to add the data to the table.

My table "tblComments" contains:
CommID
CaseID
EmployeeID
DateTime
Comment

So now, from a typed comment within a txtbox I would like to extract the written comment to be input into "Comment", the employee's ID (which I have as a tempvar from the login page) to be input into "EmployeeID" and to save the "CaseID" depending on which case is open (users view and edit cases in a form).

I have no clue how to start, I've looked into it and read about DataMacros but how this is setup and used, I have no idea.

The help so far has been amazing, so thank you, I just need the final step now!

 

[Minty]

Are you using a Web Based Access App ? Data macros aren't usually used in standard access databases?

 

[Connorbop]

Are you using a Web Based Access App ? Data macros aren't usually used in standard access databases?

No I'm using a standard access database. I wasn't sure what a data marco was, it's just something I found when trying to research a solution.

 

[Minty]

Okay you just need to run an append query based on the data available on your form.
There are two ways to do this - the more straight forward one is to create the append query in the query designer, where you can use the builders to reference the form your data is on.
You can then use macro's attached to the button wizard to run the query for you.

This would probably be the route as a beginner I would use.

The second route would be to build the query in VBA and execute it within the form.
This is slightly more long winded, but would give you an insight into basic VBA programming which will make your Access application way more powerful in the long run.