cfg1980
asked on
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("NewFieldN
tdf.Fields.Append fld
Set db = OpenDatabase(sFileName)
Set tdf = db.TableDefs("theTable")
Set fld = tdf.CreateField("NewFieldN
tdf.Fields.Append fld
Use the CreateControl procedure
>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,
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"
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,
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"
ASKER
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?
So is there a "DoCmd.OpenForm" equivalent that would open the form for editing in another DB?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That did the trick. Thanks for your help!
Glad to help :-)