Column History in MS Access 2007

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.


Post your answer or comment

comments powered by Disqus
On a form with a few columns, i have on top of each column a textbox where the user can fill in a value that will serve as filter for the recordsource of the form.
The tag of each of these textboxes has as value the name of the controlsource of the field of that column.
In the afterupdate event of these textboxes , the function "Setfilt" is called what result in a filtering of the recordsource of the form in accordance with the value entered in the textbox. See code below.

In Access 2.0 this works fine. I converted the program to Access 2007. There the program works fine for all columns, except for columns of datatype True/False ( in Dutch = JA/NEE.)
When the value "JA" or the Value "NEE" is entered in the textbox above such a column i get the following message with as title:"Parameterwaarde opgeven" what translated means : Enter Parameter value"
Below the title stands : "ONWAAR" ( = FALSE) ( e.g when entered "NEE")
and then a textbox to enter the value.
Since the code works fine for all columns except for the true/false columns, I wonder if this is a bug in MS Access 2007?

Private Function getwhere(F, v As Variant) As String

On Error Resume Next
Dim N As Integer
If Len(Trim(v)) < 1 Then v = ""
If v "" Then
Select Case VarType(v)
Case 8 'string
If F = "[Factuurnr]" Then
getwhere = " And " & F & " Like """ & v + """"
ElseIf F = "[jan]" Or F = "[feb]" Or F = "[mrt]" Or F = "[apr]" Or F = "[mei]" Or F = "[jun]" Or F = "[jul]" Or F = "[aug]" Or F = "[sep]" Or F = "[oct]" Or F = "[nov]" Or F = "[dec]" Or F = "[stock]" Then
getwhere = " And " & F & "= " & v
ElseIf F = "[klntID]" Then
getwhere = " And " & F & " =" & Str$(v)
Else
getwhere = " And " & F & " Like """ & v + "*"""
End If
Case 7 'date
getwhere = " And " & F & " = " & changedate(v)
Case Else
getwhere = " And " & F & " =" & Str$(v)
End Select
Else
getwhere = ""
End If
End Function

Function setfilt()

Dim Where As String, frm As Form
Dim N As Integer
On Error GoTo ErrorHandler
Set frm = Screen.ActiveForm
On Error Resume Next
Dim ct As Integer
If frm.Name = "OverzichtOrders" Then
N = 10
ElseIf frm.Name = "frmOverzichtgeleverdeArtikelen" Then
N = 15
Else
N = 12
End If
For ct = 1 To N
Where = Where & getwhere(frm("Value" & LTrim$(Str$(ct))).Tag, frm("Value" & LTrim$(Str$(ct))))
Next
If Where "" Then
Where = Mid(Where, 6)
frm.RecordSource = "Select * from " & frm.Name & " Where " & Where & ";"
Else
frm.RecordSource = frm.Name
End If

Afsluiten:
On Error GoTo 0
Exit Function

ErrorHandler:
Mededeling = foutbericht("setfilt", "modSystem", Err)
Resume Afsluiten
End Function

Hi All,

I am developing Windows Application using C#.net with MS ACCESS 2007 as my Database. During Development I have entered so many records in my database.I am having tables with datatype as AutoNumber..

Now my requirement is I want truncate tables I mean I want to delete all records and reset autonumber values..

In sql server database, Truncate keyword is there to truncate the tables...

But in MS Access I could not find this keyword...

So, Please any one tell me how to truncate tables in MS ACCESS 2007..

Thanks in advance..

Hello Guys, in this video I'll show you how to create Login form in MS Access 2007, 2010

http://www.youtube.com/watch?v=TB3Qp1pdNOY

Hello Guys, in this video I'll show you how to create advanced search form in MS Access 2007, 2010

http://www.youtube.com/watch?v=M6XIU5_z-UU

Hi,

I need to display the out put of a table as chart in ms access 2007. My table is as below.

ItemID Week 1 Value Week 2 Value 1001 87 5 1002 80 1 1003 42 1 1004 55 164
In the chart X co ordinate should be the "ItemID" and the Y co ordinate should be the week number (eg, week1, week2 ...etc).
Is it possible to display the chart with the above table? Or do I need to transform the table and then dosplay as chart?

Also can I transform the above table as shown below using a single Transform query ?

ItemID Week Value 1001 1 87 1001 2 5 1002 1 80 1002 2 1 1003 1 42 1003 2 1 1004 1 55 1004 2 1664
Also please let me know if I can transform the table as above is it possible to create the chart.

Please help me resolving these.

