Results 1 to 6 of 6
  • Thread Tools
  1. 4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code on opening menu form (2000 all updates)

    A split database with Front End/Back End.

    When the database opens an AutoExec macro runs to relink the Front End and Back End runs, and then the Main Menu, frmMainMenu, opens.

    <code>Below is code that I have on the Activate event for frmMainMenu...
    Private Sub Form_Activate()
    Dim stDocName As String
    stDocName = "frmCustomers"

    Dim i As Integer
    i = Nz(DMax("Outstanding", "qryBalance"), 0)
    If i >= 28 Then
    Select Case MsgBox("There are Customers with balances 28 or more days overdue." _
    & vbCrLf & " Do you wish to check the balances owing?" _
    , vbOKCancel Or vbExclamation Or vbDefaultButton1, "Balances check")
    Case vbOK
    DoCmd.Close
    DoCmd.OpenForm stDocName
    DoCmd.GoToControl "Page 3"

    Case vbCancel

    End Select

    End If
    End Sub</code>

    This runs without a problem unless a revised Front End has been copied to replace the existing one. In that case, the code errors out on the line
    i = Nz(DMax("Outstanding", "qryBalance"), 0)
    indicating that the path to the Back End is not valid.

    Interestingly enough, the tables are relinked.

    I have tried the code on the Open and Load events, with the same results.

    Any suggestions as to what I should do?

    Thanks.

    Tom

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,320
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: code on opening menu form (2000 all updates)

    At the time you get the error, what happens if you try to run "qryBalance" from the DB Window?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. 4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code on opening menu form (2000 all updates)

    The query won't run until the VBA code is reset. When you try to run the query the message is that it's not a valid path.

    If you click OK on the message box that comes up with the error message when the main menu is opened, things run fine. Tables are appropriately relinked etc.

    The initial error is a runtime error 3044.

    I should also say that this only happens the first time following a revised Front End is copied over the original. From then on, no problem.

    Tom

  5. Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,320
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: code on opening menu form (2000 all updates)

    I'm not sure what happens when an autoexec macro runs. That is, I don't know if Access waits for it to run before continuing or not. In your situation, I think it is trying to run the query before the tables have been relinked.

    You might want to relink with code from a startup form that the system runs first.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 13 Times in 13 Posts

    Re: code on opening menu form (2000 all updates)

    As you have found, the startup form loads before the AutoExec macro is run. So, as Mark suggested, you should put the code to re-link the backend tables in the On Open or On Load event of the startup form. Both occur before the On Activate event.

  7. 4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: code on opening menu form (2000 all updates)

    Mark and Hans

    Thanks. I made a small Splash form that runs prior to the Main Menu. I also called the code to refresh links from a module rather than using AutoExec.

    This makes a big difference.

    Thanks again.

    Tom

Posting Permissions

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