To create tables with VBA - To name the new table from a mask'txt box

[Amos]

I built the following Sub, in order to create tables automatically.
The button is placed on a mask where there are 3 txt box, two of them
contain the "Bank name" and "Bank a/c". I want to name the new table with a
string composed by the "Bank name"&"Bank a/c", How I could get it?
Using the following Sub I get always the same name "NEWBANKAC".

Any Tip is welcome.

Thank you.

Private Sub cmdADDNEWBANKAC_Click()
On Error GoTo TableErrCatcher
Dim cat1 As ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim str1

'Reference objects for table
Set cat1 = New Catalog
cat1.ActiveConnection = CurrentProject.Connection
Set tbl1 = New Table
'Name table and append columns
With tbl1
.Name = "NEWBANKAC"
.Columns.Append "ID N", adInteger
.Columns.Append "DATE", adDate, 10
.Columns.Append "FIN CODE", adLongVarWChar, 4
.Columns.Append "DESCRIPTION", adVarWChar, 30
.Columns.Append "DEBIT", adDouble
.Columns.Append "CREDIT", adDouble

End With

'Append new table to Tables collection
'and free catalog resource
cat1.Tables.Append tbl1
Set cat1 = Nothing

str1 = NewBankac & "new bank account has been created"
MsgBox str1, vbInformation, "Cash Flow Management"

TableErrCatcher:
If Err.Number = -2147217857 Then
MsgBox "Bank account already existing", vbInformation, "Cash Flow
Management"
End If
Debug.Print Err.Number, Err.Description

End Sub

[rkc]

"Amos" <am**********@hotmail.com> wrote in message
news:Oa**********************@twister1.libero.it.. .

I built the following Sub, in order to create tables automatically.
The button is placed on a mask where there are 3 txt box, two of them
contain the "Bank name" and "Bank a/c". I want to name the new table with a string composed by the "Bank name"&"Bank a/c", How I could get it?
Using the following Sub I get always the same name "NEWBANKAC".

Any Tip is welcome.

Re-think why you are creating a new table for each bank account instead
of adding a row to a BankAccounts table.

On to your question. You always get the same name because there is
nothing in 'your' code that ever changes the name.

This line: .Name = "NEWBANKAC"
Should be something like .Name = Me.txtBankName & Me.txtBankAcc

[Amos Marzuoli]

Thank you very much, now it works properly.
One thing more:
the table name is composed by a text box & combo box,
text box is the Bank a/c
combo box is the Currency (EU and USD)

Ex: text box = 123
combo box = EU

result is 1231, where 1 correspond to EU.

How I could get 123EU?

Thank you very much.
Amos



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[rkc]

"Amos Marzuoli" <ri***********@yahoo.com> wrote in message
news:40*********************@news.frii.net...

Thank you very much, now it works properly.
One thing more:
the table name is composed by a text box & combo box,
text box is the Bank a/c
combo box is the Currency (EU and USD)

Ex: text box = 123
combo box = EU

result is 1231, where 1 correspond to EU.

How I could get 123EU?

Thank you very much.

Probably by going into the properties sheet of the combobox and
setting the bound column to the column that holds the text instead
of the column that holds the numeric key.

[Pieter Linden]

Amos Marzuoli <ri***********@yahoo.com> wrote in message news:<40*********************@news.frii.net>...

Thank you very much, now it works properly.
One thing more:
the table name is composed by a text box & combo box,
text box is the Bank a/c
combo box is the Currency (EU and USD)

Ex: text box = 123
combo box = EU

result is 1231, where 1 correspond to EU.

How I could get 123EU?

Thank you very much.
Amos

is there a reason you don't store the two as separate fields? Something like:

CurrencyType: (text)
Amount: 123