Megin
asked on
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:
The error message is: "Compile Error: Method or data member not found"
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 SubThe error message is: "Compile Error: Method or data member not found"
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 & ")"ASKER
It still isn't working.
When I look at the code after the error, "lsttype" is highlighted.
When I look at the code after the error, "lsttype" is highlighted.
ok, double check the name of the listbox, and correct the codes accordingly
ASKER
I have triple checked that and I didn't get the name of the listbox wrong.
from VBA window, do a DEBUG > Compile
correct all errors raised.
better is, upload a copy of your db
correct all errors raised.
better is, upload a copy of your db
ASKER
The problem is with frm_Act1TO.
PMAC-Weekly-Report-Database-Back.accdb
PMAC-Weekly-Report-Database-Back.accdb
there is no form " frm_Act1TO "
ASKER
Yes there is. It is under the Unassigned Objects category, third from the bottom.
check the db that you uploaded
ASKER
Oops! I think I uploaded the backup. Here is the real one. Sorry about that!
PMAC-Weekly-Report-Database.accdb
PMAC-Weekly-Report-Database.accdb
I think your form is corrupted, try to create another form.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I swear, more than half the time the problem is something like that! Grrr.
Thank you very much. The code works now!
Thank you very much. The code works now!
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