newbie question: dynamically adding a textbox to a form

[Albert D. Kallal]

Is it possible to add a textbox to an access form with vba code at run
time (eg during the open event).

Many years ago, I would have considered the above question a bad question,
or the "wrong" question.

However, so many people use HTML forms etc, then often this occurs.

Also, people coming from a VB environment often used the "clone" ability of
controls to "add" more text box prompts on a form.

Unfortunately, in ms-access, adding, or modifying your software designs at
runtime don't work well at all (it just asking for trouble, and the design
tools are not actually built to allow this with any degree of success).

So, what do to???

So, in most cases, if you need "more" then one piece of data, or a
"un-known" number, you use a sub-form, or some type of grid control.

here is some screen shots as to what I mean:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

So, for "more" data, it hoped that you can use some type of grid display, or
perhaps even launch another form for prompting the additional data you need
to gather....

Adding extra controls to a screen via code just don't work well at all in
ms-access.....

 

[arch]

Is it possible to add a textbox to an access form with vba code at run time
(eg during the open event).

 

[Marshall Barton]

Is it possible to add a textbox to an access form with vba code at run time
(eg during the open event).

Don't even think about doing design time operations at run
time. Add the needed number of text boxes to the form and
make them visible as needed at run time.

 

[arch]

My goal was to have a form with a single textbox where a user can type a
piece of sql (eg select * from employees) and then a datagrid/subform would
be populated with this data. The number of columns in this datagrid/subform
would be unknown at design time. What would be the best way to do this?

 

[Marshall Barton]

Open a recordset on the query, loop through the recordset's
Fields collection assigning the fields as the text box
ControlSource and make the unneeded text boxes invisible.
Then set the form's Recordset property to your recordset.

The bunch of text boxes is easy to manage if you name the
text boxes with a common prefix with a sequential number
suffix (txt1, txt2, etc). E.g. (air code)

Set rs = db.OpenRecordset(strSQL, . . .)
With Me.subform.form
k=1
For Each fld In rs.Fields
.Controls("txt" & k).ControlSource = fld.Name
.Controls("txt" & k).Visible = True
k = k + 1
Next fld
For k = k To .Controls.Count
.Controls("txt" & k).Visible = False
Next k
Set .Recordset = rs
End With

 

[marius]

Hi arch,

You could try something like this
www.mvp-access.com/marius/ejemplos/awformX.zip

--
HTH,
Màrius - http://www.mvp-access.com/foro/default.asp

My goal was to have a form with a single textbox where a user can type a
piece of sql (eg select * from employees) and then a datagrid/subform would
be populated with this data. The number of columns in this datagrid/subform
would be unknown at design time. What would be the best way to do this?

 

[arch]

Well done Marius, that was exactly what I needed. It works really well.

Thanks.

Hi arch,

You could try something like this
www.mvp-access.com/marius/ejemplos/awformX.zip