MS Access: Insert a selected entry from Combo Box into table

Question

This is an example of what I am doing using MS Access. I have a table with people names and two text fields for adding telephone numbers. I created a list box with names. I managed to insert selected names from the list box and telephone numbers form the text fields (Tel1 and Tel2) into a table (ContactTable). I used the script as shown below. How can I change this script to use combo box instead of list box.

Private Sub ListBoxEnter_Click()

    Dim Name As String
    Dim Tel1 As Integer
    Dim Tel2 As Integer

    If ListBox.ItemsSelected.Count = 1 Then
        Name = ListBox.Value
        Tel1 = Tel1field
        Tel2 = Tel2field

        values = "VALUES ("
        values = values & "'" & Person_Id & "','" & Name & "','" & Tel1 & "','" & Tel2 & "')"

        SQL = "INSERT  INTO ContactTable (Person_Id, Name, Tel1, Tel2)"
        SQL = SQL & values
        DoCmd.RunSQL SQL
        Me.Tel1.Value = Null
        Me.Tel2.Value = Null
   End If

End Sub

Gustav · Accepted Answer · 2017-07-03T14:56:27.027

2

Just replace with the combobox:

Private Sub ListBoxEnter_Click()

    Dim Name As String
    Dim Tel1 As Integer
    Dim Tel2 As Integer

    If Not IsNull(Me!ComboBox.Value) Then
        Name = Me!ComboBox.Value
        Tel1 = Tel1field
        Tel2 = Tel2field

        values = "VALUES ("
        values = values & "'" & Person_Id & "','" & Name & "','" & Tel1 & "','" & Tel2 & "')"

        SQL = "INSERT  INTO ContactTable (Person_Id, Name, Tel1, Tel2)"
        SQL = SQL & values
        DoCmd.RunSQL SQL
        Me.Tel1.Value = Null
        Me.Tel2.Value = Null
   End If

End Sub

if you have two columns in the combobox:

FirstName = Me!ComboBox.Value     ' The bound column, often column 0.
LastName = Me!ComboBox.Column(1)  ' The second/other column

edited Jul 03 '17 at 14:56

answered Jul 03 '17 at 14:12

Gustav

53,498
7
29
55

Sorry, If I want to split the name filed into 2 fields for first name and last name, do I need to create two combo box/list box for the first and last name or one combo box/list box with 2 columns is OK. How can I use “Name = ListBox.Value” to add the first and last names fields to the ContactTable?. – cbebgis Jul 03 '17 at 14:52
Yes, you can have two or more columns in a combobox. See edited answer. – Gustav Jul 03 '17 at 14:57
Thank you once again, brilliant! – cbebgis Jul 03 '17 at 15:07

MS Access: Insert a selected entry from Combo Box into table

1 Answers1

Linked