Column History


Hi

I am trying to replicate the column history function seen in the "issues database template" and am struggling some what. When looking at what I could online the rules seem pretty simple. Simply add a comments field to your table ensureing it is a Memo Type. Then make Append Only - Yes. Place the comments field in your form then add a blank text box to your form in design view insert into the form the following code - =ColumnHistory("RecordSource","Column","[ID]=" & Nz([ID],0)) - I have done this and it doesn't return the Comments column history it gives me a #Error. I've been at this for a while now and am really stuck can someone please help.

Please bare in mind that I am a bit of a novice with access and am self taught so far.

Thank you in advance.


Sponsored Links:



Hi
Firstly i am totally thick - my company have asked me to do this programming data base on Access and i am totally lost - a whizz at Excel but just not grasping Access at all to be honest.
I need to date stamp comments that are input on a form - i think the formula i have to use is this 'Column History'

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

But on using it, it just returns a #type!

Does anyone now where i find the solution to this? Its way over my head - i'll even pay for the answer or make a donations - my head is just totally battered by all of this . I'm not very jargony either so as simple as possible explanations please.
If anyone is willing to help me i would be ever so grateful,
Hope some one can help a thick geordie damsel in distress.
Thanks
Deborah




Hello all,

I have researched this problem exhaustively and can't for the life of me find an answer, but I'm sure it's out there somewhere . I have a form linked to a table, and the table field "Most Recent Update" is a memo field with Append Only turned on. I would like the history to be displayed in another field, "Update History." The form is linked so that only one record is displayed at a time, and I would like the "Update History" to show the history for ONLY the current record.

At the moment, I have this code:

	Code:
	 
Private Sub Update_History_Click()
    Dim sHistory As String
    sHistory = Application.ColumnHistory("Table", "[Most Recent Update]", "Table![Item Number] = " &
Forms!Edit.CurrentRecord)
    Forms!Edit.Controls![Update History].Value = sHistory
End Sub

When I click on the "Update History" field (a text box), I get the error "Method 'Column History' of object '_Application' failed." What am I doing wrong?

I hope that makes sense; I am not very experienced with Access and am exclusively self-taught (so far). Any and all advice is appreciated; thank you in advance!




Dear Experts,
I want to populate the historyof one column into another. I refered the posts in the forum and done exactly the same.
created a filed comments with memo type and changed the append to property to yes.
IN the HIstory Text Field given the command =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0))

buth when i try to run the fomr I am getting the message #Name?

Pleas help on how to correct it.

Thanks




Hi,

Can anyone explain (simply) the ColumnHistory functionality ?

I've seen it in a couple of MS Sample databases (Lending Library for example) but I can't replicate it in any of my databses.

It involves typing some text in a text box and then this and any other historical text is shown in a text box below.

I can see that it uses some kind of Record Source (not my strongest area). The folllwing appears in the historial text box;

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

Surely there is some code associated with the above but I can't find anything anywhere.

Any help or advice gratefully received.

Thanks.




I have an Access 2010 database for outstanding issues to keep track of. On the form used to navigate through this data, I would like to have a text box that displays the column history for a different field, "Most Recent Comment." (Yes, I realize that I'm a horrible person because I put spaces in the field name. ) I'm having trouble creating code that does this; specifically, showing history for the record that is currently being displayed.

I currently have the following code as a user-defined function:

	Code:
	 
Public Function GetHistory(rowID As Integer) As String
On Error Resume Next
Dim sHistory As String
sHistory = Application.ColumnHistory("Table", "[Most Recent Update]", "ID=" + CStr(rowID))
GetHistory = sHistory
End Function

And attempt to call it by putting the following as the control source for the text box:

	Code:
	 
=GetHistory([rowID])

Any help would be greatly appreciated!




Hi all,
I've got an inventory DB in access 2010 where i track our assets. In my asset table there's a "comments" field (memo type) with the comment history. I got this from the northwind assets db or the access built in inventory db, don't recall which one. Anyway, the comment history has been awesome but i need to search the history and i can't. For example 1 asset in the history, if i view the history, i can see was first assigned to jsmith but when i search for "smith" it doesn't appear because it's in the comment history. Any suggestions would be greatly appreciated!




I'm very new to access, mostly self taught through modifying templates and getting the results I want through lots of trial and error.

I've created an access report to basically mimic a DA Form (Deparment of the Army) that records equipment inspection dates, who did the inspection, and when the next inspection is due. It also has a lot of other not important info, for the sake of my question here.

I'm using the Customer Service template for my shop needs, and have modified it quite a bit to collect all the data I need and store it as needed.

Now in order to get my access Report to fully work as an electronic format for what we essentially already do by hand on paper, I need it to be able to pull previously entered data in a table field and continue to update up to 40 new entries of data in the same table field.

It basically looks like this:

Date Updated | Person's Initials | Date Next Due
07/15/2009....|.........TG..........|..01/14/2010..




Hi,

I am an inexperienced ACCESS user. I am trying to create a query that pulls the column history, including the date/time stamp that ACCESS applies to the historical entries in the Comments column in the Issues table in the issues database in ACCESS 2007's Featured Online Templates.

I tried just leaving the criteria blank for the Comments column in query design and then selecting only the record ID; however, the query only returns the most recent entry in the Comments column. I also tried using the control syntax that ACCESS 2007 uses in the Issue Details form: =ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([ID],0)); however, when I execute the query, it prompts me for the "Record Source" to which I enter "Issues", and then the query result set is blank.

