Need assistance with report using data that varies ~

[Paul7905]

Hello !

New to Access and am looking for direction~

I want to produce a report.

I found I can produce a simple report using the Query wizard however this wizard does not provide for what I would like to do.

Let me explain the data I want to report on ~

1. the data is in a sigle table
2. the data in the table consists of inventory item data,
examples of the data fields are:

a. Item Name
b. Item short name
c. Item Size
d. Item price retail
e. Item price wholesale
f. Item product information (several fields for this)

etc. etc. ~ there are about 25 fields altogether of
various types and lengths (currency, text,
date etc)

3. the users make changes to the data in this table.
4. the users want to be able to prioduce a report, on
request, that reflects all the changes that have been
made to the data (since the last time they ran the
report)

I have figured out how to track the changes, I do this by rolling the "current" or "active" data fields into a corresponding "Previous" field ~
then, by comparing the two fields later, i can determine
which fields are not identical and therefore have been
modified.

When i do this, I also set a series of Yes/No bytes in the record that tells which field in the record has changed
(I do this because there are so many fields that may
have changed I only want to show on the report the
record key and the fields that have changed so the user
does not have to look at all of them to figure out what
is different. *(these yes/no bytes are, for example:
"WholesalePriceChg"
"RetailPriceChg"
"ItemDescription1Chg"
"ItemDescription2Chg" and so on ~

these yes/no bytesin the record correspond to the data
field(s) that the user may have changed.

I have determined that i can do a query that selects, based on a specific byte set to yes (true), the two fields (current and prior) and produce a report of all the records for which just that particular field has changed.

My problem is, I don't want to produce 35 different reports (one for each field in the records) but rather, I want to produce just one report that shows all the changes, grouped by field type that has changed, i.e. all the retail price changes, then all the wholesale price changes then all the item description changes and so on (within each showing the before change and after change data fields)~

I don't see how to do this using the report wizard ~ can someone point me in the right direction to do this (or am i stuck with 35 reports (any one of which may produce a blank page because that particular field may have not been changed in any of the records)~

thanks so much in advance ~ (by the way, a few weeks ago i knew nothing of access and with the assistance of all of you willing to share your expertice i have come a very long way quickly !! [sig][/sig]

 

[MichaelRed]

You need to look at the "dirty" property for the controls on your form.

If you want/need a report on the CHANGES, a reasonable process is to collect the relevant info in a "change history" table. I normally place:

User
RecordID
Date/Time
FieldName
Control Name
OldValue
CurrentValue

in the history table. You can place code in the Form's event [BeforeUpdate] which checks the controls collection of the form for the "dirty" flag and posts the information to the history file. Use the History file to generate the list of Items changed.

The Function below is a part of the code to do this. Obviously, you will need to modify the function to suit your needs, however it may save you some effort in generating the changes report.

In this specific instance, the [Qi] is the record ID for the record being added/edited. I differentiate betwee adding records and editing them. Here, I did not capture the "currentvalue", but "assumed" ONLY the changes were to be tracked. The [basFlgValidRec] function is NOT provided here as it is VERY specific to the application. In a generic sense, you could define your own as essientially the validation requirements of your form/application, or - more easily - delete the three lines of the If statement (assume your users NEVER make mistakes?). Also, not that memo fields are held in a different table than all other fields. Ms. Access may let you refer to history fields from text boxes, however if yous try to store them in a test field, she will truncate them. I chose to provide a seperate table for the memo fields, to minimize the size of the data set.

One LAST thought. If you use the change history approach, you can expect the history file to grow (in size) much faster than the actual data. You should incorporate a routine to remove 'ancient' history from these tables. In this app, it was STRICTLY forbidden to do this, however I did - eventually - create a seperate database where this information could be archived. Since the database was to support short term work items, we set the threshold for 'ancient' at ~ 6 months. Your application may want to use some other value but you will need to seperate or remove older records on occassion.



"Helper" Functions:


To Add records to the history table for "edititing' Records

Public Function basAddHist(Hist As String, QiVal As Long, MyCtrl As Control)
Dim dbs As Database
Dim tblHistTable As Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist)

With tblHistTable
.AddNew
!QI_Num = QiVal
!FldName = MyCtrl.ControlSource
!dtChgd = Now()
!UserID = CurrentUser()
!OldContents = MyCtrl.OldValue
.Update
End With

End Function



Add Memo type fields to seperate memo history

Public Function basAddHist(Hist As String, QiVal As Long, MyCtrl As Control)
Dim dbs As Database
Dim tblHistTable As Recordset

Set dbs = CurrentDb
Set tblHistTable = dbs.OpenRecordset(Hist)

With tblHistTable
.AddNew
!QI_Num = QiVal
!FldName = MyCtrl.ControlSource
!dtChgd = Now()
!UserID = CurrentUser()
!OldContents = MyCtrl.OldValue
.Update
End With

End Function



Just returns flag to determine if the control is one which can have an old value porperty.

Public Function basActiveCtrl(Ctl As Control) As Boolean
Select Case Ctl.ControlType
Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acTextBox
basActiveCtrl = True
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl
End Select
End Function


Main routine


Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim MyDb As Database
Dim tblHist As Recordset
Dim MyCtrl As Control
Dim MyMsg As String
Dim Hist As String

Set MyDb = CurrentDb
Set tblHist = MyDb.OpenRecordset("tblHist&quot
If (Not basflgValidRec) Then
Exit Sub
End If

If (MyFormMode.LastMode = "Add&quot Then
With tblHist
.AddNew
!FldName = "New Record"
!dtChgd = Now()
!UserID = CurrentUser()
!QI_Num = MyFormMode.frmName.QI

.Update
MyMsg = MyFormMode.frmName.QI & " Record Added."
MyMsg = MyMsg & vbCrLf & "Please note the Qi Number for the Installer"
End With
Else
For Each MyCtrl In MyFormMode.frmName.Controls
If (basActiveCtrl(MyCtrl)) Then
If ((MyCtrl.Value <> MyCtrl.OldValue) _
Or (IsNull(MyCtrl) And Not IsNull(MyCtrl.OldValue)) _
Or (Not IsNull(MyCtrl) And IsNull(MyCtrl.OldValue))) Then
If (MyFormMode.RecSrc.Fields(MyCtrl.ControlSource).Type = dbMemo) Then
Hist = &quot;tblHistMemo&quot;
Else
Hist = &quot;tblHist&quot;
End If
Call basAddHist(Hist, MyFormMode.frmName.QI, MyCtrl)
MyMsg = &quot;QI &quot; & MyFormMode.frmName.QI & &quot; Edited.&quot;
MyMsg = MyMsg & vbCrLf & &quot;History Record(s) Added to Database&quot;
End If
End If
Next MyCtrl
End If
If (MyMsg <> &quot;&quot; And Not IsNull(MyMsg)) Then
MsgBox MyMsg
End If

End Sub



[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]