No duplicates based on 3rd index, not primary 1

[billmeye]

I have a table with an autonumber primary field (so I can cascade update many other tables and so I can't remove this required index) a 2nd field that is a customer name and a third field that is a customer order. I can have many instances of the same customer, but I only want 1 instance of each order type. So, in the form, if the data entry person selects an order type that the customer alreay has open, I want the form to be able to determine that the type is already used and not allow this duplicate record. Thanks.

 

[TheAceMan1]

How are ya billmeye . . . . .

What are the actual field names?

What is the table name?

Is Customer Order string or numeric?

See Ya! . . . . . .

 

[billmeye]

Table name CustomerOrders

Autonumber
CustomerName type text
OrderType type text

Thanks for the help

 

[TheAceMan1]

OK billmeye . . . . .

In the [purple]BeforeUpdate[/purple] event of [purple]OrderType[/purple], copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   Dim DL As String, Criteria As String
   
   DL = vbNewLine & vbNewLine
   
   Criteria = "[CustomerName]='" & Me!CustomerName & "' AND " & _
              "[OrderType]='" & Me!OrderType & "'"
   
   If Not IsNull(DLookup("[OrderType]", "CustomerOrders", Criteria)) Then
      Msg = "OrderType '" & Me!OrderType & "' already exist for " & _
            Me!CustomerName & "!" & DL & _
            "Duplicate OrderTypes are Not Allowed!" & DL & _
            "Try again using a different Type . . ."
      Style = vbInformation + vbOKOnly
      Title = "Duplicate OrderType Error! . . ."
      MsgBox Msg, Style, Title
      [purple][b]Cancel = True[/b][/purple]
   End If[/blue]

See Ya! . . . . . .

 

[billmeye]

Thanks for the quick reply, I can tell your close, except I made a mistake. The order type is an integer and I think, because of this, I'm getting a type mismatch. So, can you tell me how the code should look with that change.

Thanks.

 

[PHV]

Replace this:
"[OrderType]='" & Me!OrderType & "'"
By this:
"[OrderType]=" & Me!OrderType

You may also consider creating a composite index with no dup on CustomerOrders(CustomerName,OrderType)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[billmeye]

Thanks PHV and AceMan1, you solved my troubles and taught me yet again some new code. I never tried passing multiple criteria in a dlookup.