Creating a table using VBA code 1

[WarrenB]

Hi there,
Please could someone explain to me how I would create a table using purely vba code. Let's say I want 4 fields in it: f1, f2, f3 and f4, with f1 as the primary key, all of type text. How would I go about it? Thanks in advance. =================
Warren Brown
wazzer@btinternet.com
=================

 

[CPUburn]

hi,

try this one:

dim fld_f as field
dim str_sql as string

str_sql = "CREATE TABLE mytable (f1 COUNTER CONSTRAINT PrimaryKey PRIMARY KEY);" '' create and append first field

currentdb.Execute str_sql

currentdb.TableDefs.Refresh

Set fld_f = currentdb.TableDefs("mytable&quot.CreateField("f2", dbText, 255) ' create 2nd,... fields

currentdb.TableDefs("mytable&quot.Fields.Append fld_f
' don't forget to append fields you create

grtz

CPU-burn

 

[WarrenB]

thanks a lot, just one more thing though. I want the table to be called whatever has been put into "textbox1".

Then I go
PassVar = textbox1.text
Call TableGen(PassVar)

and pass to

Public Function TableGen(ByRef RecVar as integer)
(above code)
End Function

but I get the error "Can't reference a property or method for a control unless it has a focus".

Can someone help me here please, thanks. =================
Warren Brown
wazzer@btinternet.com
=================

 

[CPUburn]

hi

the .text property of a textbox is indeed only available when that control has the focus, just lose it:

PassVar = textbox1

CPU-burn

 

[WarrenB]

Sorry this thread seems to go on and on but now i've put what you put it says "Invalid use of Null" =================
Warren Brown
wazzer@btinternet.com
=================

 

[jhowley58]

hey warren,

try accessing the value of a textbox(ie the text) by using the control name ie

passvar = textbox1.. that should get you the text without having to focus into the textbox..

John

<Fools don't suffer me Gladly>

 

[WarrenB]

Problem is though it says &quot;Invalid use of Null&quot; =================
Warren Brown
wazzer@btinternet.com
=================

 

[CPUburn]

at what line is the error occuring (could you include the code you have up to now?)

btw: you pass the variable into your function as an integer, you'd better use text i think; in that case almost anything will do, otherwise if the user does not input an integer in the text box you've got a problem..

grtz

CPU-burn

 

[WarrenB]

Here's the code so far:

Private Sub cmdOpenAccount_Click()
Dim PassVar As String

DoCmd.GoToRecord , , acNewRec
PassVar = txtAccNum ''Error comes here (on mouse over says &quot;txtAccNum = Null&quot
Call TableGen(PassVar)

End Sub =================
Warren Brown
wazzer@btinternet.com
=================

 

[CPUburn]

only if your textbox is empty this can occur. in that case you can put

PassVar = nz(txtAccNum, &quot;nameyouwantiftextisempty&quot

cpuburn

 

[WarrenB]

problem is though the textbox isn't empty and the names match, do i need to put some identifier before it, like it standard vb you could put:

passvar = form1!textbox1.text ? =================
Warren Brown
wazzer@btinternet.com
=================

 

[CPUburn]

phoewa it's getting tricky.. form1!textbox1 should do, also me!textbox1 if the code is in the module for form1 itself, but i just noticed something about your code: the line before you set passvar = textbox1 you execute:
DoCmd.GoToRecord , , acNewRec

--> textbox1 isn't a bound textbox i hope? if so, it &quot;loses&quot; its value if you go to a new record, if not you can send me this particular part of the db if you wish to, 'cause it's getting intriguing

grtz

cpuburn

 

[WarrenB]

sorted, you're right it was a bound text box. ok next problem sorry I slightly modified your code like so:

Public Function Table_Gen(ByRef RecVar As String)
MsgBox RecVar
Dim fld_a As Field
Dim fld_b As Field
Dim fld_c As Field
Dim str_sql As String

str_sql = &quot;CREATE TABLE &quot; & RecVar & &quot; (f1 COUNTER CONSTRAINT PrimaryKey PRIMARY KEY);&quot; '' create and append first field

CurrentDb.Execute str_sql
CurrentDb.TableDefs.Refresh
Set fld_a = CurrentDb.TableDefs(RecVar).CreateField(&quot;f2&quot;, dbText, 255) ''ERROR HERE
Set fld_b = CurrentDb.TableDefs(RecVar).CreateField(&quot;f3&quot;, dbText, 255)
Set fld_c = CurrentDb.TableDefs(RecVar).CreateField(&quot;f4&quot;, dbText, 255)
CurrentDb.TableDefs(RecVar).Fields.Append fld_a
CurrentDb.TableDefs(RecVar).Fields.Append fld_b
CurrentDb.TableDefs(RecVar).Fields.Append fld_c

End Function

and I get data type conversion error, any ideas? (Sorry about this going on and on) =================
Warren Brown
wazzer@btinternet.com
=================