Placing the controls dynamically in the userform and populating the values from AccessTBL

[aman1234]

Hi Guys

I am building a project using Excel as a frontend and Access as a backend. There is just one userform and the user needs to select the values in 3 comboboxes.( first select value in Combobox1 , then combobox2 will appear and then user will select the value from combobox2, then combobox3 will appear and user will need to select the value from combobox3) and based upon the values selected , there will be only one record in the Access table. Now based upon that corresponding controls will appear in the userform and the corresponding values will be populated in those controls. The new requirements in this are as below:

1.It should be dynamic – that means as we add or delete fields in the table (or even rename them) this needs to recognise that and dynamically produce a control for each field.
2.Linked to the above the system needs to recognise the data type for each field and again dynamically display an appropriate control. (Checkbox or Textbox)

I have never worked with dynamic controls so will need your help in this. So if in the table there is checkbox then checkbox will appear in the userform and if the field's data type in Text then textbox will appear in the userform and the values should be populated in the controls on the userform.

Any help will be much appreciated.

Thanks

[Kyle123]

Couple of questions:

1.It should be dynamic – that means as we add or delete fields in the table (or even rename them) this needs to recognise that and dynamically produce a control for each field.

How is the code supposed to know what sort of control you want? Checkbox, textbox, combobox etc?

Moreover however, this sounds like your database isn't set up properly. You don't dynamically add fields and change field names in a database, the structure is generally static, you just add records - I'd start by looking at your database schema and you may find you don't need to do this

[aman1234]

Hi Kyle

The table has already been built and have been used for quite a long time now. Now there is a new user interface and for that new project we need to refer to same table but we might need to add/delete or rename fields in that which will be done manually. But when the user form is shown then it should reflect all those field name and its contents based upon the 3 comboboxes values selected.

And also there will be either Textbox or checkbox controls which will be displayed dynamically and not any combobox . So we need to check if DataType is "Yes/No" then put checkbox for that field in the form and if the datatype is "Text" then put textbox for that field.

I hope it make sense. Many Thanks

[aman1234]

Hi there

I have already built the code that will create the controls dynamically on the userform . The values get populated in the controls using vba too. But now I have to perform the update operation on the control values i.e if the users want to change a record then they make some changes in the values in the userform and Press Update button to save those changes. But my code is giving me an error "Field can't be changed" Can anyone please help me in this.

Please Login or Register  to view this content.

[Kyle123]

You never, ever rename fields in a database - why would you?

I don't really understand why you want to do it dynamically - just how many fields do you have?

I really think you need to normalize your database - this will then make this whole thing trivial - could you post your database schema? An image would do

[aman1234]

Kyle, This is requirement from my Boss . He wants all the controls to appear dynamically and then we will need to update the records using vba. In future he said we will need to delete many fields from the table and add few fields or rename fields. SO I just have to work on that. No other option fomr. ((

[Kyle123]

That's daft, it's not how databases work, they aren't spreadsheets. I wish you the best of luck