Make a table with VBA (Access 2003)

[tg112001 via AccessMonster.com]

Hello,

I have a form with a combo box (named Combo1) and a command button. The
purpose of the form is to make a new table based on the combo box value after
clicking the command button. Can someone help me with the proper syntax?

The recordset should look like:

SELECT * FROM tbl1 WHERE field1 = Combo1.value

But I don't know how to use that recordset to make a new table. Can someone
help me with the correct VBA syntax to make this happen?

Thanks!

 

[Guest]

Hi
Use something like this

Dim dbs as database
Dim tdfNew as tabledef
Dim strSQL as String
Dim lngFirst as Long
Dim lngSecond as Long

lngFirst = rst!FirstValue
lngSecond = rst!SecondValue

Set dbs = CurrentDb
Set tdfNew = dbs.CreateTableDef("tblNew") ' Set the table name

With tdfNew
.Fields.Append .CreateField("Field1", dbLong) ' Create the
field
.Fields.Append .CreateField("Field2", dbLong) ' Create
the field
dbs.TableDefs.Append tdfNew ' Append
the table

Set idxNew = .CreateIndex("NumIndex")
idxNew.Fields.Append idxNew.CreateField("Field1")
idxNew.Primary = True
.Indexes.Append idxNew
End With

strSQL = "INSERT into tblNew (Field1, Field2) VALUES( " & lngFirst &
", " & lngSecond & ";"

Set qdf = dbs.CreateQueryDef("", strSQL) ' Create
new QueryDef.
qdf.Execute ' Run
the insert query

 

[Dirk Goldgar]

In

Hello,

I have a form with a combo box (named Combo1) and a command button.
The purpose of the form is to make a new table based on the combo box
value after clicking the command button. Can someone help me with
the proper syntax?

The recordset should look like:

SELECT * FROM tbl1 WHERE field1 = Combo1.value

But I don't know how to use that recordset to make a new table. Can
someone help me with the correct VBA syntax to make this happen?

Here's a pretty simple way:

CurrentDb.Execute _
"SELECT * INTO NewTableName FROM tbl1 " & _
"WHERE field1 = " & Combo1.value,
dbFailOnError

If field1 is a text field, use

"WHERE field1 = '" & Combo1.value & "'",

(assuming the combo's value won't contain the single-quote character
(') ).

 

[tg112001 via AccessMonster.com]

Thanks I'll give it a try and will let you know!

Hello,

[quoted text clipped - 9 lines]

But I don't know how to use that recordset to make a new table. Can
someone help me with the correct VBA syntax to make this happen?

Here's a pretty simple way:

CurrentDb.Execute _
"SELECT * INTO NewTableName FROM tbl1 " & _
"WHERE field1 = " & Combo1.value,
dbFailOnError

If field1 is a text field, use

"WHERE field1 = '" & Combo1.value & "'",

(assuming the combo's value won't contain the single-quote character
(') ).

 

[tg112001 via AccessMonster.com]

Hey Dirk,

The code almost works! I probably should have told you more about tbl1.

tbl1 resides in a seperate backend .mdb which holds the data on a network
server.
The frontend .mdb (where this form resides) has a link to tbl1

So it seems that the code partially works. When the code executes, it
creates the table structure with correct data types, etc., but doesn't bring
in the data.

Anything that you can suggest??

Tom

Hello,

[quoted text clipped - 9 lines]

But I don't know how to use that recordset to make a new table. Can
someone help me with the correct VBA syntax to make this happen?

Here's a pretty simple way:

CurrentDb.Execute _
"SELECT * INTO NewTableName FROM tbl1 " & _
"WHERE field1 = " & Combo1.value,
dbFailOnError

If field1 is a text field, use

"WHERE field1 = '" & Combo1.value & "'",

(assuming the combo's value won't contain the single-quote character
(') ).

 

[Dirk Goldgar]

In

Hey Dirk,

The code almost works! I probably should have told you more about
tbl1.

tbl1 resides in a seperate backend .mdb which holds the data on a
network server.
The frontend .mdb (where this form resides) has a link to tbl1

So it seems that the code partially works. When the code executes, it
creates the table structure with correct data types, etc., but
doesn't bring in the data.

Anything that you can suggest??

Tom

That suggests that the WHERE clause of your query didn't return any
records. Check the value of Combo1, taking into account the possibility
that the combo is displaying one column of its rowsource but bound to
another. Make sure that there really are records in tbl1 that have that
value in field1. Check the data type of field1 to make sure you use
quotes or don't use them, appropriately, in building the SQL statement.

 

[tg112001 via AccessMonster.com]

Dirk,
Thanks for your help. I got finally got it to work. It was the field name
that was causing the problems!

Hey Dirk,

[quoted text clipped - 12 lines]

Tom

That suggests that the WHERE clause of your query didn't return any
records. Check the value of Combo1, taking into account the possibility
that the combo is displaying one column of its rowsource but bound to
another. Make sure that there really are records in tbl1 that have that
value in field1. Check the data type of field1 to make sure you use
quotes or don't use them, appropriately, in building the SQL statement.