Dynamic Creation of Combo boxes

[timothyd]

I have a table in which a student's interest rating for a particular subject is recorded. The table links the fields of the student ID and the subject ID together to create a unique combination of the two.

I am then trying to create a form that will allow the user to input the interest rating in for an individual student with all the different subject connected to a combo box. My problem is that the subjects are prone to change based on what the user is teaching, so I can't make a static form because I have no idea what subject will be user or how many there will be.

Thus, I would like to make a dynamic form that allows me to query the subjects table to find out what subjects there are and create as many combo boxes as needed. I do not know how to create a new combo box and I have searched quite a while but have found nothing on the topic. Any help would be appreciated.

 

[Uncle Gizmo]

I don’t appear to grasp the essence of your question, please could you restate the question in different terms in the hope that I can better understand it.

 

[timothyd]

Ok, sorry if I confused you.

I have a table named 'Subjects_Table' that contains all the subject being taught by the teacher using this database. This table can grow and shrink as needed with different classes over time. I also have a table that records each student's initial interest in each subject and a table for each student. I am trying to make a form that combines each student profile with an interest rating in each subject area.

The form will have combo boxes that will allow the user to select a number 1-10 as the interest rating, but since I don't know what subject there will be exactly, I need to make the form grow and shirk as the subjects table grows and shrinks.

So my question is: is there a way to dynamically create combo boxes the correspond to each subject on the form through some sort of loop.

Here is my code so far. Let me know if that makes sense to you.

Private Sub Form_Load()
  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  Set qd = db.QueryDefs("Subjects_Sorted")
  Set rs = qd.OpenRecordset
  
  Do Until rs.EOF
    MsgBox rs![Subject Name]  '<--- right here is where I want to create a combo 
    rs.MoveNext                    'box that is based on rs![Subject Name]
  Loop
    
End Sub

 

[pbaldy]

It is probably past bedtime for Uncle Gizmo. You might want to post your table structure. You've mentioned one mistake and implied another. The one you mentioned was "a table for each student". The implied one is that you might have fields for each subject. You might want to read up on normalization. It would be a mistake to have a table for each student, or fields for each subject. You want records in related tables, not fields. Here's a start:

http://www.mdbmakers.com/forums/showthread.php?t=2583

http://www.r937.com/Relational.html

 

[georgedwilkinson]

No need to have a separate combo box for each interest rating: just create a sub-form for the table that holds your interest rating for the student/subject. As you enter the interest rating for the student, a new row will be entered into this table.

I am quite concerned that you might have a structure problem with your database. That may be why you think you need this. I base that on the statement

a table for each student

You should not need a different table for each student. You can enter them all into 1 table. I apologize if I read it wrong. But at the same time, I feel that we're not getting the right information to help you if I did read it wrong.

 

[timothyd]

I don't have a table for each student or fields for each subject. What I meant was that I have a table that holds records for each student and a table that holds records for each subject. My bad, I wrote it too fast without realizing I was throwing around terms that could be interpreted lots of different ways. I know how to develop databases since I have taken many advanced coursed on how to do it. I am, though, learning Access forms, reports, and modules for the first time. I know java really well but not VBA and need some help with the how-to. It really feels like a step backwards to be working in Access since I don't have the freedom to do as I want.

The table structure looks like this:

Student_Table
Student ID (Primary Key)
First Name
Last Name
...

Subjects_Table
Subject Name (Primary Key - I have other reasons for not using an ID as a primary key)
Notes
...

Interest_Rating_Table
Student ID (foreign key)
Subject Name (foreign key)
Interest Rating (Number 1 - 10)

As for what you are suggesting, would that only put up one combo box at a time? The reason I want to do this is so that I could have all the combo boxes up at the same time and not have to go through 10+ subjects to reach the one that I want to enter. Plus, it would be more convenient for the user to have them all on one form.

 

[gemma-the-husky]

what you COULD do is this

given a student, you extract a query picking just those courses that particular student is studying - then you present these in a SINGLE list box, and allow the user to select as many of these as he is interested in.

is that what you mean? 1 list box only!

------------
i tend to agree with the other posters - anyone thinking they need to create objects dynamically is either working at a very advanced level - or is misunderstanding a different problem.

 

[timothyd]

No, that won't work since this is for an elementary level teacher who will be teaching the entire class the same subjects. What the teacher wants is to have each student fill out what they like and don't like so that the teacher can then use that data to figure out where to work on for each student.

And, yes, I am trying to work at an advanced level. I do understand there are other ways of doing it, but they are less efficient that the way I want it done. I don't want to have to go through 10 different records for each student but rather be able to update 10 different records from one form. That may not be possible, but I want to give it a shot. So, what I really want to know is if there is a way to create a combo box in VBA or not. If there isn't then my plan is foiled. If there is, I can handle the rest.

UPDATE:

According to this website, it is possible and I can see from the example that I downloaded that it works, but I can't get Me.Controls.Add to be recognized as a real method. Could that be because it was only available in '97 or Word only?

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=259

 

