Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update a list box (2003)

    Hi,

    I am using the oncurrent event of a form to populate a text box with data from two fields of a seperate table. The data is an audit trail and I only want to see the most recent timestamp and details in the text box. The problem I have is the text box displays the correct timestamp but selects it details field from what I think is the one that starts higher in the alphabet.

    For example if I have 3 records in the audit table that match the record on the current form that our

    012345 01/08/08 Updated
    012345 01/07/08 Viewed
    012345 25/08/08 Merged

    The text box would display 25/08/08 - Viewed
    instead of 25/08/08 - Merged

    Heres the code

    Me.Text93 = DMax("TimeStamp", "TblAuditTrail", "Reference= " & Chr(34) & Me.RefId & Chr(34)) & " - " & _
    DLookup("Notes", "TblAuditTrail", "Reference= " & Chr(34) & Me.RefId & Chr(34))

    Many thanks

    Kevin
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update a list box (2003)

    I'd create two queries:

    1) A query based on TblAuditTrail.
    Select View | Totals (while designing the query).
    Add Reference and TimeStamp to the query grid.
    Set the Total option for TimeStamp to Max.
    This query returns the most recent timestamp for each reference.
    Save as qryMaxDate.

    2) A query based in TblAuditTrail and on qryMaxDate.
    Join them on Reference and on TimeStamp vs MaxOfTimeStamp.
    Add * from TblAuditTrail to the query grid.
    This query returns the most recent timestamp and the corresponding notes for each reference.
    Save as qryMaxAudit.

    3) Change the code to

    Me.Text93 = DLookup("TimeStamp", "qryMaxAudit", "Reference= " & Chr(34) & Me.RefId & Chr(34)) & " - " & _
    DLookup("Notes", "qryMaxAudit", "Reference= " & Chr(34) & Me.RefId & Chr(34))

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update a list box (2003)

    Perfect

    Thanks
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •