Placing the controls dynamically in the form and populating the values from Acces TBL

[aman]

Hi Guys

I am building a project using Access . There is just one Form 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 form 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 form and if the field's data type in Text then textbox will appear in the form and the values should be populated in the controls on the form.

Any help will be much appreciated.

Thanks

 

[gemma-the-husky]

I can't think you want this.

I think I would have a form with a sufficient number of unbound controls, and then in code set the control source to whatever field is appropriate.

I think that would work.

I would not try to add controls dynamically. Just have say 10 controls ready to use. I would probably grey out any you did not want.

 

[aman]

Thanks Gemma-the-husky. Ok If I make the controls invisible and then after the values of the comboboxes are selected then how can we rename the labels dynamically and add values in the controls?

Can you please help me in this?

 

[gemma-the-husky]

label:
controls("labelname").caption = whatever

control:
controls("ctrlname").controlsource = whatever

(I am assuming it is possibly called controlsource....)

 

[aman]

Thanks Gemma-the-husky,
Actualy I am building the project using excel as a frontend and Access as a backend. I have already written the code that will add the controls dynamically on the userform and the values get populated in all the controls. But now what I have to do is to update a record . If the user wants to make any changes in the control values and update a record then the following code is written but its giving me error message:

Dim j As Integer
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim a, i, Sum, p, k As Long
p = 0
j = 1
k = 1
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\WorkQueue.mdb;"
    Set rs = CreateObject("ADODB.Recordset")
 
 
    strsql = "SELECT * FROM TType3Tbl where Type='" & ComboBox1.Value & "' and Type1='" & ComboBox2.Value & "' and Type2='" & ComboBox3.Value & "'"
    rs.Open strsql, cn, adOpenKeyset, adLockOptimistic
 
    For Each Field In rs.Fields
   If Field.Type = 202 Or Field.Type = 3 Then
     rs.Fields(p).Value = Me.Controls("Textbox" & j).Value
        j = j + 1
     p = p + 1
 
     ElseIf Field.Type = 7 Then
 
     rs.Fields(p).Value = Format(Me.Controls("textbox" & j).Value)
     j = j + 1
     p = p + 1
   ElseIf Field.Type = 11 Then
 
     rs.Fields(p).Value = Me.Controls("checkbox" & k).Value
        k = k + 1
        p = p + 1
    End If
 
Next Field
   rs.Update