By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,480 Members | 1,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,480 IT Pros & Developers. It's quick & easy.

How to pick up a Form or Reports Property Description text

P: 4
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
Mar 16 '10 #1

✓ answered by 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.



Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     On Error GoTo err_Handler
  3.     'Find the version numbers
  4.     strVerClient = Nz(DLookup("ClientVersion", "tbl_ClientVersion"), "")
  5.     strVerServer = Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  6.  
  7.     Debug.Print "server is:" & strVerServer
  8.     Debug.Print "client is:" & strVerClient
  9.  
  10.     If strVerClient = strVerServer Then
  11.         'User has latest version, proceed
  12.         DoCmd.Close acForm, Me.Name
  13.         DoCmd.OpenForm "frm_Login"
  14.         Else
  15.         'User does not have latest version, get latest version
  16.         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"
  17.         DoCmd.Close acForm, Me.Name
  18.  
  19.         'Transfer path to Update database so it knows where to place the new version
  20.             'First check if path exists allready
  21.             Dim strSQL As String
  22.  
  23.             If DCount("*", "tbl_PathInfo", "ComputerName=Environ('ComputerName')") > 0 Then
  24.                 'Do update
  25.  
  26.                     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'))));"
  27.                 Else
  28.                 'Do insert
  29.                     strSQL = "INSERT INTO tbl_PathInfo ([ComputerName]              ,[User]                 ,[Path]                     ,[Name]                     ,dt_When)" & _
  30.                                         " Values       ((Environ('ComputerName'))   ,(Environ('USERNAME'))   ,[currentproject].[path]    ,[currentproject].[name]    ,NOW());"
  31.             End If
  32.         DoCmd.SetWarnings (False)
  33.             Debug.Print strSQL
  34.             DoCmd.RunSQL strSQL
  35.         DoCmd.SetWarnings (True)
  36.  
  37.         Shell "MSAccess.exe " & Q & "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\Update.mdb" & Q, vbNormalFocus
  38.         DoCmd.Quit
  39.  
  40.  
  41.     End If
  42.  
  43. Exit Sub
  44. err_Handler:
  45.     Dim intErrCount As Integer
  46.     intErrCount = intErrCount + 1
  47.     If Err.Number = 3044 Then
  48.         'How many times has error occured
  49.         If intErrCount = 1 Then
  50.             'Try to link to network tables
  51.             globalRelinkNetwork
  52.             Resume
  53.         ElseIf intErrCount = 2 Then
  54.             'Try to link to local tables
  55.             globalRelinkLocal
  56.             Resume
  57.         ElseIf intErrCount > 2 Then
  58.             'Quit.
  59.             MsgBox "RTL could not connect to network tables. Exiting", vbOKOnly, "Network Connection Error"
  60.             DoCmd.Quit
  61.         End If
  62.     End If
  63.  
  64.     MsgBox "Error number: " & Err.Number & ". Description: " & Err.Description
  65.  
  66. 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).




Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Const q As String = """"
  4. Private ErrorCounter As Integer
  5. Private strOldFront As String
  6.  
  7. Private Sub btn_Continue_Click()
  8.         'open the new database and return to it
  9.         Shell "MSAccess.exe " & q & DLookup("Path", "tbl_PathInfo") & "\" & DLookup("Name", "tbl_PathInfo") & q, vbNormalFocus
  10.         'exit this update application
  11.         DoCmd.Quit
  12. End Sub
  13.  
  14. Private Sub Form_Load()
  15.     Me.lbl_InstallVersion.Caption = "Installing version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  16.     Debug.Print "Before doEvents: " & Now()
  17.     DoEvents
  18.     Debug.Print "After doEvents: " & Now()
  19.  
  20.  
  21.  
  22.     Me.TimerInterval = 10000
  23.  
  24.  
  25.  
  26.  
  27.  
  28. End Sub
  29.  
  30.  
  31.  
  32. Private Sub Form_Timer()
  33.     On Error GoTo err_Handler
  34.     Me.TimerInterval = 0
  35.     'Backup users version
  36.     Dim strOldFront As String
  37.     Dim strComputerName As String
  38.     strComputerName = Environ("ComputerName")
  39.     strOldFront = DLookup("Path", "tbl_PathInfo", "ComputerName='" & strComputerName & "'") & "\" & DLookup("Name", "tbl_PathInfo", "ComputerName='" & strComputerName & "'")
  40.     Dim strBackup As String
  41.     strBackup = Replace(strOldFront, ".mdb", ".backup")
  42.     'Check if there is a older backup
  43.         If Dir(strBackup) <> "" Then Kill strBackup 'There was a backup, kill it
  44.         FileCopy strOldFront, strBackup 'Backup
  45.         DoEvents 'Allow backup to finish
  46.         If Dir(strOldFront) <> "" Then Kill strOldFront 'Kill old version
  47.         Debug.Print "About to copy"
  48.         FileCopy "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\RTL-Latest.mdb", strOldFront
  49.         Debug.Print "Copy Finished"
  50.         DoEvents
  51.  
  52.         Me.lbl_PleaseWait.Caption = "Done!"
  53.         Me.btn_Continue.Enabled = True
  54.         Me.lbl_InstallVersion.Caption = "Succesfully installed version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  55. Exit Sub
  56. err_Handler:
  57.     ErrorCounter = ErrorCounter + 1
  58.     MsgBox Err.Number & Err.Description
  59.  
  60.  
  61.     If ErrorCounter = 3 Then
  62.         Me.btn_Exit.Visible = True
  63.     End If
  64.     If ErrorCounter < 4 Then
  65.         Resume
  66.         Else
  67.             MsgBox "An error occured that I cannot fix. Please contact TheSmileyOne"
  68.             Quit acQuitSaveNone
  69.     End If
  70.  
  71. End Sub
  72.  


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.

Share this Question
Share on Google+
18 Replies


NeoPa
Expert Mod 15k+
P: 31,768
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!
Mar 16 '10 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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)
Expand|Select|Wrap|Line Numbers
  1. if instr(1,currentproject.Path,"Desktop")>0 Or _
  2.    InStr(1,currentproject.Path,"C:\")>0 Then
  3.   Msgbox "Bad Boy"
  4.   Docmd.quit
  5. 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.
Mar 16 '10 #3

NeoPa
Expert Mod 15k+
P: 31,768
That would be my approach too Smiley. Why overcomplicate when you can write a VBA routine you can use in multiple databases?
Mar 17 '10 #4

P: 4
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.
Mar 17 '10 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.



Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     On Error GoTo err_Handler
  3.     'Find the version numbers
  4.     strVerClient = Nz(DLookup("ClientVersion", "tbl_ClientVersion"), "")
  5.     strVerServer = Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  6.  
  7.     Debug.Print "server is:" & strVerServer
  8.     Debug.Print "client is:" & strVerClient
  9.  
  10.     If strVerClient = strVerServer Then
  11.         'User has latest version, proceed
  12.         DoCmd.Close acForm, Me.Name
  13.         DoCmd.OpenForm "frm_Login"
  14.         Else
  15.         'User does not have latest version, get latest version
  16.         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"
  17.         DoCmd.Close acForm, Me.Name
  18.  
  19.         'Transfer path to Update database so it knows where to place the new version
  20.             'First check if path exists allready
  21.             Dim strSQL As String
  22.  
  23.             If DCount("*", "tbl_PathInfo", "ComputerName=Environ('ComputerName')") > 0 Then
  24.                 'Do update
  25.  
  26.                     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'))));"
  27.                 Else
  28.                 'Do insert
  29.                     strSQL = "INSERT INTO tbl_PathInfo ([ComputerName]              ,[User]                 ,[Path]                     ,[Name]                     ,dt_When)" & _
  30.                                         " Values       ((Environ('ComputerName'))   ,(Environ('USERNAME'))   ,[currentproject].[path]    ,[currentproject].[name]    ,NOW());"
  31.             End If
  32.         DoCmd.SetWarnings (False)
  33.             Debug.Print strSQL
  34.             DoCmd.RunSQL strSQL
  35.         DoCmd.SetWarnings (True)
  36.  
  37.         Shell "MSAccess.exe " & Q & "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\Update.mdb" & Q, vbNormalFocus
  38.         DoCmd.Quit
  39.  
  40.  
  41.     End If
  42.  
  43. Exit Sub
  44. err_Handler:
  45.     Dim intErrCount As Integer
  46.     intErrCount = intErrCount + 1
  47.     If Err.Number = 3044 Then
  48.         'How many times has error occured
  49.         If intErrCount = 1 Then
  50.             'Try to link to network tables
  51.             globalRelinkNetwork
  52.             Resume
  53.         ElseIf intErrCount = 2 Then
  54.             'Try to link to local tables
  55.             globalRelinkLocal
  56.             Resume
  57.         ElseIf intErrCount > 2 Then
  58.             'Quit.
  59.             MsgBox "RTL could not connect to network tables. Exiting", vbOKOnly, "Network Connection Error"
  60.             DoCmd.Quit
  61.         End If
  62.     End If
  63.  
  64.     MsgBox "Error number: " & Err.Number & ". Description: " & Err.Description
  65.  
  66. 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).




Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Const q As String = """"
  4. Private ErrorCounter As Integer
  5. Private strOldFront As String
  6.  
  7. Private Sub btn_Continue_Click()
  8.         'open the new database and return to it
  9.         Shell "MSAccess.exe " & q & DLookup("Path", "tbl_PathInfo") & "\" & DLookup("Name", "tbl_PathInfo") & q, vbNormalFocus
  10.         'exit this update application
  11.         DoCmd.Quit
  12. End Sub
  13.  
  14. Private Sub Form_Load()
  15.     Me.lbl_InstallVersion.Caption = "Installing version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  16.     Debug.Print "Before doEvents: " & Now()
  17.     DoEvents
  18.     Debug.Print "After doEvents: " & Now()
  19.  
  20.  
  21.  
  22.     Me.TimerInterval = 10000
  23.  
  24.  
  25.  
  26.  
  27.  
  28. End Sub
  29.  
  30.  
  31.  
  32. Private Sub Form_Timer()
  33.     On Error GoTo err_Handler
  34.     Me.TimerInterval = 0
  35.     'Backup users version
  36.     Dim strOldFront As String
  37.     Dim strComputerName As String
  38.     strComputerName = Environ("ComputerName")
  39.     strOldFront = DLookup("Path", "tbl_PathInfo", "ComputerName='" & strComputerName & "'") & "\" & DLookup("Name", "tbl_PathInfo", "ComputerName='" & strComputerName & "'")
  40.     Dim strBackup As String
  41.     strBackup = Replace(strOldFront, ".mdb", ".backup")
  42.     'Check if there is a older backup
  43.         If Dir(strBackup) <> "" Then Kill strBackup 'There was a backup, kill it
  44.         FileCopy strOldFront, strBackup 'Backup
  45.         DoEvents 'Allow backup to finish
  46.         If Dir(strOldFront) <> "" Then Kill strOldFront 'Kill old version
  47.         Debug.Print "About to copy"
  48.         FileCopy "G:\DATA\Tender Evaluation\RTLDB\RTL_BE\Updater\RTL-Latest.mdb", strOldFront
  49.         Debug.Print "Copy Finished"
  50.         DoEvents
  51.  
  52.         Me.lbl_PleaseWait.Caption = "Done!"
  53.         Me.btn_Continue.Enabled = True
  54.         Me.lbl_InstallVersion.Caption = "Succesfully installed version nr: " & Nz(DLookup("ServerVersion", "tbl_ServerVersion"), "")
  55. Exit Sub
  56. err_Handler:
  57.     ErrorCounter = ErrorCounter + 1
  58.     MsgBox Err.Number & Err.Description
  59.  
  60.  
  61.     If ErrorCounter = 3 Then
  62.         Me.btn_Exit.Visible = True
  63.     End If
  64.     If ErrorCounter < 4 Then
  65.         Resume
  66.         Else
  67.             MsgBox "An error occured that I cannot fix. Please contact TheSmileyOne"
  68.             Quit acQuitSaveNone
  69.     End If
  70.  
  71. End Sub
  72.  


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.
Mar 17 '10 #6

P: 4
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
Mar 17 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
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 ;)
Mar 17 '10 #8

P: 4
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.
Mar 17 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 17 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
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.
Mar 17 '10 #11

P: 4
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
Mar 23 '10 #12

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi Spezzer
Could you elaborate on your point 5?

Thank you
Apr 6 '10 #13

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

Expand|Select|Wrap|Line Numbers
  1. Windows Registry Editor Version 5.00
  2.  
  3. [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location20]
  4. "Path"="C:\\Program Files\\DatabaseFolder\\"
  5. "Description"="Meaningful Description"
  6. "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
Apr 8 '10 #14

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Apr 8 '10 #15

P: 4
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
Apr 8 '10 #16

NeoPa
Expert Mod 15k+
P: 31,768
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.
Apr 8 '10 #17

P: 4
Hi

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

thanks for the tip

regs
Apr 8 '10 #18

NeoPa
Expert Mod 15k+
P: 31,768
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.
Apr 8 '10 #19

Post your reply

Sign in to post your reply or Sign up for a free account.