Solved

How to move data from list box to table?

Posted on 2014-01-21
14
419 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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 …

749 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