Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

add field function in access

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

618 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