When renaming a table I get an error message

[DogLover2006]

I have a form with a subform TempFederal on it and a button on it. On click of the button it creates a textbox and lable on the subform, it creates a table called NewYears and it also creates a new field in NewYears table. I also want the but to rename the table to Years, which is the table that the subform uses. But when I run my code I get the following error message:

Microsoft Jet database engine could not find the object. make sure the object exists and that you spelled it name and path name correct.

Here is my code:
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryCreateNewTable", acViewNormal
DoCmd.SetWarnings True

Call UpdateTable
DoCmd.RunMacro "RenameTable"

Dim frm As Form, ctlDefault As Control, ctlNew As Control
Dim intLtxt1 As Integer, intLeft, intTop1, intTop2, intNum, intSum, intNewLeft, intName
intLeft = 8760
intTop1 = 1140
intTop2 = 720
intSum = 10440 - 8760
intNum = intLeft + intSum
intLtxt1 = intNum
intNewLeft = intLeft + intSum + intSum
intName = Year(Now()) + 1

DoCmd.OpenForm "Temp_Federal", acDesign
Set frm = Forms!Temp_Federal

' Return Control object representing default Text box.
Set ctlDefault = frm.DefaultControl(acTextBox)
' Set some default properties.
With ctlDefault
.FontWeight = 700
.FontSize = 12
.Width = 1440
.Height = 300
.ControlSource = dbsTempFederal.TableDefs!Years!Fields(strName)
'dbsTempFederal.TableDefs!Years!Field(strName)
End With
' Create new text box.
Set ctlNew = CreateControl(frm.Name, acTextBox, , , , intLtxt1, intTop1)
' Set control's caption.
ctlNew.Name = intName

Set ctlDefault = frm.DefaultControl(acLabel)
' Set some default properties.
With ctlDefault
.FontWeight = 700
.FontSize = 12
.Width = 1440
.Height = 300
.TextAlign = 2
End With
' Create new lable.
Set ctlNew = CreateControl(frm.Name, acLabel, , , , intLtxt1, intTop2)
' Set control's caption.
ctlNew.Caption = intName
' Restore form.
DoCmd.Restore
DoCmd.RunMacro "saveform" 'this just close the form and subform and opens the form backup

Public Sub UpdateTable()
Dim dbsTempFederal As Database
Dim tdfNew As TableDef
Dim intName As Integer
Dim strName As String
intName = Year(Now()) + 1
strName = intName

Set dbsTempFederal = CurrentDb
' Create a new TableDef object.
Set tdfNew = dbsTempFederal.TableDefs!NewYears

' Add one new fields.

With tdfNew
.Fields.Append .CreateField(strName, dbDouble)
End With

dbsTempFederal.Close
DoCmd.Restore

End Sub


Please help and thank for your help

 

[Steve3110]

Hi

Which line do you get the error message on?

 

[DogLover2006]

Steve3110,

I gives me the error when it runs the DoCmd.RunMacro "RenameTable"

 

[Steve3110]

What exactly does that macro consist of?

Are you sure that the souce table to rename is spelt correctly?

as i'm sure you can understand its a bit hard to fix code when its in a macro i cant see.

 

[DogLover2006]

Steve3110,

I know this work becaue if I just run the qryCreateNewTable and then go to the macros and run it, It works but when the form tell it to run it just does not work and I get the error message.

The "qryCreateNewTable" make the table called NewYears

The macros has the following in it
setWarning = no
Rename = New Name = Years
Object Type = Table
Old Name = NewYears
setWarning = yes

 

[Steve3110]

Hey

Sorry i'm struggling to think of a solution. You could try replacing the code that runs the macro with:

docmd.Rename "Years", acTable, "NewYears"

That should do the same thing that the macro was doing but when it comes up to debug you should be able to debug easier.

 

[DogLover2006]

Steve3110,

I did that too but still get the message.

 

[Steve3110]

i'm at a loss then to be honest. If your positive that there is a table called "NewYears" when the macro is run, then i dont know what to suggest

 

[DogLover2006]

Steve3110,

Thank you for all your help. I will just have to think of another way around it.

 

[Remou]

Is it possible that the Make Table query has not completed? Is it creating a large table?

 

[jebry]

Hi!

Is the table, or a query based on the table, the recordsource of the form running the code. If so that would interfere with renaming the table.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry,

Yes it is

 

[jebry]

Hi!

To rename a table, you must save it and you can't save it if it is open or if a form or query using it are open. Maybe you can do it from another form?



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Remou]

Or set the Record Source of the form to something else before renaming.

 

[DogLover2006]

jebry & Remou,

Thank you for your help