Update Combo Box with Requery

[schreinman]

Within a form with combo boxes, the user can click on the label for the combo box if the desired selection is not present. This opens another form where the new option can be added. After closing out of that form I want the new option to appear in the combo box but can't seem to find the correct event to do this. AfterUpdate event on the combo box will not show the new selection until the user first selects another option and then can reselect the newly added option.

How can I get the combo box to show the newly added option the first time?

Thanks for any help you can share on this.

 

[Poppa Smurf]

In the Got Focus event of your combo box use the following line.

DoCmd.Requery "name of combo box"

 

[John Big Booty]

The On Click event for your the label probably looks something like this;

    Dim lngcombo As Long

    If IsNull(Me![ComboName]) Then
        Me![ComboName].Text = ""
    Else
        lngcombo = Me![ComboName]
        Me![ComboName] = Null
    End If
    DoCmd.OpenForm "frm_YourFormName", , , , , acDialog
    [COLOR="Purple"][B]Me![ComboName].Requery[/B][/COLOR]
    If lngcombo <> 0 Then Me![ComboName] = lngcombo

Your code is probably missing the higlighted line of code.

 

[schreinman]

Thank you to Poppa Smurf and John BB for your help! I can now move on to the next thing...

 

[Dairy Farmer]

Why not add a Not In List sub? That way the user can add what they want. If it doesn't exist it will ask them if they wish to add it. The list form doesn't open and the new item is auto selected.

Private Sub [COLOR=red]ComboName[/COLOR]_NotInList(NewData As String, Response As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strMsg As String
 
        strMsg = "'" & NewData & "' is not an available [COLOR=red]Item[/COLOR]" & vbCrLf & vbCrLf
        strMsg = strMsg & "Do you want to add '" & NewData & "' ?"
        strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to add or No to select an [COLOR=red]Item[/COLOR]."
 
    If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new [COLOR=#ff0000]Item[/COLOR]?") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("[COLOR=red]TableItem[/COLOR]", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs![COLOR=#ff0000]Field1[/COLOR][COLOR=#000000] = NewData[/COLOR]
'if you have more than 1 field to fill in
        rs![COLOR=red]Field2[/COLOR] = InputBox("What is the [COLOR=#ff0000]Field2[/COLOR]?", "[COLOR=#ff0000]Field2[/COLOR][COLOR=#000000] ")[/COLOR]
        rs![COLOR=red]Field3[/COLOR] = InputBox("What is the [COLOR=#ff0000]Field3[/COLOR]?", "[COLOR=#ff0000]Field3[/COLOR][COLOR=#000000] ")[/COLOR]
        rs.Update
 
        If Err Then
            MsgBox "An error occurred. Please try again."
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If
 
    End If
 
    Set rs = Nothing
    Set db = Nothing
End Sub

The add

Private Sub [COLOR=#ff0000]ComboName[/COLOR]_GotFocus()
    Me![[COLOR=#ff0000]ComboName[/COLOR]].Requery
End Sub