code on opening menu form (2000 all updates)

[THWatson]

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

[MarkLiquorman]

At the time you get the error, what happens if you try to run "qryBalance" from the DB Window?

[THWatson]

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

[MarkLiquorman]

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.

[HansV]

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.

[THWatson]

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