Access: VBA to create table fields from record values

[arcaidius]

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
Length	Decimal	Yes/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.

 

[arcaidius]

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

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]

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]

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?

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]

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]

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.