Solved

Populate combo box from code

Posted on 2014-01-08
3
595 Views
Last Modified: 2014-01-08
I am attempting to populate a combo box based on values chosen in two other combo boxes. When I run the generated SQL from the code I get correct values. The combo box remains blank however, if I manually use the drop down arrow, the value is there and I can chose it but I need it to set the combobox value automatically. Requery doesn't seem to do it...

How can I modify it so that it doesn't just generate the value but also sets it as the combo box choice?

Private Sub Course_Name_AfterUpdate()
    Dim strSQL As String
    With Me![CourseID]
        If IsNull(Me![Course Name]) Then
            Me.[CourseID].RowSource = ""
        Else
            strSQL = "SELECT tblCourses.[CourseID] FROM tblCourses WHERE tblCourses.[Course Name] = '" & Me![Course Name] & "'" & " AND tblCourses.category = '" & Me![Category] & "'"
            Me.[CourseID].RowSource = strSQL
            Me.CourseID.Requery
         End If
    End With
End Sub
0
Comment
Question by:boukaka
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39765372
You don't need the requery at the end... setting the rowsource is adequate.

Try adding this before the End Sub:

Me.CourseID =Me.CourseID.Itemdata(0)

Open in new window

0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39765412
Setting the RowSource doesn't populate the control.  Setting the RowSource will make a value "visible" if before the change, the value wasn't included in the RowSource.

For example, you have chosen Bridgeport as a city from a combo that is filtered by state.  If the state is NY, the combo will look empty since Bridgeport isn't in the RowSource but if you change the state to CT, then Bridgeport becomes visible.  Is this what you are talking about?

Combos have a bound ControlSource which specifies which column of the form's RecordSource the data is saved to.  They also have a bound RowSource which is a list of valid values.  The ControlSource may contain a value that is not in the RowSource and if it does, it will appear blank even though it isn't.
0
 

Author Closing Comment

by:boukaka
ID: 39765478
That's the ticket! Thanks :)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question