unique values for non must field

[bambo]

Hi,

I've got an ms access table with customers details. some customers ask to make a customer card for them and then they get a customer card no. so card_no is not a compulsory field. however, I'd like to make sure that this field can never have duplicate values.

any ideas how ?

[GaryMazzone]

Since null is allowed in the field I would normally try and get a count from the database where the card_no is equal to the value you want too input. If the count comes back > 0 then the number is already in use.

[bambo]

so you are talking about a solution in the interface, in each place where the card no can be modified.

1. isn't there a way to do in the database.
2. how do I make sure that all updates to this field will pass through this check? (there are many places where it can be modified)

thanks,

[Hack]

Yes, you can do something in the database and that is set the field up to not allow duplicates.

However, there still MUST be code in your interface as well. That code would have to deal with the error that would be generated by the database if a duplicate number was attempted to be entered. Each place in your code where something can be written to this field would have to have the code that dealt with the potential of this error.

[GaryMazzone]

Create a function and call the function. If nulls are allowed you can't depend on the DB to control this (only one null will be allowed). Another option could be to create a table in the DB that hold nothing but a number. This table holds one row just long integer value. When you want to create a new card_no you select the number from this table, then increment the number by 1 and now create the new card_no with that returned number.

[bambo]

card numbers should also be allowed to be modified manually, and not only automatically (otherwise it would very easy to handle)

[Hack]

Modified manually how?

How does this manual modification impact what you are writing?

[GaryMazzone]

Then you are back to checking the count of card_no each time you do something.