AllowAddNew Property

[mikeisvfp]

Hello Experts,

I have a combobox in a grid that auto populates as the user types in for example: "Lights"
if lights is wildcard the user can finish typing lights or simply select it, the AllowAddNew property is set to .T. of the grid
so the user can type something new like say for example: muffler and muffler will automatically be stored for the next time.
However my problem is, if there is a wildcard and the user selects that wildcard, it will create duplicate entry.

Is there a way to stop it from duplicating entries?

 

[Olaf Doschke]

Well, this just depends on you to detect the duplicate before the user leaves the row. You can use the grids beforerowcolchange event to detect that and disallow the change of the row or revert it at that moment. It all depends on buffering the grid recordsource, so you can TABLEREVERT() or TABLEUPDATE() at that moment. A row level buffering would be an idea here, but it's easier, if you always go for table buffering mode 5.

First you check, whther the change is a row change, then, if you're in a new row with RECNO(), which is negative for new rows, when the table is buffered.
Finally you check for double values by either SQL, selecting any other row with same value in a certain field or use INDEXSEEK() with the second parameter lMovepointer set as .f. to not move the record pointer within the recordsource, so you stay on the current, new record and still check other records via the index. That of course requires an index on the field to check.

So in short that would be code in the grid's BeforeRowColChange:
IF BitTest(This.RowColChange,0) AND RECNO()<0 AND INDEXSEEK(fieldtocheck,.f.,this.recordsource,"dupcheck")
* double value, remove that new record, before it's really saved
TABLEREVERT(.F.)
ENDIF

With the prerequisites: 1. The table is buffered, 2. the field to check for double is to be changed from "fieldtocheck" to the real name 3. the field to check is indexed with a tag named "dupcheck" and 4. the table is buffered with buffermode 5 or 3. and for buffering to work SET MULTILOCKS ON (should be a default you set, anyway).

Bye, Olaf.

 

[mikeisvfp]

Hi Olaf

I am getting a "no parameter statement found" ?

 

[Olaf Doschke]

You need to keep the parameter line in the BeforeRowColChange event. It has a parameter nColIndex, and while the code doesn't needs that parameter, it still has to exist, or the event fails to run.

Bye, Olaf.

 

[mikeisvfp]

Beauty, so it no longer creates duplicates, but now i cant addnew, so if i type "Hello" it should save it?

 

[Olaf Doschke]

What do you mean? It always deletes the new record, when you leave it?
What about the prerequisites? Is your table buffered, etc.?

Bye, Olaf.

 

[mikeisvfp]

no, I mean if i enter in something new it doesnt stick\save
so if i enter in "HELLO" and hit enter or tab over it doesnt save?
and yes i did folow the prerequisites

 

[Olaf Doschke]

Well, then the help is wrong about INDEXSEEK. It says: "INDEXSEEK( ) returns false (.F.) when you are attempting to find a value in the most recently created record".

It seems the way the grid adds a ne records, indexseek finds it already. You always of course find one occurrance of an entered value in the new record itself.

Then you'll need to change that to IndexSeek, moving the record pointer, to see if it finds a different record with the same value:

...
Set Message To
If BitTest(This.RowColChange,0)
   lnRecno = Recno()
   If IndexSeek(fieldtocheck,.T.,this.recordsource,"dupcheck") And Recno() = lnRecno
      Set Message To 'false alarm'
   Else
      Go Record lnRecno
      TableRevert(.F.)
      Set Message To 'removed/reverted duplicate'
   EndIf
EndIf

I also removed the check for Recno()<0, because if a user changes an existing record to a duplicate value, you also need to check that and revert to the old non dupllicate value, then.

You may also make that whole check of the table values delayed in an overall save method, instead, where you verify the uniqueness of each record. A candidate index helps about this, as it disallows and duplicate.

Bye, Olaf.

 

[Olaf Doschke]

Another change is needed:

...
Set Message To
If BitTest(This.RowColChange,0)
   lnRecno = Recno()
   If IndexSeek(fieldtocheck,.T.,this.recordsource,"dupcheck") 
      If Recno() = lnRecno
         Set Message To 'false alarm'
      Else
         Go Record lnRecno
         TableRevert(.F.)
         Set Message To 'removed/reverted duplicate'
      Endif
   EndIf
EndIf

Bye, Olaf.