Hello,
We have application in ms-access 2007. He want "View All" option in the combo box. So, if any user will check "veiw All" checkbox all the checkboxes should be automaticaly checked and if he uncheck any it sould uncheck the "View All" check box. combo data is coming from DB only and sometimes flitering from previous combo box.

Thank you very much in advance for your support.

Regards,
Amit A.

Hello, i am new to progrmaming in MS access 2007 although i have some basic programming in C++, Java, Pawn and some web design languages (PHP, xHTML, CSS, Javascript).

for my database i need to make it so that when i enter the Product Name from a dropdown menu it gets the product ID from another table.. (so, I choose product name "shoe" then it queries the "qry_Products" table for the product with the name as "shoe" and then gets the ID.."PA01" for example... and puts it into the Product_ID in the original table where i chose the name from the drop down menu... )

Im pretty sure i need programming in access to do this or maybe even macros?... Ive already made a query which can do this for all my records although i have to run it manually and this causes problems..

-thanks

This is my first post so forgive me if I don't do it correctly.

I am converting a number of MS Access 2003 apps to MS Access 2007. I have found a problem in some, but not all, of the reports converted. MSA 07 seems to change the height of the page footer to 22", regardless of what it was to begin with. In addition, it will not allow me to change from the 22" to anything else. I have found two solutions, both onerous; delete the footer and re-create it and copy all sections to a new blank report.

I have seen this problem posted on other sites but I have never seen an answer posted. I am hoping that someone might have a more elegant solution than the ones I mentioned. Thanks very much

i am quite new to Ms access, i made a small application in ms access 2010.

1- my Colleague who has 2007 can not open it,"Unrecognized database format"
2- in the assign activities codes form, can you please suggest more elegant solution, crosstab will be perfect in this case but unfortunately it can not be updated.
3- in Graphic report form, how to delete the second combobox selection, when i change the first selection.


thanks very much

Hi,

I am using MS Access 2007 and VS 2010. I want to develop an application with back end as MS Access. But I want to create stored procedures in MS Access.
Is it possible to create in MS Access or I have to hard-code all the necessary queries in my application. If possible please send how to create and maintain it in MS Access.

Any other software is needed to achieve it?

Regards,
Selvam S

Hello,

I would like to implement in my applicaton the new feature - collecting data using e-mail options. But I have the following problem.

I want that the action of creating and sending e-mail messages is raised when the user click on the button on my form in MS Access aplication.

For now I found a very difficult solution for this problem as following:

- user click on the button on my form ( application send to me e-mail message - )

- I click on the External Data tab in the Collect Data group "Manage Replies". In the Manage data Collection Messages dialog box I choose "Resend this E-mail Message"


Could you please suggest a better way for processing this action.

I would be very grateful for your help

Best regards,

Karmen

Good Morning All!

So I have created this database for a lessons learnt system. Is it possible in MS Access 2007 to have security on it. What I mean is I want certain individuals (project heads) to have access to alter, add and save the database, while everyone else can only view it. This lessons learnt database contains hundreds of lessons learnt and I have created a search filter for ease, however I don't want unauthorized individuals to make changes to the database. I have been reading other threads and noted that jet-level security was mentioned, however it has been disabled in access 2007. Thanks for reading!

Does anyone know why MS Access 2007 does not use the same charting engine as the rest of Office 2007.

I find the new engine in Office 2007 very nice indeed and a significant improvement over previous versions, both aesthetically and functionally, so why am i still left with crap options for charting in MS Access 2007?????

Many thanks in advance for any info...

Cheers
ndeans

hi,
I have successfully bound the sub forms in MS access to the tables present in the database and run the application .

Now i wish to have a bigger database and thus shifted to oracle for database.Thus i created tables in oracle database and linked them to MS access using ODBC . But now my subforms are not working at all . They are not showing the data properly and they are not editable too . I can't even add a new row through the sub form now . Could anyone guide me as to what problem it could be ?

Note: the link table names and the previous access tables names are the same .

Hi

How to get MAX YearMonth Value using SQL query in MS Access 2007. Pls note YearMonth is stored as Text

I'm executing below SQL and it's returning all the records from the table


	Code:
	Select MAX([YearMonth]) from tbl_Revenue_Assets_Split GROUP BY [YearMonth]

Output is coming:
YearMonth
201201
201202
201203
201204
201205

I want only 201205 (YYYYMM).

Any idea how to achieve this?

I am working with MS/Access 2007 and was a heavy user of MS/Access 2003. I am not crazy about the way MS/Access has been changed.

