×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need assistance with report using data that varies ~

Need assistance with report using data that varies ~

Need assistance with report using data that varies ~

(OP)
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 !!

RE: Need assistance with report using data that varies ~

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")
    If (Not basflgValidRec) Then
        Exit Sub
    End If

    If (MyFormMode.LastMode = "Add") 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 = "tblHistMemo"
                            Else
                                Hist = "tblHist"
                        End If
                        Call basAddHist(Hist, MyFormMode.frmName.QI, MyCtrl)
                        MyMsg = "QI " & MyFormMode.frmName.QI & " Edited."
                        MyMsg = MyMsg & vbCrLf & "History Record(s) Added to Database"
                    End If
                End If
            Next MyCtrl
        End If
    If (MyMsg <> "" And Not IsNull(MyMsg)) Then
        MsgBox MyMsg
    End If

End Sub



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close