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

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Amos
    #1

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

    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.ActiveConn ection = 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 "DESCRIPTIO N", adVarWChar, 30
    .Columns.Append "DEBIT", adDouble
    .Columns.Append "CREDIT", adDouble

    End With

    'Append new table to Tables collection
    'and free catalog resource
    cat1.Tables.App end 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


    Tags: None
    • rkc
      #2
      Re: To create tables with VBA - To name the new table from a mask'txt box


      "Amos" <amosmarzuoli@h otmail.com> wrote in message
      news:OaAbc.1090 81$FJ6.4024920@ twister1.libero .it...[color=blue]
      > 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[/color]
      a[color=blue]
      > 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.[/color]

      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



        Comment

        • Amos Marzuoli
          #3
          Re: To create tables with VBA - To name the new table from a mask'txt box

          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!

            Comment

            • rkc
              #4
              Re: To create tables with VBA - To name the new table from a mask'txt box


              "Amos Marzuoli" <ricoveriamoci@ yahoo.com> wrote in message
              news:406ef65f$0 $200$75868355@n ews.frii.net...[color=blue]
              > 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.[/color]

              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.







                Comment

                • Pieter Linden
                  #5
                  Re: To create tables with VBA - To name the new table from a mask'txt box

                  Amos Marzuoli <ricoveriamoci@ yahoo.com> wrote in message news:<406ef65f$ 0$200$75868355@ news.frii.net>. ..[color=blue]
                  > 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[/color]
                  is there a reason you don't store the two as separate fields? Something like:

                  CurrencyType: (text)
                  Amount: 123

                    Comment

                    Previous template Next
                    Working...