Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    228

    Unanswered: repopulate combo box after addition

    I feel like I'm doing too much work for what I need done. I have a combo box that will be populated with entries already in the database. The user is not limited to entries in the list though. If they choose to type in a new entry I want it to appear in the combo box immediately. The code I came up with is below but, like I said it seems like I'm doing too much for just a simple task...

    Private Sub Agency_Project_Number_AfterUpdate()
    Dim strSelect As String
    Dim strFrom As String
    Dim strWhere As String
    Dim strOrderBy As String
    Dim strSQL As String

    'save the current record
    DoCmd.RunCommand acCmdSaveRecord

    ' re-populate the drop down list
    strSelect = "SELECT DISTINCT tblAngencyInfo.[Agency Project Number] "
    strFrom = "FROM tblAngencyInfo "
    strWhere = "WHERE (((tblAngencyInfo.[Agency Project Number]) Is Not Null)) "
    strOrderBy = "ORDER BY tblAngencyInfo.[Agency Project Number]"
    strSQL = strSelect & strFrom & strWhere & strOrderBy
    Me.[Agency Project Number].RowSource = strSQL
    End Sub

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by ottomatic
    I feel like I'm doing too much work for what I need done. I have a combo box that will be populated with entries already in the database. The user is not limited to entries in the list though. If they choose to type in a new entry I want it to appear in the combo box immediately. The code I came up with is below but, like I said it seems like I'm doing too much for just a simple task...

    Private Sub Agency_Project_Number_AfterUpdate()
    Dim strSelect As String
    Dim strFrom As String
    Dim strWhere As String
    Dim strOrderBy As String
    Dim strSQL As String

    'save the current record
    DoCmd.RunCommand acCmdSaveRecord

    ' re-populate the drop down list
    strSelect = "SELECT DISTINCT tblAngencyInfo.[Agency Project Number] "
    strFrom = "FROM tblAngencyInfo "
    strWhere = "WHERE (((tblAngencyInfo.[Agency Project Number]) Is Not Null)) "
    strOrderBy = "ORDER BY tblAngencyInfo.[Agency Project Number]"
    strSQL = strSelect & strFrom & strWhere & strOrderBy
    Me.[Agency Project Number].RowSource = strSQL
    End Sub

    Hey, why don't you try this in the AfterUpdate event of the SaveCommandButton:

    DoCmd.Requery "Agency Project Number"

    Oh, and just one tip that I have learned....correct your naming conventions for your controls. DO NOT USE SPACES... If you must use... the underscore ( _ ) or do as I am many other do....i.e.

    txtFName = a first name text box
    cboItems = an Items ComboBox

    as you have above...it should be this:
    tblAgencyProjectNumber.

    And one further tip, it is sometimes best to base your forms and reports on QUERIES, and not the tables themselves. Hope this helps you out.

    have a nice one,
    Bud

Posting Permissions

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