Comments Box!

[Connorbop]

Hello,
I am learning how to use VBA after playing with macros for a while. A brief description for what I'm attempting to do: I have a case management system which needs a comment box to show historic actions from various employees on different cases. Each comment needs to ONLY display on the relevant case alongside the commenting employees name and the date/time stamp.

EG:
Joe Smith 30/01/2017 12:00:00
Thank you for your update John.

John Smith 30/01/2017 11:57:12
This is a comment for case 1.

BUT it is very important that case 1 only displays case 1 comments, case 2 only displays case 2 comments and so on...

So far I have had a go at trying to do this, you'll have to excuse my poor attempt, I pretty much have no idea what I am doing! Below is some code on my add comment button.

Private Sub CM_cmdAddComment_Click()
    If IsNull(Me.CM_txtComment) Then
    MsgBox "Please enter a comment in the box provided", vbExclamation, "Error 011"
        Else
        DoCmd.SetParameter "prmCaseReference", CaseID
        DoCmd.SetParameter "prmComment", CM_txtComment
        DoCmd.SetParameter "prmOfficerReference", Forms!Homepage.HP_txtCurrentID
        DoCmd.RunDataMacro "tblComments.AddComment"
    End If
End Sub

This doesn't work at all, I get an error when it tried to set the textbox content as a parameter... Can anyone advise how I go about doing what I need to do?

 

[jdraw]

Please show us your tables and relationships (jpg).

 

[Connorbop]

See attached. I know they're probably laughable, I'm just guessing as I go along..

 

[jdraw]

You have Officers and Cases and Comments, but we haven't heard how these are related.
For instance, (and these may not be true--adjust as required)

All Cases are created/recorded by 1 or more Officers
An Officer can create/record 1 or more Cases
Each Case has 1 or more Comments

We really need a statement/description of WHAT the business is about.
Even a description of Case would be helpful.

Do you have any Officers that are not related to any Case?
Can a Case be created/recorded by more than 1 Officer?
Can Comments be made by any Officer?

 

[static]

To add a new record use INSERT.

Private Sub CM_cmdAddComment_Click()
    If IsNull(Me.CM_txtComment) Then
        MsgBox "Please enter a comment in the box provided", vbExclamation, "Error 011"
    Else
        currentdb.execute "insert into tblComments (casereference,officerreference,record,comment) values ('" & _
			CaseID & "','" & CM_txtComment & "','" & Forms!Homepage.HP_txtCurrentID & "')"
    End If
End Sub

 

[Connorbop]

The database is related to property and estates management and the cases are for any properties/estates that are flagged to us for a variety of reasons. Anyone can change the information within a case and each case is assigned to an officer as a sort of "officer in charge".

When a case is opened in the Case Manager form you're able to see all the information regarding a case, what I am wanting to add is a "History" tab which enables officers to make a comment on the case and leaving an audit trail, with the latest comment at the top. For example:


Officer Joe 12:00:00
I have spoken to Property Services to have the below actioned.

Officer Tim 11:50:45
Received an email from Corporate Estates to confirm we are responsible for the maintenance of the land.

Officer Jane 10:00:00
Are we required to maintain this property?


However I need comments to be displayed ONLY on that case. So if I have CaseID 1 open in the Case Manager it displays the comments made on that case, if I move to CaseID 2 it no longer displays the CaseID 1 comments and only displays comments relevant to CaseID 2.

It's hard to explain, but I think this will help you understand what I'm trying to do?

 

[jdraw]

I think this draft model covers the relationships you have mentioned.

I think you want Date and Time on the Comment, not just the time as indicated in your example.