take values from listbox on form and INSERT INTO table

I have a piece of code that I am trying to use to insert values from a 
listbox on a form into a table, but I keep getting an error message:
"Microsoft Access can't find the field 'frmROEsList' referred to in your 
expression."

It stops right at the line:
Set frm = form!frmROEsList

I thought this line is supposed to define the form, frmROEsList, but Access 
is thinking there needs to be a field.  Why/what field?  I am trying to get 
the data from the listbox.  

Here is my code as is when a user clicks a line in the listbox:
Private Sub lstOracleLinesForROEs_Click()
Dim frm As Access.Form
Dim ctl As Control
Dim db As DAO.Database
Dim varItem As Variant

Set db = CurrentDb
Set frm = Form!frmROEsList
Set ctl = frm!lstOracleLinesForROEs

For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO tblTest(Oracle#)" & " VALUES (""" & 
ctl.ItemData(varItem) & """)"
Next varItem
End Sub


0
Utf
8/27/2007 4:22:03 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
2244 Views

Similar Articles

[PageSpeed] 11

After form! you need the name of the form, then the control name

-Dorian

"worksfire1" wrote:

> I have a piece of code that I am trying to use to insert values from a 
> listbox on a form into a table, but I keep getting an error message:
> "Microsoft Access can't find the field 'frmROEsList' referred to in your 
> expression."
> 
> It stops right at the line:
> Set frm = form!frmROEsList
> 
> I thought this line is supposed to define the form, frmROEsList, but Access 
> is thinking there needs to be a field.  Why/what field?  I am trying to get 
> the data from the listbox.  
> 
> Here is my code as is when a user clicks a line in the listbox:
> Private Sub lstOracleLinesForROEs_Click()
> Dim frm As Access.Form
> Dim ctl As Control
> Dim db As DAO.Database
> Dim varItem As Variant
> 
> Set db = CurrentDb
> Set frm = Form!frmROEsList
> Set ctl = frm!lstOracleLinesForROEs
> 
> For Each varItem In ctl.ItemsSelected
> db.Execute "INSERT INTO tblTest(Oracle#)" & " VALUES (""" & 
> ctl.ItemData(varItem) & """)"
> Next varItem
> End Sub
> 
> 
0
Utf
8/27/2007 4:46:00 PM
Reply:

Similar Artilces:

Subtracting values in a query
I have a query with multiple repeating values, each value has a set of readings assigned to it How do I subtract the max/min from the readings for each value (looking for change over time)? Example: Value Reading over time 1 2 1 3 1 0.5 2 3 2 4 2 1 3 7 3 2 3 0.3 I would like Access to automatically subtract 3 - 0.5 for value 1; 4 - 1 for value 2; and so on. Is this possible? Thanks! Replace 'z' with your [Value] and 'x' ...

Does anyone know of a software application for Pivot tables other. #2
We have some very complex pivot tables in Excel that unfortunately are not very stable or flexible when you make changes. The data list is used to generate multiple pivot tables in the same workbook but the data and the number of records can change frequently. One time we might have 18000 records and the next time we might have 2500 records. So far it seems that everytime we have to change the data we have to reset the data source range for each pivot table. I believe each pivot table was copied via Edit, Move or Copy, rather than being generated as anew pivot table or based on anot...

Display Custom Market & Value
Hi Maybe this is a simple chart example but I am not able to figure it out. I have some data in the format below. Date 1 Value 1 Jan 10 2 Jan 20 3 Jan 40 4 Jan 30 5 Jan 50 and so on (around 1000 entries) Now I have some events that happen at certain values. Like at 30 there is a positive event that has a value +10%. Lets say at 40 value, I have a negative event has a value -10%. I store this information in two columns. I can also concatenate it if needed. I want the Text "P 10%" along with a Marker at the value ...

Bookmarks take me away from email
I use "Actions > Send Web Page by E-mail". Then setup the bookmark, and a link to the bookmark in Outlook. This link takes the recipient to the web page (www.webpage.com#bookmark), rather than jumping them to that part of the email. It seems like this should be possible since you can set the link to go to a bookmark. I don't even get how it thinks I would possibly want it to go to a web page. Help is much appreciated! ...

Creating a Chart in an Access Form
I am trying to create a chart in an Access form. The data I need displayed is set up in 10 columns in a table. Each column represents a point, where the field name is the x-value and the number entered is the y-value. How can I display these 10 points as a line on a chart? The table is set up like this: Heading: Xval1 Xval2 Xval3 Xval4 Xval5 .... ________________________________________ Record1: Yval1 Yval2 Yval3 Yval4 Yval5 .... Record2: Yval1 Yval2 Yval3 Yval4 Yval5 .... I set the table up this way to simplify y-value data entry and because the x-values are c...

Concatenate spaces before each alt-enter seperated value in a cell?
I hope I am making sense. Within one cell, I have several values seperated by alt-enter. So it looks this: abcdef <alt-enter> abcdef <alt-enter> abcedf <alt-enter> I want spaces before each "abcdef" so it will look like this: abcdef <alt-enter> abcdef <alt-enter> abcedf <alt-enter> This gets close: =REPT(" ",4)&SUBSTITUTE(A1,CHAR(10),CHAR(10)&REPT(" ",4)) But it actually adds extra characters at the right end of the string. Change the 4 (in both spots) to how many space characters you want. wa...

Take Ownership
What does it mean to Take Ownership of a file or folder? <PeoplesChoice@Chicago.net> wrote in message news:gjqqv5ddgj7uesie7vdej1tshbv49unr1u@4ax.com... > What does it mean to Take Ownership of a file or folder? It means to transfer ownership of an object from the original owner to you, (your account). Google ' Take Ownership ' for more information. To take ownership of a file or a folder How to take ownership of a file You must have ownership of a protected file in order to access it. If another user has restricted access and you are the computer administ...

Recall Deleted Form
I accidently deleted a very large form. By backup CD for some reason cannot be read. Is there any way I can recall a recently deleted form? Thanks Len On Thu, 12 Apr 2007 04:06:00 -0700, Len <Len@discussions.microsoft.com> wrote: >I accidently deleted a very large form. By backup CD for some reason cannot >be read. Is there any way I can recall a recently deleted form? > >Thanks > >Len Unfortunately, probably not. Note that a backup CD will have the database marked read-only - you can copy it to your hard disk and clear the Read Only property by viewing the...

Options Button (forms Contrrol)
When I add pairs of option buttons in group boxes, when i get to the third pair the button automatically the control cell from the first group. The happens with subsequent entries too. Adjust the size of your group boxes so it doesn't overlap the other group boxes or option buttons. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/showthread.php?t=165042 [url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url] Thanks joel. The group...

Restrict number of records returned by query on form
I have a form with a list box on it. The list box displays the result of a query. The Openargs which is input to the form contains a number. I want to limit the list box to that number of records. That's all. It can display any of the records as long as the number does not exceed this input variable number. The rest of the records do not appear. What is the simplest way of doing this? Should I try to put a SELECT TOP or something like that in my query or should I add a numbering column and use the where clause to select only those records <= the openargs. Robert &qu...

Can I print a 18" form in Excel
I need to print a custon contract form excel. I do not have the parper size I need is not in the drop down in the page set box? Excel should adapt to whatever printer you have, if you don't have a printer where you can customize the print size then excel won't give that choice either, it's all in the printer -- Regards, Peo Sjoblom "peetzaman" <peetzaman@discussions.microsoft.com> wrote in message news:EA9F0EB0-981D-43D2-8DFC-9472D0EBDA8C@microsoft.com... > I need to print a custon contract form excel. I do not have the parper size > I need is not in...

Trying to lookup based on a condition of two values
I have three columns A, B and C. I want too look up the value in C where A and B match different values. Example: A, B ,C apple, color, green apple , price, 30 lemon, taste, tangy lemon, price 20 I want to lookup A=apple B=price to return the price 30. Assuming that the combination of A + B is unique... =SUMPRODUCT(--(A2:A10="apple"),--(B2:B10="price"),C2:C10) Better to use cells to hold the criteria. E2 = apple F2 = price =SUMPRODUCT(--(A2:A10=E2),--(B2:B10=F2),C2:C10) -- Biff Microsoft Excel MVP "Trying to lookup based on a c...

formulas show up instead of values
I have data that came from a mainframe. The cells aer formated as General. If A1 is 3 and in B1 I enter =A1, what I see in B1 is =A1 instead of 3. I've reformated A1 to a format of number but it doesn't change B1 - it still says =A1. I do not have view formulas turned on under Tools/Options/View. What else could this be? Change the format of B1 to general (it's probably set to text at this time) -- Michael Hopwood (Phobos) "Sherry" <nowhere@microsoft.com> wrote in message news:#iXHnsrsDHA.1196@TK2MSFTNGP12.phx.gbl... > I have data that came from a mainframe...

How can I show values, not formulas in Excel?
When I add a VLOOKUP function and fill down, the formula, not the resultant value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in not checked. Thanks! Could be that your cells are formatted as text, format as General. HTH JG "jwbass" wrote: > When I add a VLOOKUP function and fill down, the formula, not the resultant > value, is displayed in the cells. The DISPLAY FORMULA checkbox in OPTIONS in > not checked. > > Thanks! Make sure it is not formatted as text -- mphell0 -------------------------------------------------------------...

calculation taking too long
Currently I have a sheet where I enter my data and then another sheet within the same workbook that makes various calculations based on this data. But every time I enter data, it takes a long time for my formulas to calculate, upwards of a minute. I am pretty sure this is because my formulas contain entire columns as ranges (i.e. A:A rather than A1:A100). However I need to use A:A in my formulas as my data sheet is indefinitely long and I will continue to add to this over the years. I don't want to have to go back and change all my formulas to A1:A200 every time my data e...

Inserted excel files into publisher document (printing problems)
I have imbedded some excel files (charts, graphs, etc.) into a publisher document. The files are linked to the original excel file so that they will automatically update when the document is opened. The formatting and everything looks great. However, when I print the document, the excel grids are printed. The gridlines are not set up to print in the original .xls file. Does anyone know how I can keep the gridlines from printing? ...

Adding up non numerical values
In one column I will have a variety of letters. (A, B, C etc) In another cell I want to have the sum of all the A's in that column. In a different cell I want to have the number of B's in the column - and so on. How do I do this? Thanks in advance! Try this: =COUNTIF(A1:A100,"A") =COUNTIF(A1:A100,"B") etc., where I have assumed your data is in A1:A100. Hope this helps. Pete On Sep 21, 9:10 am, Wooster <Woos...@discussions.microsoft.com> wrote: > In one column I will have a variety of letters. (A, B, C etc) In another > cell I want to have ...

Deletion service cannot clean up tables
I get the deletion service logging in the Application log every 10 minutes the following error: Event ID: 5895 Source MSCRMDeletionService Error: Can't clean up the following tables: Account; Activity. I have CRM and SQL on separate machines and used a custom user, promoted as a domain admin to install CRM???? Is this something I can clean up in CRM or is there a log for this service somewhere???? Many thanks Chris. Chris, There is a known issue with the deletion service when it tries to delete records that have attacments associated with them. You can manually fix this by...

sumif values are same
How do I sum column A if the value C is common? A B C 15.2 2 aa1 23.0 1.5 aa1 12.3 1.5 aa1 0.7 1.5 aa1 15.2 2 bb1 15.2 2 cc1 23.0 1.5 cc1 12.3 1.5 cc1 0.7 1.5 cc1 Try this: =3DSUMIF(C:C,"aa1",A:A) will add all the values in column A which have a value of aa1 in the corresponding cell of column C. Hope this helps. Pete On Apr 4, 9:13=A0am, "crapit" <biggerc...@yahoo.com> wrote: > How do I sum column A if the value C is common? > > A =A0 =A0 =A0 =A0 =A0B =A0 =A0 =A0 =...

Calculating a value in a form that fills in the original table
I use a form that has 2 subforms. In one of the subforms I type in a product number, which automatically brings up the value of that product. Then I tab over to enter the quantity. I wanted the form to automatically fill in the total price (quantity x unit price) when I enter the quantity, which I was able to get it to do by going to properties, but what it doesn't do is fill that value (total price) into the table that lists the total price. It does fill it in if I do not have that formula in the form and I just enter the total price by hand. Is there anyway to set it up to au...

Creating a form question
I'm a tecaher and we need to do head counts every day by period. Is there a way to create a form that will allow each teacher to input their information and then send it to a secretary who can merge all of the forms together to get all the info into one document and also a final total of students for each period of the day? I've created a form in word for teachers to fill out, but I'm not quite sure about combining all of the info - or if it's even possible. Thanks! ...

Script to insert Row
How would I write a scrtip to insert a row under the number in A that holds a "NS" or "MT"? I would also like it to copy the row that has "NS" or "MT" in it and paste it in the row that was inserted underneith. When it copies the row it will copy the whole row except what is in A. Example Before A B 1 MT 12 2 D 14 3 x 15 Example After 1 2 1 MT 12 2 12 3 D 14 4 X 15 Thanks Sub CopyInsert() 'Find the last cell in column A ...

Getting value of Option button
Hi I put some option buttons (radio buttons) on an Excel worksheet and found it hard to get the values of these buttons. When using f.ex. For Each S In ActiveSheet.Shapes msgbox s.Name Next S I can see that all the buttons are on the sheet but I can't read tha value, which is the mai goal to use option button. Any help would be greatly appreciated. Blind gate What kind of optionbutton--from the control toolbox toolbar or from the Forms toolbar? 'control toolbox toolbar MsgBox Worksheets("sheet1").OptionButton1.Value 'forms toolbar MsgBox...

Paste values and Number format
In a file, I had a column set to have 1 decimal place. When I paste values from another area, the formatting does not stay, but instead the number of decimal places from the original spot is used. Even if I change the number format afterward, it will not take. If I type in a number in that cell, it will round to the correct decimal place. If I create a new file, the number format works. This file has conditional formatting, but if I remove it, the decimal place problem remains. What else can I try to troubleshoot this? Hi maybe use 'Edit - Paste Special - Values' -- Regard...

Function in Table
I have a question, can you set up a table to automatically add or subtract two fields in that table. I have two currency values and I want the one to subtract the one value from the other if it is higher than the one and if it is not then I want it to add. Is there anyway that it can happen, please let me know. Thanks In a word: No. However you can do this in a query, form, or report as needed. That's usually the best way to handle this. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Anthony" wrote: > I ...