Solved

How to move data from list box to table?

Posted on 2014-01-21
14
415 Views
Last Modified: 2014-01-22
I am using vba code behind a button that is supposed to move data entered into my form into a table.

I am trying to use the same code that I have used in my other forms, but this time I have a list box included in the form and the code is throwing an error at me when it gets to that part.

Are list boxes handled differently in VBA?

Here is my code:

Private Sub cmdAdd_Click()

Dim strSql As String

strSql = "INSERT INTO Activities (ActTypeID, ActDesc, StoID, ToID) VALUES (" & Me.lsttype & ", " & Me.txtdesc & ", " & Me.cmbSTO & ", " & Me.cmbTO & "')"
CurrentDb.Execute strSql, dbFailOnError

End Sub

Open in new window


The error message is: "Compile Error: Method or data member not found"
0
Comment
Question by:Megin
  • 6
  • 6
  • 2
14 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798696
if these fields (ActTypeID, ActDesc, StoID, ToID) are all Number Data types
use this

strSql = "INSERT INTO Activities (ActTypeID, ActDesc, StoID, ToID) VALUES (" & Me.lsttype & ", " & Me.txtdesc & ", " & Me.cmbSTO & ", " & Me.cmbTO & ")"
CurrentDb.Execute strSql, dbFailOnError


If any of the fields above is Text Data type, lets say  (ActDesc)  , use this

strSql = "INSERT INTO Activities (ActTypeID, ActDesc, StoID, ToID) VALUES (" & Me.lsttype & ", '" & Me.txtdesc & "', " & Me.cmbSTO & ", " & Me.cmbTO & ")"
CurrentDb.Execute strSql, dbFailOnError
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39798724
Looks like you have an single quote at the end of your SQL string.  Assuming that ActDesc accepts Text data type and the rest are numbers, your string should be:
strSql = "INSERT INTO Activities (ActTypeID, ActDesc, StoID, ToID) VALUES (" & Me.lsttype & ", '" & Me.txtdesc & "', " & Me.cmbSTO & ", " & Me.cmbTO & ")"

Open in new window

Ron
0
 

Author Comment

by:Megin
ID: 39798729
It still isn't working.

When I look at the code after the error, "lsttype" is highlighted.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798732
ok, double check the name of the listbox, and correct the codes accordingly
0
 

Author Comment

by:Megin
ID: 39798739
I have triple checked that and I didn't get the name of the listbox wrong.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798740
from VBA window, do a DEBUG > Compile
correct all errors raised.

better is, upload a copy of your db
0
 

Author Comment

by:Megin
ID: 39798742
The problem is with frm_Act1TO.
PMAC-Weekly-Report-Database-Back.accdb
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798749
there is no form " frm_Act1TO "
0
 

Author Comment

by:Megin
ID: 39798754
Yes there is. It is under the Unassigned Objects category, third from the bottom.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798764
check the db that you uploaded
0
 

Author Comment

by:Megin
ID: 39798767
Oops! I think I uploaded the backup.  Here is the real one.  Sorry about that!
PMAC-Weekly-Report-Database.accdb
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39798786
I think your form is corrupted, try  to create another form.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39798809
The problem lies with Me.txtdesc.  This doesn't exist, it should be Me.txtactdesc.  I changed that and your code worked.  I'm not sure why it highlights lsttype... it's confused I guess :-)

Ron
0
 

Author Closing Comment

by:Megin
ID: 39800341
I swear, more than half the time the problem is something like that! Grrr.

Thank you very much. The code works now!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
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.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

17 Experts available now in Live!

Get 1:1 Help Now