Problem converting VB Code from Access 2.0 to Access 2003

  • Thread starter Thread starter John Halloran
  • Start date Start date
J

John Halloran

When attempting to convert a database to 2003 I get a
message unable to compile. When I then try to print a
label using a macro mcrPrintLabel it states unable to
find function.

The Arboretum Foundation has used the macro which opens a
query to create a table called TempTable and then calls
for VBA with the line "RunCode" calling for function
Print_Label(). The function is as follows:

Function Print_Label()

' This sets up using the tblTempTable with a primary
key.

Dim MYDB As Database, tblMasterTable As Table,
tblLabelTable As Table
Set MYDB = CurrentDb()
Set tblMasterTable = MYDB.OpenTable("tblTemp")
Set tblLabelTable = MYDB.OpenTable("tblLabel")
tblMasterTable.Index = PRIMARYKEY

' Goto the first record of the table.
tblMasterTable.MoveFirst

' Read the record.
Do Until tblMasterTable.EOF
tblMasterTable.Edit
X = tblMasterTable("NumberOfLabels")

' Copy the record a certain number of times
For J = 1 To X
tblLabelTable.AddNew

tblLabelTable("Vendor") = tblMasterTable
("Vendor")
tblLabelTable("Department") =
tblMasterTable("Department")
tblLabelTable("Classification") =
tblMasterTable("Classification")
tblLabelTable("Name") = tblMasterTable
("Name")
tblLabelTable("BloomTime") =
tblMasterTable("BloomTime")
tblLabelTable("Tallness") = tblMasterTable
("Tallness")

tblLabelTable("Color") = tblMasterTable
("Color")
tblLabelTable("SellPrice") =
tblMasterTable("SellPrice")
tblLabelTable("BulbsInBag") =
tblMasterTable("BulbsInBag")

tblLabelTable.Update

Next J

' Go back to the first table and get the next record.
tblMasterTable.MoveNext
Loop

' Print the report for the temp table.
DoCmd.OpenReport "rptLabels3Across", A_PREVIEW


' Delete the temp table.
tblMasterTable.Close

tblLabelTable.MoveFirst

' Delete each record in a loop
Do Until tblLabelTable.EOF
tblLabelTable.Delete
tblLabelTable.MoveNext
Loop

' Close the tblLabel Table
tblLabelTable.Close

End Function

Any help would be appreciated.
 
first check that you have DAO3.6 in references (menu tools-references).
then try to compily all, check what compiler says you exactly
 
The reference to DAO3.6 is checked. The message when try
to run the function is "user-defined type not defined"
with the line ", tblMasterTable As Table" highlighted.
John
 
John, try this

Function Print_Label(

' This sets up using the tblTempTable with a primary key

' Changes to cod
Dim MYDB As DAO.Databas
Dim tblMasterTable As DAO.TableDe
Dim tblLabelTable As DAO.TableDe
Set MYDB = CurrentDb(
Set tblMasterTable = MYDB.OpenRecordset("tblTemp", dbOpenTable
Set tblLabelTable = MYDB.OpenRecordset("tblLabel", dbOpenTable

' End of change

tblMasterTable.Index = PRIMARYKE

' Goto the first record of the table
tblMasterTable.MoveFirs

' Read the record
Do Until tblMasterTable.EO
tblMasterTable.Edi
x = tblMasterTable("NumberOfLabels"

' Copy the record a certain number of time
For J = 1 To
tblLabelTable.AddNe

tblLabelTable("Vendor") = tblMasterTable("Vendor"
tblLabelTable("Department") = tblMasterTable("Department"
tblLabelTable("Classification") = tblMasterTable("Classification"
tblLabelTable("Name") = tblMasterTable("Name"
tblLabelTable("BloomTime") = tblMasterTable("BloomTime"
tblLabelTable("Tallness") = tblMasterTable("Tallness"
tblLabelTable("Color") = tblMasterTable("Color"
tblLabelTable("SellPrice") = tblMasterTable("SellPrice"
tblLabelTable("BulbsInBag") = tblMasterTable("BulbsInBag"
tblLabelTable.Updat

Next

' Go back to the first table and get the next record
tblMasterTable.MoveNex
Loo

' Print the report for the temp table
DoCmd.OpenReport "rptLabels3Across", A_PREVIE

' Delete the temp table
tblMasterTable.Clos

tblLabelTable.MoveFirs

' Delete each record in a loo
Do Until tblLabelTable.EO
tblLabelTable.Delet
tblLabelTable.MoveNex
Loo

' Close the tblLabel Tabl
tblLabelTable.Clos


End Functio
 
John,
Try explicitly declaring your db and table. If you've got
both DAO and ADO references access can get confused when a
reserved word is common to both.

Dim MYDB As DAO.Database, tblMasterTable As DAO.Table,
tblLabelTable As DAO.Table
 
When I "Dim tblMasterTable As DAO.Table" I get the
message "ompile error User-defined type not defined".
Similarly with tblLabelTable.

John
 
Thanks I finally got past the Dim statements. NOW I get
a "compile error method or data member not found" message
on the line " tblMasterTable.Index = PRIMARYKEY"
John
 
Back
Top