User Defined Type not recognised - can anyone fix it please???

[londonkiwi]

PLEASE PLEASE PLEASE PLEASE HELP!!!
The following function (Import_Data) fails to work in Access2000. MyError is called, and I get either "File not Found: msau8032.dll" or "User Type not Defined"

Import_Data() imports a txt file (delimited) into the appropriate Access97 database table.

I can send the whole zipped file to anyone if you can please look at it???
************************************
MyError:

' Display Error message box and resumes
MsgBox Error(Err)
DoCmd.Hourglass False
MsgBox "Error Importing File. Not Done Correctly", 16, "ERROR"
Exit Function

*****************************************
Most of the code is down here - that being the Import_Data() and GetFileName functions. I THINK the error is a result of the GetFileName function falling over???? What do you think??


Function Import_Data()

Dim file_name As String
Dim SITE_NO As String
Dim SURVEY_NO As String
Dim SITE_NAME As String
Dim No_Records As Integer
Dim WADT As Integer
Dim AADT As Integer
Dim GROUP As String

Dim Criteria As String, MyDB As Database, MySet As Recordset

On Error GoTo MyError:


' Get name of file to be imported into the database
file_name = GetFileName()
file_name = Trim(file_name)

' Cancel press on Dialog Box
If file_name = "" Then
Exit Function
End If

' Display the HourGlass on the screen
DoCmd.Hourglass True

' Delete old records in TC_IMPORT
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From TC_IMPORT"
DoCmd.SetWarnings True

' Import text file to a temp table before appending it
DoCmd.TransferText acImportDelim, "IMPORT_SPEC", "TC_IMPORT", file_name

