How to use global variable to return to same place in a reopened form

[rew2009]

Now that I understand Global variables I would like to apply it to another problem that I want to solve. I have recast the form of my application from one that had 16 tabbed pages into one that has one main page which acts as a switchboard and 15 popup forms replacing those other tabbed pages. You guys advised me to do this and it has speeded up the operation of the application and I don’t have the limit on the number of objects that I had before so it is working well. However, when I close a popup form that has a subform embedded that looks like a list box it loses its place in the list of records. I want to be able to reopen the popup form and return the place on the list in its subform that it was at when it was previously closed. I assumed that the best way would be to store a value relating to its last record position in a global variable and then retrieve and use it when reopening the popup form. I did not have this problem when I used tabbed pages because they never closed. But I don’t know how to do this. Any thoughts?

 

[ProgramError]

There are three or four posible ways of doing this depending on whether you require the positions to be saved or not when the whole database is closed.

You could hide the forms rather than closing them which is probably the easiest method but uses the computer's resources because .

Using VBA you could define a globle variable for each form or maybe an array. When closing the form you need to save the current selected record position of the subform into the variable. When opening the form you will have to 'goto' that record using the variable. You will lose this position when the database is closed.

You could save the position in a specifically designed table and use the lookup method to retrieve it. Again you will have to goto the position as mentioned previously. Using this method the position will be saved when the database is closed.

there are threads on here explaining all these. Just do a search for them.



Ian Mayor (UK)
Program Error
9 times out of 10 I know what I'm talking about. This must be my tenth reply.

 

[rew2009]

I know how to define global variables but what kind of code would you use to save the record info in a global variable, say "strA1" and how would you write the code to "goto" a specific record on open? Also, how do you hide a form? I can see that these methods should work, but how do you do it. Thanks

 

[TheAceMan1]

Howdy rew2009 . . .

