MS Access/VBA Add Field to Form

I'm trying to write a VBA program to add a new field to a MS Access database table, then to add the field as a text box on a form.  I've figured out how to create the field with the code below, but is it possible to programatically add the field to a form?


Set db = OpenDatabase(sFileName)
Set tdf = db.TableDefs("theTable")
Set fld = tdf.CreateField("NewFieldName", dbText, 255)
tdf.Fields.Append fld
cfg1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TheSloathCommented:
Use the CreateControl procedure
0
mbizupCommented:
>programatically add the field to a form?

Yes... but you can't do this very dynamically (you can't do this while viewing your form in normal view).  You can, through code open a form in design view, add the control, save, and reopen the form in normal view:

Add the following function to a module:

Function addTextBox(strFieldName as string, strFormName as string, strTBName)
    Dim ctl As Control
    DoCmd.OpenForm  strFormName, acDesign, , , , acHidden
    Set ctl = CreateControl(strFormName, acTextBox, acDetail)
    ctl.name = strTBName
    ctl.ControlSource = strFieldName
    Set ctl = Nothing
    DoCmd.Close acForm, strFormName, acSaveYes
    DoCmd.OpenForm strFormName, acNormal, , , , acWindowNormal
   
End Function

Add the function call to your code like this:
....
tdf.Fields.Append fld
AddTextBox fld.Name, "frmYourFormName", "txtYourNewTextBoxName"
0
cfg1980Author Commented:
That works great....except I need it to update a form that is not in the same database.  Essentially I'm trying to build an "updater" file for our users.  They would open my updater file and it would add the field to the form on their Access DB.

So is there a "DoCmd.OpenForm" equivalent that would open the form for editing in another DB?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
Use an access object for the other database and add the databasepath to the parameter list:

Function addTextBox(strFieldName as string, strDatabasePath, strFormName as string, strTBName)
Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase strDatabasePath
objAccess.Visible = True

    Dim ctl As Control
    objAccess.DoCmd.OpenForm  strFormName, acDesign, , , , acHidden
    Set ctl = objAccess.CreateControl(strFormName, acTextBox, acDetail)
    ctl.name = strTBName
    ctl.ControlSource = strFieldName
    objAccess.DoCmd.Close acForm, strFormName, acSaveYes
    objAccess.DoCmd.OpenForm strFormName, acNormal, , , , acWindowNormal

    Set ctl = Nothing
    Set objAccess = Nothing
   
End Function

Then revise the call to the function accordingly:
AddTextBox fld.Name, "C:\dbapps\YourDatabase.mdb", "frmYourFormName", "txtYourNewTextBoxName"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cfg1980Author Commented:
That did the trick.  Thanks for your help!
0
mbizupCommented:
Glad to help :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.