By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,474 Members | 3,214 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,474 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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

Nov 12 '05 #2

P: n/a
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!
Nov 12 '05 #3

P: n/a
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.



Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.