Create textboxes on form using VBA (1 Viewer)

[jmsjazz]

I have a form with checkboxes, indicating fields which can be selected for a query. The strSQL variable works fine, and I can use QUERYDEF to create a query from the variable.

I wonder if it is possible to generate a form to display only the fields chosen - i.e. I don't want a general form with all fields, with only the chosen ones displaying data. Instead, if I select only 5 of the 10 fields, I want the form only to show those 5 fields.

I toyed with the idea of creating textboxes for each field, and using the .visible property, but then I would have lots of gaps on the form.

I hoped it might be possible to use a loop with a value set something like

For i = 1 to fieldcount
textbox(i).recordsource = selectedfld(i)
'somehow know where to place the textbox!?!!
Next

Does anyone know of a solution?

Thanks in advance

 

[rzw0wr]

Yep, its possible.
You would likely have to format the entire form with VBA.
Add a text box, move it to the proper location, name it...........

But it is possible

Dale

EDIT:
I should have mentioned that this is not done.
My error.

 

[jmsjazz]

Do you know how to add a text box to a form in VBA? And how to position /size it?

 

[wilderfan]

Hi, jmsjazz.

I have the same problem. I'd like to be able to insert x number of unbound text boxes onto a form, after the user indicates the number of text boxes required.

I'm certainly not an expert at this, but I do know that creating a number of text boxes beforehand and making them invisible is not considered "best practices".

And like yourself, I'm not sure how to shift the form's other controls downward once the new text boxes are inserted onto the form.

Hopefully, one of the access black belts will come to the rescue.

 

[Galaxiom]

Controls can be added to a form using VBA but it isn't done.

Firstly it has to be done in Design mode which means the database cannot be distributed as an executable and cannot be run with Access runtime.

Secondly a form has a limit of about 760 controls in its life after which you cannot add more until you export it to a new database.

AFAIK, adjusting the Visible property isn't just best practice, it is the only practice.

 

[Galaxiom]

The textboxes can be moved by using a loop to test the visible property and adjust the Top property to bring it up to a specified position below the previous visible control. You need to account for the Height of the precvious control too.

Distances on a form are measured and set in VBA using Twips (Twentieth of an Imperial Point). There are 1440 twips in an inch (about 567 in a centimetre).

 

[missinglinq]

...Controls can be added to a form using VBA but it isn't done...

...adjusting the Visible property isn't just best practice, it is the only practice...

FYI, the above isn't just Galaxiom's opinion, it's what the vast majority of experienced Access developers are going to tell you!

Deciding how many Textboxes are needed, at runtime, usually means that the database has major design problems. When a Record needs to have X number of bits of related information, where X can be anywhere from none to an infinite number, you need a second Table to hold these bits of data, with it being used as the Many-side in a One-to-Many Relationship.

Linq ;0)>

 

[wilderfan]

Agreed that there would be a second table involved.

But what if you want to enter data through the use of unbound text boxes - using a single form and a "SAVE" button which would be clicked after reviewing all the entries ?

If you choose to go the unbound route in entering data for both the 1st table and the 2nd table, then wouldn't it be okay to enter the DRAFT data, look it over and then SAVE to the 2 tables once you're satisfied that all the entries are fine?

In that case, could a person not use VBA code to create the x number of extra unbound text boxes, the contents of which would be saved to the 2nd table?

 

[Galaxiom]

Read post #5 again. Adding controls at runtime is simply not a plausible design solution.

 

[wilderfan]

Given the runtime issue and the imperfect option of creating a fixed number of invisible unbound text boxes, it looks like I'll have to input the data using bound text boxes and simply have a sub-form for the entries to the 2nd table. Usually I prefer to make unbound entries first, review them and then click a SAVE button. But the unbound option is obviously problematic when you try to use a single form to make entries to a 2nd table.

 

[Galaxiom]

You could have tables dedicated to accepting the entries from the bound textboxes then use a query to write them to the real tables when you press the save button.

Obviously each user woud need a separate table. I would recommend this table is held in a separate local database to avoid writing and deleting record in the front end.

As with any unbound system you should also consider the management of conflicing updates from multiple users.

 

[Galaxiom]

An alternative to the temporary tables is a fabricated ADO recordset. These live only in memory so you don't need the tables.

 

[wilderfan]

Thanks for your suggestions, Galaxiom.

 

[jmsjazz]

In the time between my original post and your replies, I did get it to work using a routine that included this:

For i = 1 To C
With CreateControl( _
formname:="searchreport", _
ControlType:=acTextBox, _
Section:=acDetail, _
ColumnName:=fcontrol(i), _
Left:=leftpos, _
Top:=0.2 * twipsPerInch, _
Height:=0.25 * twipsPerInch, _
Width:=fwidth(i))
fc = Len(fcontrol(i))
fcontrol(i) = Mid(fcontrol(i), 2, fc - 1)
fctrl = Left(fcontrol(i), fc - 2)
.Name = fctrl
End With
With CreateControl( _
formname:="searchreport", _
ControlType:=acLabel, _
Section:=acHeader, _
ColumnName:=fctrl, _
Left:=leftpos, _
Top:=0.5 * twipsPerInch, _
Height:=0.25 * twipsPerInch, _
Width:=fwidth(i))
End With
leftpos = leftpos + 250 + fwidth(i)

Next i

Earlier in the code, fcontrol(i) and fwidth(i) were allocated when the user chose the field to be added to strSQL. I was pretty pleased with myself until I read your replies!!

I will go back to the drawing board - thinking about ways to move the position of the controls on the form as well as their visible property, rather than creating a new set of controls each time. Thanks for saving me the problem of discovering the issues in a few weeks time.

 

[ChrisO]

As others have said, a lot of time can be wasted trying to create controls at runtime.

However, there is still a need to create controls automatically under some circumstances. For example: if we needed 700 controls, which are sequentially numbered, then it would be a right pain to do it manually. Apart from the time taken, the number of errors in their construction would be horrendous. But, when done automatically, they are created perfectly.

Over the past decade, the attached Access2003 demo is what I have been using to create sequentially numbered controls. It allows for the creation of up to 750 controls, in a new Form, in an MDB file. The controls can then be copied and pasted into the real Form.

The primary consideration is how those controls will be used. It is, therefore, time well spent creating the controls with the correct name and sequential number along with any other properties the controls share.

The actual position, size and visibility should be done at runtime.

Chris.

 

[jmsjazz]

Hi, jmsjazz.

I have the same problem. I'd like to be able to insert x number of unbound text boxes onto a form, after the user indicates the number of text boxes required.

I'm certainly not an expert at this, but I do know that creating a number of text boxes beforehand and making them invisible is not considered "best practices".

And like yourself, I'm not sure how to shift the form's other controls downward once the new text boxes are inserted onto the form.

Hopefully, one of the access black belts will come to the rescue.

Hi..I have managed to do this...let me know if you would like to see my attempt! I took the advice of not creating controls, instead making them visible and positioning using left, top and width parameters.