Adding value to list box

Posted on 2009-02-21
Last Modified: 2012-05-06
How do I add the selected value from a combo box to a list box.  The list box is bound so using lstboxname.additem generates error that rowsource must be Value List.  Please help
Question by:chobe
    LVL 61

    Expert Comment

    If you need to keep the listbox rowsource in table/query format, you need to update the undelying table with the selected value:

    Dim strSQL as string
    strSQL = "INSERT into MyTable (MyField) VALUES ('" & me.MyCombo & "')" '<--- for text
    strSQL = "INSERT into MyTable (MyField) VALUES (" & me.MyCombo & ")" '<--- for numeric
    Currentdb.execute strSQL , dbfailonerror

    This code would go in the After Update event of your combo.
    Note that text needs to be delimited with quotes (first SQL string)
    Numeric data needs no delimiters (second SQL string)
    Pick the correct format depending on your field's datatype.
    It may also be prudent to add code to check your table to make sure you are not adding duplicate values to your table.
    LVL 9

    Expert Comment

    This also works, and will allow multiple diff entries sources:
    Dim strSQL as string = Format("INSERT into MyTable (MyField) VALUES ('{0}') " , me.comboBox1.SelectedItem)
    Dim strSQL as string = Format("INSERT into MyTable (MyField , Myotherfield) VALUES ('{0}' , '{1}') " , me.comboBox1.SelectedItem, textbox1.text)

    Author Comment

    In my stupidity I overlooked the need to link this value to the current record.  The record number is in a second text box.  The code from mbizup is straight forward and works, but only write 1 value to the table.  I get errors trying Hawkvalley1's code (.SelectedItem) and "Control must have focus" which I don't understand.  I tried to modify mbizup's code with idea from Hawkvalley1 but get type mis-match error.

    strSQL = "INSERT into tblPCRs (pcr_ID,[Project Number]) VALUES (" & Me.cboChgControlList & ", " & Me.TxtProjectNumber & ")" '<--- for numeric
    LVL 61

    Accepted Solution

    <I get errors trying Hawkvalley1's code >

    I believe Hawkvalley1's code is VB, which is not syntactically equivalent to Access VBA.
    In Access VBA, you cannot set the value of a variable in a Dim statement.

    <type mis-match error>
    What are the data types of the fileds in question?
    I'm guessing that ID is numeric (no delimiters needed) and ProjectNumber is text (needs quotes to delimit text). Look closely at the quotes surrounding Project Number - there are embedded single quotes:

    strSQL = "INSERT into tblPCRs (pcr_ID,[Project Number]) VALUES (" & Me.cboChgControlList & ", '" & Me.TxtProjectNumber & "')"


    strSQL = "INSERT into tblPCRs (pcr_ID,[Project Number]) VALUES (" & Me.cboChgControlList & ", " & chr(34) & Me.TxtProjectNumber & chr(34) & ")"

    LVL 9

    Expert Comment

    My Mistake...

    Author Closing Comment

    Thanks mbizup - that did the trick!

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now