"Compile error: User-defined type not defined" ??

[cameron]

Hi,

Our client has an Access 2000 file format mdb file which I am working on. I have Access 2002 on my machine.

I found this Open FIle Dialog function that works perfectly fine when I tried it on the Northwind.mdb (also Access 2000 file format). However when I copy this function into the client's file and tried to execute it, I get this error "Compile error: User-defined type not defined" and this line code is highlighted "...fDialog As Office.FileDialog"

I have verified in Tools/References/Microsoft Access 10.0 Object Library is selected. I have to create a form with the Open FIle Dialog to allow a user to pick a *.mdb file and then the code will do a Open DB on this *.mdb file, read in records from one of the tables and append the new records to a current table.

Does anyone know what is causing the error ?

Private Sub cmdFileDialog_Click()

'Requires reference to Microsoft Office 10.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant

'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog

.AllowMultiSelect = False
.Title = "Please select the .mdb file to import data from"
.Filters.Clear
.Filters.Add "Access Databases", "*.MDB"
If .Show = True Then
For Each varFile In .SelectedItems
Me.txtFileToOpen = varFile
Next
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

 

[MacDermott]

The FileDialog object is not available in Access 2000.
You can find code to open a file/save dialog at
http://www.mvps.org/access/api/api0001.htm

HTH
- Turtle

Hi,

Our client has an Access 2000 file format mdb file which I am working on.

I have Access 2002 on my machine.

I found this Open FIle Dialog function that works perfectly fine when I

tried it on the Northwind.mdb (also Access 2000 file format). However when
I copy this function into the client's file and tried to execute it, I get
this error "Compile error: User-defined type not defined" and this line code
is highlighted "...fDialog As Office.FileDialog"

I have verified in Tools/References/Microsoft Access 10.0 Object Library

is selected. I have to create a form with the Open FIle Dialog to allow a
user to pick a *.mdb file and then the code will do a Open DB on this *.mdb
file, read in records from one of the tables and append the new records to a
current table.

 

[cameron]

Hi

Thank you for the information - have tried and it works just the way I want it.

A co-worker showed me an alternative method to using this code - using the Microsoft Common Dialog 6.0 - add the icon in my form and the object and methods become available to me - I tried it while at office and it works good and the code is cleaner. But now, at home, when I open my *.mdb file, it tells me Microsoft Common Dialog 6.0 is missing. I supposed I have to get this OCX into my machine

Could you explain why your solution is the preferred choice ? I know with the alternative method, once rolled out to the client and their agencies - I have no idea how this is going to be done, we cannot assume Microsoft Common Dialog 6.0 is available for them

Would appreciate your highlight to this question. Thank you.

 

[MacDermott]

Aside from the need to provide the ocx (which I'm not sure is actually
licensed for distribution except in a package created with the Developer's
Edition), there is an issue of compatibility.
Visual Basic 6.0 contains an ocx of the same name, but whose functionality
is just different enough to cause confusion.
So if your target PC happens to have a VB application on it using that ocx,
and your Access application uses it, you won't be able to have both
applications run.

All of which is avoided by using the API calls directly, instead of wrapping
them in the ActiveX control.

HTH
- Turtle

Hi,

Thank you for the information - have tried and it works just the way I want it.

A co-worker showed me an alternative method to using this code - using the

Microsoft Common Dialog 6.0 - add the icon in my form and the object and
methods become available to me - I tried it while at office and it works
good and the code is cleaner. But now, at home, when I open my *.mdb file,
it tells me Microsoft Common Dialog 6.0 is missing. I supposed I have to
get this OCX into my machine.

Could you explain why your solution is the preferred choice ? I know with

the alternative method, once rolled out to the client and their agencies - I
have no idea how this is going to be done, we cannot assume Microsoft Common
Dialog 6.0 is available for them.

 

[cameron]

Thank you so much for your response and your help to guide me in the right direction

I will remove the OCX stuff and put the API codes back in like the way it works before

Much appreciated...

cameron