UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

3 Pages V  < 1 2 3  
Reply to this topicStart new topic
> Public Variables    
 
   
strive4peace
post Dec 10 2007, 12:01 AM
Post #41

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Pete,

thank you for you very kind comment

http://www.utteraccess.com/forums/showflat...;Number=1553092

I wanted to follow up with another tip
... since the values of Database Properties are NOT kept with you Import stuff into another database,
it is a good idea to make a procedure to define all of the Database Properties that you are using

For purposes of this example, I am using 3 database properties
(and it is a good idea to preface the properties you create, I haved used 'Default'):

DefaultUserID -- this is a Long Integer identifying the person this Front-End (or database) belongs to
DefaultConvertCase -- this is a boolean value to determine if Access should convert case on data entry (ie, I change to ProperCase for names and addresses, etc)
DefaultIsAdmin -- this identifies if the user has Admin priviledges -- if so, code will break when there is an error

So... to quickly set up your properties again, make another procedure in your mod_DatabaseProperties
(assuming you will group all the related general code together) module:

CODE
'~~~~~~~~~~~~~~~~~~~~~ RunSetDatabaseProperties
Sub RunSetDatabaseProperties()
   Dim mPropName As String _
   , mPropType As Long _
   , mValue
  
   mPropName = "DefaultUserID"
   mPropType = dbLong
   mValue = -1
  
   SetDatabaseProperty mPropName, mPropType, mValue
  
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"
  
   mPropName = "DefaultConvertCase"
   mPropType = dbBoolean
   mValue = True
  
   SetDatabaseProperty mPropName, mPropType, mValue
  
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"
  
   mPropName = "DefaultIsAdmin"
   mPropType = dbBoolean
   mValue = True
  
   SetDatabaseProperty mPropName, mPropType, mValue
  
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"
  
End Sub


and, then, for summary purposes, here are the other procedures you would want in this module:

CODE
'~~~~~~~~~~~~~~~~~~~~~ SetDatabaseProperty
Function SetDatabaseProperty( _
   pPropName As String _
   , pPropType As Long _
   , pValue) As Byte

   'set up Error Handler
   On Error GoTo Proc_Err
    
   CurrentDb.Properties.Append CurrentDb.CreateProperty( _
      pPropName, pPropType, pValue, False)
  
Proc_Exit:
   Exit Function
  
Proc_Err:
   'property is not defined
   If Err.Number = 3367 Then
      CurrentDb.Properties(pPropName) = pValue
      Resume Proc_Exit
   End If
  
   MsgBox Err.Description, , _
        "ERROR " & Err.Number _
        & "   SetDatabaseProperty"
  
   'press F8 to step through code and debug
   'remove next line after debugged
   If IsAdmin() Then Stop:    Resume

   Resume Proc_Exit
End Function

'~~~~~~~~~~~~~~~~~~~~~ DeleteDatabaseProperty
Function DeleteDatabaseProperty( _
   ByVal pPropName As String _
   ) As Byte

   'ignore errors -- Property may not be set
   On Error Resume Next
  
   CurrentDb.Properties.Delete pPropName
  
Proc_Exit:
   MsgBox pPropName & " is deleted", , "Done"
   Exit Function
  
End Function
  
'~~~~~~~~~~~~~~~~~~~~~ IsPropertyDefined
Function IsPropertyDefined( _
   ByVal pPropName As String) As Boolean

   'set up Error Handler
   On Error GoTo Proc_Err
  
   IsPropertyDefined = False
  
   Dim mVar
  
   mVar = CurrentDb.Properties(pPropName)
      
   IsPropertyDefined = True
  
Proc_Exit:
   Exit Function
  
Proc_Err:
   Resume Proc_Exit
End Function
  
'~~~~~~~~~~~~~~~~~~~~~~~~~~ IsAdmin
'in some of the error handling routines, IsAdmin is tested
'if true, code stops executing so that it can be debugged
'set to TRUE for development
'set to FALSE for distribution to others that do not have Admin privileges
  
Function IsAdmin() As Boolean
   On Error Resume Next
   IsAdmin = CurrentDb.Properties("DefaultIsAdmin")
End Function
  
'... and, for good measure ...
  
'~~~~~~~~~~~~~~~~~~~~~ ShowDatabaseProperties
Sub ShowDatabaseProperties()
   Dim prp As Property
   Dim i As Integer
   i = 0
   On Error Resume Next
   For Each prp In CurrentDb.Properties
      Debug.Print i, prp.Name;
         Debug.Print " = ", prp.Value
      i = i + 1
   Next prp
   Set prp = Nothing
End Sub


It is good to set up a form to set and change Database Properties.
For purposes of this example, I have a form called "f_System_Defaults"
that displays and allows the user to change the example properties

do something like this on the OPEN event:

CODE
'~~~~~~~~~~~~~~~~~~~~~ Form_Open
Private Sub Form_Open(Cancel As Integer)

   'if properties ar not set, load them with default values

   If Not IsPropertyDefined("DefaultUserID") Then
      SetDatabaseProperty "DefaultUserID", dbLong, -1
   End If
    
   If Not IsPropertyDefined("DefaultIsAdmin") Then
      SetDatabaseProperty "DefaultIsAdmin", dbBoolean, True
   End If
  
   If Not IsPropertyDefined("DefaultConvertCase") Then
      SetDatabaseProperty "DefaultConvertCase", dbBoolean, True
   End If
  
