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

Connie

I guess I can wash my hands now. :O)

Regards,

Bill

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
Sent: Tuesday, July 03, 2012 2:03 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: CurrentDb.Execute failing with Error 3022

Bill,

Sorry to disappoint you but I think/hope it's fixed! I couldn't get the problem
to duplicate itself on the one I was sending to you. I had added the Back End to
it. So I wondered if something was corrected in that process. Realized that I'd
Compacted and Repaired the front end but not the back end. Did that and I can't
duplicate the problem. I'm crossing my fingers :-) but if it crops up again I'll
be back for help.

Thanks for walking with me on this!
Connie

--- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , mrsgoudge <no_reply@...>
wrote:
>
> Most definitely! Twill take me a bit to get the data altered so it's
nonidentifiable. As long as it's your fingers, I'm okay if it gets a bit dirty!
>
> --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
wrote:
> >
> > Connie
> >
> > Can you upload a zipped sample with the forms and tables in question? Maybe
if we can get our grubby little fingers on it we can figure out why this is
failing.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , mrsgoudge <no_reply@>
wrote:
> > >
> > > Bill,
> > >
> > > This morning it worked the first time. But not after that. I created
another test listing and clicked Off and am still getting the same message.
> > >
> > > I don't know what other info to send/tell you.
> > >
> > > Your help is invaluable!
> > > Connie
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
wrote:
> > > >
> > > > Connie
> > > >
> > > > Ah, that makes more sense. I re-read your posts and the INSERT statement
looks fine to me. Did the query run okay?
> > > >
> > > > Bill
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , mrsgoudge <no_reply@>
wrote:
> > > > >
> > > > > Bill
> > > > >
> > > > > I appreciate the explanation. I'll have to check that out. Thanks!
> > > > >
> > > > > In this instance I'm not moving records. I'm changing the
CurrentListing field from True to False and entering a new record into the
Withdrawn table with the relevant data--ListID as the foreign key and the date
it was withdrawn.
> > > > >
> > > > > Thus the ListID was entered into the Withdrawn table and then at the
end of the code the Withdrawn form was opened to that ListID so that the
withdrawn date could be entered.
> > > > >
> > > > > Thanks again!
> > > > > Connie
> > > > >
> > > > >
> > > > >
> > > > > --- In MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> , "Bill Mosca" <wrmosca@>
wrote:
> > > > > >
> > > > > > 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
<mailto:MS_Access_Professionals%40yahoogroups.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
<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]