List boxes fed from queries

[Phonik]

Hi peeps

I have a list box that contains 4 fields and the list box is located on a form.
One of the fields on the form is [Date logged]. This is also colum 2 of the list box.
I am trying to write some code to say that if the Date logged on the form is different to the one in the list box, run an append query.
Basically, the principal behind this is to have an audit trail of when records have been ammended. The first time a record is created, a base record is created. It is this base record that will populate the list box along with all other record changes that occurr from that point on.
I am trying to say that if the [Date logged] does not match the last record in the list then it knows it needs to append the record to the history table again as a change has been made. I would really appreciate some help here folks. Thanks so much!

 

[WayneRyan]

Phonik,

Using your scenario, you are only checking when a record is currently displayed on
the screen. In other words, if you don't "visit" a record with that form, then
you won't have any Audit Trail data.

To do this, use the form's OnCurrent event, populate the listbox to get the history,
then check the date;

Me.ListBox.RowSource = "Select F1, [Date Logged], F3, F4 " & _
                       "From   YourHistoryTable " & _
                       "Where  YourPK = " & Me.PK
Me.ListBox.Requery
If CDate(Me.ListBox.ItemData(Me.ListBox.Listcount - 1).Column(1)) <> Me.[DateLogged] Then
   Docmd.RunSQL "Insert Into YourHistoryTable ..."
   Me.ListBox.Requery
End If

Notes:
1) The 2nd column in the ListBox is .Column(1) <-- They start at 0
2) You have to convert the "[Date Logged]" in the ListBox back into a date, as in
this context it's a string.

Also, If you use the Search Facility here and look for "Audit Trail" you'll find
a demo by GHudson. It has been very well received in prior posts on this topic.
That way, you log changes as they happen, not as they're "seen".

hth,
Wayne

 

[Phonik]

Great stuff! Got it to work!

Thank you so much!