Dim rs As DAO.Recordset Error

[Bateman28]

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

 

[Jeanette Cunningham]

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

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