Defined Type not Defined

[Mona]

I am using Access 2000 and I am getting the following message: user defined type not defined, for the following 2 lines of code:

Dim dbs As Database
Dim tdf As TableDef


I am trying to create 1 large report that gives an agency's name as the form/report page header and a list of Names, SubAgency, and Email on the body/detail of the page.
I want to break up the large report into smaller reports based on each agency and I want create a file for each report.
I generated the following code but that was suggested by someone else but I cannot get it to work. I have very little VB experience. Any suggestions on how to procede or why I am getting this message?

Public Sub PrintAgencyReport()

Dim dbs As Database
Dim tdf As TableDef
Dim rstAgency As Recordset
Dim strWhere As String
Dim strReportName As String
Dim strFileName As String
Dim rstTestForRecords As Recordset
Dim strWhereTest As String
' Return reference to current database
Set dbs = CurrentDb

' Return reference to Agency table
Set tdf = dbs.TableDefs!Table1

' Open the AgencyList table containing agencies
Set rstAgency = dbs.OpenRecordset("Table1")

' Loop through AgencyList table to get the agency _
parameter for the report and path to send report

With rstAgency
.MoveFirst
Do Until .EOF
strReportName = "Report1"
strWhere = "[Agency]='" & ![Agency] & "'"
strFileName = "c:\FDLEfiles" & ![Agency] & "Report1.html"
strWhereTest = "Select * From qryReport1 Where" & strWhere
Set rstTestForRecords = dbs.OpenRecordset(strWhereTest)
If rstTestForRecords.BOF And rstTestForRecords.EOF Then
Else
DoCmd.OpenReport strReportName, acViewPreview, , strWhere
DoCmd.OutputTo acOutputReport, strReportName, "HTML(*.html)", strFileName
DoCmd.Close
End If
.MoveNext
Loop
.Close
End With
End Sub

 

[Mona]

Why can't I declare these statements as I did?

 

[josephyeo]

You are not alone, Mona. I have the same problem. The same program with the same code works and suddenly it refuses to do so giving me the same error message as yours. I think possibly, you and I have copied or transferred the program from one computer to another as I have been doing - from office to home and vice versa. Does anyone else experience this??

The solution I found was to use the code or form of one that works and change it round.

 

[R. Hicks]

Try changing all the following:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rstAgency As DAO.Recordset
Dim strWhere As String
Dim strReportName As String
Dim strFileName As String
Dim rstTestForRecords As DAO.Recordset
Dim strWhereTest As String
' Return reference to current database
Set dbs = CurrentDb

I don't use Access 2000, but I know you must set the references different in Access 2000 since the addition of ADO.

HTH
RDH




[This message has been edited by R. Hicks (edited 01-21-2001).]