Auto Increment Help Please

[Quinlivan]

Hey Guys... New guy here. I am building a database to track the progression of chemical mixtures. In my mixture table I have a Mixture field and a Version field.

In my form I have a browse that shows the mixture name and version number. There is a version number because a "Master Mixture" may require several alterations before it is perfected. I would like a chemist to be able to highlight the record that needs to be remixed and then click a button to open the selected record for editing but have the version number do an auto increment for a mixture with the same name (curent record). When the button is clicked, whether it is on the 3rd version or the 1st, it should automatically update to the next highest version associated with that mixture name field.

How do I make this happen? Please help, I'm stuck!

[MikeC]

Re: Auto Increment Help Please

Joe,
Are you keeping a history of the versions...the way you describe the sequence you are not as when a record is simply edited, you no longer will have any history. Answering this will help to give a more specific answer. Otherwise, all you would need is to add whatever you want to your version field's value in the OnChange of a field you decide upon to be one that requires a version update.


And Welcome to the messageboard!!....we try to answer all questions but some fall by the wayside if not enough info is given for a guess to be hazarded.....you will find that many questions are answered very quickly if a sample is given (I don't believe a sample is warranted in this case, but could be dependent upon level of expertise and understanding by you and us).

[Quinlivan]

Re: Auto Increment Help Please

Hey Mike... I want there to be a record of every version created. The name of the mixture will always stay the same but the version number will change every time a new version is created. Hope this helps. Thanks.

[Tom Cone Jr]

Re: Auto Increment Help Please

Joe, you won't be able to use the auto-increment field rule for this. It produces a single sorted list of field values, and increments the highest value it finds when the new record is saved. You want separate sorted lists for each possible "mixture", incrementing the highest version value found for each particular mixture when the record is saved. You're going to have to do some custom scripting to achieve this.

If it were me I'd use two table fields. One for the mixture, one for the version. I'd index both fields. I'd also index the concatenated combination of these fields. These would probably be stored in a separate supporting table to expedite the computation. When computing the next "version" for a given mixture I'd probably use a custom UserDefinedFunction (UDF).

In pseudocode this function would:

Open the table of mixtures/versions

Set the index to the concatenated

Fetch_find the first mixture

Step through the records until you get to the last version

Capture and increment that version value

Enter a new mixture/version record with an incremented field value

close the supporting table

Increment the captured value and use it in the new record being entered in the main table.


NOTE: An even faster approach would store only the "last" mixture/version record in the supporting table. the UDF would fetch the mixture in the supporting table, increment the version, save it, and use the incremented version value in the main table.