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, 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