Create text boxes based on user input

[sunilvedula]

I have been searching this forum but to no vail. what i require is able to create multiple text boxes based on user input. For eg if user says 5 then 5 text boxes need to be created and so on so forth. I can predefine the text boxes which i can make them appear and disappear based on the input but that would make the a lot of space unsused since this form is designed based on a word template. I am exporting form to snapshot viewer which i am attaching to email (lotus notes)and sending it automatically. Even if it is possible to auto create text boxes how do i ensure coding after the form is submitted?

 

[dbDamo]

Would I be correct in assuming that the text boxes to be added are existing fields from the query/table the form is based on?

If this is the case (and I do hope it is), then you obviously have a limit on the number of text boxes that can be added, eg you only have so many fields.

This could be achieved like you say by hiding/unhiding the controls using their visible property based on user input.

If you are concerned about the blank space on the form, you could then then use the InsideHeight property of the form to resize based on which controls are visible.

To sum up -

1. add all controls to your form
2. in the forms OnOpen event set your text boxes visible property to false
3. create a blank command button next to the UserInputTextBox
4. place your code to unhide the hidden text boxes and resize the form in the command buttons OnClick event. Something along the lines of:-

If Me.UserInputTextbox = 1 Then
Me.HiddenTextBox1.Visible = True
ElseIf Me.UserInputTextBox = 2 Then
Me.HiddenTextBox1.Visible = True
Me.HiddenTextBox2.Visible = True
'etc etc until you've covered your last hidden control (im using 5)
ElseIf Me.UserInputTextBox >5 Then
MsgBox "can only add 5 text boxes"
End If
 
If Me.HiddenTextBox5.Visible = True Then
Me.Form.InsideHeight = 9000
ElseIf Me.HiddenTextBox4.Visible = True Then
Me.Form.InsideHeight = 8500
etc etc...
Else Me.Form.InsideHeight = 6500
End If

Obviously you will need to play around with this as this code is off the top of my head, but it should give you some direction.

 

[missinglinq]

And what happens to these texboxes when you move to another record? Or if these textboxes are unbound and you're only using them to fill in data for your email, what happens to them the next time you open your form?

The first problem with dynamically creating and destroying controls on a form, which sounds like what you're trying to do, is that a form has a limit (754) on the number of controls it can have, and this limit is for the lifetime of the form! In other words, once you done this operation repeatedly, even if you delete the controls after you're thru using them, they still count towards the limit, and eventually you'll hit the limit and will error out anytime you try to use the form in this manner.

The second problem is that the form has to be in Design View, not Form View, when these textboxes are created. So, in order to not have a lot of "unused space," you'd have to

1. Open the form is Form View
2. Except and assign the number the user inputs into a Global variable
3. Close the form
4. Open the form in Design View
5. Create your textboxes
6. Save and close the form again
7. Open the form again in Form View
8. Do your thing
9. Close the form
10. Open the form in Design View again
11. Delete the textboxes
12. Save and close the form

Is it really worth this kind of effort to avoid a little white space?

dbDamo's suggestion is the only reasonable approach to this, if you simply have to do it! But even it sounds like an awful lot of effort for no real gain.

Database forms should be designed by Database Developers, not by the end users. End users adding fields, which is essentially what you're talking about, is what is done in spreadsheets, not in relational databases.

 

[dbDamo]

Like I said, my suggestion was based on the assumption that the text boxes to be hidden/unhidden were bound and even then I agree that there really doesn't seem to be much to be gained by going through all the effort of doing this, but I offered the solution and it is now down to him.

I have in the past used a similar approach to hiding/unhiding controls for example additional contact details fields, but I have ever had to, or even wanted to, resize the form based on what controls are visible.

 

[Galaxiom]

You could use unbound textboxes and then set each of their Control Sources based on the user input. Any that are not used can have their Visible property set to false.

 

[sunilvedula]

ok first of all sorry for the repost. I thought that the earlier post was never posted as i had issues with the internet. Anyhow will explain. There is a form in excel which bank employees fill and send it to another region. Now in this form they enter details of multiple transactions. some times it might be required to enter say 10 to 50 and sometimes only 1 (depends on different scenarios) and in excel they go on adding multiple lines as and when required. I have designed the form as it is in excel kind of replicated it. now i have issues with adding mulitple line in our case i require adding text boxes so as to fill them with info as and when required.

 

[sunilvedula]

Would I be correct in assuming that the text boxes to be added are existing fields from the query/table the form is based on?

no this is a predesigned template (form) on which the user fills in and the data goes back to the table. Hence my question is it possible to create multiple text boxes based on user requirment (in executable file) and then if possible how do i send them to table after being submitted?

 

[sunilvedula]

And what happens to these texboxes when you move to another record? Or if these textboxes are unbound and you're only using them to fill in data for your email, what happens to them the next time you open your form?

that is what i am not clear about and hence try to know it

The first problem with dynamically creating and destroying controls on a form, which sounds like what you're trying to do, is that a form has a limit (754) on the number of controls it can have, and this limit is for the lifetime of the form! In other words, once you done this operation repeatedly, even if you delete the controls after you're thru using them, they still count towards the limit, and eventually you'll hit the limit and will error out anytime you try to use the form in this manner.


The second problem is that the form has to be in Design View, not Form View, when these textboxes are created. So, in order to not have a lot of "unused space," you'd have to

1. Open the form is Form View
2. Except and assign the number the user inputs into a Global variable
3. Close the form
4. Open the form in Design View
5. Create your textboxes
6. Save and close the form again
7. Open the form again in Form View
8. Do your thing
9. Close the form
10. Open the form in Design View again
11. Delete the textboxes
12. Save and close the form

