Connie-
Is the first part of the code editing the current record? If so, then I instead
of:
Set rstL = db.OpenRecordset("SELECT * FROM Listings WHERE ListID = " & Me.ListID
& ";")
rstL.Edit
rstL("CurrentListing").Value = 0
rstL.Update
I would do:
' Set the status to OFF
Me.Status = "OFF"
' Make sure any changes are saved
If Me.Dirty Then Me.Dirty = False
' Update the underlying record
CurrentDb.Execute "UPDATE Listings SET CurrentListing = 0 WHERE ListID = " &
Me.ListID, dbFailOnError
Then further down do:
Dim strSQL As String
'Make a new record in the Withdrawn table and enter the ListID
strSQL = "INSERT INTO Withdrawn (ListID) VALUES (" & Me.ListID & ")"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.OpenForm "Withdrawn", , , "ListID = " & Me.ListID
The Debug.Print should help you figure out what's going on if you still get the
error.
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 6:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] CurrentDb.Execute failing with Error 3022
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