• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

How to move data from list box to table?

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
Megin
Asked:
Megin
  • 6
  • 6
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
IrogSintaCommented:
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
 
MeginAuthor Commented:
It still isn't working.

When I look at the code after the error, "lsttype" is highlighted.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Rey Obrero (Capricorn1)Commented:
ok, double check the name of the listbox, and correct the codes accordingly
0
 
MeginAuthor Commented:
I have triple checked that and I didn't get the name of the listbox wrong.
0
 
Rey Obrero (Capricorn1)Commented:
from VBA window, do a DEBUG > Compile
correct all errors raised.

better is, upload a copy of your db
0
 
MeginAuthor Commented:
The problem is with frm_Act1TO.
PMAC-Weekly-Report-Database-Back.accdb
0
 
Rey Obrero (Capricorn1)Commented:
there is no form " frm_Act1TO "
0
 
MeginAuthor Commented:
Yes there is. It is under the Unassigned Objects category, third from the bottom.
0
 
Rey Obrero (Capricorn1)Commented:
check the db that you uploaded
0
 
MeginAuthor Commented:
Oops! I think I uploaded the backup.  Here is the real one.  Sorry about that!
PMAC-Weekly-Report-Database.accdb
0
 
Rey Obrero (Capricorn1)Commented:
I think your form is corrupted, try  to create another form.
0
 
IrogSintaCommented:
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
 
MeginAuthor Commented:
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now