Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Location
    New York
    Posts
    160

    Unanswered: Error in Filtering

    Hey,

    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.
    Code:
    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
            rs.MoveNext
            Loop
            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:
    Code:
            If ans = 0 Then
                'new ticket
                Me.FilterOn = False
                Exit Sub
            Else
                'pull up existing ticket via ticket number entered
                'check if the ticket number entered is valid
                Me.cboSearch.Undo
                Me.FilterOn = True
                Me.Filter = "[Ticket Number] = '" & ans & "'"
                Me.Requery
            End If
    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.

    Plz help

    thnx
    Last edited by vextout; 10-25-05 at 12:06.
    Beyond Limitation

  2. #2
    Join Date
    Jun 2003
    Posts
    19

    Try this

    Vextout,

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •