Solved

Populate combo box from code

Posted on 2014-01-08
3
592 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 34

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help writing a query 6 71
Syntax Error in Query 7 30
append to an ms access field 6 16
What logic to build in order to get a weekly reminder 9 38
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

914 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now