How to pick up a Form or Reports Property Description text

[johnbe]

I'm trying to find a way to pick up the values entered into the description box of a Forms property in vba. By that I mean the property when a form is right clicked and property selected and the subsequent text entered into the Description box..

This may sound like an odd thing to be doing but the reason we are doing this is that when we modify forms (and reports by the way) we use the Description box of Properties to enter a version number so that we can see from the main database window, the current version number of the form. What we would like to do, is pick up that version number and display it on the form when it is open so we can confirm that users have the correct form version number when reporting errors to us. This is because we have a problem with users making copies of the database and saving them to their desktop instead of using shortcuts (its an ongoing problem even though we keep explaining why they should not do this!)

So to recap, when a form opens we would like to be able to pick up the Description text from its Properties and use this as a simple label on the form.

I have no problem utilising the value when I have it but I cannot figure out how to get to this value in vba.

Any ideas would be gratefully received.

John

[NeoPa]

I've a horrible feeling I've found this one before, but I can't find it now. It's always irritated me that such things should be so counter-intuitive to find and use.

Welcome to Bytes!

[TheSmileyCoder]

I tried looking for the variable in ALOT of places...but sorry, no luck.

To prevent users running local versions, you could do (not bulletproof)

if instr(1,currentproject.Path,"Desktop")>0 Or _
   InStr(1,currentproject.Path,"C:\")>0 Then
  Msgbox "Bad Boy"
  Docmd.quit
end If

What I do is have the frontend database upon startup check a linked table tbl_ServerVersion against a local table, tbl_ClientVersion. Each table only has 1 row. If the 2 rows don't match, it will close the currentDB, open the update DB, which then copys the new frontend over the users old frontend, and voila, Frontend is updated, the update DB closes itself, and opens the frontend.
Im sure there are smarter ways of doing this (real installers/patches etc) but this works for me, and its pure VBA coding. If you want I can share more details.

[NeoPa]

That would be my approach too Smiley. Why overcomplicate when you can write a VBA routine you can use in multiple databases?

[johnbe]

Many thanks Smiley and your method is very intriguing. Although we use the Description box for our benefit, i.e. to track the version numbers during design, you're method would seem to ensure that our intrepid users would always have a correct version.

I understand the logic in how your acheiving this and if you wouldn't mind I'd be very appreciative to see how you actually acheive this.

We already user a version number table, in the basic format you have described, to allow a forced logout of our users, so we could probably utilise the same table. It's the update DB and the copying of the the correct frontend over the users frontend that I'm not clear on how to acheive.

[TheSmileyCoder]

Structure
All project related tables in 1 backend, all update dependent tables (2) in Update.mdb.

First part is frm_CheckForUpdates, which is opened upon user login.
Checks the server version vs. local version, and if they differ, it will write an entry into Update.mdb, detailing where the user has chosen to store their local frontend. It then uses a shell command to open the Update.mdb, and closes this database. The code behind the form is shown beneath.

You can ignore the error handling, part, its something I use to automatically relink tables depending on whether im working at home developing, or at the office. It does not do any smart synchronising, I just have a old copy of the backend with me.

Private Sub Form_Load()
    On Error GoTo err_Handler
    'Find the version numbers
    strVerClient = Nz(DLookup("ClientVersion", "tbl_ClientVersion"), "")
    strVerServer = Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
 
    Debug.Print "server is:" & strVerServer
    Debug.Print "client is:" & strVerClient
 
    If strVerClient = strVerServer Then
        'User has latest version, proceed
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "frm_Login"
        Else
        'User does not have latest version, get latest version
        MsgBox "You are using Version: " & strVerClient & vbNewLine & "A new version " & strVerServer & " of Cityringen RTL is available" & vbNewLine & vbNewLine & "Press OK now to download", vbOKOnly, "New version available"
        DoCmd.Close acForm, Me.Name
 
        'Transfer path to Update database so it knows where to place the new version
            'First check if path exists allready
            Dim strSQL As String
 
            If DCount("*", "tbl_PathInfo", "ComputerName=Environ('ComputerName')") > 0 Then
                'Do update
 
                    strSQL = "UPDATE tbl_PathInfo SET tbl_PathInfo.[User] = (Environ('USERNAME')),tbl_PathInfo.[ComputerName]=(Environ('ComputerName')), tbl_PathInfo.Path = [currentproject].[path], tbl_PathInfo.Name = [currentproject].[name],tbl_PathInfo.dt_When=Now() WHERE ((tbl_PathInfo.[ComputerName]=(Environ('UserName'))));"
                Else
                'Do insert
                    strSQL = "INSERT INTO tbl_PathInfo ([ComputerName]              ,[User]                 ,[Path]                     ,[Name]                     ,dt_When)" & _
                                        " Values       ((Environ('ComputerName'))   ,(Environ('USERNAME'))   ,[currentproject].[path]    ,[currentproject].[name]    ,NOW());"
            End If
        DoCmd.SetWarnings (False)
            Debug.Print strSQL
            DoCmd.RunSQL strSQL
        DoCmd.SetWarnings (True)
 
        Shell "MSAccess.exe " & Q & "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\Update.mdb" & Q, vbNormalFocus
        DoCmd.Quit
 
 
    End If
 
Exit Sub
err_Handler:
    Dim intErrCount As Integer
    intErrCount = intErrCount + 1
    If Err.Number = 3044 Then
        'How many times has error occured
        If intErrCount = 1 Then
            'Try to link to network tables
            globalRelinkNetwork
            Resume
        ElseIf intErrCount = 2 Then
            'Try to link to local tables
            globalRelinkLocal
            Resume
        ElseIf intErrCount > 2 Then
            'Quit.
            MsgBox "RTL could not connect to network tables. Exiting", vbOKOnly, "Network Connection Error"
            DoCmd.Quit
        End If
    End If
 
    MsgBox "Error number: " & Err.Number & ". Description: " & Err.Description
 
End Sub

The Update.mdb launches a form upon load, which has these functions. Upon load it will set a timer to seconds, before it actually does anything. This is because the original database frontend can take a short while to properly close.
It changes the original frontend from .mdb to .backup, and then copies over the new frontend (which I place in the same location for each update) to the same location and filename that the user had his local frontend in (in case some pesky user decides to rename it).

Option Compare Database
Option Explicit
Const q As String = """"
Private ErrorCounter As Integer
Private strOldFront As String
 
Private Sub btn_Continue_Click()
        'open the new database and return to it
        Shell "MSAccess.exe " & q & DLookup("Path", "tbl_PathInfo") & "\" & DLookup("Name", "tbl_PathInfo") & q, vbNormalFocus
        'exit this update application
        DoCmd.Quit
End Sub
 
Private Sub Form_Load()
    Me.lbl_InstallVersion.Caption = "Installing version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
    Debug.Print "Before doEvents: " & Now()
    DoEvents
    Debug.Print "After doEvents: " & Now()
 
 
 
    Me.TimerInterval = 10000
 
 
 
 
 
End Sub
 
 
 
Private Sub Form_Timer()
    On Error GoTo err_Handler
    Me.TimerInterval = 0
    'Backup users version
    Dim strOldFront As String
    Dim strComputerName As String
    strComputerName = Environ("ComputerName")
    strOldFront = DLookup("Path", "tbl_PathInfo", "ComputerName='" & strComputerName & "'") & "\" & DLookup("Name", "tbl_PathInfo", "ComputerName='" & strComputerName & "'")
    Dim strBackup As String
    strBackup = Replace(strOldFront, ".mdb", ".backup")
    'Check if there is a older backup
        If Dir(strBackup) <> "" Then Kill strBackup 'There was a backup, kill it
        FileCopy strOldFront, strBackup 'Backup
        DoEvents 'Allow backup to finish
        If Dir(strOldFront) <> "" Then Kill strOldFront 'Kill old version
        Debug.Print "About to copy"
        FileCopy "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\RTL-Latest.mdb", strOldFront
        Debug.Print "Copy Finished"
        DoEvents
 
        Me.lbl_PleaseWait.Caption = "Done!"
        Me.btn_Continue.Enabled = True
        Me.lbl_InstallVersion.Caption = "Succesfully installed version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
Exit Sub
err_Handler:
    ErrorCounter = ErrorCounter + 1
    MsgBox Err.Number & Err.Description
 
 
    If ErrorCounter = 3 Then
        Me.btn_Exit.Visible = True
    End If
    If ErrorCounter < 4 Then
        Resume
        Else
            MsgBox "An error occured that I cannot fix. Please contact TheSmileyOne"
            Quit acQuitSaveNone
    End If
 
End Sub
 

Im sure there is some part I forgot the explain, but if you have questions ask. Important thing when doing a new frontend is to remember to set both the clientversion in the frontend, and the server version in the update.mdb or the loop will not end :P

Hope you can use this.

[johnbe]

Many thanks and having had a quick browse through it all seems to make sense. I will give this a go and if I have any problems I will let you know. If it all goes well then please take this a big thank you for your assistance.

Cheers
John

[NeoPa]

As we're on this topic I'll just share some ideas for you both. Just for consideration, you can decide whether or not you prefer the concept.

I generally use CMD files to do any work on databases which require them to be closed. CMD files can be created from within an Access process quite easily. I generally have a template file stored in a table which I can change on the fly. For instance, the template may have commands that point to a folder, but this is only known at run-time (EG. The folder the database is running from). I use Replace() here to convert updatable parameters found in the template table into actual values stored to the file (*.CMD).

When ready, simply execute the CMD file using Shell(), then close Access. The CMD file can be relied on to re-open the database when the processing is completed (because you've included that in the template). I suggest this because it doesn't require guessing in advance how long a process might take. It's synchronous.

I hope this helps. If not, then please ignore ;)

[johnbe]

Many thanks Neo.
I can see why you would do this but I can't say I have ever used CMD or BAT files to any great extent so it would take a little more time for me to go this route but it is certainly worth me looking into as its a learning curve for me at the moment!
If you have any more info that would help me to understand this I would appreciate it.

[NeoPa]

I'll see if I can dig something up.

I was talking to Mary (MSquared) fairly recently about just such a technique. We talked for a while and she got a solution working, no trouble.

[NeoPa]

It appears that I have nothing written down for this. Can I assume that you will have some code that will determine whether or not a replacement version should be copied over the top of the original?

If so, I could look at posting some data that would go into your template table for you. This would be the fundamentals of the job and we could progress from there.

BTW: I use CMD files as they are always guaranteed to be available, and they are pretty basic in their functionality. Nothing too complicated to get to grips with. Mostly using simple, command line type commands linked together into a CMD script.

[spezzer]

Hi - I looked at this thread with a view to finding out how to grab database object description properties, but as I read through I realised you are trying to do something which I have applied very effectively using bat files. In my version of the 'User Interface Refresh' I send each user a shortcut via email I (in this case a lotus notes button ) which creates a desktop shortcut. This shortcut points at the server where the deployment files are stored and runs a bat file which carries out several tasks. By using a shortcut the black cmd box can be minimised and simply appears in the task bar momentarily while files are uploaded. The bat steps are:-

1. create host folder/directory in program files
2. copy accdb file to host folder
3. copy shortcut to host folder (calls accdb with runtime arg)
4. copy font file to windows\fonts
5. run regedit to add accdb to trusted locations
6. run the shortcut

This ensures that the user is always using the latest version with no macro warnings. If they do mess about with the copy in program files it gets over-written anyway. Any other tasks can easily be added to the bat file.

Just a taster, but if interested I can elaborate.

regs

[TheSmileyCoder]

Hi Spezzer
Could you elaborate on your point 5?

Thank you

[spezzer]

Hi the following text in a reg file will add a database folder to the list of safe locations used by Access 12 (2007) :-

Windows Registry Editor Version 5.00
 
[HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location20]
"Path"="C:\\Program Files\\DatabaseFolder\\"
"Description"="Meaningful Description"
"AllowSubFolders"=dword:0

Note
The Location Number should unique - a high value will prevent it from overwriting existing values entered manually.
The use of double backslash in the folder path ensures the path is valid

regs

[TheSmileyCoder]

Hi and thank you for your quick reply. How do you use that from within your bat file? I have never really toyed around with the registry before.

[spezzer]

Hi - to call the reg file from the bat file use the following syntax:-

regedit/s "\\servername\folderpath\filename.reg"

Note - The use of server UNC is not essential but prefer it to drive mappings and therefore could use, say F:\ instead of \\servername

regs

[NeoPa]

I recommend a space before the "/", but otherwise all very good advice.

In most cases the .REG file to be applied will also work simply by invoking the name of the file itself. .REG is associated already with REGEDIT.EXE /s.

Also, have you ever considered moving from .BAT files to .CMD ones? Unless you are still supporting pre-NT based systems (WinME was the last of those), there is no reason to continue with .BAT files. .CMD files are, in all ways, as good or better.

I prefer to create and delete the .CMD files from within my database and invoke them from a Shell() call. Powerful & quite fun.

[spezzer]

Hi

CMD instead of BAT? - new to me so will take a closer look

thanks for the tip

regs

[NeoPa]

I doubt you'll be sorry you did.

CMD /? will give you some background info to start with. Fundamentally, the difference is that a .BAT script still uses COMMAND.COM to process the file, whereas .CMD uses CMD.EXE.