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]
Tidak ada komentar:
Posting Komentar