End Sub


Now that we know all the referenced Database Properties have a value, we can do this on the LOAD event

CODE
'~~~~~~~~~~~~~~~~~~~~~ Form_Load
Private Sub Form_Load()
      
   'admin
   Me.DefaultUserID = _
      CurrentDb.Properties("DefaultUserID")
   Me.echoDefaultUserID = _
      CurrentDb.Properties("DefaultUserID")
  
   Me.IsAdmin = _
      CurrentDb.Properties("DefaultIsAdmin")
      
   'user -- for purposes of this example, I have Admin and User settings combined
   Me.ConvertCase = _
      CurrentDb.Properties("DefaultConvertCase")
      
End Sub


echoDefaultUserID is a textbox to show the numeric value of DefaultUserID
DefaultUserID is a combobox
IsAdmin is a checkbox
ConvertCase is a checkbox

labels are named:
Label_controlname

on the AfterUpdate event of each control,
you will want to write the new value to the respective Database Property

CODE
'~~~~~~~~~~~~~~~~~~~~~ DefaultUserID
Private Sub DefaultUserID_AfterUpdate()

   Dim mPropName As String _
   , mPropType As Long _
   , mValue
    
   If IsNull(Me.DefaultUserID) Then
      mValue = -1
   Else
      mValue = Me.DefaultUserID
   End If
    
   mPropName = "DefaultUserID"
   mPropType = dbLong
  
   SetDatabaseProperty mPropName, mPropType, mValue
  
   Me.echoDefaultUserID = _
      CurrentDb.Properties("DefaultUserID")
    
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"

End Sub
    
'~~~~~~~~~~~~~~~~~~~~~ DefaultConvertCase
Private Sub ConvertCase_AfterUpdate()

   Dim mPropName As String _
   , mPropType As Long _
   , mValue

   mPropName = "DefaultConvertCase"
   mPropType = dbBoolean
   mValue = Me.ConvertCase
    
   SetDatabaseProperty mPropName, mPropType, mValue
    
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"
  
   'bold associated label if property is True
   Me.Label_ConvertCase.FontBold = Me.ConvertCase
  
End Sub
  
'~~~~~~~~~~~~~~~~~~~~~ DefaultIsAdmin
Private Sub IsAdmin_AfterUpdate()
   Dim mPropName As String _
   , mPropType As Long _
   , mValue

   mPropName = "DefaultIsAdmin"
   mPropType = dbBoolean
   mValue = Me.IsAdmin
    
   SetDatabaseProperty mPropName, mPropType, mValue
    
   MsgBox mPropName & " is " _
   & CurrentDb.Properties(mPropName) _
   & " for this database", , "Done"
    
   'bold associated label if property is True
   Me.Label_IsAdmin.FontBold = Me.IsAdmin
End Sub


To get more information on any keyword in the code you want help on, simply click on it and press F1

... and this discussion would not be compete without a big THANKS!!! to Brent (dateAdrenaline)
who is the one who turned me on to using Database Properties
Go to the top of the page
 
+
strive4peace
post Dec 10 2007, 01:49 AM
Post #42

UtterAccess VIP
Posts: 20,318
From: Colorado



ps

oh, and 2 more statements at the bottom of the form LOAD event:

CODE
   Me.Label_ConvertCase.FontBold = Me.ConvertCase
   Me.Label_IsAdmin.FontBold = Me.IsAdmin


