Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    turning off a warning (AccessXP)

    I have an administrative form with various reports. One of the reports creates a table. I have the close button set to delete the table object as the form is closed. If the report that creates the table is run I get a message "the program can't find the object tblReceivables." I think I need a If Then Else statement, but I am unsure of how to write the IF part of the statement.

    Thanks for all of the help as I have worked on this project. Delivery is tomorrow.
    Fay

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: turning off a warning (AccessXP)

    I'm not sure I understand what you are saying here.

    >>If the report that creates the table is run I get a message "the program can't find the object tblReceivables<<
    I presume that the table tblReceivables is created by the form that you run the report from.
    Is the error message generated from the report program or the form that generates the table then runs the report?

    If you think we could solve this faster because of your time frame, why don't you send a copy of your database.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turning off a warning (AccessXP)

    Let me try again.
    Adminsitrative form has 12 reports on the form.
    One of those reports, rptAssociationTotals, creates a table called tblReceivables.
    When I click the close button for the Administrative form the form is closed, dud. At the same time the tblReceivables is deleted.
    If I did not open rptAssociationTotals the table called tblReceivables is not created.
    In this case I get a message after the Administrative form is closed that "it couldn't find the object tblReceivables."

    How can I avoid this message.
    Fay

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: turning off a warning (AccessXP)

    What you can do is to put the following just prior to the delete statement:
    On Error Goto 0
    and put the normal On Error after the delete statement.

    An alternative is to test for the specific error code in the error processing and bypass the msgbox.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: turning off a warning (AccessXP)

    My understanding is that you want to delete the table if it exists.
    Try this
    <pre>Dim db As dao.Database
    Dim tdf As dao.TableDef
    Set db = CurrentDb()
    Dim present As Boolean
    Dim strTable As String
    present = False

    strTable = "tblReceivables"
    For Each tdf In db.TableDefs

    If tdf.Name = strTable Then
    present = True
    Else
    present = False

    End If
    If present Then
    ' msgbox ("present")
    DoCmd.DeleteObject acTable, strTable

    End If
    Next tdf

    set tdf = nothing
    set db = nothing
    </pre>

    Regards
    John



  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turning off a warning (AccessXP)

    I tried this code and got the debug message box for error 7874.
    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    DoCmd.Close
    On Error Goto 0

    DoCmd.DeleteObject acTable, "tblReceivables"

    Exit_cmdClose_Click:
    Exit Sub
    On Error GoTo Err_cmdClose_Click
    Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

    Thanks Pat
    Fay

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: turning off a warning (AccessXP)

    Another simple solution is to delete the table when you close the report rather than when you close the form.

    You could put the code in the onclose event for the report. If the report has been opened then the table will exist.
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: turning off a warning (AccessXP)

    John's suggestion will work, else try this:

    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    DoCmd.Close
    On Error Goto 0
    DoCmd.DeleteObject acTable, "tblReceivables"
    On Error GoTo Err_cmdClose_Click

    Exit_cmdClose_Click:
    Exit Sub
    On Error GoTo Err_cmdClose_Click <-----delete this line
    Err_cmdClose_Click:
    MsgBox Err.Description
    Resume Exit_cmdClose_Click

    End Sub

    Or you could try this:
    Private Sub cmdClose_Click()
    On Error GoTo Err_cmdClose_Click

    DoCmd.Close
    DoCmd.DeleteObject acTable, "tblReceivables"

    Exit_cmdClose_Click:
    Exit Sub
    On Error GoTo Err_cmdClose_Click <-----delete this line
    Err_cmdClose_Click:
    If Err <> 7874 then
    MsgBox Err.Description
    End If
    Resume Exit_cmdClose_Click

    End Sub

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turning off a warning (AccessXP)

    Hi John. You are working at a level of code above me. But I plugged it in. But on running the first line gave me "user-defined type not defined" message. As I said I don't know what to do.

    Fay

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turning off a warning (AccessXP)

    Boy that is so simple. BUT when I tried it I get a message saying "can'e delete because it is already in use.
    Fay

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: turning off a warning (AccessXP)

    I don't use XP, but my code works with both 97 and 2000, so I guess you need to create a reference to the DAO library.
    Regards
    John



  12. #12
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: turning off a warning (AccessXP)

    Pat I used your second code. AND IT WORKED!!

    Thanks Pat and John for all your help. I plan to get much stronger doing VBA in the future.

    I am also going to try to figure out why it doesn't work on close in the form. Doesn't make sense.

    Fay

  13. #13
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: turning off a warning (AccessXP)

    I just tried deleting a table in the close event and got the same problem you did.

    That good idea wasn't so good after all. Can't think of a way around this.
    Regards
    John



  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: turning off a warning (AccessXP)

    >>I am also going to try to figure out why it doesn't work on close in the form. Doesn't make sense.<<

    You mean the close of the report don't you? I believe that it is still in use by the report until the report exits completely, but I may be wrong.

    A problem you may still have is that if you run the report in preview mode and you exit the report you will leave the table undeleted as it will still be in use. Also if the report is a long print job and you exit the form the same problem occurs. So when you go to run the report again you will find that the table is already there unless you are trapping this as well.

    Glad you got it to work.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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