' Get site number and survey number from temp table
SITE_NO = DFirst("[SITE_NO]", "TC_IMPORT", "&quot
SURVEY_NO = DFirst("[SURVEY_NO]", "TC_IMPORT", "&quot

' Count the number of records in the TC_IMPORT table
No_Records = DCount("*", "TC_IMPORT&quot



'Check to see if the Site_No is in TC_INDEX table

Criteria = "SITE_NO = '" & SITE_NO & "'" ' Define search criteria.
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MySet = MyDB.OpenRecordset("TC_INDEX", DB_OPEN_DYNASET) ' Create dynaset.
MySet.FindFirst Criteria ' Locate first occurrence.

If MySet.NoMatch Then
DoCmd.Hourglass False
MsgBox "The Site Number for this file is not in the Database. Enter the site details in table TC_INDEX and try again", 16, "ERROR"
'Quit Sub
Exit Function
Else
GROUP = MySet![GROUP]
End If

MySet.Close ' Close table.



'Check to see if Temp_table has already been appended to the table TC_3C3S

Criteria = "SITE_NO = '" & SITE_NO & "' AND SURVEY_NO = '" & SURVEY_NO & "'" ' Define search criteria.

Set MySet = MyDB.OpenRecordset("TC_3C3S", DB_OPEN_DYNASET) ' Create dynaset.
MySet.FindFirst Criteria ' Locate first occurrence.

If Not MySet.NoMatch Then
DoCmd.Hourglass False
MsgBox "The data has already been imported. Can not import over existing data", 16, "All Ready in Database"
'Quit Sub
Exit Function
Else ' Temp_Table not all ready in Database
DoCmd.SetWarnings False
' Run query to append TC_IMPORT to TC_3C3s table
DoCmd.RunSQL Append_Query()


' Run query to add record to TC_Summary table

'Calaculate WADT for both direction
WADT = Cal_WADT(No_Records)

' Calculate AADT for section of road using traffic count guideline (Nov 94)
AADT = Cal_AADT(SURVEY_NO, WADT, GROUP)

' Add record for lane 1 to TC_Summary , Only half records will be used eg lane 1
DoCmd.RunSQL Summary_Query(CStr(No_Records / 2), 1, WADT, AADT)

' Add record for lane 2 to TC_Summary , Only half records will be used eg lane 2
DoCmd.RunSQL Summary_Query(CStr(No_Records / 2), 2, WADT, AADT)

DoCmd.SetWarnings True
End If

MySet.Close ' Close table.
MyDB.Close ' Close data base

' Turn off Hourglass
DoCmd.Hourglass False
MsgBox "File Import Correctly", 0, "OK"

' Ever thing went Ok EXIT sub before doing error handling stuff
Exit Function

' Error handling routine
MyError:

' Display Error message box and resumes
MsgBox Error(Err)
DoCmd.Hourglass False
MsgBox "Error Importing File. Not Done Correctly", 16, "ERROR"
Exit Function

End Function

***********************************************

Function GetFileName() As String
' Return path of a file chosen by user in OpenFile dialog box.
' (This function works in conjunction with GetMDBName2 and StringFromSz to
' display a File-Open dialog that prompts user for a file
Const OFN_SHAREAWARE = &H4000
Const OFN_PATHMUSTEXIST = &H800
Const OFN_HIDEREADONLY = &H4
Dim ofn As wlib_GetFileNameInfo
' Fill ofn structure, which is passed to wlib_GetFileName.
ofn.hWndOwner = 0
ofn.szFilter = "Databases (*.txt)|*.txt|All(*.*)|*.*||"
ofn.NFilterIndex = 1
ofn.szTitle = "Select File to Import"
ofn.Flags = OFN_SHAREAWARE Or OFN_PATHMUSTEXIST Or OFN_HIDEREADONLY
ofn.szDefExt = "txt"

' Call wlib_GetFileName function and interpret results.
If (GetMDBName2(ofn, True) = False) Then
GetFileName = StringFromSz(ofn.szFile)
Else
GetFileName = ""
End If
End Function

***********************************************


Private Function GetMDBName2(gfni As wlib_GetFileNameInfo, ByVal fOpen As Integer) As Long

' This function acts as a cover to MSAU_GetFileName in MSAU200.DLL.
' wlib_GetFileName terminates all strings in gfni structure with nulls and
' then calls DLL version of function. Upon returning from MSAU200.DLL, null
' characters are removed from strings in gfni.
Dim lRet As Long
gfni.szFilter = RTrim$(gfni.szFilter) & Chr$(0)
gfni.szCustomFilter = RTrim$(gfni.szCustomFilter) & Chr$(0)
gfni.szFile = RTrim$(gfni.szFile) & Chr$(0)
gfni.szFileTitle = RTrim$(gfni.szFileTitle) & Chr$(0)
gfni.szInitialDir = RTrim$(gfni.szInitialDir) & Chr$(0)
gfni.szTitle = RTrim$(gfni.szTitle) & Chr$(0)
gfni.szDefExt = RTrim$(gfni.szDefExt) & Chr$(0)

lRet = wlib_MSAU_GetFileName(gfni, fOpen)
gfni.szFilter = StringFromSz(gfni.szFilter)
gfni.szCustomFilter = StringFromSz(gfni.szCustomFilter)
gfni.szFile = StringFromSz(gfni.szFile)
gfni.szFileTitle = StringFromSz(gfni.szFileTitle)
gfni.szInitialDir = StringFromSz(gfni.szInitialDir)
gfni.szTitle = StringFromSz(gfni.szTitle)
gfni.szDefExt = StringFromSz(gfni.szDefExt)
GetMDBName2 = lRet
End Function

***********************************************

Private Function StringFromSz(szTmp As String) As String
' If string terminates with nulls, return a truncated string.
Dim ich As Integer
ich = InStr(szTmp, Chr$(0))
If ich Then
StringFromSz = Left$(szTmp, ich - 1)
Else
StringFromSz = szTmp
End If
End Function

 

[Durkin]

It looks like you are missing a reference to some library probably MSAU200.DLL. I've got Office 2000 on my machine and when I do a search for it, it's not there. The error message you got means that you are declaring a variable of a type which access knows nothing about. When you use the wlib_GetFileNameInfo type it doesn't know what you're talking about. If you do a compile it should go straight to the line with the unknown data type, thus highlighting the offending variable. Durkin
alandurkin@bigpond.com

 

[londonkiwi]

Durkin, thanks for the advice. A dumb question, but if I can;t find the library msau200.dll (which I take it is a library that ships with Access ect), where do you find it???

cheers

lk

 

[Durkin]

I went and looked up that dll in the MSDN and it only returned articles about Access 2 and Access 4.5 so I presume you're upgrading a fairly old DB. It's possible that the dll is a Win 3.x library. I thought I better have a proper look at your code, which is what I should have done in the first place, to see what exactly you're trying to do. What you need to use, I think, is a common dialog control. This allows you to open all the common dialogs used in windows like FileOpen, FileSave, Printers, Fonts etc. It should be listed in the ActiveX controls section of your toolbox. There is plenty of help available on it but if you need anymore don't just post your queries up here. Durkin
alandurkin@bigpond.com

 

[Durkin]

Excuse me, I just read the last line that I wrote. What I meant to say was if you need anymore just post your queries up here.X-) Durkin
alandurkin@bigpond.com