insert values into new table

[aceavl]

hi,
I have a table that holds all the data of any clients and a form for adding
more clients.
i need to create a new table with the name of every client that the user puts
in the field RS,
put the key of the old table into the new one (ID) and then link them.

so far i can make the new table with the ID field, and using variables name
it.
when i try to INSERT INTO the new table the key, it tells me that it can't
find that table, but if go to the table manager and chec there it is created.
I've tried with refresh and such.
anyone can help?

Dim tblName As String
Dim db As Database
Dim tdfNew As TableDef
Dim fldNew As Field
Dim valID As Integer
Dim strSql As String

'- get the name of the new table
tblName = Me.RS.Value

'- get the ID
valID = Me.ID.Value

'- set a reference to the database
Set db = CurrentDb

'- create the table
Set tdfNew = db.CreateTableDef(tblName)

'- add integer field
Set fldNew = tdfNew.CreateField("ID", dbInteger)

'- save the new field
tdfNew.Fields.Append fldNew

'- save the new table design
db.TableDefs.Append tdfNew

'- refresh the tables
db.TableDefs.Refresh

'- reclaim the memory
Set fldNew = Nothing
Set tblNew = Nothing
Set db = Nothing

'- put the sql into a variable
strSql = "INSERT INTO ['" & tblName & "'](ID) VALUES (" & valID & ")"

'--- run the sql
DoCmd.RunSQL strSql

 

[Marshall Barton]

I have a table that holds all the data of any clients and a form for adding
more clients.
i need to create a new table with the name of every client that the user puts
in the field RS,
put the key of the old table into the new one (ID) and then link them.

so far i can make the new table with the ID field, and using variables name
it.
when i try to INSERT INTO the new table the key, it tells me that it can't
find that table, but if go to the table manager and chec there it is created.
I've tried with refresh and such.
anyone can help?

Dim tblName As String
Dim db As Database
Dim tdfNew As TableDef
Dim fldNew As Field
Dim valID As Integer
Dim strSql As String

'- get the name of the new table
tblName = Me.RS.Value

'- get the ID
valID = Me.ID.Value

'- set a reference to the database
Set db = CurrentDb

'- create the table
Set tdfNew = db.CreateTableDef(tblName)

'- add integer field
Set fldNew = tdfNew.CreateField("ID", dbInteger)

'- save the new field
tdfNew.Fields.Append fldNew

'- save the new table design
db.TableDefs.Append tdfNew

'- refresh the tables
db.TableDefs.Refresh

'- reclaim the memory
Set fldNew = Nothing
Set tblNew = Nothing
Set db = Nothing

'- put the sql into a variable
strSql = "INSERT INTO ['" & tblName & "'](ID) VALUES (" & valID & ")"

'--- run the sql
DoCmd.RunSQL strSql

You have extra ' around the table name:

strSql = "INSERT INTO [" & tblName & "] (ID) VALUES (" &
valID & ")"

 

[aceavl via AccessMonster.com]

thank you very much Marshall!!! that solved the problem!

I have a table that holds all the data of any clients and a form for adding
more clients.

[quoted text clipped - 50 lines]

'--- run the sql
DoCmd.RunSQL strSql

You have extra ' around the table name:

strSql = "INSERT INTO [" & tblName & "] (ID) VALUES (" &
valID & ")"

 

[aceavl via AccessMonster.com]

thank you very much Marshall!!! that solved the problem!

I have a table that holds all the data of any clients and a form for adding
more clients.

[quoted text clipped - 50 lines]

'--- run the sql
DoCmd.RunSQL strSql

You have extra ' around the table name:

strSql = "INSERT INTO [" & tblName & "] (ID) VALUES (" &
valID & ")"