Problem facing in building Combo Boxes

[arpitraval]

I am using MS Access 2007. and now i am trying to learn Forms. I have one table which is related to one college information. I want to create a form in which i can have three boxes (one of them is combo box) so in the combo box i ll have three semesters Spring 2008 summer 2008 and fall 2008 so if i select summer 2008 than i can automatically have the enrollment count of that semester for the electrical engineering department. So once i select the semester than in the rest of the two normal boxes have the information of enrollment and the electrical engineering department. So how do i do it? please let me know as soon as possible. Once again i am working on MS Access 2007.

Thanks
Arpit Raval

 

[CyberLynx]

See you other duplicate post.

Please do not create duplicate posts arpitraval. Thank you.

 

[arpitraval]

Thanks for your reply. sorry for two messages but actually i was not getting answer as quick as i want so i thought may be i put the subject wrong that's why i copy and paste it with a proper subject.
And as i could not make you understand regarding my question i am sorry for that let me try with simple sentences.

I have a table in which i have three columns. first columns contains semesters, second column contains enrollment count. third column contains department. there are three different semesters for example 2008 spring, summer and fall. departments are electrical engineering, mechanical engineer, computer science. So now the total number of rows are 100 so here terms are repeating so many times. now below is the thing which i want in form.

I need a two combo box and one text box. in first combo box i want three semesters so that i can select one of them so once i select them now i have to select the department in the second combo box. again here the departments are repeating in the main table but i want them only one time in the combo box. so once i select both i want to have the numbers automatically in the third box which is text box that how many enrollment numbers in that particular department. I hope i can make you understand this.

Thanks
Arpit Raval

 

[CyberLynx]

Below is code to work with the three controls.

Private Sub Form_Open(Cancel As Integer)
   [COLOR="DarkGreen"]'Fill the Semester Combo Box with distinct semesters
   'contained within the Table
   'Ensure tha the Row Source Type s set to Table/Query.[/COLOR]
   Me.cboSemester.RowSourceType = "Table/Query"
   [COLOR="DarkGreen"]'Place a SELECT query into the RowSource of the Semester
   'combobox to retrieve the all the different Semesters in
   'Table. The DISTINCT statement is used to ensure that no
   'duplicate items are listed.[/COLOR]
   Me.cboSemester.RowSource = "SELECT DISTINCT Semesters FROM [COLOR="Red"][I]WhateverYourTableIsNamed[/I][/COLOR] " & _
                              "ORDERED BY Semesters;"

   [COLOR="DarkGreen"]'Ensure that the Department combo box is set to Table/Query.
   'This is actually default and should not be required![/COLOR]
   Me.cboDepartment.RowSourceType = "Table/Query"
   
   [COLOR="DarkGreen"]'Disable both the Department Combo and the Enrollment Count
   'Text Box to ensure that the Semester is selected first.[/COLOR]
   Me.cboDepartment.Enabled = False
   Me.txtEnrollmentCount.Enabled = False
End Sub

Private Sub cboSemester_Click()
   [COLOR="DarkGreen"]'Does the Semester ComboBox actually contain anything?
   'If so then....[/COLOR]
   If IsNull(Me.cboSemester) = False Then
      [COLOR="DarkGreen"]'Set the RowSource property for the Department ComboBox.
      'Again, a SELECT query is used for this and again, the
      'DISTINCT statement is used to ensure that duplicate
      'departments are not added to the list. Only Departments
      'matching the Semester are listed. Within the ComboBox we
      'also add a second column for the Enrollment Count for each
      'listed Department.[/COLOR]
      Me.cboDepartment.RowSource = "SELECT DISTINCT Department,EnrollmentCount FROM [COLOR="Red"][I]WhateverYourTableIsNamed[/I][/COLOR] " & _
                                   "WHERE Semester='" & Me.cboSemester & "' ORDERED BY Department;"
      [COLOR="DarkGreen"]'Enable the Department ComboBox so that now
      'something can be selected from that box.[/COLOR]
      Me.cboDepartment.Enabled = True
   End If
End Sub

Private Sub cboDepartment_Click()
   [COLOR="DarkGreen"]'Once the department is selected, pull out the Enrollment
   'Count from the second Combo Column and place it into the
   'Enrollment Count Text Box.[/COLOR]
   Me.txtEnrollmentCount = Me.cboDepartment.Column(1)
   [COLOR="DarkGreen"]'Now enable the Enrollment TextBox[/COLOR]
   Me.txtEnrollmentCount.Enabled
End Sub

Good luck...

.

 

[arpitraval]

Thank you so much