By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Insert query in VBA access

P: 1
hi, I have a problem with a insert query in vba acces. each time run it, there is an error message "run-time error 424, Object require"
Expand|Select|Wrap|Line Numbers
  1. Private Sub Save_Click()
  2.     Dim dbs As Database
  3.     Set dbs = CurrentDb
  4.     'Insert all records of selected film id in tickets table
  5.      dbs.Execute ("INSERT INTO tickets_tbl ( pid, seats, total )SELECT projections_tbl.pid, temp1.seats, " & Str(Froms!tickets!total) & " FROM Projections_tbl, temp1 WHERE sel = true and Projections_tbl.rid = temp1.rid")
  6.     'Update number of seats left in projection table
  7.     dbs.Execute ("UPDATE projections_tbl INNER JOIN temp1 ON projections_tbl.rid = temp1.rid SET seats_left = seats_left-temp1.seats WHERE fid = " & Str(Forms!tickets!fid) & "")
  8.     'Delete all contents in temp1
  9.     dbs.Execute ("DELETE * FROM temp1")
  10.     'clear total,paind and change
  11.     Me.paid = ""
  12.     Me.change = ""
  13.     Me.fid = ""
  14.     Me.title = ""
  15.     Me.duration = ""
  16.     Me.rated = ""
  17.     Me.Refresh
  18. End Sub
the dbs.execute ("insert into...") is highlighted!!

can anyone help me?
thanks
tarounen.
Mar 27 '12 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I have two suggestions for you :
  1. As the items from the DAO library and the ADODB library overlap so much, it is very important to declare such objects explicitly :
    Expand|Select|Wrap|Line Numbers
    1. Dim dbs As DAO.Database
  2. Check out How to Debug SQL String. Your problem is with the SQL string so posting the VBA code is not too helpful. It would make sense to post the SQL string so that it's readable. Posting it all in a small number of very long lines will be appreciated by no-one, which might mean they look elsewhere for questions to help with ;-)
Mar 27 '12 #2

mshmyob
Expert 100+
P: 903
I don't know if this part is a typo

Expand|Select|Wrap|Line Numbers
  1. Str(Froms!tickets!total)
  2.  
but it should be Forms not Froms.

Also your last quote should be after the ) not before.

Expand|Select|Wrap|Line Numbers
  1. Projections_tbl.rid = temp1.rid") 
  2.  
cheers,
Mar 28 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.