[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 935
  • Last Modified:

Access: VB: Make code work: Rename table fields

Whenever I use the following code I get the following error: Runtime error 3191. Cannot define a field more than once. When I move my mouse over j in debug mode, it says j=1.

Will someone help me make it work? (It must work in Access 2000-2003)
               
'Changes table field names
                   
Sub Chgfieldnames

Dim dbs As Database, rst1 As Recordset, j As Integer
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("table1")
                                 
                    rstm.MoveFirst
                                     
                        j = 0
                        With dbs.TableDefs("table2")
                        Do
                           
                            .Fields(j).name = rst1![value]
                             j = j + 1
                             Counter = 50 - 1
                           
                            rst1.MoveNext
                           
                        Loop While (Counter > 0)
                        End With
                               
End Sub                

0
ouestque
Asked:
ouestque
  • 3
  • 3
  • 3
2 Solutions
 
Jim P.Commented:
Are you sure that you don't duplicate values in Table1?
0
 
Rey Obrero (Capricorn1)Commented:
where did you open this recordset

    rstm.MoveFirst



Sub Chgfieldnames

Dim dbs As Database, rst1 As Recordset, j As Integer
Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("table1")
                                 
                    rstm.MoveFirst
                                     
                        j = 0
                        With dbs.TableDefs("table2")
                        Do
                           
                      '      .Fields(j).name = rst1![value]

                          .Fields(j).name = rst1(0)   'you have to specify which field value

                             j = j + 1
                             Counter = 50 - 1
                           
                            rst1.MoveNext
                           
                        Loop While (Counter > 0)
                        End With
                               
End Sub                
0
 
Rey Obrero (Capricorn1)Commented:
or you can be more explicit

.Fields(j).name = rst1("nameOFField")   'nameOFField is the name of field from table1
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ouestqueAuthor Commented:
My mistake cap... I accidently put rstm there when I was troubleshooting code. Pretend it is not there.

JIMPEN: Are you sure that you don't duplicate values in Table1?

What is happening when It says I am duplicating values in table1?
GUESS: Trying to name two fields the same name?
0
 
Rey Obrero (Capricorn1)Commented:
your guess is correct.
did you try the revision i did?
0
 
ouestqueAuthor Commented:
Wait! I figured out what I was doing wrong duh :)
I had a table with field names from 1....256
I was using the above code to rename the fields according to what was under a certain field. The problem was many of the data under this field was numbers. Therefore the code above tried to rename field one 20. (But 20 already exists)



Thanks so much for your help!! I will divide the points between both of you for your help.
0
 
ouestqueAuthor Commented:
Cap: did you try the revision i did?

Yes. Thanks for the revision. Nonetheless the problem is described above.

Thanks again for everyones help
0
 
Jim P.Commented:
That's. And even not having duplicates in Table1, if some other column Table2 already has that name then you will run into the problem.

To find dupes in Table1
------------------------------------------
Select Field_Name, Count(*) As Row_cnt
From Table1
Group by Field_Name
Order By Row_cnt Desc
------------------------------------------

And maybe go through Table2 first and rename them to Field1, Field2, etc before updating them from Table1.
0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now