Looking at the first few lines of code you posted
Private Sub ImportMonthlyMBD_Click()
On Error GoTo Err_ImportMonthlyMBD_Click
Dim sSQL As String 'The SQL String To Be Executed
Dim nResp As Integer
Dim rs As DAO.Recordset 'The DAO recordset
Dim rs1 As DAO.Recordset 'The DAO recordset
Dim rs2 As DAO.Recordset 'The DAO recordset
Set myDB = CurrentDb 'Use this database
Set rs = Nothing 'Tidy up the recordset
Set rs1 = Nothing 'Tidy up the recordset
Set rs2 = Nothing 'Tidy up the recordset
sSQL = ""
these 3 lines
Set rs = Nothing 'Tidy up the recordset
Set rs1 = Nothing 'Tidy up the recordset
Set rs2 = Nothing 'Tidy up the recordset
would be put at the exit handler for the code.
So, first step is to comment out those 3 lines of code under set myCB =
CurrentDb (put an apostrophe in front of each line).
Next see if the code compiles
The code you posted only uses rs, but does not use rs1 and rs2.
So at the exit handler, use
On Error Resume Next
Set rs = Nothing
between the 1st line of the exit handler and the exit sub line.
Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
"Bateman28" <(E-Mail Removed)> wrote in message
news:8BF3FAC0-2ED3-4A0B-BD99-(E-Mail Removed)...
> Hi
>
> I Am trying to create an import routine from an on-click demand function
> button within a form. I have done this many times before from using a
> button
> function within a form, but for some reason when trying to create the
> import
> button function from the on-click demand this time around I seem to be
> failing at the first hurdle and I don't understand why. When I try to run
> the
> button function I get the following error message "User - Defined
> type
> not Defined"! I have experience with VB code but it's not extensive. I
> have
> posted the beginning of my code to see if anyone can help me out....If you
> could it would be greatly appreciated Thanks
>
> Private Sub ImportMonthlyMBD_Click()
> On Error GoTo Err_ImportMonthlyMBD_Click
>
> Dim sSQL As String 'The SQL String To Be Executed
> Dim nResp As Integer
>
> Dim rs As DAO.Recordset 'The DAO recordset
> Dim rs1 As DAO.Recordset 'The DAO recordset
> Dim rs2 As DAO.Recordset 'The DAO recordset
>
> Set myDB = CurrentDb 'Use this database
> Set rs = Nothing 'Tidy up the recordset
> Set rs1 = Nothing 'Tidy up the recordset
> Set rs2 = Nothing 'Tidy up the recordset
>
> sSQL = ""
>
> dDate = Date
> sDate = Format(dDate, "dd-mmm-yyyy")
>
> 'Import all Data from the Alldata File
>
> nResp = MsgBox("Do you wish to Import the Monthly Data?", vbYesNo)
>
> If nResp = vbYes Then
>
> 'Clear out the old data from the DailyTempTable First
> DoCmd.SetWarnings False
> sSQL = "delete * from DailyTempTable;"
> DoCmd.RunSQL (sSQL)
>
> sFileName = Me![MBDMonthlyDatePathname]
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
> "DailyTempTable", sFileName, True
>
> 'First of all - Put all the records with a blank Title & blank First
> Name fields into the DailyJobsExceptions Table
>
> sSQL = "insert into DailyJobsExceptions select * from DailyTempTable
> where isnull(DailyTempTable.[Title])And where isnull(DailyTempTable.[First
> Name]);"
> DoCmd.RunSQL (sSQL)
>
> sSQL = "delete from DailyTempTable where isnull(DailyTempTable.[Title])
> And where isnull(DailyTempTable.[First Name]);"
> DoCmd.RunSQL (sSQL)
>
> sSQLnextval = "select max(MainTable.RefNo) from MainTable;"
> Set rs = CurrentDb.OpenRecordset(sSQLnextval)
>
> If Not rs.EOF Then
> nNextRefNo = rs.Fields(0) + 1
> Else
> nNextRefNo = 1
> End If
> Set rs = Nothing 'Tidy up the recordset
>
>
|
Similar Threads
