?
Solved

add field function in access

Posted on 2013-02-02
3
Medium Priority
?
387 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 2000 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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

762 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