none
does the combo box wizard save data somewhere, or do I have to go get it RRS feed

  • Question

  • The combo box wizard contains this statement:

    “When you select a row in the combo box, you can store a value from that row in your database, or you can use the value later to perform an action.”

    When it says “you can use the value later”, I take that to mean “deal with the selected value using some VBA”.

    But is there a field or property of the combo box I’m overlooking?


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Monday, July 3, 2017 9:33 PM

Answers

  • Hi Peter,

    >> But is there a field or property of the combo box I’m overlooking?

    You need to use VBA, and a simple code like below to get value and text.

    Private Sub Combo13_Change()
    Debug.Print Combo13.Value, Combo13.Text
    End Sub
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 7:55 AM
  • When it says “you can use the value later”, I take that to mean “deal with the selected value using some VBA”.

    Hi Peter,

    I am not too good in that formal use of language, but I would think if a combo box can find its data, then you can also find it, on any moment, but - of course - with a little bit of VBA.

    Imb.

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 9:12 AM
  • A bound combo box allows you to select and store a value in a column in the current row of the table on which the form is based.

    An unbound combo box allows you to select a value and perform some action on the basis of the selected value, e.g. in the combo box's AfterUpdate event procedure the following moves the form to the selected contact or to an empty new record to enter a new contact:

    Private Sub cboGotoContact_AfterUpdate()

        Const MESSAGETEXT = "No matching record"
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                ' go to new record and move focus to FirstName control
                DoCmd.GoToRecord acForm, Me.Name, acNewRec
                Me.FirstName.SetFocus
            Else
                With Me.RecordsetClone
                    .FindFirst "ContactID = " & ctrl
                    If Not .NoMatch Then
                        ' go to record by synchronizing bookmarks
                        Me.Bookmark = .Bookmark
                    Else
                        MsgBox MESSAGETEXT, vbInformation, "Warning"
                    End If
                End With
            End If
        End If
        
    End Sub

    The RowSource property of the combo box in this example is:

    SELECT ContactID, FirstName & " " & LastName, 1 AS SortColumn,
    LastName,Firstname  FROM Contacts  
    UNION
    SELECT 0, "<New Contact>", 0,"",""  
    FROM Contacts
    ORDER BY SortColumn, LastName, FirstName;

    The value of the combo box is not 'stored' anywhere as such, other than in memory, it is assigned to the control's Value property by selecting the row in the control and is only available while that row is selected.  If you wanted to store the value and use it later, after the form has been closed for instance, then you would need to assign its value to a variable, or pass it to another form or report via the OpenArgs mechanism, in the AfterUpdate event procedure.

    Ken Sheridan, Stafford, England

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 11:37 AM

All replies

  • Hi Peter,

    >> But is there a field or property of the combo box I’m overlooking?

    You need to use VBA, and a simple code like below to get value and text.

    Private Sub Combo13_Change()
    Debug.Print Combo13.Value, Combo13.Text
    End Sub
    

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 7:55 AM
  • When it says “you can use the value later”, I take that to mean “deal with the selected value using some VBA”.

    Hi Peter,

    I am not too good in that formal use of language, but I would think if a combo box can find its data, then you can also find it, on any moment, but - of course - with a little bit of VBA.

    Imb.

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 9:12 AM
  • A bound combo box allows you to select and store a value in a column in the current row of the table on which the form is based.

    An unbound combo box allows you to select a value and perform some action on the basis of the selected value, e.g. in the combo box's AfterUpdate event procedure the following moves the form to the selected contact or to an empty new record to enter a new contact:

    Private Sub cboGotoContact_AfterUpdate()

        Const MESSAGETEXT = "No matching record"
        Dim ctrl As Control
        
        Set ctrl = Me.ActiveControl
        
        If Not IsNull(ctrl) Then
            If ctrl = 0 Then
                ' go to new record and move focus to FirstName control
                DoCmd.GoToRecord acForm, Me.Name, acNewRec
                Me.FirstName.SetFocus
            Else
                With Me.RecordsetClone
                    .FindFirst "ContactID = " & ctrl
                    If Not .NoMatch Then
                        ' go to record by synchronizing bookmarks
                        Me.Bookmark = .Bookmark
                    Else
                        MsgBox MESSAGETEXT, vbInformation, "Warning"
                    End If
                End With
            End If
        End If
        
    End Sub

    The RowSource property of the combo box in this example is:

    SELECT ContactID, FirstName & " " & LastName, 1 AS SortColumn,
    LastName,Firstname  FROM Contacts  
    UNION
    SELECT 0, "<New Contact>", 0,"",""  
    FROM Contacts
    ORDER BY SortColumn, LastName, FirstName;

    The value of the combo box is not 'stored' anywhere as such, other than in memory, it is assigned to the control's Value property by selecting the row in the control and is only available while that row is selected.  If you wanted to store the value and use it later, after the form has been closed for instance, then you would need to assign its value to a variable, or pass it to another form or report via the OpenArgs mechanism, in the AfterUpdate event procedure.

    Ken Sheridan, Stafford, England

    • Marked as answer by Peter N Roth Tuesday, July 11, 2017 8:45 PM
    Tuesday, July 11, 2017 11:37 AM
  • Thank you all for verifying my understanding, and Ken for expanding it, as usual.

    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, July 11, 2017 8:47 PM