[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Adding value to list box

Posted on 2009-02-21
Medium Priority
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
  • 2
  • 2
  • 2
LVL 61

Expert Comment

ID: 23702631
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.

Expert Comment

ID: 23702978
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

ID: 23703216
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

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 61

Accepted Solution

mbizup earned 400 total points
ID: 23703271
<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) & ")"


Expert Comment

ID: 23703275
My Mistake...

Author Closing Comment

ID: 31549675
Thanks mbizup - that did the trick!

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

868 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