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

Connie

Technically speaking, there is nothing wrong with:
Dim msg, style, title, Response As String

What that is doing, as John said, is to declare msg and style as variants and Response as a string.Variants can hold any data type even objects. Being so flexible makes debugging difficult.

I see that as sloppy coding. It should be:
Dim msg As String
Dim style As String
Dim title As String
Dim Response As String

But even with that, I'm not happy. Response is a reserved word and is used as a parameter in some events like BeforeUpdate. And style could eventually be one, too, in future versions.

Next, VBA has specific naming conventions so one developer could pick up where another has left off without having to disect the code to figure out what is what. Our Links folder has a link to the most used VBA convention at http://c85.cemi.rssi.ru/access/Books/A97ExSol/index06.htm

It's very detailed and you probably don't want to follow every little thing, but variable prefixes are pretty much carved in stone when I write code.

I would have done this:
Dim strMsg As String
Dim strStyle As String
Dim strTitle As String
Dim strResponse As String

Something about this whole business of moving a record out of one table and into another bothers me. Why are you doing that instead of just marking a record with a Yes/No field as Dropped or Inactive or whatever else you want to call it? You can include whatever other fields that are needed for the ones you are now moving.

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, mrsgoudge <no_reply@...> wrote:
>
> 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]
> >
>