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!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/200711/1

[NevilleT]

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 news:7aee9c48822e6@uwe,

tg112001 via AccessMonster.com <u34664@uwe> wrote:
> 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
(') ).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

[tg112001 via AccessMonster.com]

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

Dirk Goldgar wrote:
>> 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
>(') ).
>

--
Message posted via http://www.accessmonster.com

[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

Dirk Goldgar wrote:
>> 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 news:7afba4f502a6a@uwe,

tg112001 via AccessMonster.com <u34664@uwe> wrote:

> 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!

Dirk Goldgar wrote:
>> 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.
>

--