Access: VBA to create table fields from record values

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
90
I need some help with VBA code to create new fields in a Table based on the records from a different table.
So Table1 will have 2 fields, a name field and data type field.
Example:

FieldName FieldType Required
LengthDecimalYes/No

My goal here is that using a form for Table1 to edit names and type of input allowed, and then either use a check box or command button to create that field in Table2 for each record.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
90
I have this code so far, but need help to make it work for each record and use the record values instead of the value "TestField" in the code

VBA Code:
Option Compare Database

Private Sub Needed_AfterUpdate()

    AddFieldToTable

End Sub


Sub AddFieldToTable()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()

    Set tdf = db.TableDefs("Readings")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField("TestField", dbText, 80)
    Debug.Print "Field added."

End Sub
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
90
An alternative I could use is name all the fields Field1, Field2 etc. and make code to display record values as the name in a query?
 

arcaidius

Board Regular
Joined
Dec 4, 2018
Messages
90
Update: Still need some help, I do have code that works if I put a number value in the data type field that represents the DataTypeEnum enumeration (DAO).

Can anyone help me make this code so it loops through all the records instead of me having to enter code for each one?

VBA Code:
Private Sub Command9_Click()

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    
    'Initialize
    Set db = CurrentDb()

    Set tdf = db.TableDefs("Readings")
    
    'Add a field to the table.
    tdf.Fields.Append tdf.CreateField(DLookup("Field", "Sheet1", "ID=1"), DLookup("DataType", "Sheet1", "ID=1"))
    Debug.Print "Field added."

End Sub
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,587
Office Version
  1. 365
Platform
  1. Windows
Since you are still looking for help, I have removed the 'Mark as solution' checkmark otherwise most helpers will not even look at your thread.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
3,786
Office Version
  1. 365
Platform
  1. Windows
you would need to replace hard coded parameters (e.g. "ID=1") with references such as Me.myControlNameHere in order to make the parameter(s) dynamic. This will require you to properly concatenate the expression parts and the references. How this is done depends on what the data types are: strings and dates require delimiters - strings require quote marks (sometimes single quote marks are needed), dates require octothorpes (#) and numbers must not use delimiters. You could research how to concatenate strings/date references as a start. Once you have modified your code and successfully executed it once (if you wanted to do this one at a time using data from form fields), you'll know that your concatenation using form fields was correct. However, if you want code to do this multiple times in one operation, you will need to loop through a recordset and concatenate using the recordset fields instead of form field references, so research how to loop over a recordset. I advise you to step through such code the first time as it's easy to forget to move through the recordset. If you do that and simply run it, you can end up entering a never ending loop.

You might want to bookmark the following because you seem to be using words for names that you should not be using
- Microsoft Access tips: Problem names and reserved words in Access

Also, the fact that you want to modify a table in this manner suggests your table schema is designed like a spreadsheet, and that would not be a good thing. So maybe review database normalization first to see if you shouldn't even be trying to do this at all.
 

Forum statistics

Threads
1,176,255
Messages
5,902,166
Members
434,945
Latest member
ngabrieln

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top