[timothyd]

I found another link that seems to give me more of what I want, but this time I can't seem to make a new combo box on the form that I am using but only if I create a new form. It gives me the error that I have to be in design view to add a control.

http://msdn.microsoft.com/en-us/library/aa221167(office.11).aspx

 

[timothyd]

I found another link that seems to give me more of what I want, but this time I can't seem to make a new combo box on the form that I am using but only if I create a new form. It gives me the error that I have to be in design view to add a control.

http://msdn.microsoft.com/en-us/library/aa221167(office.11).aspx

 

[Uncle Gizmo]

PLEASE NOTE I WOULD HAVE POSTED THIS COMMENT SEVERAL HRS AGO, BUT I WAS CONSTANTLY THWARTED FROM POSTING BY THIS SITE NOT BEING ACCESSIBLE.

MS Access is designed to handle databases in a particular way. It helps you with your database creation by providing forms and controls that you can use in the construction of your database. This provides you with a simple and quick way to work, however if you want something a bit different, then you either have to be very clever, or consider using a different programming environment.

The way I would tackle it in MS Access would be to add to a number of combo boxes to the form, have them hidden, (invisible) and just make them visible and move them to the correct position programmatically as required. I believe it would be possible to do it this way although there may be some issues I haven’t considered.

However if I was asked to provide the functionality you are asking for, I would not use MS Access, I would use VB .Net. There is a free download available from Microsoft, I suggest you downloaded and have a look, I’m sure what you want could be accomplished simply with this free software.

 

[timothyd]

In using VB .net, would that work in place of VBA? If not, then I will just have to deal with what I have since I am doing this as my senior project for the educational department at college and I can't turn back now that I am half way done with the semester. I have already gotten the whole dynamic thing to work so far and I believe I can make it work if I just spend a bit more time on it. I will post results when done.

Right now my problems is that I need to open my form in design view to add the combo boxes and then change the view to form view. This is hard since I can't seem to initialize the form as a variable.

Private Sub Interest_Rating_Button_Click() 'pressed in the 'Add Student Form

  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  Set qd = db.QueryDefs("Subjects_Sorted")
  Set rs = qd.OpenRecordset
  
  DoCmd.OpenForm "Interest_Rating_Form", acDesign, , , , acWindowNormal
  
  Dim frm As Form
  Set frm = ?

  ' I need to make the form I open to be set as a variable so that I can call
  ' NewControls with the form.  I can't send Me because that is referencing
  ' the form that I am pushing the button in and using the On_Load or
  ' On_Open on the other form is not working for some reason
  
  frm.RecordSource = "Subjects_Table"
  Dim x As Integer
  x = 75
  
  Do Until rs.EOF
    NewControls frm, rs, x
    x = x + 375
    rs.MoveNext
  Loop
    
End Sub

Sub NewControls(frm As Form, rs As DAO.Recordset, x As Integer)
    
    Dim ctlLabel As Control, ctlCombo As Control
    Dim intDataX As Integer, intDataY As Integer
    Dim intLabelX As Integer, intLabelY As Integer

    ' Set positioning values for new controls.
    intLabelX = 2000
    intLabelY = 300
    intDataX = 750
    intDataY = 250

    ' Create unbound default-size text box in detail section.
    Set ctlCombo = CreateControl(frm.Name, acComboBox, , "", "", _
        intLabelX, x, intDataX + 75, intDataY)

    ' Create child label control for text box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , _
         ctlCombo.Name, rs![Subject Name], 75, x, intLabelX, intLabelY)

    ' Restore form.
    DoCmd.Restore
    
End Sub

 

[Uncle Gizmo]

You may find this thread of interest

http://www.pcreview.co.uk/forums/thread-2370280.php

 

[Uncle Gizmo]

And this one:

http://tek-tips.com/viewthread.cfm?qid=1498416&page=28

 

[Uncle Gizmo]

And a sample DB here may be of interest:

http://www.access-programmers.co.uk/forums/showthread.php?t=154067

 

[timothyd]

I figured it out how to make the form that was just opened to be declared as a variable.

DoCmd.OpenForm "Interest_Rating_Form", acDesign, , , , acWindowNormal

Dim frm As Form
Set frm = Forms![Interest_Rating_Form]

 

[vbaInet]

Just thought I should say if you're still considering going ahead with your approach then remember to clean up during Load and UnLoad.

My preferred way of handling this would have been similar to georgedwilkinson's approach but this time with 2 multi-select listboxes. On the left would be a list of the Interest Ratings and the right the list of Pupils. All they do is select which Ratings, select the pupils and click a button to Assign. I would imagine the number of Interest Ratings would most certainly be way less than that of the number of Pupils, so you just simply loop through the Pupils listbox and assign to each selected Rating.

 

[timothyd]

vbaInet,

You are right that I need to clean up on Load and Unload. My question is then: how do you get a form to run a script that opens in design view. The onload and onopen methods only seem to work if the form is in form view. Thus, I am having to write all of my code in another form and I don't like doing that.

 

[vbaInet]

