Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Module crisis. (Access2k)

    Below is a module code, I would expect this code to output a message saying that "There are no records" when the record source which is a query called "ClassicTicket" is empty and wouldn't open the merged document. but it does, why is this? I am no expert in visual basic and didn't have any idea on what I was doing wrong. PLEASE help.

    Sub ClassicTicket()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim objWord As Object
    Set db = CurrentDb
    'Run append query to add records to the Classic table (system warnings = off)
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE [tblClassic].* FROM [tblClassic] WITH OWNERACCESS OPTION;", -1
    DoCmd.OpenQuery "AppendToClassic", acNormal, acEdit
    DoCmd.SetWarnings True
    Set rst = db.OpenRecordset("ClassicTicket")
    If rst.RecordCount = 0 Then
    MsgBox "There are no records", vbOKOnly
    Set rst = Nothing
    Set db = Nothing
    Exit Sub
    Else
    Set objWord = CreateObject("Word.Basic")
    objWord.AppShow
    'objWord.AppMaximize "", 1 (optional)
    objWord.FileOpen "S:SRI_WO~1TRADEA~1classi~2.doc"
    DoCmd.RunSQL "DELETE [tblClassic].* FROM [tblClassic] WITH OWNERACCESS OPTION;", -1
    End If
    Set rst = Nothing
    Set db = Nothing
    End Sub

  2. #2
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module crisis. (Access2k)

    Hi Princess

    Assuming that your query "ClassicTickets" will return records if there are any -
    The line
    If rst.RecordCount = 0 Then
    is your problem.
    When you open a recordset you are on the first record of the set.
    To fill the recordset and obtain the RecordCount you need to move to the last record of the set with:
    rst.MoveLast
    then your RecordCount will be correct.
    A better way to test for an empty recordset is to test for both rst.BOF and rst.EOF being true (Beginning of File and End of File respectively)

    the code would be:
    If rst.BOF and rst.EOF Then...

    HTH
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module crisis. (Access2k)

    Thank you, Steve, Thats seems to be doing the trick. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>
    <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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