Pass variable to GOTO

[JohnPapa]

Is there a way to pass a variable to the GOTO statement?
Thanks

 

[nanscombe]

Are you trying to jump to different label depending on a value?

Select Case yourVariable
Case 1
  Goto label01
Case 2
  Goto label02
Case 3
  Goto label03
...
Case else
  Goto labelCatchAll
End Select

Select Case yourVariable
Case "A"
  Goto labelA
Case "B"
  Goto labelB
Case "C"
  Goto labelC
...
Case else
  Goto labelCatchAll
End Select

 

[Galaxiom]

However such code is considered poorly structured often derided as "spaghetti coding".

 

[nanscombe]

... but can never the less, if used sparingly, be useful at times.

 

[CJ_London]

why not simply use:

Select Case yourVariable
Case 1
  label1 code here
Case 2
  label2 code here
Case 3
  label3 code here
...
Case else
  label catchall code here
End Select

and /or put your label code in a function or procedure with parameters

 

[nanscombe]

All based on the same principle.

If you only have a few lines of code for each option, put it in the individual Case statements.

If you have more code, which may be reusable, see it you move it to separate Subs and Functions.

If all else fails, the initial unpopular separate labels option. I have to admit I do use it on rare occasions when I want to keep everything in a single piece of code but keep it looking readable.

 

[JohnPapa]

Thanks for all your responses, but the initial request (perhaps poorly stated) is whether it is possible to define the GOTO label on the fly, in other words,

GOTO VariableLabel where VariableLabel is defined on the fly.

Why do I need this functionality? I keep on adding functionality in a sub, let's say Update 1, Update 2, Update 3, Update n. Not all clients are at the same update level. Client A may need to execute Updates 2 onwards, Client B may not need to execute any updates etc. The GOTO (which I avoid as much as possible) will skip the specific Update level, which is stored in the Client db.

For example, a Client has completed Update 1 and receives the latest release. The software will skip the code for Update 1 and start executing from Update 2. If the label after Update 1 is simply "1:" I need to execute
GOTO 1

John

 

[CJ_London]

OK, in answer to your original question it is not possible to pass a variable to the GOTO statement., but there are other options as detailed in the other posts which may do the job for you

 

[stopher]

I agree that you should avoid using GOTO. Instead create separate functions/subs for each update.

You can call each function/sub using a variable name using Application.Run

The following will run a sub called Update1 with args "arg1" and "arg2".

Dim Update as String
Dim i as Integer
i=1
Update = "Update" & i
Application.Run Update, "agr1", "arg2"

I guess you could easily create a table of clients and what updates they need then just loop through the updates using the above.

hth
Chris

 

[JohnPapa]

Thanks for your replies, my first guess was that you could not pass a variable and that had been confirmed by the research which I did.

Regarding the updates, we are talking about updating a commercial product with additional functionality. The usual updates include.
1) Adding a table
2) Adding a field in a table

Less often we need to
1) Change the size of a field
2) Change the field structure of an existing table.

The first more common updates are accomplished by calling a sub and passing the name of the table or field and the update level. There is only one version of the software and it is backward compatible. The software reads the Update level from the client db and performs the remaining updates if any. The GOTO will simply skip any updates that have already been done.

John

 

[nanscombe]

This is a broad outline of how I would go about doing such incremental updates.

Global appCurrentVersion as variant

Public Function someCodeToGetVersion()
' If the version number were stored in a field called versionNumber in table versionTable
  someCodeToGetVersion = DLookUp("versionNumber", "versionTable")
End Function

Public Sub doUpdates
  appCurrentVersion = someCodeToGetVersion()
  If appCurrentVersion = n.m Then Exit Sub ' Exit because it is already the current version

  If appCurrentVersion < 1.1 Then doUpdates_1_1
  If appCurrentVersion < 1.2 Then doUpdates_1_2
' ...
  If appCurrentVersion < n.m Then doUpdates_n_m ' Where current version is n.m
End Sub

Public Sub doUpdates_1_1()
  Dim strSql as String

  Docmd.SetWarnings False
' Code to do updates to version 1.1
' ...
' ...
  appCurrentVersion = 1.1
  strSql = "Update versionTable SET versionNumber = " & appCurrentVersion
  Docmd.RunSql strSql
  Docmd.SetWarnings True

End Sub

Public Sub doUpdates_1_2()
  Dim strSql as String

  Docmd.SetWarnings False
' Code to do updates to version 1.2
' ...
' ...
  appCurrentVersion = 1.2
  strSql = "Update versionTable SET versionNumber = " & appCurrentVersion
  Docmd.RunSql strSql
  Docmd.SetWarnings True

End Sub

Public Sub doUpdates_n_m()
  Dim strSql as String

  Docmd.SetWarnings False
' Code to do updates to version n.m
' ...
' ...
  appCurrentVersion = n.m
  strSql = "Update versionTable SET versionNumber = " & appCurrentVersion
  Docmd.RunSql strSql
  Docmd.SetWarnings True

End Sub

 

[Galaxiom]

A function with a name assembled as a string can be run by using Eval("functionname") or Eval(strFunctionName)

 

[ChrisO]

I think the process, as described so far, is incorrect.

If it is early stages in the life of the database then this is how it did it in the past. With one exception, all of the commercial databases I have created have been ‘databases at a distance’.

What the term ‘databases at a distance’ means is that a front end needs to guarantee its own survival when it runs after being sent to a customer. Guarantee means to create whatever it needs when it runs. When it runs means each and every time it runs.

