Automatically append database entries in Access

I want to create a database of products where each product can have multiple colours. I wanted to know if it's possible to create a form开发者_StackOverflow社区 which allows the user to enter the Product Name, Colour One, Colour Two, and Colour Three, but only add the record if that exact product doesn't exist already (perhaps an "add sale" button?). If it does already exist, then it would need to increment a particular field in the table UnitsSold. (When the first record is created the field UnitsSold would be 1).

Essentially this is to record sales of custom products which can have different color combinations. I tried to make this by myself, but whenever I added a product with the same details, it just added another individual record (defying the point of the DB).

The only reason I want this functionality is so the user doesn't have to search through what will eventually become an extremely long list of convoluted records just to increment the UnitsSold field by 1 whenever they make a sale!

You can use instr, I reckon:

sSQL = "SELECT * FROM Table As t " _
  & "WHERE Instr(t.Colour1 & "," & t.Colour2 & "," t.Colour3," & Me.Colour1 & ") > 0 " _
  & "AND Instr(t.Colour1 & "," & t.Colour2 & "," t.Colour3," & Me.Colour2 & ") > 0 " _
  & "AND Instr(t.Colour1 & "," & t.Colour2 & "," t.Colour3," & Me.Colour3 & ") > 0 "

However, I do not think your products can have an infinite number of colours, so you may wish to store a list of possible colours with RGB or such like that will allow the colour order to be determined.