Creating a table from a form, is it possible ??

[harrisharris]

HI,

I want to create a table from a form, and pass user inputed value at the same time. Can anybody help ? Thanks in advance !

Cheers

Chris

 

[Mongooses]

Not sure I understand what you are attempting to do. Do you want to open a form and have a table created with the fields from the form and then pass the values from the form to the table? Without really understanding what you are attempting - here's how you can create a table quite easily

Function CreateNewTable()
On Error GoTo ErrorHandler
Dim dbDatabaseName As Database
Dim tdTableName As TableDef
Set dbDatabaseName = CurrentDb ('assumes you want the table in the same database)
Set tdTableName = CurrentDb.CreateTableDef("Table Name&quot

'add fields
tdTableName.Fields.Append .CreateField([Name], [type],[size])

'add table to database
dbDatabase.TableDefs.Append tdTable

dbDatabase.Close

ErrorHandler:
'Your error handler code
' err# 3010 = table already exists
End Function

HTH

 

[harrisharris]

Thanks very much, but I still need some help

1. Where do I insert this code (I am stilla bit of a beginner on Access

and

2. How do I allocate a name to each new table created from the form?

Many thanks again in advance

 

[Mongooses]

How many times is this form going to be used - i can envision a database with hundreds of tables if this form gets used often. However to answer your question - you can put the code just about anywhere - you might want to have the table created when the form loads (probably not a good idea in case the user changes his/her mind about filling it out) I suggest you place the code under a button. So that when the user finishes filling out the form s/he clicks a button to submit the data and the table is created then the information is written to the table. You place a button on the form then under the event property "On Click" you click the button with the three dots and then add the above code except for the first line (Function CreateNewTable). As for the naming convention - you will need to devise a method to give each table a unique name - you could possible use a date&time or if there is a name field on the form might want to use Name+Date/Time. If you go with the date & time it look something like this:
Dim strTableName as String
strTableName = Date & Time
Set tdTableName = CurrentDb.CreateTableDef(strTableName)
After the table is created you will need to write the data from the form to the table.
HTH
JC

 

[blondends]

Thank you very much for that! You have gotten me out of sticky patch !

Cheers,

Chris

 

[blondends]

Um, sorry to pick your brains again... but, i have tried to insert this code under the button in the code editor. The only problem is that it will not work. The variable declaration bit is the problem... :

Dim dbDatabaseName As Database

Am i suposed to change any of this ?? But it seems to not recognise Database as a variable type ( i think that is right )

Sorry to be a pain,

Chris

 

[Mongooses]

Sorry - you must be using Access 2000. Try changing:
Dim dbDatabaseName As Database
Dim tdTableName As TableDef
to
Dim dbDatabaseName As Variant
Dim tdTableName As Variant

If that doesn't work let me know and I will give you a rewrite for the code.

HTH
JC

 

[blondends]

Cheers,

I will try that !

Chris

 

[blondends]

Right, i have tried that and the problem now is:

tdTableName.Fields.Append .CreateField([Name], [type], [size])

This comes up with a syntax error. I tried deleting the space between Apepend and .CreatField but that didn't work, just came up with the same problem. i tried deleting the '.' before the CreateField and that said that i didn't declair the function or something like that. What should the line look like if i was to write it myself ? for example, am i suposed to have changed the [name] for the name i want the field to be ? if so how do i write it in ?

Cheers

Chris

 

[Mongooses]

Ok Chris - lets say I wanted a field called Last Name, it is a text field that is 20 chararacters long, in the Table called Employees then the code would be:

Dim tdNewTable as Variant
Set tdTableName = CurrentDb.CreateTableDef("Employees&quot

'add fields to table
tdNewTable.Fields.Append .CreateField("Last Name", vbText, 20)

'add table to database
CurrentDb.TableDefs.Append tdNewTable

If you are running into trouble with the syntax place the cursor on one of the words and hit F1 - this will bring up help and show you the syntax as well as examples. Ok when you get the table created and the correct fields in the table you are going to need to add data. Let me know when you get there but you will need to use the AddNew & Update functions.

HTH
JC

 

[harrisharris]

Thanks, i give that a go right now...

and i thought that access was easy !!!

Chris

 

[harrisharris]

hi,

This line is giving problems, it is asking for a '=' at the end !?

tdNewTable.Fields.Append .CreateField("Last Name", vbText, 20)

it also goes red, i'm not sure if that helps !!?

Chris

 

[Mongooses]

Chris - I know the source of the problem - because you are using Access 2K by default it uses ADO and the code I have been giving you is for DAO - you have two options learn ADO - which I have only just begun or on the menu of the VBA editor go to "Tools", "References" and place a check in the "Microsoft DAO 3.6 Object Library" then use the following code and all will be well. Sorry about taking you down a long winding road.

Dim tdNewTable as TableDef
Set tdTableName = CurrentDb.CreateTableDef("Employees&quot

'add fields to table
tdNewTable.Fields.Append .CreateField("Last Name", vbText, 20)

'add table to database
CurrentDb.TableDefs.Append tdNewTable

HTH,
JC

 

[harrisharris]

Well, i think that it is just not meant to be !

I have add the Microsoft DAO Ref. and now it does recognise the .CreateField part but is still saying it is not a valid syntax.

Thankyou very much for your help, i have learnt alot whilst trying to get this to work. Please don't feel that you have to find a way to do this, i might just tell the shop whose database i am creating that there isn't a chance of it happening and going about it in a different way.

Cheers

Chris


thanks again !!!

 

[RobJDB]

Harrisharris,
Did you ever get this to work? I've got exactly the same problems! As I read down this thread, I was thinking "yes, that happened to me, and then that happened, and then, yes, that too!" but then I found that there was no solution at the end.
I'd be grateful if you could share any further tips with me.

Cheers,

Rob