ACCESS FEED: [MS_AccessPros] Re: CurrentDb.Execute failing with Error 3022

John,

Thanks for the input.

Is there a problem with:
Dim msg, style, title, Response As String? I thought I'd gotten that from a reputable source so I've been cutting and pasting that all over my database :-( . Shows I had a top level understanding but not a real understanding of what I was doing--bummer.

Me.Status is an unbound box that looks up whether it's Off, Pending or sold. So no need to save that change.

Withdrawn has three fields: WithdrawnID, ListID and WithdrawnDate. Only the ID's are required. Both ID's have indexes on the individually.

Crazy. I'll see if Bill has any thoughts, play with it a bit more, sleep on it and if I can't get it working I'll try you all again.

Thanks again--
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Several problems (not necessarily related to you duplicate record error).

> Dim msg, style, title, Response As String
>
> Only Response will be a String. The other three variables will be a Variant.
>
> If Me.Dirty Then
> Me.Dirty = False
> End If
>
>
>
> This is occurring too early in the code. Later, you do:
>
>
>
> Me.Status = "OFF"
>
>
>
> .. which will "dirty" the record again. You should set Status and THEN save the
> record, but do it before you try to update the current record in the background.
>
> strSQL = "INSERT INTO Withdrawn (ListID) VALUES (" & lngListID & ")"
> 'to debug strSQL in next line remove '
> Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
>
>
>
> Are there other values in Withdrawn that are required or have a unique index
> with a default value? It's probably not ListID that is causing the duplicate
> error, but I can't explain why it might work when you run it a second time.
>
>
>
>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> <http://www.viescas.com/> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> Sent: Monday, July 02, 2012 9:25 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: CurrentDb.Execute failing with Error 3022
>
>
>
>
>
> John and Bill,
>
> I changed my code per John's suggestion to
> 'Make a new record in the Withdrawn table and enter the ListID
> Dim strSQL As String
> strSQL = "INSERT INTO Withdrawn (ListID) VALUES (" & lngListID & ")"
> 'to debug strSQL in next line remove '
> Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
>
> I also changed the order of events so that nothing happens until the problem
> line is executed so that I don't have the withdrawal process half processed.
>
> Worked once with the All Listing form once and now it doesn't. Am still getting
> the error with the CurrentDb.Execute strSQL line.
>
> I used your suggestion, Bill, and pasted the debug line into a query:
>
> INSERT INTO Withdrawn (ListID) VALUES (2237) and Access turned it into:
> INSERT INTO Withdrawn ( ListID )
> SELECT 2237 AS Expr1;
>
> This code has been working for a long time. I'm remembering another instance
> where something was workin for a long time and then something happened and major
> problems. That the computer speed was important and now I'm realizing that on
> Friday I changed the CurrentPrice query that is used in the Listings form and
> that had a HUGE impact on the speed of loading the Listings form and other
> forms. Could that be a factor here???
>
> I'm putting all of the code behind the button just in case it's an issue. I'm
> pretty sure not--it's mostly just protecting from inputting incorrect data.
>
> I'm sooooo thankful for your help!
> Connie
>
> 'If new record
> If Me.NewRecord Then
> 'Message box giving directions
> MsgBox "To mark a listing as Off, go to that record THEN press Off button."
> Exit Sub
> End If
>
> 'See if the ListID exists in the pending table
> If Not IsNull(DLookup("ListID", "Pending", "ListID = " & Me.ListID)) Then
> MsgBox "This property is pending." & vbNewLine & "Please undo the pending." &
> vbNewLine & "Then off the listing."
> Exit Sub
> End If
>
> 'See If in WD table
> If Not IsNull(DLookup("ListID", "Withdrawn", "ListID = " & Me.ListID)) Then
> MsgBox "This property is off the market already"
> Exit Sub
> End If
> 'See If in Sold table
> If Not IsNull(DLookup("ListID", "Sales", "ListID = " & Me.ListID)) Then
> MsgBox "This property is Sold." & vbNewLine & "Please undo the sale first."
> Exit Sub
> End If
>
> If Me.Dirty Then
> Me.Dirty = False
> End If
>
> Dim msg, style, title, Response As String
> msg = "If you click Yes the listing is no longer current" & vbNewLine & "Are you
> sure you want to mark this Listing as off?"
> style = vbYesNo
> title = "Mark Listing as Off the Market"
> Response = MsgBox(msg, style, title)
> If Response = vbYes Then
> Dim lngListID As Long
> lngListID = Me.ListID
> If Me.Dirty Then Me.Dirty = False
>
> 'Make a new record in the Withdrawn table and enter the ListID
> Dim strSQL As String
> strSQL = "INSERT INTO Withdrawn (ListID) VALUES (" & lngListID & ")"
> 'to debug strSQL in next line remove '
> Debug.Print strSQL
> CurrentDb.Execute strSQL, dbFailOnError
> 'Status changes to "OFF"
> Me.Status = "OFF"
> ' Update the underlying record and change Current to False
> CurrentDb.Execute "UPDATE Listings SET CurrentListing = 0 WHERE ListID = " &
> lngListID, dbFailOnError
> DoCmd.OpenForm "Withdrawn", , , "ListID = " & lngListID
> End If
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
> wrote:
> >
> > Connie
> >
> > First step is to debug the SQL INSERT statement. Put a breakpoint on the line
> ( shortened the line length to avoid Yahoo's line wrap):
> > CurrentDb.Execute "INSERT INTO Withdrawn (ListID) VALUES (" _
> > & Me.ListID & ")", dbFailOnError
> >
> > When the code breaks copy:
> > "INSERT INTO Withdrawn (ListID) VALUES (" _
> > & Me.ListID & ")"
> >
> > Paste it into the Immediate window and type a question mark to the left of it:
> > ?"INSERT INTO Withdrawn (ListID) VALUES (" _
> > & Me.ListID & ")"
> >
> > Press Enter. That will print the string so you can see exactly what it is. At
> this point, does it look right? Is the ListID what you expected?
> >
> > Copy the result and paste it into a new query in SQL view. Run it to see if it
> works.
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> > http://www.thatlldoit.com
> > Microsoft Office Access MVP
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> >
> > --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , mrsgoudge <no_reply@>
> wrote:
> > >
> > > Good morning!
> > >
> > > The Listings form has the option of opening with a current listings or all
> listings. Today I found that when the button "Off" is clicked it works
> differently for the two sets of data. The purpose of the button is to mark the
> listing as not current, put the listing into the withdrawn table and open the
> withdrawn form to add the extra info.
> > >
> > > All of the above events happen when the form contains all listing (, past
> and current). But when the recordset is only the current listings, clicking the
> button results in an Error 3022 message. "The changes you requested to the table
> were not successful because they would create duplicate values in the index,
> primary key or relationship..." It's strange because I've checked several times
> and the ListID that I'm trying to add to the withdrawn table is not in that
> table.
> > >
> > > I've stepped through the code and it happens at the CurrentDb.Execute line.
> > >
> > > Code for the Off button is below along with the code that opens the form to
> only show current listings.
> > >
> > > Thanks!
> > > Connie
> > >
> > > Dim msg, style, title, Response As String
> > > msg = "If you click Yes the listing is no longer current" & vbNewLine & "Are
> you sure you want to mark this Listing as off?"
> > > style = vbYesNo
> > > title = "Mark Listing as Off the Market"
> > > Response = MsgBox(msg, style, title)
> > > If Response = vbYes Then
> > > 'Set CurrentListing in Listing form to No
> > > Set rstL = db.OpenRecordset("SELECT * FROM Listings WHERE ListID = " &
> Me.ListID & ";")
> > > rstL.Edit
> > > rstL("CurrentListing").Value = 0
> > > rstL.Update
> > >
> > > 'Make a new record in the Withdrawn table and enter the ListID
> > > CurrentDb.Execute "INSERT INTO Withdrawn (ListID) VALUES (" & Me.ListID &
> ")", dbFailOnError
> > > 'Status changes to "OFF"
> > > Me.Status = "OFF"
> > > DoCmd.OpenForm "Withdrawn", , , "ListID = " & Me.ListID
> > > ' Clean up
> > > rstL.Close
> > > Set rstL = Nothing
> > > Set db = Nothing
> > > End If
> > >
> > > CODE USED FOR OPENING ONLY CURRENT LISTINGS:
> > > 'Open Listings form
> > > DoCmd.OpenForm "Listings", _
> > > WhereCondition:="Listings.CurrentListing = -1"
> > > 'Set Form title/caption to "current Listings"
> > > Forms!Listings.FormTitle.Caption = "Current Listings"
> > >
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>