Results 1 to 4 of 4
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Record Warning Message (2003)

    The Prev table in the attached mdb has a gap in the date/hour progression because a record is missing for the 15th hour on June 24, 2006.
    What is the best way to go about spotting it upon opening the file?

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

    Re: Missing Record Warning Message (2003)

    The sum of all hours in a day is 1+2+...+24 = 300. If the sum for a particular day in the table is less than 300, one or more hours are missing. For a rough check, you could create a query qryMissing:
    <code>
    SELECT Giorno
    FROM prev
    GROUP BY Giorno
    HAVING Sum(Ora)<300
    </code>
    Create a function StartUp in a standard module:
    <code>
    Public Sub Startup()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String
    If DCount("*", "qryMissing") > 0 Then
    strMsg = "There are records missing for the following dates:"
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("qryMissing", dbOpenForwardOnly)
    Do While Not rst.EOF
    strMsg = strMsg & vbCrLf & rst!Giorno
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    MsgBox strMsg, vbExclamation
    Else
    MsgBox "All OK", vbInformation
    End If
    End Sub
    </code>
    Call this function from the startup form of the database or from a macro named AutoExec (using the RunCode action).

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Record Warning Message (2003)

    Thank you Hans, perfect as usual. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    I suppose if I wanted to call the StartUp sub from the AutoExec macro I'd have to convert it into a function since the description of the RunCode action says, "Enter the name of the Function procedure to be executed".

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

    Re: Missing Record Warning Message (2003)

    Sorry, yes, it should have been a function (I did mention function in my reply, but forgot to modify the code).

Posting Permissions

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