Solved

add field function in access

Posted on 2013-02-02
3
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

726 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