Solved

add field function in access

Posted on 2013-02-02
3
352 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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now