One important thing needs to be remembered: there is never any destruction only creation. Tables are added never destroyed, fields are added never destroyed, field sizes are only ever increased never decreased, a change in field type is a new field not a change of type of an existing field, if old data is required in the new field it is copied not moved and so on. No destruction at all, redundancy yes but no destruction.

If we look at it that way then there is no need for a version table. What is required is a configuration table which is local to the front end. That table describes all modifications required by all versions up to and including the current version of the FE. Records in the configuration table must remain unchanged for versions other than the current version.

But, once the current FE has been sent to the customer it to must remain unchanged in the configuration table. (If it helps, we can view this as a documented transaction between us and the customer. The document has been sent and we cannot withdraw that document. Any correction to that document must take place as an additional correction to that document, not as a replacement of that document.)

But the requirement is for the FE to bring the BE up to a level in which the FE can survive. It is not required for the FE to go any further other than for what the FE needs for survival.

----------

So this is the process at FE startup…

Disconnect all linked tables.

Open a recordset of the configuration table in the FE.

Apply all version requirements whether they are needed or not.
(Once the change code is stable the only error should be is if the change has already been made. That error can be ignored because the error on change will no affect the existing data and all we are interested in is if the change has been made.)

Re-link the tables.

----------

And now for testing, which is a very important part and best done before the end user sees it.

Take any back end, at any version level, and run the new FE against it. In fact, test any old FE against any BE and it should survive.
The requirement is that, even locally before shipment, the FE should survive without the destruction of any BE data.

----------

In concept then this is about removing all external dependencies from the FE.

The FE should run. The FE should run without external dependencies. The FE should run without references, it should be late bound. The FE should be late bound on tables and fields which the FE can not predict. The FE should run even if the tables and fields do not exist.

If the FE is dependent on tables and fields it should create them. But the FE should not change anything which another version of the FE requires.

----------

A practical solution does not involve jumping to variables at all. It involves thinking about the ongoing process. The ongoing process is about storing the changes required by the FE in a local configuration table in the FE and processing that entire table each and every time the FE starts up. It processes all changes from start to the current level of the FE which requires those changes. The current level is the last level because that is all the configuration table needs. There is no future knowledge of versions required. Hence, the processing of the configuration table is always from start to end.

The code required to process the changes is also in the FE; where else would it be?
The FE looks after itself up to its own version, which is defined in its own configuration table as the last entry, and the related code which is also in the FE.

The FE therefore looks after itself.

----------

It is altogether a different bucket of bolts working on ‘databases at a distance’. ‘Databases at a distance’ require self preservation and that requires self containment which inturn requires isolation from external influences.

And by the way, bollocks to performance issues at startup; you want it to work, not just get a high speed crash.


Chris.

 

[Galaxiom]

One important thing needs to be remembered: there is never any destruction only creation. Tables are added never destroyed, fields are added never destroyed, field sizes are only ever increased never decreased, a change in field type is a new field not a change of type of an existing field, if old data is required in the new field it is copied not moved and so on. No destruction at all, redundancy yes but no destruction.

So you are saying for example that if a new version expands the capability of a single value per record construct out into a new related table to support multiple records then the new version should continue to write to the original field as well as the new table when records are added.

This would also mean that a new version FE would need to check for data in the original field every time it read a record and write that to the related table.

Moreover any change to a record in the new table would need to be checked against the single field and either update it to the new value from the related table or choose a record to substitue it.

That is a hell of a lot more complex than a simple bound form.

 

[nanscombe]

For your example, I would imagine that the single field in the original table would be migrated to the new table, and used by the application, the original data would then probably just be ignored rather than destroyed.

Before:
Table01 - Field01 (use)

After:
Table01 - Field01 (ignore)
Table02 - Field01 (use)

 

[Galaxiom]

So it woundn't cause an error but it wouldn't show data either. Makes sense.

It can be pretty important to remove the old data. We had redundent data in oue of our systems when email address was taken out to a related table. However the old data wasn't removed.

Sure enough the old field got used to send out email when the addresses were extracted. Three different staff say the email field and used it before I went looking.

Moreover I had to explain this change to the vendor's engineer when we had a problem with email address validation on the front end. Not just the level one tech but even to the level two when they escalated the incident!

 

[nanscombe]

I was lucky enough to work in a tightly controlled, Corporate environment where all copies of software were updated at once, after testing with a small pilot roll-out, so everyone was on the same version.

All I had to do was hand over a new FE, a new BE and provide some in-built migration routines that would suck the data from the old version to the new.

I would imagine if you are a third party supplier things are a lot different.

 

[Cronk]

Nanscombe (Nigel) provides the answer in his earlier post.

Why does a variable have to be passed depending on what upgrade is to be installed?

I have a distributed app. The backend data has the currently installed version number. When an updated front end is sent out with the current version number, a check is done on first opening the new front end to check against the currently installed data version. And then all upgrades in order are executed by calling the appropriate function.

The only amendment I would make to Nigel's code is to call both upgrade 1.3 and 1.4 functions if the currently installed version was 1.2, not do them on sequential opening of the application.

Cronk

 

[nanscombe]

In theory my code should just apply each incremental upgrade in order, but only if it is required.

1.1 -> 1.2 -> 1.3 -> 1.4
1.2 -> 1.3 -> 1.4
1.3 -> 1.4
1.4 - no upgrade