Create a Table with Dynamic Field Names...

You need to create an UNnormalzed table with, let's say, 5 (five) rotating years worth of data (see bolded section)... either to export to Microsoft Excel or just for review.  For this scenario, you want to review all your Clients' Service Calls and whether they were Under Contract or not for the past 5 years.  Whatever your reason the issue is not creating the table it's APPENDING the data.

1.  Create your table...  You can call from a button with Call MakeFYDTable
2.  To APPEND and UPDATE Data to a Table created Dynamically click here.
You need to add your own Error Handling code!
The difficult I do immediately, the impossible takes a little bit longer.
Function MakeFYDTable()
'From http://www.access-diva.com/
 
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
'Initialize the table.
Set db = CurrentDb()
Set tdf = db.CreateTableDef("tblFiveYearsData")
 
'Specify the fields.
With tdf
    Set fld = .CreateField("AccountNumber", dbText, 50)
    .Fields.Append fld
    .Fields.Append .CreateField("PrimaryName", dbText, 100)
    .Fields.Append .CreateField("DBA", dbText, 100)
    .Fields.Append .CreateField("Address1", dbText, 50)
    .Fields.Append .CreateField("Address2", dbText, 50)
    .Fields.Append .CreateField("CityName", dbText, 50)
    .Fields.Append .CreateField("StateID", dbText, 2)
    .Fields.Append .CreateField("ZipCode", dbText, 10)
    .Fields.Append .CreateField("PhoneAreaCode", dbLong)
    .Fields.Append .CreateField("PhoneNumber", dbText, 8)
    .Fields.Append .CreateField("PhoneExtension", dbText, 10)
    .Fields.Append .CreateField("FirstName", dbText, 50)
    .Fields.Append .CreateField("LastName", dbText, 50)
    .Fields.Append .CreateField("County", dbText, 25)
    .Fields.Append .CreateField(Year(Date) & "-ServiceCalls", dbDouble)
    .Fields.Append .CreateField(Year(Date) - 1 & "-ServiceCalls ", dbDouble)
    .Fields.Append .CreateField(Year(Date) - 2 & "-ServiceCalls", dbDouble)
    .Fields.Append .CreateField(Year(Date) - 3 & "-ServiceCalls", dbDouble)
    .Fields.Append .CreateField(Year(Date) - 4 & "-ServiceCalls", dbDouble)
    .Fields.Append .CreateField(Year(Date) & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 1 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 2 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 3 & "-OnContract", dbBoolean)
    .Fields.Append .CreateField(Year(Date) - 4 & "-OnContract", dbBoolean)
End With
 
db.TableDefs.Append tdf
Set fld = Nothing
Set tdf = Nothing
 
'Clean up
Application.RefreshDatabaseWindow 'Show the changes
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
 
End Function 
VBA
Tips (Main)
Home
Creating a Multi-Value field using Alphabet
Copy Fields Down from above Record
Loop thru records and OutPutTo seperate .RTF or .PDF
Modified Spell Check
Code Snippets
Lock\Unlock Bound Controls
Loop while renumbering two columns
Snippets for Exporting to Excel
Log Field Changes
Log Record Deletions
Check for Duplicate Values
ValidateData()
ClearClipboard()
Selecting an Excel Worksheet from Access
Send eMail to Multiple Recipients
Cancel Save in a Bound Form
Automatically Send eMail Notifications
fFindBookmark()
Looping Records to Send eMail
fxlFindReplace()
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.