“Access Memo Field Append Only” function

Question

Hi, I’m in the process of trying to put together a multi user Dbase for a community organisation I’m volunteering with. We work with a range of individuals and we need to record our contacts with clients.

In the program I intend (?) to use memo/txt fields to collect information, to display the column history of each contact, to do this I’ve used the “Access Memo Field Append Only” function. It works okay so far. (I’m open to suggestions if there’s a simpler way.)

My issues is, when the History info is displayed it appears as; “Version: Date/Time Group. Blah Blah Blah”.

So I can show which volunteer made the note on the record is there a way of changing “Version” to reflect the Volunteers User ID?

I’m no wizard at this in fact, it’s a little like the blind guiding the vision impaired, so be as non-tech in your answers as you can please… TIA

Answer 1

Hi. I am also a bit disadvantaged when it comes to that feature because I only use it in SharePoint and not in Access. But since it's a built-in feature, I am not really sure that you can change its behavior. The closest I could see happening to what you want is only to modify what's displayed to the user, but you'll have to add a way to tag the comment with the user's ID yourself. Just a thought...

Answer 2

Tom,

Thankyou, I had considered that but, like in life I've been having trouble getting "relationships" to work for me. I know it's got to be something simple but I can't see what it is I'm doing wrong...LOL

James,

What problem are you having with relationships? This is not difficult. You have a clients table. That table has a ClientID as a Primary Key. Then you have another table:

tblClientcontacts

ClientContactID (PK Autonumber)

ClientID (Foreign Key)

ContactDate

ContactBy

ContactNotes

Next you create a subform bound to tblClientcontacts and embed it on your Contacts form linked on ClientID.

From there you add a new record with a note using the subform . The subform will display your history of notes for the client.

Answer 3

You just need a separate table which includes a foreign key column referencing the primary key of your current table.  The table should include columns for the current user name, the date/time when the row is inserted, as well as a column for the note. You then open a form bound to the new table and open it in dialogue mode from a button on the main form.  You'll find an example of a form opened in dialogue mode this way in FormsDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the value of the primary key, ClientID, is passed to the second form via the OpenArgs mechanism with:

    Const FORMNAME = "frmEvents"

    Const MESSAGETEXT = "No current client record."

    Dim strCriteria As String

    strCriteria = "ClientID = " & Me.ClientID

    ' be sure a record has been entered

    If Not IsNull(Me.ClientID) Then

        ' save current client record

        Me.Dirty = False

        ' open linked form in dialogue mode

        DoCmd.OpenForm FORMNAME, _

            WhereCondition:=strCriteria, _

            WindowMode:=acDialog, _

            OpenArgs:=(Me.ClientID)

    Else

         MsgBox MESSAGETEXT, vbExclamation, "Invalid Operation"

    End If

and in the frmEvents form's open event is assigned as the DefaultValue property of a control bound to the foreign key ClientID column in the Events table to which the second form is bound, with the following code:

    If Not IsNull(Me.OpenArgs) Then

        Me.Event.SetFocus

        Me.cboClient.DefaultValue = """" & Me.OpenArgs & """"

   End If

For an example of how to insert the current user's Windows login name and the current date time into a record see the ChangedRecordDemo file in the same OneDrive folder.

Answer 4

Tom,

Thankyou, I had considered that but, like in life I've been having trouble getting "relationships" to work for me. I know it's got to be something simple but I can't see what it is I'm doing wrong...LOL

Answer 5

Generally it is a bad idea to have one memo field to enter notes into over time.

Rather use a table with several fields, including EmployeeID, ContactDate, Notes, and maybe other fields such as Status or FollowUpDate. Each interaction would create a new record.