Insert query in VBA access

[Tarounen Nursoo]

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"

Private Sub Save_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
    'Insert all records of selected film id in tickets table
     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")
    'Update number of seats left in projection table
    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) & "")
    'Delete all contents in temp1
    dbs.Execute ("DELETE * FROM temp1")
    'clear total,paind and change
    Me.paid = ""
    Me.change = ""
    Me.fid = ""
    Me.title = ""
    Me.duration = ""
    Me.rated = ""
    Me.Refresh
End Sub

the dbs.execute ("insert into...") is highlighted!!

can anyone help me?
thanks
tarounen.

[NeoPa]

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 :
   

   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 ;-)

[mshmyob]

I don't know if this part is a typo

Str(Froms!tickets!total)

but it should be Forms not Froms.

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

Projections_tbl.rid = temp1.rid")

cheers,