Database settings - constants or Dlookup?

[Moss100]

Hello all, I am after some database design advise.

I have certain values that I want to use in various areas of my database.

For example I have a field that stores an Inpection Interval.

To start with a value of 3 months will be used, but this may later change to 4 months.

At present I have a global constant in a module called IntervalReminder = 3

This value is used on various forms for text box calculations.

Of course if I change the constant to 4, the change is reflected thoughout the database.

The users however will not be familiar with going into modules to change values, so I was thinking of creating a Settings table (fronted with a form) and using Dlookup to get values to the calculated textboxes.

Functionally both ways seem to work fine, but I was wondering what the best or normal approach is. A settings table seems straightforward or, is there a way to make it easy for users to change constant values without opening the module?

Many thanks for any direction / comments. Mark.

 

[PHV]

Definitively use a setting table.
The end user should never use the VBE.


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Moss100]

Thanks - is it possible to set a global constant with a Dlookup,

Ie. Could I use set the constant value from a settings table using Dlookup?

Or should the global constant be replaced by Dlookup

Thank you.

 

[PHV]

You may use a global variable instead of a constant ans set its value in a startup routine.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

So that you do not have to repeat the dlookup code, you can wrap it to make it easier to reuse

Public Function GetInterval() as double
GetInterval = dlookup("Interval","TableSettings")
end function

Then you can reuse easily even in queries

Select someID where (date()-[lastInspection])> GetInterval()

 

[MajP]

If you use GlobalVariables, you will have to wrap them anyways to use in a query, and will have to make sure they are set before calling them. Also (probably unlikely), but if you throw an untrapped error they will go out of scope.

Public GlobalInterval as Integer

Public Function GetInterval() as double
'needed if used in a query
GetInterval = GlobalInterval
end function

but you would have to set at startup
GlobalInterval = dlookup("Interval","TableSettings")

 

[Moss100]

Thanks for the input guys.

Generally I want to use the settings for calculated textboxes, not queries (if that changes things)???

Can someone tell me why the following does not work? I am trying to set the constant value by a Dlookup of the Settings table

Public Const g_Reminder_Interval_In_Months = DLookup("[Set_Interval_Mths]", "tbl_Settings", "[Set_ID]=1")

Thank you Mark

 

[dhookom]

You probably can't set the value using a function in the declaration.

Consider setting the value of g_Reminder_Interval_In_Months in the ON OPEN event of your initial splash form.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Generally I want to use the settings for calculated textboxes, not queries (if that changes things)

Not really, but if you want to use in a query, or as a default value on a form then you would have to wrap it in a function.

So as previously stated set your global variables by calling a sub from your initial splashform or autoexecmacro.

In a standard module

'ModuleSettings
Public g_Reminder_Interval_In_Months as long
Public g_OtherVariable as string
Public g_AnotherVariable as double
Public g_oneMoreVariable as currency

Public Sub SetAllGlobalVariables
  g_Reminder_Interval_In_Months = DLookup("[Set_Interval_Mths]", "tbl_Settings", "[Set_ID]=1") 
  g_OtherVariable = ...
  g_AnotherVariable = ...
  g_oneMoreVariable = ...
end sub

'Then if you want to use in query or form setting create a wrapper

Public Sub GetReminderInterval
  GetReminderInterval = g_Reminder_Interval_In_Months
end sub

 

[Andrzejek]

Shouldn't it be:

Public [blue]Function[/blue] GetReminderInterval() [blue]As Long[/blue]
  GetReminderInterval = g_Reminder_Interval_In_Months
end sub

???

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[MajP]

Yep. Thanks.