Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Populate combo box from code

Posted on 2014-01-08
3
Medium Priority
?
606 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 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 39

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

618 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