prompt need to locate missing linked table necessary to open main menu form

[Mark Kubicki]

For my main menu, I need to have a back end table linked to the current
project. That the table located ina backend file (*.mdb) may have been
moved, renamed, or missing, in which case I want to prompt the user as to
what file should be used...

Q. behind which event should I place code? I've tried (behind the main
menu form): Open, Load, etc. but none of them get me to the point where I
can ask the question.

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_form_Open
Me.pgProjectInfo.SetFocus
Err_form_Open:
MsgBox ("need add prompt for file here")
End Sub

Again, this is the main menu form (the one that the user sees when they
first open the project.

many thanks in advance, Mark

 

[NevilleT]

Hi Mark
Another way to do it is to create a module. You can then call the module
using an Autoexec macro. Check if the table is there and if not display a
messagebox. If it is there, open the main menu.

I use this code to check if a table called tblPeople exists

dim varPath as variant
'--------------------------------------------
' Check if there are linked tables.
varPath = DLookup("Database", "MSysObjects", "Name='tblPeople' And
Type=6")

 

[NevilleT]

Hi Mark

Suggest you create a subroutine in a module with the checking code and call
the module subroutine from an autoexec macro. If the check fails, you can
display the messagebox. If it succeeds, you open the main menu.

I use this code to check if a table (tblPeople) exists.
Dim varPath as variant
'--------------------------------------------
' Check if there are linked tables.
varPath = DLookup("Database", "MSysObjects", "Name='tblPeople' And
Type=6")

if a varPath length is >0 then a path exists. The next thing you need to do
is to see if it is a valid path. I have a generic function I use to see if a
file exists.

'---------------------------------------------------------------------------------------
' Procedure : funFileExists
' Author : Neville Turbit
' Date : 09/06/07
' Purpose : Check if an external file exist
'---------------------------------------------------------------------------------------
'
Public Function funFileExists(strPath As Variant, Optional lngType As Long)
As Boolean

On Error Resume Next

funFileExists = Len(Dir(strPath, lngType)) > 0

Exit_funFileExists:
On Error GoTo 0
Exit Function

End Function

So assuming a path exists you can check it is valid by using

If funFileExists(strPath) = True Then
All is well in the world
Else
Find another linked table
End If

NevilleTurbit
www.projectperfect.com.au