Is there any way to create a query that, when executed, returns the complete column history for the Comments column, or is the ColumnHIstory function limited to forms and reports? Thank you.




Hi,

I was using a Append Only Memo Field in Access 2007 to show Column History for "Comments".

However, I have now converted the back-end to SQL Server 2008 whilst the Front-end is still Access 2007. Since I have done this, the field that shows the column history for "Comments" does not work anymore, it shows "#Error"

This is the control source used in the form:

	Code:
	=ColumnHistory([RecordSource],"Comments","[Asset_No]=""" & Nz([Asset_No],0) & """")

It was working before the conversion.

The column "Comments" is set to nvarchar(MAX) in SQL Server.

I'm not really sure what needs to be done, I would appreciate all the help.

This has happened to all the columnhistory fields.

Thanks




Hi,

I was using a Append Only Memo Field in Access 2007 to show Column History for "Comments".

However, I have now converted the back-end to SQL Server 2008 whilst the Front-end is still Access 2007. Since I have done this, the field that shows the column history for "Comments" does not work anymore, it shows "#Error"

This is the control source used in the form:
Code: =ColumnHistory([RecordSource],"Comments","[Asset_No]=""" & Nz([Asset_No],0) & """") It was working before the conversion.

The column "Comments" is set to nvarchar(MAX) in SQL Server.

I'm not really sure what needs to be done, I would appreciate all the help.

This has happened to all the columnhistory fields.

Thanks




VBA - unique business rule
Double click an item in a list box to remove it. But, keep a history of additions and deletions for later reports.

A disconnected listbox hold values. The listbox is filtered from ID_NEPA when other controls on the form are selected.
The listbox only shows Activity = A (active)

A double-click on a listbox item identifies the ID, then updates the Activity to an I (inactive) and also time-stamps the Deleted field.




	Code:
	Private Sub lstWellsAssociated_DblClick(Cancel As Integer)
      ' A double click here will appear to remove an item from the listbox
      Dim dbNEPA_Well                 As DAO.Database
      Dim rstNEPA_WellTable           As DAO.Recordset
      Dim SQLNEPA_WellTable           As String     '
      Dim NEPA_WellTableName          As String
      Dim NEPA_WellSelectedItem       As Integer
10    On Error GoTo Error_DeleteToNEPA_Well
20    NEPA_WellSelectedItem = Me.lstWellsAssociated.Column(0) ' get ID_NEPA_Wells  from double click
 
30        NEPA_WellTableName = "NEPA_Wells"
40        SQLNEPA_WellTable = "SELECT " & NEPA_WellTableName & ".* FROM " & NEPA_WellTableName & ";"
 
          '  Open the disconnected table
50        Set dbNEPA_Well = CurrentDb()
60        Set rstNEPA_WellTable = dbNEPA_Well.OpenRecordset(SQLNEPA_WellTable, dbOpenDynaset)
          '   Note: To Delete - change A to I and insert date in Deleted Column
70        rstNEPA_WellTable.FindFirst ("[ID_NEPA_Wells] =" & NEPA_WellSelectedItem)
          ' Find selected double click
80        rstNEPA_WellTable.Edit
90            rstNEPA_WellTable!Activity = "I"        ' A turns to I for inactive - only A shows in list
100           rstNEPA_WellTable!Deleted = Now()
110       rstNEPA_WellTable.Update
 
          '     Close the NEPA_Well database
120       rstNEPA_WellTable.Close
130       Set rstNEPA_WellTable = Nothing
140       Set dbNEPA_Well = Nothing
 
150       Me.lstWellsAssociated.Requery
160       Me.lstWellsByArea.Requery ' an associated list box
 
180       Exit Sub
Error_DeleteToNEPA_Well:
190       Err.Clear
200       Set rstNEPA_WellTable = Nothing
210       Set dbNEPA_Well = Nothing





I am developing an Access database to keep track of recurring tasks. When a task is completed, a date is entered and a button clicked. The code then has the record copied to a history table, then resets the start and due dates based on the frequency. I am getting an error when I run the code. The message just says "Error", so I am not sure what's wrong. Below is the code for the button. I realize it's rather complex, so if anyone is willing to help and could PM me, I would be glad to provide a link where you can download my project.

I really appreciate the help. Below is the code:

Private Sub Button96_Click()
'1. The record is copied to the "History" table for historical records.
'2. The Current Due Date changes to the next due date based on the recurring requirement specified
' (i.e. weekly, monthly, etc.), or if it is "one time", it deletes the record.
'3. If recurring, the Completed Date field goes blank (because it is reset).

On Error GoTo Button69_Err

Dim MyStr As String
Dim intErrNum As Integer


' Save the record first, by going to the next record, and then back to the current one.
' This is OK even if there is no next record, since it will go to a new record

DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious

If IsNull([completion date].Value) = True Then
MsgBox "The completion date is empty", vbExclamation, "Need completion date"
[completion date].SetFocus
Exit Sub
End If

If IsNull(Combo116.Column(0)) = True Then
MsgBox "Don't have a frequency field", vbExclamation, "Need frequency info"
Combo116.Dropdown
Exit Sub
End If


MyStr = "INSERT INTO History ( [OLD_ID#], [SUPERINTENDENT], [TASK DESCRIPTION], [REQUESTED_BY], [DUE DATE], [START DATE], " & _
"[COMPLETE DATE], [STATUS_COMMENTS], [COMMITTMENT], [GROUP], [INDIVIDUAL], [FREQUENCY] ) " & _
"SELECT " & Str([TASK NUMBER].Value) & " AS Expr1, " & Str([SUPERINTENDENT].Value) & " AS Expr2, " & _
Chr(34) & [TASK DESCRIPTION].Value & Chr(34) & " AS Expr3, " & Chr(34) & [REQUESTED_BY].Value & Chr(34) & " AS Expr4, " & _
"#" & [Text128].Value & "#" & " AS Expr5, " & "#" & [START DATE].Value & "#" & " AS Expr6, " & _
"#" & [completion date].Value & "#" & " AS Expr7, " & Chr(34) & [STATUS_COMMENTS].Value & Chr(34) & " AS Expr8, " & _
Chr(34) & [COMMITTMENT].Value & Chr(34) & " AS Expr9, " & Chr(34) & Combo116.Column(0) & Chr(34) & " AS Expr10, " & _
Chr(34) & GROUP_COMBO.Column(0) & Chr(34) & " AS Expr11, " & Chr(34) & [INDIVIDUAL].Value & Chr(34) & " AS Expr12;"


DoCmd.SetWarnings False ' Set warning off
DoCmd.RunSQL MyStr ' Add current record to History table


If Combo116.Column(2) = 0 Then ' If the frequency is "One Time" or "Other", delete current record
'MsgBox "This record will be deleted"
MyStr = "Delete Task.[ID#] FROM Task WHERE (((Task.[ID#])=" & Str([TASK NUMBER].Value) & "));"
DoCmd.GoToRecord , , acNext ' Go to a new record
DoCmd.RunSQL MyStr ' Delete the record that was active before
Me.Requery ' Requery the form after a record is deleted
DoCmd.GoToRecord , , acFirst ' Go to the first record

Else ' Otherwise, it's a recurring task, so set its properties

'MsgBox "This record will be incremented with " & Str(Combo116.Column(2)) & " " & Combo116.Column(1)
Text128.Value = DateAdd(Combo116.Column(1), Combo116.Column(2), Text128.Value) ' increment the date, if recurring
[START DATE] = DateAdd(Combo116.Column(1), Combo116.Column(2), [START DATE])
[completion date].Value = Null ' Completion date goes blank
DoCmd.GoToRecord , , acNext ' save the record
DoCmd.GoToRecord , , acPrevious
End If



DoCmd.SetWarnings True ' Set warnings on




Button69_Exit:

Exit Sub




Button69_Err:

intErrNum = Err
Select Case intErrNum
Case 2105
MsgBox "This is a new task. Need more data to complete it", vbExclamation, "Need more data"
Case Else
MsgBox "Error!"

End Select
Resume Button69_Exit
End Sub




In a table that stores records that will evolve over time and you want to log a history of these changes. Should you:
1. Make a table for each of the columns (fields) and only log that change and time?
2. Should you make a duplicate table of the original and insert a copy of the entire record on each change regardless of which field is modified?
3. Store the duplicate record in the current table; but designate it is as an archive?
4. Do you have a better suggestion?

Thanks Ya’ll




I have a databse of bird ringing (banding) which I have turned into an encouter history by using the pivot table capability in MS Access. Example attached.

For each animal, identified by a ring number in column B, there is a set of "encounters" over time. A blank in a date column indicates that the animal was not seen or captures in that year. A number in the column indicates that it was seen or captured n times.

To format these data correctly for subsequent surival probability analysis, I need to replace all of the blanks, in each of the "year" columns with a 0 and anything that isn't blank (e.g. 1, 2 or 3) with 1. So, I need columns F to L to look like C to D.

It was simple enough to do this one column at a time...

UPDATE [Capture history intermediate] SET [Capture history intermediate].[1971] = "0"

WHERE ((([Capture history intermediate].[1971]) Is Null))

But I have a lot of data, spanning 40 years (i.e. 40 columns) and I will probably have to do this conversion many times as I work on sub-sets of my master database.

So, question is, is there a short-cut way to avoid having to create 40 different queries and run each one seperately? It's probably obvious to folks with more than the couple of months experience I have with SQL!!

Cheers

Denis




I'm filling in for someone who has a strange love for mail merge and now I need to covert employee history from a row to a column for the mail merge.

I currently have a list of employees and every change in position is listed as a new row. Since all employees are not the same some employees have more rows than others. What I need to do is move the history rows into columns. So what I'll end up with is one single row per employees with their history going to the right. Some employees will have more columns than others. I tried doing a crosstab but that will only let me pick one column from the original table.

I have three columns that I need repeated over and over to reflect their work history.

this is what im working with
Name or ID will be the key
Job start date
Job title
department

currently every one of theese fields is in a column but every entry has its own row.

I need a single row for every employee and the columns to repeat to the right to reflect every move.

Can someone help me I'm stuck

I doesnt matter if the output is in something other than access




I've looked though this forum and over the net but can't seem to find exactly what I'm looking for, maybe I'm searching for the wrong thing, and I have been stumped for sometime.

I'm trying to do an employee database, I've been able to do the basic personal history on one tab, a job history on the second tab using a continuous form, its the third one that has me confused as to how to get it to work.

What I'm trying to do is:
The user looks up an employee
They go to the training tab
They can see which training the employee should have done and the date they did it (A blank date means it has not been done yet)

I currently have:
1. A table with a job history of all employees (which works for tab 2) (shows their current position for checking what training they need)
2. A table with a field called training and a field called date complete- this is the table I want to display on my form (like the history it will be displayed as a continous form) (this table is the information I want to display)
3. Another table with the positions and types of training (I have tried the talbe in both configurations- positon as the rows and training as the column headers and vice versa so I can do it either way) the fields are all just yes/no boxes where it be can be ticked if the postions need that kind of training. (the table to check against)

My thought was to find the position down the first column, then display all columns were yes is ticked as training needed- using that as the link to get the dates from the last table. I just can't do this.

I'm happy to change the layout of the tables- I just need the basic end information and a way to check which training a position should have as they are not all the same, the only other issue- more postions and training may get added in the future, so it needs to be easy to expand the tables and understandable by people who may not be very literate with computers.




I have a field in the Client table called CLIENT NAME which has 2 columns. The first combines the First, MI & Last Name and the second column is a unique Client ID number.

This column appears in 2 other tables which will be the many of the 1 side (represented by Client table).

In the Family form & Work History forms, the bound column is the second (the ID number). Both fields on the form and in the tables show that the 2nd column, the ID number is the bound field. YET when run, it works only in one form. In the other form, the saved filed is the first column (the name).

Any ideas?




We have an office full of people that all have the same metrics to manage and we are riding the dashboard wave. I have the report that spits out a page for each person with the data they have to manage daily and that works great.

What I need to do now is display the trending data. Each day I run the report I have a query that sums each persons data that I need. Now I have to find a way to put that into a table so I can add the graph to the report to show the trend. So every day I would like to append a column to the table with that days information so the graph will show a history. Additionally I would like only keep the record for a rolling 30 day period. Lastly I am trying to limit the amount of work in the future if people leave/hire new in the office. I could write/manage tables and queries for all 15 people but I am trying to keep it flexible so we can contiune to use the system if I leave I can hand it off to a capable person and not have to have him or her worry about re writing querys and things of the such. Right now I have 3 tables and 2 queries to run the whole thing and it's not sensitive to the amount of data or people in the workflow.

I am decent with .vba. I did alot of work with excel but with offsets in a more visual type environment so I wouldn't be opposed to scripting it via macro/code since there is a good chance I will have to and it would help me learn a great deal which is why I took a stab at this project to begin with.

Thanks for all your help in advance.




Hi,

I am new to Access and am trying to self teach as I write a new database for work. I want a comments history on a form and with a little investigation found that a text box on the form with the following in the expression builder should do the trick -

=ColumnHistory("SPP Records","Progress Entry","[ID]=" & Nz([ID],0))

SPP Records is the main table and Progress Entry is the memo field / column (with append only applied) where the comments are entered.

However, all I get back is #Name?

If I revise the code to -

=ColumnHistory("SPP Records","Progress Entry","[ID]=10")

Then the comments history is returned, but this will obviously only work for the record with ID=10.

What am I doing wrong?

Thanks!