Addt'l Info. Now I'm getting the same message whether it's All Listings or Current Listings. My coworker got the message on the All Listings form and then clicked the button again and it worked.
--- In MS_Access_Professionals@yahoogroups.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"
>
Tidak ada komentar:
Posting Komentar