Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Delete SQL 3219

Posted on 2004-04-28
3
Medium Priority
?
661 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:illion
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 11

Expert Comment

by:phileoca
ID: 10942955
Set rsInvoice = db.OpenRecordset("DELETE Invoice.* FROM Invoice")

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

Accepted Solution

by:
Section37 earned 500 total points
ID: 10943093
Use OpenRecordset for Select statements, for Insert, Update, and Delete try using Execute, ie.

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

-or-

CurrentDb.Execute("DELETE * FROM Invoice")

Shouldn't have an error with that one.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 10943119
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"
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question