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

 

WithdrawnID is AutoNumber.

I'll do a find/replace for the Dim msg issue.

Thanks again!
Connie

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
>
>
> I assume WithdrawnID is AutoNumber, right?
>
>
>
> As for: Dim msg, style, title, Response As String
>
>
>
> It should be:
>
>
>
> Dim msg As String, style As String, title As String, Response As String
>
>
>
> If you don't include the As clause with each variable, it becomes a Variant.
>
>
>
> 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 10:32 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [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
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of mrsgoudge
> > Sent: Monday, July 02, 2012 9:25 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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>
> > <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>
> > <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]
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (10)

Recent Activity:

• New Members 5
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___