Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Unanswered: Updating ComboBox to populate ListBox

    Hi! This is my first time posting, although I read around a bit before actually joining

    My experience so far has been with Java/JSP/MySql, but I just started working with MS Access (and VB) at my new job in Vietnam, so I'm trying to learn how things work.

    Anyway, I have a few tables:
    Students
    Course Info
    Courses (these are the ongoing courses)
    Course Enrollment (this maps Students to Courses)

    What I want to do in the long run is to create a form that will enable the user to add students to an ongoing course, so they can select/deselect students from a list and save them. For now, I'm just trying to get used to Access and VB in general, so what I'm trying to do is select a course from a Combo Box, and have that action update a ListBox with the students that are enrolled in that course.

    I created a query that will return all the ongoing courses, and put that in the Row Source of the Combo Box. Using VB, I can retrieve the Course ID using the After Update event. But now, I'm stuck. How do I use that value to run another query, in VB? And how do I return the results of the query to update the ListBox?

    This is what I have in my function so far:

    Code:
    Private Sub coursesCombo_AfterUpdate()
    
    Dim cID As Long
    
    cID = coursesCombo.Value
    End Sub
    The query I want to be able to run is:

    SELECT Students.[Student ID], Students.[Full Name], Students.[Email] FROM [Course Enrollment] INNER JOIN [Students] ON [Course Enrollment].[Student ID] = Students.[Student ID] WHERE [Course Enrollment].[Course ID] = cID

    Then, I want to populate the ListBox with the results.

    I know that I'm asking for a lot so if it's easier, you can just point me to a good website for learning this stuff, and I'll greatly appreciate it. Being in Vietnam makes it difficult to obtain books in English legally (or I don't know where to get them).

    Thanks in advance!

  2. #2
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Thimbles...

    Code:
    Private Sub coursesCombo_AfterUpdate()
    
    Dim cID As Long
    Dim strSQL As String
    
    cID = coursesCombo.Value
    
    strSQL = "SELECT Students.[Student ID], Students.[Full Name], Students.[Email] " & _
             "FROM [Course Enrollment] " & _
             "INNER JOIN [Students] " & _
                 "ON [Course Enrollment].[Student ID] = Students.[Student ID] " & _
             "WHERE [Course Enrollment].[Course ID] = cID"
    
    nameOfYourListBox.rowSource = strSQL
    
    End Sub
    You'll also need to set the listbox up like:

    Code:
    Row Source Type: Table/Query
         Row Source: >LEAVE BLANK<
       Column Count: 3
       Column Heads: Yes
      Bound Columns: 1
    Leaving the blue bit blank, just incase you were thinking of writing that in.
    Looking for the perfect beer...

  3. #3
    Join Date
    Sep 2011
    Posts
    8
    Wow, awesome. That was helpful, and educational. And here I was thinking it would be a super long, multi-step process.

    Thank you very much!

  4. #4
    Join Date
    Sep 2011
    Posts
    5

    Similar Situation

    Thanks for posting this. I have a similar situation that I need to figure out.

    I'm creating a login database that contains 1 table called "studentschedules" which has 6 columns (ID, an auto number... StudentID, CourseID, CourseName, ProfessorLast, ProfessorFirst)

    I have another table called "visitors" that I need to records visits to with the same column names except with visit in front, e.g. VisitStudentID, VisitCourseID, etc..

    I have a form called logger with a textbox called "realid" and a listbox called "stulist" and I need to populate the stulist with all of the courses tied to the number entered in realid. Then, the student selects the course and clicks the login button and it records the entry in visitors.

    I've tried adapting your code to my database, but I'm messing it up I think. Could you take a look?

    Code:
    Private Sub realid_AfterUpdate()
    Dim cID As Long
    Dim strSQL As String
    
    cID = realid.Value
    
    strSQL = "SELECT studentschedules.[StudentID], studentschedules.[CourseID], studentschedules.[CourseName] " & _
             "FROM [studentschedules] " & _
             "WHERE [studentschedules].[StudentID] = cID"
    
    stulist.RowSource = strSQL
    End Sub
    Thanks!

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    "WHERE [studentschedules].[StudentID] = " & cID
    You want the value of cID, not the litteral string "cID"
    Have a nice day!

  6. #6
    Join Date
    Sep 2011
    Posts
    5

    Syntax Error

    Thanks for the help! I tried what you said and it gives me a syntax error and highlights this:

    Code:
    strSQL = "SELECT studentschedules.[StudentID], studentschedules.[CourseID], studentschedules.[CourseName] " & _
             "FROM [studentschedules] " & _
             "WHERE [studentschedules].[StudentID] = " cID

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    You're missing the '&'.

    Copy and paste:

    Code:
    "WHERE [studentschedules].[StudentID] = " & cID
    and you should be golden...
    Looking for the perfect beer...

  8. #8
    Join Date
    Sep 2011
    Posts
    5

    Weird

    Thanks!

    I added the &, now it says run-timme error "6" overflow and highlights this

    Code:
    cID = realid.Value
    Thanks for your help, I really appreciate it.

  9. #9
    Join Date
    Sep 2011
    Posts
    5

    but

    BUT, it does properly add the headings if I hit enter

  10. #10
    Join Date
    Sep 2011
    Posts
    5

    fixed

    ok, fixed. Instead of Long use Double (the student IDs had 14 digits)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •