You can do an @XLookup to check if the number you assigned already exists and keep incrementing until it does not.

You can also use another method of assigning a number.
http://www.lantica.com/Support/sesame_onesheet_11.html

Quote:


@XLookupR returns the highest existing value that does not exceed the key. The 9999999 is meant to be a number higher than any that may exist to ensure you get the highest existing value.

Louis,

To check for duplicate entries, I use something similar to the following code in the On Element Change event of the element that I am checking. (i.e. Project#, in your case.)

if Project# = @Xlookup(@FN, Project#, "MyForm!Project#", "Project#")
     @msgbox("This field should be unique.  Please verify before continuing.", "", "")
 

FYI: The 3rd parameter that includes the name of the form, is usually needed if the application has multiple databases - especially if the name is used again in another database. I've found that it is a good idea to always include the form name, so that it will still work if you later add more databases to the application.

So, I programmed my project# field (on element change event) with the following:

If(@IsNew)
{
    If(@Add)
         {
           If Project# = @Xlookup(@FN, Project#, "Projects!Project#", "Project#") then
           {
                 NotifyForm(1)
                 @msgbox("This Project# is a duplicate number.  Please verify before continuing.", "", "")
                 ThrowFocus(Project#)
           }
     }
}


If(@Update)
         {
           If Project# = @Xlookup(@FN, Project#, "Projects!Project#", "Project#") then
           {
                 NotifyForm(1)
                 @msgbox("This Project# is a duplicate number.  Please verify before continuing.", "", "")
                 ThrowFocus(Project#)
           }
     }

In ADD mode, it works and forces you back to the field thereby updating the number.

In Search mode, it warns you but stll allows you to save with a duplicate #, even though I have included NotifyForm(1).

Is there some other statement that I could insert to prevent it from saving in search mode ?

Thanks for all the input.

Louis

Ray:

I tried NotifyForm(0) but it didn't do anything.

However, I think it is being over-ridden On Form Entry by NotifyForm(2) and NotifyForm(-2) to prevent record advance (extended mode) in Search/Update.

Thanks,

Louis