I have a form based on a query in access 2k3.
i have a combobox that is bounded to a column called MoveID which is used to search for people. There can me multiple MoveIDs, but each record has a unique field called TicketNumber.
When a user choose the record (moveid), i code in the Private Sub cboSearch_BeforeUpdate(Cancel As Integer) and lookup other tickets with the same MoveID and dislpay it into an inputbox. I prompt the user to enter the ticketnumber they want to see or 0 for a new ticket.
Set conn = CurrentProject.Connection
sql = "Select a.[Ticket Status], a.[Category], a.[Task], a.[Issue Description], a.[MoveID], a.[Ticket Number]"
sql = sql & " FROM [Current Help Desk Tickets] a WHERE a.MoveID=" & cboSearch.Value
rs.CursorLocation = adUseClient
rs.Open sql, conn
If rs.RecordCount > 0 Then
str = "This user has " & rs.RecordCount & " Ticket(s) already existing" & vbCrLf
Do While Not rs.EOF
str = str & vbCrLf
str = str & "Ticket Number: " & rs(5) & vbCrLf
str = str & "Ticket Status: " & rs(0) & vbCrLf
str = str & "Category: " & rs(1) & vbCrLf
str = str & "Task: " & rs(2) & vbCrLf
str = str & "Issue Desc: " & rs(3) & vbCrLf
str = str & vbCrLf & "Enter the ticket number you wish to use. Enter 0 for a new ticket"
ans = InputBox(str, "Ticket Already Exists")
Now i want to filter the results to that 1 ticket entered:
If ans = 0 Then
Me.FilterOn = False
'pull up existing ticket via ticket number entered
'check if the ticket number entered is valid
Me.FilterOn = True
Me.Filter = "[Ticket Number] = '" & ans & "'"
but i get an error here --> Me.Filter = "[Ticket Number] = '" & ans & "'"
"the changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."
I understand that you cannot have a dupe of a unique number (ticket number) but i am trying top do an UPDATE to that record, not insert a whole new record
I was also thinking, if i can do just a "go to record" type of thing, and based on the TicketNumber, if that is possible. So i did this:
Me.Recordset.FindFirst "[Ticket Number]='" & ans & "'"
But got this error --> This action was cancelled by an associated object.
I searched left and right and have yet to find a working solution.
Without seeing your DB, here is a suggestion. Use the combo box to look up MoveIDs, but do not use it as the control source. Depending how things are set up, I think you are creating a new record when you select a MoveID from your combo box. The associated Ticket value is probably being set as either a '0' or "null" (which is probably a duplicate). You are getting the error when you filter because you are leaving the record, which has the duplicate Ticket value.
Let me know if this works for your situation...otherwise, there are other options of running checks prior to duplicate records being created.