Solved

How to move data from list box to table?

Posted on 2014-01-21
14
420 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
14 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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 …

691 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