asked on

Delete SQL 3219

Set rsInvoice = db.OpenRecordset("DELETE Invoice.* FROM Invoice")

Its says runtime error 3219 on this row, the whole code:

Dim Cost As Integer
Dim Total As Integer
Dim iFrom As Integer
Dim iTo As Integer
Dim Found As Boolean
Dim bTransfer As Boolean
Dim db As DAO.DataBase
Dim rsArrivals As DAO.Recordset
Dim rsInvoice As DAO.Recordset
Dim rsTransfer As DAO.Recordset
Dim rsRates As DAO.Recordset
Dim rsMisc As DAO.Recordset
Dim rsHotel As DAO.Recordset
Dim rsLocation As DAO.Recordset
Dim rsInvoiceNumber As DAO.Recordset

' open Database Connection
Set db = CurrentDb()

' open recordset Arrivals
Set rsArrivals = db.OpenRecordset("SELECT *, Arrivals.[Party ID] AS ID FROM (Arrivals INNER JOIN Party ON Party.[Party ID] = Arrivals.[Party ID]) INNER JOIN [Tour Operator] ON Party.[Tour Operator] = [Tour Operator].[Tour Operator ID] WHERE Party.[Tour Operator] = " & Me![Tour Operator] & " AND Arrivals.[Arrival Date] BETWEEN #" & Me!Date1 & "# AND #" & Me!Date2 & "#")
If Not rsArrivals.EOF Then
Found = True
End If

' open recordset Invoice
Set rsInvoice = db.OpenRecordset("DELETE Invoice.* FROM Invoice")
Set rsInvoice = db.OpenRecordset("SELECT * FROM Invoice")

Do While Not rsArrivals.EOF

' add new record to Table
rsInvoice.AddNew
rsInvoice![Reference ID] = rsArrivals![Reference ID]
rsInvoice![Party Name] = rsArrivals![Party Name]

Set rsTransfer = db.OpenRecordset("SELECT * FROM Transfers WHERE [Party ID] = " & rsArrivals!ID)
If Not rsTransfer.EOF Then
bTransfer = True
End If

Do While Not rsTransfer.EOF
Set rsHotel = db.OpenRecordset("SELECT Direction FROM Hotel WHERE Hotel = '" & rsTransfer![Transfer From] & "'")

If rsHotel.EOF Then
Set rsLocation = db.OpenRecordset("SELECT Direction FROM Locations WHERE Location = '" & rsTransfer![Transfer From] & "'")
If Not rsLocation.EOF Then
iFrom = rsLocation!Direction
End If
rsLocation.Close
Set rsLocation = Nothing
Else
iFrom = rsHotel!Direction
End If
rsHotel.Close

Set rsHotel = db.OpenRecordset("SELECT Direction FROM Hotel WHERE Hotel = '" & rsTransfer![Transfer To] & "'")
If rsHotel.EOF Then
Set rsLocation = db.OpenRecordset("SELECT Direction FROM Locations WHERE Location = '" & rsTransfer![Transfer To] & "'")
If Not rsLocation.EOF Then
iTo = rsLocation!Direction
End If
rsLocation.Close
Set rsLocation = Nothing
Else
iTo = rsHotel!Direction
End If
rsHotel.Close
Set rsHotel = Nothing

Set rsRates = db.OpenRecordset("SELECT Cost FROM [Transfer Rates] WHERE [Tour Operator] = " & Me![Tour Operator] & " AND From = " & iFrom & " AND To = " & iTo & " AND [Transfer Type] = " & rsTransfer![Transfer Type])
If Not rsRates.EOF Then
Cost = Cost + rsRates!Cost
End If
rsTransfer.MoveNext
Loop
Total = Cost
rsInvoice!Transfer = Cost
Cost = (rsArrivals!Adults + (rsArrivals!Childs * rsArrivals![Child Handling])) * rsArrivals![Handling Fee]
Total = Total + Cost
rsInvoice!Handling = Cost
Cost = rsArrivals![Agent Fee]
Total = Total + Cost
rsInvoice!Agent = Cost

Set rsMisc = db.OpenRecordset("SELECT DISTINCTROW Miscellaneous.[Party ID], Sum(Miscellaneous.Cost) AS Total FROM Miscellaneous WHERE Miscellaneous.[Party ID] = " & rsArrivals!ID & " GROUP BY Miscellaneous.[Party ID]")
If Not rsMisc.EOF Then
Cost = rsMisc!Total
Total = Total + Cost
rsInvoice!Misc = Cost
End If

rsInvoice!Total = Total
rsInvoice.Update
rsArrivals.MoveNext
Loop

' close recordset
If bTransfer Then
rsRates.Close
Set rsRates = Nothing
End If
If Found Then
rsMisc.Close
Set rsMisc = Nothing
rsTransfer.Close
Set rsTransfer = Nothing
End If
rsInvoice.Close
Set rsInvoice = Nothing
rsArrivals.Close
Set rsArrivals = Nothing

Set rsInvoiceNumber = db.OpenRecordset("LastInvoiceNumber")
Cost = rsInvoiceNumber!LastInvoiceNumber
rsInvoiceNumber!LastInvoiceNumber = Cost + 1
rsInvoiceNumber.Update
rsInvoiceNumber.Close
Set rsInvoiceNumber = Nothing

' close connection
Set db = Nothing

phileoca

Set rsInvoice = db.OpenRecordset("DELETE Invoice.* FROM Invoice")

?????
try this:
Set rsInvoice = db.OpenRecordset("DELETE * FROM Invoice")

ASKER CERTIFIED SOLUTION

Section37

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Bink

From the VB help file for the OpenRecordset method, regarding the "Source" parameter:

A String specifying the source of the records for the new Recordset. The source can be a table name, a query name, or
an SQL statement that returns records. For table-type Recordset objects in Microsoft Jet databases, the source can only
be a table name ^^^^^^^^^^

DELETE queries are action queries. The most you can hope to return is the number of rows affected. If you need to clear the table, use this code instead:

CurrentDB.Execute "DELETE * FROM Invoice"