One of the minor things I noticed with saving queries in MS/Access 2007, is that if you position the lower part of the query that contains the data fields selected and record criteria to a certain location, the next time you open that query, the save location has moved upwards. I just don't know why this is happening.

I also don't like that when you open a query in design view and then just close it without making any changes, the query's modified date changes to today's date.

Maybe I use to the way MS/Access 2003 worked, but both of these oophs are very annoying and maybe it is something I am doing wrong that is causing these minor problems.

I would appreciate any advice.

Ed

Can i play the sound file (.wma) in Ms Access? I want to play it when the form load.

Thanks,

Hi All,
I have created a database grants.accdb in MS Access 2007. I have tables which have many to many relation and one to many relations. For this I need to design forms. But I am able to figure out how my form structure should be to correctly enter my data. I have created some forms looking at examples but they are not working. I am also attaching my database. If you can tell me how my forms should be designed I will be really grateful. I dont know how to use coding in MS Access. If there is any template resembling this database it would be really helpful to me.


Thanks
Shilpa

I have a database that allows the users to filter the records based on criteria entered into an input box.

The code applies filters, stored in queries, that allow for the users to enter text into an Input Box and the filter applies to the form.

This has worked great in MS Access 1997, 2000, and 2003. Now in MS Access 2007, it no longer works. I have done some searching, found some work arounds, none of which worked for me, and also found it is a known issue that has not been fixed by MS. Wonderful.

If I open up the .mde (a 2003 file, not even a 2007 file) with MS Access 2007, and click to apply the filter, the first time the filter applies correctly, limiting my results.

But after this first time, the filter application no longer works at all. The pop-up input boxes appear for the other filters, but the results do not return correctly.

I just want to clear the previous filter value, go back to all the records, and then apply the new filter value.

I have tried...

me.requery
me.refresh
me.filteron = false
me.filteron = true
docmd.showallrecords

The only thing that works is closing the form with the filters, and then re-opening it.

Has anyone run into this issue with MS Access 2007, and if so, is there a work around for it? I am about to pull my hair out fighting with this thing for hours on end, something that should and has worked with no problems at all.

Thank you,

T.J.

Team,

How do I get a database that was created in MS Access 2003 to display and function properly in MS Access 2007?

Peace,

two_smooth

Good Morning All!

So I have created this database for a lessons learnt system. Is it possible in MS Access 2007 to havesecurity on it. What I mean is I want certain individuals (project heads) to have access to alter, add and save the database, while everyone else can only view it. This lessons learnt database contains hundreds of lessons learnt and I have created a search filter for ease, however I don't want unauthorized individuals to make changes to the database. I have been reading other threads and noted that jet-level security was mentioned, however it has been disabled in access 2007. Thanks for reading!

I have two fields; Aircraft_Reg and Aircraft_Type on a form pulled out from the table (tblAircraft_Details) in MS Access 2007 as following:

Aircraft_Reg: Aircraft_Type:

tblAircraft_Details:


ID /Aircraft_Reg/ Aircraft_Type
1 / A5-EAA / A320
2/ A5-EAB / A320
3 / A5-EAC/ A320
4 / A5-EAD / A320
5/ A5-RAA/ B737
6 / A5-RAB / B737
7 / A5-RAC/ B737
8/ A5-RAD/ B737
9 / A5-GAA/ B747
10/ A5-GAB / B747
11 /A5-GAC/ B747
12 / A5-GAD/ B747
13/ A5-KAA/ A340
14/ A5-KAB/ A340
15 / A5-KAC/ A340
16 / A5-KAD / A340


When users select A5-EAA or A5-EAB or A5-EAC or A5-AED on Aircraft_Reg, I want Aircraft_Type to appear automatically as A320 , when A5-RAA or A5-RAB or A5-RAC or A5-RAD is selected as B737 , when A5-GAA or A5-GAB or A5-GAC or A5-GAD is selected as B747 and when A5-KAA or A5-KAB or A5-KAC or A5-KAD is selected as A340.


Please help me achieve the above either with VB code or macro or query. I would appreciate if you provide me with step by step process to solve the above as I am not really good in understanding access terms!


Thank you and best regards.

Does anyone know if there is an auto save feature in MS Access 2007. I would like my file to backup every few minutes automatically. I have had some recent power surges and have lost too much work. I tried clicking the Office button, then access options from the dropdown menu, but from there I do not see any option for autosave. Tks.

There is a requirement to display the results (Tope 3 Dates to flow side-by-side) for a single column query in MS Access. The application was written in 2003, but has been converted to 2007.

Any help is appreciated.

Thanks.

psimpson


Not finding an answer? Try a Google search.