londonkiwi
Programmer
I have produced an Import_Data() Function to be run in Access2000. It aim is to import processed data (a txt file), (checking validity duplicates etc) and place that data in the appropriate tables.
I have the following defined "Dim MyDB As DATABASE"
I have received the following error msg, and cannot understand why it has arisen
"Complie Error - User-defined type not defined"
I apologise in advance, but have pasted the code below. Can someone please tell me what dumb
things I have done, and how to fix it...please
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, MySet As Recordset
Dim Criteria As String
Dim MyDB As DATABASE
Dim MySet As Recordset
On Error GoTo MyError:
' Get name of file to be imported into the database, call the Function GetFileName
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", ""
SURVEY_NO = DFirst("[SURVEY_NO]", "TC_IMPORT", ""
' Count the number of records in the TC_IMPORT table
No_Records = DCount("*", "TC_IMPORT"
'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)
' WAS Set MySet = MyDB.OpenRecordset("TC_INDEX", DB_OPEN_DYNASET) ' Create dynaset.
Set MySet = MyDB.OpenRecordset("TC_INDEX", dbOpenDynaset) ' 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. These two fields can
' be considered the primary key. Consider future counts on future dates!
Criteria = "SITE_NO = '" & SITE_NO & "' AND SURVEY_NO = '" & SURVEY_NO & "'" ' Define search criteria.
' WAS Set MySet = MyDB.OpenRecordset("TC_3C3S", DB_OPEN_DYNASET) ' Create dynaset.
Set MySet = MyDB.OpenRecordset("TC_3C3S", dbOpenDynaset) ' 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 "Houston - we have a problem - Error Importing File. Not Done Correctly", 16, "ERROR"
Exit Function
End Function