Is it really worth this kind of effort to avoid a little white space?

dbDamo's suggestion is the only reasonable approach to this, if you simply have to do it! But even it sounds like an awful lot of effort for no real gain.

Agreed it is possible in spreadsheet which i am aware of. But is it possible in access?

Database forms should be designed by Database Developers, not by the end users. End users adding fields, which is essentially what you're talking about, is what is done in spreadsheets, not in relational databases.

but we need to design databases based on customer requirment. Am i right, else how would our datbase be a good one.

 

[dbDamo]

Have you looked at my suggestion?

 

[sunilvedula]

Hi I did look at your suggestion and hence i replied too. As i have mentioned earlier the form textboxes do come from any table. they will need to be created new and then be able to input the data into table (mind you that i am not sure if i need to create fileds in the table) or maybe it can create fields in the table also dynamically when submitting.

 

[lcook1974]

Could you post an example Excel sheet?

What do you mean by "any" table? That doesn't make sense. Tables in a database have to have some type of relationship with each other.

YOU as the developer would have to create ALL the fields YOU think you will need, then follow dbDamo suggestions.

End users are smart but they can really mess things up if you give them free reighn on a Database.

LWC

 

[dbDamo]

You really do need to create these fields in the table yourself. Allowing your users to design your table (which is essentially what they will be doing by 'dynamically' adding fields) is a very bad idea, I cannot stress that enough.

 

[RossWindows]

I think what's happening is you don't yet understand Relational Database Theory.
You're trying to replicate something from Excel into Access, but you're still thinking in terms of Excel.
Please corect me if I'm wrong, but that's just the feeling I have.

 

[Mr. B]

Even thinking about dynamically adding and removing controls on demand is also a very bad idea. There is a lifetime limit of a total of 754 controls that can be added to any form. Therefore, if you start adding and then removing controls, you are going to eventually run into that limit.

You will need places all of the controls that your users will need and just display them as needed. You might consider using a tab control to hold related controls. Then you can simply hide one or more pages of the tab control as needed.

Also, with the tab control it is possible to use it without the user seeing the "tabs" at all. You can make it appear without the frame and make it work like a wizzard.

Just my thoughts on the subject.

 

[RossWindows]

Could you post an example Excel sheet?

I think that would be a great idea.

 

[sunilvedula]

Hi All,

Thanks a ton for trying to resolve my issue. I am extremly sorry for the confusion. i agree to ross that i am trying to replicate excel but i am also aware of the theory of RDBMS but maybe not enough as u people out there. This was a kind of requirement my business partner had and i was trying to figure a way out. I had developed around 15 DB till date (very few) and i am still learning and i thank you all for the help.

Here as Ross said my partners want to replicate something which in excel it is possible. I have tried to explain that it is difficult (and not good one according to you all) to be able to create dynamically because of so many reasons. BUt the requirement is also like that. Ok i am attaching you the excel sheet. I have created a form in the same lines which also i have attached. In the place where you can see "Other Information: This box must be completed for all Counterfeit cases."
below that there are multiple things that the user needs to fill. Here the requirement is that it can be mutiple transactions that he might require to fill. At present we have given space for three. But it can be upto 50 transactions also and we are not sure of the number. Hope this is clear else please ask me and will be more than happy to oblige

 

[dbDamo]

Ok. Having now looked at your Spreadsheet (which is not very good - I don't mean to be harsh) and your Database there is only one thing to suggest, and that is that you read up on normalisation.

You say you are aware of the theory of RDBMS, but you are attempting to create a flat file Database, which suggests you do not grasp the concept.

When you have read up some more, you will see that what you are trying to do is achievable quite simply. You will have a table for transactions which means that each record can have multiple transactions without the need for hiding/unhiding/dynamically creating controls in forms.

There are plenty of decent guides on normalisation and general Database design in the Theory and practise of Database design Sub-Forum in this Forum.

 

[lcook1974]

dbDamo hit right on the head.

This forum is great and I know I've learned a ton from here. These guys are always happy to help. I don't think we're giving up on you, just want some you to do some research and education first.

LWC

 

[sunilvedula]

When you have read up some more, you will see that what you are trying to do is achievable quite simply. You will have a table for transactions which means that each record can have multiple transactions without the need for hiding/unhiding/dynamically creating controls in forms.

There are plenty of decent guides on normalisation and general Database design in the Theory and practise of Database design Sub-Forum in this Forum.

Will read for sure and try to get some better understanding. there is nothing harsh about what you said. i am still a learner and am always keen to do it. i am also taking the help of the site "http://www.devshed.com/c/a/Administration/Database-Normalization/1/" to determine which normalization i have followed or just created as u said just a flat db. somewhere down the line i think i forgot about it. thanq for reminding me.

But just to clarify did you mean that i can create another table which will contain all these transactions for each of the record. Like table2 which will hold all the sub transactions for each form with form no being the unique key identifier for both the tables. If that is the case how do i make the user enter multiple transactions in the same form without the need to create a sub form.

i have attached a screen shot where i will require the user to enter multiple entries and how do i do that? I know i am missing something but if u you can guide me through this it could be of help. Also this is just a part of the whole database. I had to delete all othe tables and forms just to ensure that it has the right size to get uploaded. Also just to remind that after all this this form with the same way as it appears on the form should get mailed to different countries. I created a report in the same way but i have doubts will it go throught or not. If for three entries it worked fine but if more i dont know?

 

[Rich]

What you have displayed here is simply a continuous form in use as a subform, you really need to read up on normalis(z)ation, when you've gotten your head around the concept it shouldn't be too difficult to design the tables and relationships between them