Hello,
I am stuck on a problem that I have research but have not been able to fix I have a code that adds the info from a list box to a table where the Bidder Id is the index key I have 4 of these List boxes, but if I go into the second text box or any of them and try adding different data but the same bidder ID I get the error message "Index Key cant have duplicate values" so I read online to try an append query that appends the one column that I want to add to the row that matches the bidder ID but I get the error message that it will create a duplicate index key even though it doesn't append the index key so my next hope is that I can add on to the VBA code that I already have to be able to find the matching bidder number in the table with the bidder number of the list box and add the data to the column named "200". here is my code below:
Private Sub Winner_200_Click()
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("Select * From Winner_Pick")
rec.AddNew
rec("200") = Me.Winner_200.Column(2)
rec("Ambassador Name") = Me.Winner_200.Column(0)
rec("Bidder Number") = Me.Winner_200.Column(1)
On Error GoTo ErrHandler
ErrHandler:
If Err.Number = 3022 Then
DoCmd.OpenQuery ("SL200")
Me.Winner_200.Requery
Exit Sub
End If
rec.Update
DoCmd.OpenQuery ("Update200")
Me.Winner_200.Requery
Set rec = Nothing
Set db = Nothing
End Sub
SL200 is the append query that runs when the error message comes up saying "Duplicate index Key" but the append query doesn't work.
Winner_200 is the list box.
update200 is used for changing the value of table where the list box gets its info from so that it drops off the list box once that data has been entered into the new table
Winner_Pick is the table where I have the info going to once I click the row in one of the list boxes
any help would be greatly appreciated this is for a charity Auction that my job is hosting.