Creating a Settings table...

A Settings table is where you let your Users define their own defaults.  This comes in handy when you are distributing your database and you know everyone is not going to want the same Default.

In Example A the Users have a chance to select if they want to print all the Articles or just the Article they are on.  In Example B Users can set a multitude of Defaults.
You then place the below on you On_Click event of your Preview (or Print) Command Button being sure to change the names of the fields and report to match yours...

If IsNull(Me.txtArticleID) Then
   MsgBox "Nothing to do, select an Article and retry!", vbInformation, "Preview"
     If DLookup("sPrintingArticles", "tblSettings") = True Then
       DoCmd.RunCommand acCmdSaveRecord
       DoCmd.OpenReport "rptArticle", acViewPreview, , "aArticleID = " & Me.txtArticleID
       DoCmd.RunCommand acCmdSaveRecord
       DoCmd.OpenReport "rptArticle", acViewPreview
     End If
End If

You can download the sample database that contains this table and report here.
For this example, used in the Multicolumn Report database, we are allowing Users to print all articles or just the article they are reviewing.

tblSettings only has two fields sSettingID (Autonumber, Primary Key) and sPrintingArticles (Yes/No, Default False).
The difficult I do immediately, the impossible takes a little bit longer.
Tips (Main)
Naming Conventions
Where are the Options?
Options for Current Database
Object Designers
Trusted Locations
Repair (or Remove) MISSING or Broken References
Export Specifications
Import Specifications
Navigation Pane Options
Setting up a Model Database
Default Buttons and Legend
Set Subdatasheet to [NONE]
Not_In_List Event
Handling Attachments
Database Design Tips
In this example you give Users the ability to change a variaty of defaults, i.e.
Path to Logo used on Reports
Path to Report Templates
Invoice starting number
Path to images used by database
This is especially helpful when deploying a database.  To do this you're going to need two tables, one to hold the Setting Types (tlkpSettingTypes) and one to hold the actual Settings (tblSettings) related by sSettingTypeID (PK-tlkpSettingTypes) to sSettingTypeID (FK-tblSettings).
Then copy\paste the below into a Module and call by fncSetting(EnterYourNumberHere).
Function fncSetting(lngSettingTypeID As Long) As String
 'To use fncSetting(1)
    If DCount("sSettingTypeID", "tblSettings", "[sSettingTypeID] = " & lngSettingTypeID) = 0 Then
        MsgBox "Setting has not been set.  Go to Settings on Switchboard and set.", vbCritical + vbOKOnly, "Settings"
        If DLookup("sSetting", "tblSettings", "[sSettingTypeID] = " & lngSettingTypeID) <> "" Then
            fncSetting = DLookup("sSetting", "tblSettings", "[sSettingTypeID] = " & lngSettingTypeID)
            MsgBox "Setting has not been set.  Go to Settings on Switchboard and set.", vbCritical + vbOKOnly, "Settings"
        End If
    End If

End Function
You should create a Form for your Users to enter their defaults.  You really don't want them entering data directly into the table.  To see a sample form click here.
Click to send feedback...
This site uses cookies to collect data on usage. By continuing to browse this site you consent to this policy. Find out more here.