Prevent double entry in the table via the form VFP9

[Eliott]

My friend ask me to make some small app based on free tables. He planned to use this app for mass processing data, i.e. he'll read them from papers and enter via form into tables. I wish to suppress him to enter same value twice or more times. I planned to make some routine which will check two situation:
1) new record adding: when he click Save button check is there already in the table same value (field Salesman) as value filled in form; if not exist such value then add it into table or pop up messagebox with error message
2) editing existing record: in moment when he push Save button check is there any record with same value except this one; if yes pop up error, if not then save it
I tried it to "hide" inside cmdAdd.Click() event but wasn't successful. I tried it distinguish add mode and edit mode and navigation mode based on buttonset1.addmode and buttonset1.editmode but I didn't got working solution...
Is there some clean code that could perform this task? Thank you.

There is no good nor evil, just decisions and consequences.

 

[JRB-Bldr]

I wish to suppress him to enter same value twice

One of the most simple ways is to create an Index on the Field(s) that you wish to prevent duplication of.

Then before committing entries to the data table, you do a SEEK() on the new values into the table.

If the SEEK() is not found, no duplicates, but if the SEEK() if found then there would be a duplicate.

Remember that things would be both Case and Length specific so you must take that into consideration.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

2) edit exising record. You normally know this in advance, because editing an existing record begins with navigating to the existing record and displaying it.

This may be connected to 1) detecting, if a record already exists.

A strategy to solve both questions at the same time is to let the user enter the primary identifier value of the record you use to reidentify it in the first place, in your case the salesmen.

So in the first place only allow entering or choosing a salesmen, then search it with indexseek or locate, whatever suits you, if found, you edit that record, if not you can make that a new record.

Bye, Olaf.

 

[Eliott]

To jrbbldr, Olaf: thank you for your fast response... there are two different ways, I'll try both of them and then decide which one is better in this case. But where to put Seek() part of code, inside Click() event of cmdAdd or buttonrefresh() of Buttonset() to execute before saving data?
Thank you again.

There is no good nor evil, just decisions and consequences.

 

[Olaf Doschke]

You'd search the recorxd even before editing.
Repeating my idea:

Set all controls readonly (via SetAll), only set Salesman editable. In the valid() or lostfocus event of the Salesman put the search for the entered value, if not found, append blank replace salesman with this.value, if found, edit that record. In any case all controls can be set readonly=.f. at that moment, and their controlsources will let them display the found record or the new one.

Bye, Olaf.

 

[Eliott]

thanks Olaf, nice idea.

There is no good nor evil, just decisions and consequences.

 

[JRB-Bldr]

where to put Seek() part of code

You put it immediately preceeding the code where you would attempt to add the new data (I have no idea where you are doing that).

cSeekKey = <New Data>

SELECT MyTable
SET ORDER TO Chk4Dupes
IF !SEEK(cSeekKey)
   * --- NO Dupe Found ---
   <do whatever>
ELSE
   * --- Dupe Found ---
   <do whatever>
ENDIF

Good Luck,
JRB-Bldr