I believe that's the only way to create controls on the fly whether it be reports or forms. Something you would have to deal with if you're keen on your approach.

My question is then: how do you get a form to run a script that opens in design view.

My suggestion would be to create a table and use a memo field to store your code, however, I don't think it would be easy to read the contents line by line because writing to the VBA editor is done line by line. So the only way is to write your "template" code in a module and comment the whole block of code unless your project won't compile. You would need to know the start line number and end line number so you can control where you want to start reading from. You write each line onto your form in design view, starting at the end (plus 1) of your form module, then use Replace() and Left() to get rid of the comment characters of each line.

After the whole setup you then need to SAVE and COMPILE using code. Now compiling is where you would have problems because (I think) you cannot compile NEW objects if there are Open objects in any collection. Don't quote me on that.

Another solution would be to create the maximum number of combo boxes there can ever be and use those instead of trying to create them. That is if you can restrict the user to a max number of Ratings. Code wise you just use a module.

 

[timothyd]

This code works great to create the boxes I need and it changes every time I add a new subject in. I still haven't linked the combo boxes to the right fields yet nor the form to a particular student, but I will get there. For now, I have figured out how to make the combo boxes load dynamically which is what I was after.

My next problem is that I have to delete all the boxes out when I close the form otherwise it will cause problems the next time I open it.

Private Sub Interest_Rating_Button_Click()

  Dim db As DAO.Database
  Dim qd As DAO.QueryDef
  Dim rs As DAO.Recordset
  Dim frm As Form
  
  Set db = CurrentDb
  Set qd = db.QueryDefs("Subjects_Sorted")
  Set rs = qd.OpenRecordset
    
  DoCmd.OpenForm "Interest_Rating_Form", acDesign, , , , acWindowNormal
  Set frm = Forms![Interest_Rating_Form]
  frm.RecordSource = "Subjects_Table"
  
  Dim padding As Integer
  Dim labelWidth As Integer
  Dim labelHeight As Integer
  Dim labelTopPosition As Integer
  Dim labelLeftPosition As Integer
  Dim comboBoxWidth As Integer
  Dim comboBoxHeight As Integer
  Dim comboBoxTopPosition As Integer
  Dim comboBoxLeftPosition As Integer
  Dim index As Integer

  padding = 75
  index = 1
  labelWidth = Find_Longest_Record(frm, rs)
  labelHeight = 350
  labelTopPosition = padding
  labelLeftPosition = padding
  comboBoxWidth = 750
  comboBoxHeight = 350
  comboBoxTopPosition = padding
  comboBoxLeftPosition = labelWidth + (padding * 2)
  
  rs.MoveFirst
  
  Do Until rs.EOF
    New_Controls frm, rs![Subject Name], labelWidth, labelHeight, labelTopPosition, _
                 labelLeftPosition, comboBoxWidth, comboBoxHeight, _
                 comboBoxTopPosition, comboBoxLeftPosition
    labelTopPosition = labelTopPosition + labelHeight + padding
    comboBoxTopPosition = comboBoxTopPosition + comboBoxHeight + padding
    index = index + 1
    rs.MoveNext
  Loop
  
  DoCmd.Close acForm, frm.Name, acSaveYes
  DoCmd.OpenForm "Interest_Rating_Form", acNormal, , , acFormEdit, acWindowNormal
  
End Sub

Sub New_Controls(frm As Form, subjectName As String, labelWidth As Integer, _
                 labelHeight As Integer, labelTopPosition As Integer, _
                 labelLeftPosition As Integer, comboBoxWidth As Integer, _
                 comboBoxHeight As Integer, comboBoxTopPosition As Integer, _
                 comboBoxLeftPosition As Integer)
    
    Dim ctlLabel As Control, ctlCombo As Control
    
    ' Create unbound combo box in detail section.
    Set ctlCombo = CreateControl(frm.Name, acComboBox, , "", "", _
        comboBoxLeftPosition, comboBoxTopPosition, comboBoxWidth, comboBoxHeight)

    ' Create child label control for combo box.
    Set ctlLabel = CreateControl(frm.Name, acLabel, , ctlCombo.Name, _
         subjectName, labelLeftPosition, labelTopPosition, labelWidth, labelHeight)

    ' Restore form.
    DoCmd.Restore

    
End Sub

Function Find_Longest_Record(frm As Form, rs As Recordset) As Integer
  
  Dim longestSubject As Integer
  Dim textLength As Integer
  Dim str As String
  Dim tempLable As Control
  
  longestSubject = 0
  Set tempLabel = CreateControl(frm.Name, acLabel)
      
  Do Until rs.EOF
    str = rs![Subject Name]
    tempLabel.Caption = str
    tempLabel.SizeToFit
    textLength = tempLabel.Width
    
    If textLength > longestSubject Then
      longestSubject = textLength
    End If
    
    rs.MoveNext
  Loop
  
  DeleteControl frm.Name, tempLabel.Name
  
  Find_Longest_Record = Math.Round(longestSubject + 1)

End Function