Solved

How to move data from list box to table?

Posted on 2014-01-21
14
416 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

24 Experts available now in Live!

Get 1:1 Help Now