UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> column history    
 
   
coconut
post Feb 18 2010, 09:54 AM
Post #1

New Member
Posts: 9



I have a form appointment display which i would like a feild to show the changes to the memo field.
I have set the memo field in table to append yes. this code =ColumnHistory([Appointments],"PatientNotes","[PatientID]=" & Nz([PatientID],0)) i have tried and no luck in this instance. I have been pointed to DoCmd.acCmdShowColumnHistory but i cannot seem to het this to work. Any thoughts?
Go to the top of the page
 
+
robcooper
post Feb 18 2010, 11:48 PM
Post #2

Microsoft Corp UtterAccess VIP
Posts: 1,839
From: Bellevue, WA



Hi,

Welcome to UtterAccess! (IMG:http://www.utteraccess.com/forum/style_emoticons/default/smile.gif)

The first argument to the ColumnHistory function is a string. Can you try:

CODE
=ColumnHistory("Appointments", "PatientNotes", "PatientID=" & Nz(PatientID,0))


Hope this helps,
Go to the top of the page
 
+
coconut
post Feb 19 2010, 10:29 PM
Post #3

New Member
Posts: 9



#Error
Go to the top of the page
 
+
robcooper
post Feb 20 2010, 04:39 PM
Post #4

Microsoft Corp UtterAccess VIP
Posts: 1,839
From: Bellevue, WA



Can you attach the database where this occurs, or a subset of the database as a sample?
Go to the top of the page
 
+
coconut
post Feb 20 2010, 11:17 PM
Post #5

New Member
Posts: 9



frmPatients is the main form. i would like a text feild on appointment display form to show the patient notes history.

Thanks
Attached File(s)
Attached File  UtterAccess.zip ( 189.14K ) Number of downloads: 25
 
Go to the top of the page
 
+
robcooper
post Feb 21 2010, 02:12 PM
Post #6

Microsoft Corp UtterAccess VIP
Posts: 1,839
From: Bellevue, WA



Thanks for the database. Are you storing multiple Appointment records per patient and trying to see history for multiple records in the AppointmentNotes field or a single record?

An AppendOnly memo field is a multiple-value field meaning that there are multiple records stored in the single field. When you change the value of an AppendOnly field, Access 2007 adds a new record to the underlying table that stores the multiple-value data. ColumnHistory therefore shows all the changes made to this field for a single Appointment record. It will not show history across multiple Appointment records.

What this ultimately means is that the ColumnHistory function needs a unique record to be returned in the queryString argument. In other words, in a one-to-many relationship between Patients->Appointments, if you have PatientID=1 in the Appointments table more once, the ColumnHistory function will fail if the queryString returns more than one record. If you are changing the value of the AppointmentNotes field for a single Appointment records and want to see the column history, you'll need to make sure that the record returned by the queryString argument is unique. The best way to do this is to use the Primary Key:

=ColumnHistory("Appointments", "AppointmentNotes", "AppointmentID=" & [AppointmentID])

If the intent is to view all AppointmentNotes for Appointments for a selected PatientID, you'll need to write your own function using a Recordset.

On a related note, I opened this database in Access 2010 and didn't see any relationships. Is this just a sample? Are there relationships in your copy of the database?

Thanks,
Go to the top of the page
 
+
coconut
post Feb 21 2010, 08:33 PM
Post #7

New Member
Posts: 9



Rob, there is relationships. Relationship report attached. I would like to click on appointments tab and have a text field show the changes. With That said, I have noticed that it works on a bound form, but not an unbound form. I use dlookup to gather information to populate form.

Thanks
Attached File(s)
Attached File  relationships.zip ( 37.1K ) Number of downloads: 17
 
Go to the top of the page
 
+
robcooper
post Feb 22 2010, 01:15 AM
Post #8

Microsoft Corp UtterAccess VIP
Posts: 1,839
From: Bellevue, WA



Thanks for the additional information. Can you clarify whether you're trying to view the AppointmentNotes from a single Appointments record or multiple records for a given PatientID? If it's for multiple records you won't be able to use the ColumnHistory function in this way.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 17th April 2014 - 02:47 PM