Refering to field with variable in sub 1

[MePenguin]

I picked up the code to change table names from TonyJollans:

http://www.tek-tips.com/viewthread.cfm?spid=705&newpid=705&sqid=589110

and have been trying to work it into a module as a sub for changing field names in any table passed to it... with problems.
I keep getting error '3265 - Item not found in this collection'... Seems to be a problem with how I refer to a field with a variable...
Help please! Code follows:

Public Sub AlterFieldName(TableName, OldName, NewName)
'Thanks to TonyJollans on [URL unfurl="true"]www.tek-tips.com[/URL] forum for the original code (dated 20030629)

Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Dim tjProp As DAO.Property

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!(TableName)

Set tjFld = tjTab.Fields!OldName ' THIS IS THE PROBLEM LINE
'if I replace OldName with an actual field name it works...

Set tjProp = tjFld.Properties!Name

tjProp = NewName
tjDb.Close
Set tjProp = Nothing
Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing
End Sub

The sub is called with:

Call AlterFieldName("TConvertedCountsheet", OldFieldName, NewFieldName)

where OldFieldName, NewFieldName are strings.

 

[kweitzel]

Hi MePenguin,

if I understand everything right, you declare the strings "OldFieldName" and "NewFiledName" in the onClick event (or maybe onLoad or so) as a private variable.

You should maybe try to declare them as public.


hope this helps

Klaus

 

[jrbarnett]

Hello,

Replace the line:

tjFld = tjTab.Fields!OldName

with:

tjFld = tjTab.Fields(OldName)

and when you pass the fields to the sub, ensure that the old and new fieldnames are enclosed in quote marks as well as the table.

John

 

[MePenguin]

Klaus - you assume correctly, the variables are declared in an onClick event. But I don't think that should be a problem, since it is the values of the variables that are being passed to the called sub - and they get there. (Making the vars public didn't help)

john's first suggestion gives me a "compile error: Type-declaration character does not match data type" with ".Fields!" on the problem line highlighted.

I'm not entirely sure if understood correctly about the quotation marks, but I've changed the definitions to:

OldFieldName = "'F" & sampleloop + 1 & "'"
NewFieldName = "'" & SampleName(sampleloop) & "'"

so that the passed strings include quotation marks - is that correct? Doesn't work though...

If I replace the problem line with

Set tjFld = tjTab.Fields!F2 'where F2 is known field

then it works...
any more ideas?

 

[MePenguin]

PS. Sorry, meant to say "then it works... for the first field".

 

[MePenguin]

And now I have to apologise to John - you were right with the replacement, I was just too stupid to see that you had taken the '!' out! (!)

Thanks, and have a star!

Phil