VB Script to Select NextVal from Sequence

[TomWilgaWH]

I've created a Sequence (TB_NOMEN_NO). 

When a new feature is created, the default setting of the NAME_NUMBER field of the feature is set to pull from the above sequence, but I'd like to create a VB Script button in order to pull the next available value. This button would be used for features that are already digitized.

We are going through an entire re-naming of our system so rather than creating a new feature, I'd like to overwrite the existing NAME_NUMBER field with the next available value from the TB_NOMEN_NO sequence. 

Thanks in advance.

[fieldguy]

some questions. can you provide some test data (start point and explain your expected results)?

is TB_NOMEN_NO a system variable or something else?

if a new feature is created, do you want to check all existing "NAME_NUMBER" fields to find the highest number? 

or, check the TB_NOMEN_NO value?

are you editing existing features or adding new, or both?

what is the workflow? where is your planned "VB Script button" going to be? do you have a user form? 

[TomWilgaWH]

Hi @fieldguy, thanks for your response.

To eleborate...

• I am using forms (industry model).

• I created TB_NOMEN_NO as a sequence.
• Currently, when a new feature is created, the NAME_NUMBER field is defaulted to pull the next value available from TB_NOMEN_NO. This is all 'out of the box' capability with Map 3D. This is done from the form designer.
• We will need to edit existing data as well as add new data. Again, when a new feature is created, the NAME_NUMBER field will automatically pull the next available value from TB_NOMEN_NO.

The issue comes with editing existing data. I need to modify the NAME_NUMBER field of the form so that it is the next available number from TB_NOMEN_NO. Since there is already a value in the NAME_NUMBER field, I'd like to program a script button to pull the next available number from TB_NOMEN_NO and place it in the NAME_NUMBER field.

Hopefully that helps..

[fieldguy]

i think that existing data has to be modified before new data can be added. that means the program would have to check each "NAME_NUMBER" field to check if the value is in the sequence already.  if it is, then the next available TB_NOMEN_NO (NextVal) would be the position of the value in the sequence + 1. if it isn't, then the "NAME_NUMBER" value will be set to the next available TB_NOMEN_NO (the value in the NextVal position).

you might also need a dictionary or datatable that indicates each TB_NOMEN_NO and a boolean whether it is used or not. 

i would start by setting NextVal to 1 (the position of the first value in the sequence).  i would use another integer (CurrentVal = -1) when checking each existing "NAME_NUMBER" field.  if the NAME_NUMBER value is in the sequence (at position CurrentVal), NextVal will be CurrentVal + 1.  but, how can you be sure that the value in that position has not been used already? 

you mention "out of the box" capability with pulling the next value from TB_NOMEN_NO - i have never seen or used that feature.  that's why i ask what "TB_NOMEN_NO" is - is the sequence an array of integers or strings defined in code?

i am willing to investigate this further but not without test data.

[TomWilgaWH]

I think I will create a video to explain as it's a bit difficult to explain via text. 

I'll post up a video shortly.

[stelli]

Hi Thomas,

try this:

dim Val as long
dim SQL as string

SQL = "select MySequence.NEXTVAL from DUAL"
Val = me.ConnectionTools.LngValue(SQL)

Stelli