>>>>To be honest, it was my error in this stored procedure, which I just fixed. However, what if NextID table could not be opened or locked? In this case this function still would return 0 and we would end with 0 as Primary Key...
>>>
>>>I think my equivalent function returns -1 instead.
>>>
>>>In any case, you could have a trigger rule that forbids 0 (or -1) as PK.
>>>
>>>Hilmar.
>>
>>Hilmar,
>>
>>Could you please expand this idea? I've never done this before, so I'm curious, how it's done in your system? If 0 (or -1) would be returned, this record should not be appended at all. How can I achieve it?
>>
>>Thanks a lot in advance.
>
>Sure!
>
>Function SerialNumber() (my version of the function that returns serial numbers) is called from field default value. Example: Clients are stored in table ClientMain. PK field is called Client. Default value for this field is SerialNumber("ClientMain").
>
>This function opens table SerialNumber, seeks for the table, increments the value, and returns the incremented value. If something goes wrong, the function returns an invalid value (-1).
>
>Being the PK, you can't have more than one record with this value; you will immediately get "Trigger Failed" on the second invalid record.
>
>While I didn't actually do this, you could also call a function from Insert & Update Triggers, that rejects the value -1 for the PK. This function would be placed in stored procedures.
>
>Returning NULL for invalid, instead of -1 (as proposed in another reply you got), would work as well, and might conceivably save you of the trouble of using triggers. However, personally, I prefer to postpone all validation until TableUpdate(). I am not sure know if this would work with NULL values.
>
>Hilmar.
Hilmar,
My question was about preventing invalid input directly in tables. In forms I use Table buffering and catch all problems gracefully (or, at least, I hope so). However, if user just opens table in command window (use myTable), then append blank, my original procedure didn't prevent from 3 "0" despite the fact, that I have a PK set. I hope, that Steve's solution would work for these situations. I already implemented it, but haven't tested yet.
If it's not broken, fix it until it is.
My Blog