Solved

add field function in access

Posted on 2013-02-02
3
366 Views
Last Modified: 2013-02-02
I found a function already written and want to pass the datatype to as i want to create a table setup routine and have different data types to create.

I tried adding a third variable to pass Called [datatype]  and substituted but it fails error 3421 datatype not found and bombs out on the error handler.

help would be appreciated

I found the original code at http://www.eraserve.com/tutorials/MS_ACCESS_VBA_Create_Field.asp

called function: Dim x As Boolean
x = CreateField("TblExportVinstems", "Seq", "dbText")
x = CreateField("TblExportVinstems", "Otherfieldname", "dbLong")


Function CreateField( _
      ByVal strTableName As String, _
      ByVal strFieldName As String, _
      ByVal strDataTypeName As String
) _
      As Boolean
Set fld = tdf.CreateField(strFieldName, strDataTypeName)


Function CreateField( _
      ByVal strTableName As String, _
      ByVal strFieldName As String) _
      As Boolean

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Creates a Text field, other data types listed
   '
   ' strTableName : Name of table in which to create the field
   '
   ' strFieldName : Name of the new field to add to table
   ' Returns True on success, false otherwise
   '
   'USAGE: CreateField "TABLENAME", "FIELDNAME"

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim fld As DAO.Field
   Dim tdf As DAO.TableDef

   Set Db = Application.CurrentDb
   Set tdf = Db.TableDefs(strTableName)

   ' First create a field with data type = Text
   Set fld = tdf.CreateField(strFieldName, dbText)

   'A few Alternate datatypes: for DAO - Note: The listed Complex data types require
         ' Access 2007 or higher
   'Long = dbLong or dbComplexLong
   'Single = dbSingle or dbComplexSingle
   'Double = dbDouble or dbComplexDouble
   'Integer = dbInteger
   'Decimal = dbDecimal or dbComplexDecimal
   'Text = dbText or dbComplexText
   'Memo = dbMemo
   'Currency = dbCurrency
   'Yes/No = dbBoolean
   'Date = dbDate

   ' Appending the field
   With tdf.Fields
      .Append fld
      .Refresh
   End With
   CreateField = True

ExitHere:
   Set fld = Nothing
   Set tdf = Nothing
   Set Db = Nothing

   MsgBox "Create Field Complete"
   Exit Function

errhandler:
   CreateField = False

   With Err
      MsgBox "Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "CreateField"
   End With

   Resume ExitHere

End Function

Open in new window

0
Comment
Question by:PeterBaileyUk
  • 2
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 38846458
You need to pass the datatypes as constants not as strings...

x = CreateField("sheet1aa", "Seq", dbText)
x = CreateField("sheet1aa", "Otherfieldname", dbLong)

and this means that the parameter should be a variant not a string

ByVal strDataTypeName
0
 

Author Comment

by:PeterBaileyUk
ID: 38846469
perfect is there a way of testing if the fieldname exists when i open the form?
0
 

Author Closing Comment

by:PeterBaileyUk
ID: 38846496
thx
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question