Is this possible? (1 Viewer)

[MICHELE]

I have a field called LoadDate that changes very often. I want to track every time this changes. I want to know each date it changes and what the new date was, also knowing all the previous date changes.

I thought about making a new table to track these changes. Could I get this new table to update this new info every time the LoadDate changes and only when it changes?

It seems like it would work, I just can't figure out how to make the new table update at the right time.

Thank you for any help!

 

[sambo]

Sounds like a table relationship issue. If you want many dates for one record, then you need to set up a one to many relationship between the two elements.

Structure your tables like this..
-- TABLE1 --
tblStuff -- Table Name
StuffID -- Primary Key (AutoNumber)
StuffField1
StuffField2

-- TABLE2 --
tblDates -- Table Name
DateID -- Primary Key (AutoNumber)
StuffID -- Foreign Key (Number (Matches value from TABLE1))
LoadDate -- (Date/Time)


Now go to the Relationship Window and create a 1 to Many Relationship ON StuffID

Now when you change (update) the LoadDate on your form, put this code in your after update...

Sub txtLoadDate_After_Update
  Dim db as DAO.Database, rst As DAO.Recordset
  Set db = CurrentDb
  Set rst = db.Openrecordset("tblDates")
  With rst
    .AddNew
    !SuffID = Me.StuffID  'Relate To The Current Record on your Form
    !LoadDate = Now()
    .Update
  End With
  rst.Close
  db.Close
End Sub

You may have to tweak your form's RecordSource a little bit in order to show only the latest LoadDate, but this should give you a start on the relationships.

 

[MICHELE]

a little confused still

This looks like it will do exactly what I need! But I'm a little confused.

LoadDate is currently being entered into the 1st table. Will I need to have it entered now in the 2nd table instead of the 1st or in addition.

Also, I'm not real good in VBA and I wrote your code, but it stops on the 1st line with this message "User-defined type not found." I'm bad and don't usually declare my variables unless I have to. It will probably get me in trouble soon.

 

[sambo]

Click On Tools->References in your VBA Code Window. Make sure that the reference to Microsoft Access DAO 3.6 is Checked. This will let you use the DAO library.

Shame on you for not explicitly declaring your variables... There is no better time than the present to learn.

You are correct about LoadDate going into the second table. It no longer exists in the first table. This is called Normalization. That's one of those big words that they teach us at University, but we don't pay attention until we get to our jobs and we actually have to put it into practice.

I suppose if Mrs. Hartman glances over this post she will have a thing or two to say about Normalization... She always does!!

Make sure to open the table up and take a look at how you can now drop down many LoadDates (Child Records) for each single Parent Record.

Good Luck..

 

[MICHELE]

Woo hoo! This is pretty cool! Thanks A LOT!

So I guess now I have a parent, child, grandchild relationship???