Taking into account the worse secnario (returning to a record after you've closed then reopened the DB), I have to introduce you to [blue]DB Properties![/blue] Be aware ... these properties are [blue]non-volatile[/blue] and stay with the DB even if its closed or transported elsewhere. [blue]The properties can simply be looked at as presistant read/write global variables that never die![/blue]. Normally variable [blue]scope[/blue] has to be taken into account.

You'll be using the [blue]PrimaryKey[/blue] of each subform as a reference to return to the last selected record in that subform. As such you'll be making a [blue]DB Property[/blue] to hold the [blue]PrimaryKey[/blue] of each subform of interest. The properties only have to be made once ... so lets get on with it ...
[ol][li]In the modules window click new.[/li]
[li]Click save and name the module [blue]modDBProps[/blue].[/li]
[li]Copy/paste the following in the module:

[blue]Public Function CreateDbProp(prpName As String, prpTyp As Long, prpVal) As Boolean
   [green]'prpTyp: dbBoolean , dbByte, dbCurrency, dbDate, dbDecimal, dbDouble,
   '        dbFloat, dbInteger, dbLong, dbMemo, dbSingle, dbText[/green]
On Error GoTo GotErr
   Dim prp As Property
      
   Set prp = CurrentDb.CreateProperty(prpName, prpTyp, prpVal)
   CurrentDb.Properties.Append prp
   CreateDbProp = True
   
SeeYa:
   Set prp = Nothing
   Exit Function
   
GotErr:
   Call DbPropErrMsg(prpName)
   Resume SeeYa

End Function

Public Function GetDbProp(prpName As String)

On Error GoTo GotErr
   GetDbProp = CurrentDb.Properties(prpName)
   Exit Function

GotErr:
   Call DbPropErrMsg(prpName)
   'Returns NUll if property not found.
   GetDbProp = Null
   
End Function

Public Function SetDbProp(prpName As String, ByVal prpVal) As Boolean
   'Note: you can't set a db property to Null!
   
On Error GoTo GotErr
   
   If IsNull(prpVal) Then prpVal = ""
   CurrentDb.Properties(prpName) = prpVal
   SetDbProp = True
   Exit Function
   
GotErr:
   Call DbPropErrMsg(prpName)

End Function

Public Function DelDbProp(prpName As String) As Boolean

On Error GoTo GotErr
   CurrentDb.Properties.Delete prpName
   DelDbProp = True
   Exit Function
   
GotErr:
   Call DbPropErrMsg(prpName)
      
End Function

Public Sub DbPropErrMsg(prpName As String)
   
   If Err.Number = 3265 Or Err.Number = 3270 Then
      Msg = "DB property '" & prpName & "' Not Found!@ @"
      Style = vbInformation + vbOKOnly
      Title = "Property Not Found Error! . . ."
      Call uMsg
   ElseIf Err.Number = 3367 Then
      Msg = "The db property '" & prpName & "' Already Exist!" & _
            "@The creation of this property is CANCELLED! . . .@"
      Style = vbInformation + vbOKOnly
      Title = "Can't Create . . . Property Exists!"
      Call uMsg
   ElseIf Err.Number = 3421 Then
      Msg = "Can't set the DB property '" & prpName & "' !" & DL & _
            "@The value supplied doesn't match the data type of the property!@"
      Style = vbInformation + vbOKOnly
      Title = "Data Type Conversion Error! . . ."
      Call uMsg
   Else
      Msg = "Error " & Err.Number & ": " & Err.Description & "@ @"
      Style = vbCritical + vbOKOnly
      Title = "System Error! . . ."
      Call uMsg
   End If

End Sub[/blue]

[/li]
[li]Save the module.[/li]
[li]Stay in the module and call up the [blue]Immediate Window[/blue] (Ctrl+G). Your going to make the properties here! To do so just enter the [blue]CreateDbProp[/blue] function preceded by a question like so:

[blue]?CreateDbProp([blue]prpName[/blue], [blue]prpTyp[/blue], [blue]prpVal[/blue])[/blue]

... and fill in the arguements. Note: [blue]prpTyp[/blue] is shown in [green]green[/green] at the top of the function. [red]Do not use any quotations here![/red] Also bear in mind that [blue]prpTyp[/blue] [blue]should follow the type of the primarykey for the subform in question![/blue]

For [blue]prpVal[/blue] use quotes [blue]"[/blue]Your Text[blue]"[/blue] if [blue]prpTyp[/blue] is dbMemo or dbText ... hash marks [blue]#[/blue]date[blue]#[/blue] for dbDate.
As an example: say we have a subform named [blue]subMain1[/blue] with a primarykey type of [blue]Long Integer[/blue]. In the immwdiate window you would have something like:

[blue]?CreateDbProp("subMain1LV", dbLong, 0)[/blue]

When you hit enter the function returns true if the property was created. Note the LV in the name subMain1[purple]LV[/purple] simply means [blue]Last Visited[/blue]. This is in the interested of keeping the name short ... which you should be doing thruout the db. Also note that you have to initialize a value for [blue]prpVal[/blue]!

So now you have a db property called [purple]subMain1LV[/purple]. Continue in the same way for your other subforms.[/li]
[li]From here you simply call the functions [blue]GetDbProp(prpName)[/blue] & [blue]SetDbProp(prpName, prpVal)[/blue] to get and set the properties! Keep the prpType you set in mind when you set the value![/li]
[li]Close the module.[/li][/ol]
Now your ready to try one of the sunforms and its db property. Open the subform in design view and in the [blue]On Unload[/blue] event copy/paste the following ...

[blue]   SetDbProp "[purple][B][I]dbpropertyname[/I][/B][/purple]", Me![purple][B][I]YourPrimarykeyName[/I][/B][/purple][/blue]

... and the [blue]On Load[/blue] event:

[blue]   Me.Recordset.FindFirst "[[purple][B][I]YourPrimarykeyName[/I][/B][/purple]] = " & GetDbProp("[purple][B][I]dbpropertyname[/I][/B][/purple]")[/blue]

Save and perform your testing.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[rew2009]

How are you doing TheAceMan.... This is a very interesting way to skin the cat!! When I first saw your code I thought "no way am I going to understand this". But as I re-read the code it began to dawn on me what you are doing. It certainly is a better way of doing it than I was originally thinking, ie, using global variables which are lost after closing the database. I am heading into a very busy week now that we are using my application, but I will try it and keep you appraised. Thanks.