Code to assign an ID when button is clicked

Question

I have designed a simple database to keep track of company contacts. Now, I am building a form to allow employees to add contacts to the database.

On the form itself, I have all the columns except the primary key (contactID) tied to a text box. I would like the contactID value to be (the total number of entered contacts + 1) when the Add button is clicked. Basically, the first contact entered will have a contactID of 1 (0 + 1 = 1). Maybe the COUNT command factors in?

So, I am looking for assistance with what code I should place in the .Click event. Perhaps it would help to know how similar FoxPro is to SQL.

Thanks

Answer 1

The method you recommend for assigning ContactIDs is not a good idea. If two people are using the application at the same time, they could each create a record with the same ContactID.

My recommendation is that you use VFP's AutoIncrementing Integer capability. That is, set the relevant column to be Integer (AutoInc) in the Table Designer. Then, each new row gets the next available value, but you don't have to do any work to make it happen.

Answer 2

There are various ways to do this. Probably the simplest is to attempt to lock the table with flock() when saving, and if successful do:

calc max id_field to lnMax

Then when inserting your new record use lnMax+1 as the id_field value. Don't forget to

unlock all

... after saving. You'll want to ensure that 'id_field' has an index tag on it, and that you handle the case where someone else might have the table locked.

You can also do it more 'automagically' with a stored procedure.