(I wasn't going to put these in here - but it is nice to boldface the label of a checkbox when the value is true)
Go to the top of the page
 
+
pere_de_chipstic...
post Dec 10 2007, 04:56 PM
Post #43

UtterAccess VIP
Posts: 8,609
From: South coast, England



I found your discussion about variables fascinating and illuminating – Many thanks. It was only then that I realised that I use different types of variables in quite specific ways, dependant on their ‘longevity’.

Global Variables
I use these to transfer ‘Short term’ data
e.g. for reports which the ‘On Open’ event uses to write data into a control on the report,
or to write the reports control source SQL. Important to ensure that data is always created immediately before it is used, this (I think) get’s round one of Crystal’s distrust of GVs

Hidden Form Variables
I don’t like the overhead of having a form open just to store variables, but have used hidden controls on a visible form to hold variables. I use these as ‘medium term’ variables that change dependant on the user’s input

Table in access
I use this to hold data that needs to be saved between log-ons – e.g to automatically show the last user on a log on form, etc

Properties
This one I haven’t used before, feel that this would be most useful for holding data about the application that was ‘unvarying’, e,g, the version of the FE and BE of the database. (But I could be persuaded otherwise!)

Would be interested in your comments

Kind regards

Bernie

PS. May I add my congratulations to you, Crystal, on your 15000 posts. (I don’t know how you do it!) You really made me feel most welcome as a new user to Utter Access; your help was clear, pertinent and given in a friendly and supportive manner – many thanks. Keep up the brilliant work!
Go to the top of the page
 
+
strive4peace
post Dec 10 2007, 10:09 PM
Post #44

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Bernie,

glad you found the discussion interesting (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) thanks for adding on

I am curious ... what is the significance of your username?


PS ... and thank you for you very nice comment!



Edited by: strive4peace2007 on Mon Dec 10 22:11:21 EST 2007.
Go to the top of the page
 
+
pere_de_chipstic...
post Dec 11 2007, 05:57 AM
Post #45

UtterAccess VIP
Posts: 8,609
From: South coast, England



Chipstick is my younger daughter’s nick name (don’t ask me why!) she always signs off as ‘chipstick’ when she e-mails me from school. One particular e-mail was about her French, which I replied to as ‘pere de chipstick’

When I joined Utter Access, it was her suggestion that I use ‘pere_de_chipstick’

May I also be curious? and ask where is ‘nowhere’?
Go to the top of the page
 
+
strive4peace
post Dec 11 2007, 07:29 AM
Post #46

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Bernie,

interesting story, thanks

Pennsylvania
Go to the top of the page
 
+
petesmall
post Dec 12 2007, 06:17 PM
Post #47

UtterAccess Addict
Posts: 142
From: USA-New Jersey



Hi Crystal,

Thanks for this thoroughly worked out scheme. I will try it out tonight. And thanks for the hot tip about making the check box labels bold when true.

Bbest,
Pete
Go to the top of the page
 
+
strive4peace
post Dec 12 2007, 06:27 PM
Post #48

UtterAccess VIP
Posts: 20,318
From: Colorado



your welcome, Pete (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+
duanecwilson
post Dec 14 2007, 05:17 PM
Post #49

UtterAccess Guru
Posts: 886
From: Greenville, SC



I would like to rev this discussion up for at least two more questions as I have been reading several long posts on global variables and other options.

1) First of all, someone mentioned scalar variables. I am trying to figure out what they are and where and how to implement them.

2) Database properties - In all the posts I have read, I didn't read one mention of multi-user databases, particularly the ones that are not split - I know they are supposed to be, but this is the real world, and not all of them are or will be. But if database properties are used, then each user will step on the other one, won't they? (Same with tables, I suppose)
Go to the top of the page
 
+
strive4peace
post Dec 14 2007, 05:36 PM
Post #50

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Duane,

1. I'm going to leave this up to Leigh (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) I would think they would be regular data types such as single, double, long, string, etc

2. the Database properties go in the FRONT end -- at least that is where I would put them. You could, of course, put database properties in the back-end and, in the code to read/write them, you would have to reference the back-end database, not CurrentDb





Edited by: strive4peace2007 on Fri Dec 14 17:44:54 EST 2007.
Go to the top of the page
 
+
niesz
post Dec 14 2007, 05:54 PM
Post #51

Utter A-fishin'-ado
Posts: 17,952
From: Cincinnati, Ohio, USA . . . ><((((°>



Hey Duane.

The reason that you didn't read much about non-split DBs is because from a professional developer point of view, they should always be split. Much easier to manage. The only time I work with non-split DBs are during the early stages of development or when it is a personal DB to be used by myself only.

If you're going to go with the non-split DB (not recommended), and you were using table based variables, you would have to add an additional field to the table to indicate which user is using the variable. DB Properties would not be a viable option.
Go to the top of the page
 
+
LPurvis
post Dec 14 2007, 06:31 PM
Post #52

UtterAccess Editor
Posts: 14,284
From: England (North East / South Yorks)



Crystal is bang on - and echos my brief explanation in the other thread where I first mentioned them.
Go to the top of the page
 
+
strive4peace
post Dec 15 2007, 08:17 AM
Post #53

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Duane,

I PM'd Leigh to ask him what he meant -- scalar variables, in a mathematical sense are those that only have magnitude (amount) and direction (negative, positive). Scalar variables can only hold one value at a time (as opposed to arrays and records, for instance, which can have several elements)

Using "scalar" is not a common way of expressing variables in Access, but Leigh's background is math (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)

does this clear it up for you?
Go to the top of the page
 
+
strive4peace
post Dec 15 2007, 09:16 AM
Post #54

UtterAccess VIP
Posts: 20,318
From: Colorado



Hi Leigh,

thanks -- I didn't see there were more messages on page 2 ... thought after you ate and rewarded yourself (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) that you forgot ... glad to see you didn't <smile>




Edited by: strive4peace2007 on Sat Dec 15 9:17:07 EST 2007.
Go to the top of the page
 
+
duanecwilson
post Dec 17 2007, 03:26 PM
Post #55

UtterAccess Guru
Posts: 886
From: Greenville, SC



Thank you. That clears it up. I had a good idea but just wanted to make sure I was on the right path..
Go to the top of the page
 
+
strive4peace
post Dec 19 2007, 02:21 PM
Post #56

UtterAccess VIP
Posts: 20,318
From: Colorado



you're welcome, Duane (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif)
Go to the top of the page
 
+

3 Pages V  < 1 2 3
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 20th April 2014 - 01:23 PM