VBA Access function

[niravm]

need a function to do:

- Loop through all fields in a table (use tabledef) (use any table – tablename should be a parameter for the function)

- There needs to be a timestamp-field which is set on some records and not on others

- For each textfield (only textfields – ignore all others!) check if there are new values (compared between the ones with time-stamp and without ) without time stamp will be first default entry and with time stamps will be updated entry

- If there are new values add the name of the field and the new values to a log-table

- After looping through all textfields and putting the name of the field and the values into the log-table – export the log table to Excel and show it in Excel. (alternatively you can log directly to Excel)


The end result in Excel should look something like this:

Fieldname1
NewValue1
NewValue2
NewValue3

Fieldname2
NewValue1
NewValue2

Fieldname3
NewValue1

[hansup]

- Loop through all fields in a table (use tabledef) (use any table – tablename should be a parameter for the function)

You can examine each of the fields in a TableDef.

Public Sub ListFields(ByVal pTable As String)
Dim db As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim strMsg As String

On Error GoTo ErrorHandler

Set db = CurrentDb
Set tdf = db.TableDefs(pTable)
For Each fld In tdf.Fields
Debug.Print fld.name
Next fld

ExitHere:
On Error GoTo 0
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
strMsg = "Error " & Err.Number & " (" & Err.description _
& ") in procedure ListFields"
MsgBox strMsg
GoTo ExitHere
End Sub

[niravm]

Yes I am using same method,


Example:
tblMember
ID Name SurName DOB Address datetimestamp
1 Nirav Mehta 01/12/1978 24 abc street
2 N Mehta 01/12/1978 3433 19/05/2011 12:00:00
3 N M 01/12/1978 3434 19/05/2011 14:00:00
4 Chiu Mok 01/01/1980 11 street
5 c m 01/01/1980 11 street 19/05/2011 10:00:00
6 ch Cooke 01/01/1981 23 dawnstreet
7 chris Cooke 01/01/1980 adaf 19/05/2011 16:00:00
8 Ni Me 01/11/1980 22 high street 19/05/2011 16:00:00

Result should be
Name
Nirav
Chiu
ch
SurName
Mok
Address
24 abc street
23 dawnstreet

[hansup]

Yes I am using same method

In that case, show us the code you have so far. Indicate which lines throw errors, and the error messages. If errors aren't the problem, explain what the code does differently than what you want.

I'm doubtful that you'll get anyone to write you a complex function from scratch which meets your specifications. Usually you must pay money for that kind of service.

[niravm]

My code is blow..

I need to check if dateTimeStamp is null means new record than it should check uniqueness of that field.


On Error GoTo TableInfoErr
' Purpose: Display the field names, types, sizes and descriptions for a table.
' Argument: Name of a table in the current database.
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
db.Execute ("Delete * from tblLog")
Set tdf = db.TableDefs(strTableName)

For Each fld In tdf.Fields
If (fld.Type = dbText) Then
Dim strSQL As String
strSQL = "select Distinct( " & fld.Name & " ) from " & strTableName & " WHERE (((tblMember.[datetimestamp]) Is Null)) "

Dim strIn As String
Dim strOut As String
Dim strName As String
Dim strQuery As String

strQuery = strSQL
strName = "tblLog"


Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateTableDef("tblLog", strSQL)
qdf.Close




'WriteToXLS (strSQL)
' MsgBox strSQL
'Debug.Print fld.Name
'With DoCmd
' .SetWarnings False
' .RunSQL strSQL
' .SetWarnings True
'End With
End If

Next


TableInfoExit:
Set db = Nothing
Exit Function

TableInfoErr:
Select Case Err
Case 3265& 'Table name invalid
MsgBox strTableName & " table doesn't exist"
Case Else
Debug.Print "TableInfo() Error " & Err & ": " & Error
End Select
Resume TableInfoExit

End Function

Public Function WriteToXLS(customQuery As String)

Dim rst As DAO.Recordset
Dim cnt As Integer

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim rng As Excel.Range

Dim fileName As String

Set appExcel = Excel.Application
appExcel.Visible = True
Set wbk = appExcel.Workbooks.Add
Set wks = wbk.Worksheets(1)
Set rng = wks.Range("A2:I4001")
wks.Cells(1, 1).Value = "Generating data..."

Set rst = CurrentDb.OpenRecordset(customQuery)
If (rst.RecordCount > 0) Then
cnt = 1
For Each fld In rst.Fields
wks.Cells(1, cnt).Value = fld.Name
cnt = cnt + 1
Next fld

Call rng.CopyFromRecordset(rst, 4000, 26)
End If

rst.Close
Set rst = Nothing
End Function

[hansup]

Leaving aside the issue of Excel output format, it seems your goal is to create an audit log. Therefore I will suggest this solution provided by Allen Browne:
Creating an Audit Log
http://allenbrowne.com/appaudit.html

If you prefer to create your own solution, and want help here, tell us about the context in which your code will run. It seems reasonable to me to audit changes make through a form. If you're intending to run audit code from a different context, tell us about it.