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