Using VBA to require certain fields be filled in on an unbound form in MS Access.

On some of the data entry forms that I use there are fields (text box controls) that are required and others that are optional. Since I normally use unbound forms I can easily identify blank required fields and remind the user to fill them in.

On this form has three controls that are for required fields:

If the user leaves the Collection Number, the Scope/Content Note, or the Languages blank I need the code to identify the blank required fields and prompt the user to filled them in. This is pretty easily done by using the function Nz() to convert the null values from the required fields to “DataError” and the optional ones to a zero-length string.

In the On_Click event of the Save button (cmdSave_Click), after dimensioning the variables I pick up the values from the controls and use “DataError” to isolate where the user needs to add data:

    strCollNum = Nz(Me.txtCollNum, "DataError")

    strTitle = Nz(Me.txtTitle, "")

    strScopeContent = Nz(Me.txtScopeContent, "DataError")

    strType = Nz(Me.txtType, "")

    strFormat = Nz(Me.txtFormat, "")

    strExtent = Nz(Me.txtExtent, "")

    strDate = Nz(Me.txtDate, "")

    strLocation = Nz(Me.txtLocation, "")

    strRestrictions = Nz(Me.txtRestrictions, "")

    strRelation = Nz(Me.txtRelation, "")

    strLanguage = Nz(Me.txtLanguage, " DataError ")

    strSubjects = Nz(Me.txtSubjects, "")

    strKeywords = Nz(Me.txtKeywords, "")

    strNotes = Nz(Me.txtNotes, "")

        'Put the three required fields together in one string, strError.

        strError = strCollNum & strDesc & strLanguages

        'Test the strError to see if it contains DataError.

        If strError Like "*DataError*" Then

        'If strError contains DataError test each required field.

            If strCollNum Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtCollNum.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

                Me.txtCollNum.BackColor = RGB(199, 208, 227)

            End If

            If strDesc Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtDescription.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

                Me.txtDescription.BackColor = RGB(199, 208, 227)

            End If

            If strLanguages Like "DataError" Then

            'For a DataError make the back color for the text box red.

                Me.txtLanguage.BackColor = RGB(255, 0, 0)

            Else:

            'Otherwise the back color for the text box should be the original blue.

               Me.txtLanguage.BackColor = RGB(199, 208, 227)

            End If

            'Prompt the user to fill in the missing date and exit the sub.

            MsgBox "Red fields are required. Please complete form and re-save.", vbCritical

            Exit Sub       

       Else:

            'The code to write the data to the tables would go here to execute when all three 

             'required fields contain data.

      End If

Here is what the form looked like when I tried to save it after entering a collection number without a Scope/Content Note or Language.

Since the sub was exited after the message box was presented the user will be returned to the form when the message box is closed: the record cannot be saved unless the required fields contain data. Whether or not they contain valid data is another question, but using a similar set of tests the data can be validated and rejected if not valid.

Next and Previous Buttons for MS Access Bound Forms

Normally I prefer to use unbound controls on my forms since I feel it gives me more flexibility and control, however on a recent project I needed to use bound controls. This form has 13 subforms in a tab control for 13 different audio-visual media formats. I couldn’t allow the users to create new records using the navigation buttons on the subforms since that would create records in the table with no way of linking them back to the proper subform. I, instead, added a “New Item” button to each subform, which creates the new record and inserts the media format into it, and deleted the navigation buttons. This meant I needed Next and Previous buttons on the subforms.

Back on 07Ju11 I explained how to make Next and Previous buttons on an unbound form. It’s a little different on a bound form. I did, however, want some of the same behavior. I don’t like the Previous button to show if you are on the first record or the Next button to show if you are on the last record. This prevents the user from click a button when there is nowhere to go. The code below is how I control the display of the buttons. I ended up including Previous, Next, and Last buttons, a First button could also be included if you like. I also included a text box to let the user know that they are on “record 1 of 7”, or wherever they are.

For the sake of simplicity I used the exact same code for all three buttons:

     Private Sub cmdPrevious _Click()

     'Private Sub cmdNext _Click()

     'Private Sub cmdLast _Click()

          Dim intIndex As Integer

          Dim intCount As Integer

          'For the Previous button use:

          DoCmd.GoToRecord , , acPrevious

          'For the Next button use:

          'DoCmd.GoToRecord , , acNext

          'For the Last button use:

          'DoCmd.GoToRecord , , acLast

          'Get the count of records in the form's record set.

          intCount = Me.Recordset.RecordCount

          'Debug.Print " Count = " & intCount

          'Get the cursor position in the recordset.

          'Since the cursor position for the first record is zero

          'add 1 to the cursor position so that the last one will equal the record count.

          intIndex = Me.Recordset.AbsolutePosition + 1

          'Debug.Print "Postion = " & intIndex

          If intCount = intIndex Then

          'The last record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Next and Last buttons, one which has the focus,

          'make sure the Previous button is visible and move the focus to it.

               Me.cmdPrevious.Visible = True

               Me.cmdPrevious.SetFocus

               Me.cmdNext.Visible = False

               Me.cmdLast.Visible = False

          ElseIf intIndex = 1 Then

          'The first record is being displayed, only the Previous button should show.

          'In order to change the visible status of the Previous button, which has the focus,

          'make sure the Next button is visible and move the focus to it.

               Me.cmdNext.Visible = True

               Me.cmdNext.SetFocus

               Me.cmdPrevious.Visible = False

               Me.cmdLast.Visible = True

          ElseIf intIndex > 1 And intIndex < intCount Then

          'The displayed record is not the first or last record, all buttons should show.

          'Since you are not changing the visible status of the button used 

          'the focus does not need to be reset.

               Me.cmdPrevious.Visible = True

               Me.cmdNext.Visible = True

               Me.cmdLast.Visible = True

          End If

         'Give the user an indication of where they are in the record set.

          Me.txtItemCount = "Item " & intIndex & " of " & intCount

     End Sub

Getting Data From Another Form in MS Access

Sometimes in order to run the VBA code I need data from a form other than the one that contains the code. For example, I have a form that shows all the data for an folder in a collection, frmFolderData. In order to pull the data to populate that form I need to know the folder’s index number. If frmFolderData is being opened from the list of folders in frmSeriesFolderList it is pretty simple:

     lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)

Where lngFolderID is a long variable for the folder index number, which is in the first column of the list box.

However if that is the code I use and then try to open frmFolderData from frmEADFolders I will get an error because frmSeriesFolderList is not open. I need to test for whether or not the form I want is open, or in this case, which of the two forms I could use is open. To do that I use the IsLoaded property.

Here is the code used in frmFolderData to decide whether to pull the needed data from frmSeriesFolderData or frmEADFolders:

     If CurrentProject.AllForms("frmSeriesFolderList").IsLoaded Then

          lngFolderID = Forms!frmSeriesFolderList.lstFolder.Column(0)

     ElseIf CurrentProject.AllForms("frmEADFolders").IsLoaded Then

          lngFolderID = Forms!frmEADFolders.lstFolders.Column(0)

     End If

Spell Check MS Access Form Before Updating Table

I got tired of having to spell check after my students entered data into our publications database, so I created a function that spell checks the control values when before the record is saved.

This is the function I added to my BasicFunctions module:

Public Function fnSpellCheck(strTextBox As Control)

     Dim strSpell As String

     'Debug.Print strTextBox

     'This spell check code is from

     'http://www.access-programmers.co.uk/forums/showthread.php?t=130780

     'Posted by forum user "icezebra"

     'Get the value from the text box that is being spell checked.

     strSpell = strTextBox

     'Trying to spell check a null value or zero-length string will result in an error.

     If IsNull(Len(strSpell)) Or Len(strSpell) = 0 Then

     'A null value or zero-length string will skip the spellchecker.

     Else:

     'Define what is to be spell checked.

          With strTextBox

               .SetFocus

               .SelStart = 0

               .SelLength = Len(strSpell)

          End With

          DoCmd.SetWarnings False

          'Run the spellchecker.

          DoCmd.RunCommand acCmdSpelling

          DoCmd.SetWarnings True
          'Note that the line above will turn the warnings back on.

          'If you had the warnings off and want them off comment our or delete the SetWarnings.

     End If

End Function

To use this function I call it in the On Click event of the Save button, before the variables have been assigned values:

     'Running the spell check function on a null or zero-length control value will result in an error.

     If IsNull(me.txtTitle) Then

     'If the value in the control is zero-length the spell checker is skipped.

     Else:

     'Otherwise, the spell checker runs

          Call fnSpellCheck(Forms!frmPamAddition.txtTitle)
     End If
     'Pick up the value for the variable from the form.
     strTitle = me.txtTitle

     If IsNull(me.txtAuthor) Then

     Else:       
          Call fnSpellCheck(Forms!frmPamAddition.txtAuthor)
     End If
     strAuthor = me.txtAuthor

     If IsNull(me.txtOrganization) Then

     Else:         
         Call fnSpellCheck(Forms!frmPamAddition.txtOrganization)

     End If
     strOrganization = me.txtOrganization
     If IsNull(me.txtPublisher) Then

     Else:     
         Call fnSpellCheck(Forms!frmPamAddition.txtPublisher)

     End If
     strPublisher = me.txtPublisher
     If IsNull(me.txtPlaceOfPublication) Then

     Else:        
         Call fnSpellCheck(Forms!frmPamAddition.txtPlaceOfPublication)

     End If
     strPlaceOfPublication = me.txtPlaceOfPublication

     If IsNull(me.txtNote) Then

     Else:

         Call fnSpellCheck(Forms!frmPamAddition.txtNote)

     End If
     strNote = me.txtNote

By listing the controls one after another in this way they will all be spell checked in sequence before the record is saved. After all controls have been checked the rest of the Save button’s On Click event runs.

Changing List Box Results in MS Access

Our publications database currently has about 81,000 items listed in it, mostly union and company grey literature. The database opens to a search form which contains a list box. This used to load with all the items when opened, but once we got past 60,000 it was slow to load. Since there is no need for all items to load I looked for ways to limit the list box results. There is probably a way to load 100 random records, but I wasn’t able to figure one out so instead it started with the first 100 records and each time the database is opened or the form refreshed it opens the next 100 in sequence.

To control where the record set begins I added a new table to the database, tblLoad. There are only two fields and one record in the table. The ID field has a value of 1 and never changes, while the Start field contains the lower limit for the list box record set and is initially had a value of 1 but the value is increased by 99 whenever the search form opens or is refreshed.

Here is the VBA code for the search form’s On Open event:

Private Sub Form_Open(Cancel As Integer)

DoCmd.SetWarnings False

     Dim strSQL As String           'This is the SQL query that provides the record set for the list box.

     Dim lngStart As Long           'This establishes the lower limit of the record set.

     Dim lngEnd As Long            'This establishes the upper limit of the record set.

     Dim lngMax As Long           'This is the ID for the last record in tblPams.

     'Pick up the lower limit from tblLoad.

     lngStart = DLookup("[Start]", "tblLoad", "[ID] = 1")

     'Debug.Print "lngStart = " & lngStart

     'Create the upper limit by adding 99 to the lower limit.

     lngEnd = lngStart + 99

     'Debug.Print "lngEnd = " & lngEnd

     'Pick up the ID for the last record in tblPams.

     lngMax = DMax("[PamID]", "tblPams")

     'Debug.Print "lngMax = " & lngMax

     'To avoid generating a error when the SQL query is run

     'test to make sure that lngEnd is not greater than lngMax.

     If lngEnd > lngMax Then

     'If lngEnd is greater than lngMax reset lngEnd to lngMax.

          lngEnd = lngMax

     Else:

     'Otherwise, keep lngEnd the same.

          lngEnd = lngEnd

     End If

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams "

     strSQL = strSQL & "WHERE (tblPams.PamID BETWEEN " & lngStart & " AND " & lngEnd & ") "

     strSQL = strSQL & "ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     Me.lstPamList.RowSource = strSQL

     'If the last record in the table has been reached we need to reset the lower limit to 1.

     If lngEnd = lngMax Then

          DoCmd.RunSQL "Update tblLoad SET tblLoad.Start = 1 Where tblLoad.ID = 1"

     Else:

     'Otherwise, the current upper limit becomes the next lower limit.

          DoCmd.RunSQL "UPDATE tblLoad SET tblLoad.Start = " & lngEnd & " Where tblLoad.ID = 1"

     End If

DoCmd.SetWarnings True

End Sub

Next and Previous Buttons on a MS Access Form

Since I generally use unbound controls on my forms when a record is selected from a listbox the built-in navigation buttons do not work, the form only sees one record. In order to allow users to navigate back and forth through the list in the listbox I needed to create my own navigation buttons.

The key to creating Next Record and Previous Record buttons is to have a public variable to hold the list index value.

     Option Compare Database

     Public intIndex As Integer

This variable is initially populated in the form’s On Load event.

     intIndex = Forms!frmFolderItemList.lstItems.ListIndex

The On Click event for the Next Record button takes intIndex, adds 1 to it and moves the focus of the listbox to that List Index value. Then the On Load event is called.

     Private Sub cmdNext_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex + 1

          'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

         Call Form_Load

     End Sub

The On Click event for the Previous Record button subtracts 1.

     Private Sub cmdPrevious_Click()

         'Debug.Print "intIndex = " & intIndex

         intIndex = intIndex - 1

         'Debug.Print "intIndex_Next = " & intIndex

          Forms!frmFolderItemList.lstItems.ListIndex = intIndex

          Call Form_Load

     End Sub

This works very well, except when the user tries to click the Next Record button when the last record is already being displayed, or the Previous Record button when the first record is being displayed. To fix this the Previous Record button needs to be hidden when the listbox focus is on the first record, and the Next Record button needs to be hidden when the focus is on the last record. You can determine where the focus is in the list by comparing the List Index to the List Row Count.

Dimension a variable for the List Row Count in the On Load event for the form and populate it.

     Dim intRowCount As Integer

     intRowCount = Forms!frmFolderItemList.lstItems.ListCount

The variable for the List Index, intIndex, is a public variable that was populated earlier in the On Load event in order to be available for the On Click events of the Next Record and Previous Record buttons. The variable is simply reused here.

     'You cannot hide a control that has focus, so make sure the focus goes to some

     'control other than the Next Record or Previous Record buttons.

     Me.txtDescription.SetFocus

     If intRowCount > 1 Then

     'For a list with at least two items the Next button will be used

     'as long as you are not already at the last item.

          If intIndex = intRowCount - 2 Then

          'The List Index starts at 0 and the Row Count starts at 1 and counts the column heads.

          'If you are using the column heads you need to subtract 2 from the Row Count in order

          'to identify the last item in the list and hide the Next button.

          'If you are not using the column heads you need to subtract only 1.

               cmdNext.Visible = False

          Else:

               cmdNext.Visible = True

          End If

     Else:

     'When there is only one item in the list the Next button is not used.

          cmdNext.Visible = False

     End If

     If intRowCount > 1 Then

     'For a list with at least two items the Previous button will be used

     'as long as you are not already at the first item.

          If intIndex = 0 Then

          'The first item in the list will always have a List Index value of 0,

          'so if that is true hide the Previous button.

               cmdPrevious.Visible = False

          Else:

               cmdPrevious.Visible = True

          End If

     Else:

     'When there is only one item in the list the Previous button is not used.

          cmdPrevious.Visible = False

     End If

Validating a Year Field in MS Access

For my folder list database, which is where students enter basic folder level data for our finding aids, I only want a four-digit year or two four-digit years separated by a dash in the data field. The reason for this is that programming that runs against the folder list later relies on that format for the date. I have been having a problem with students remembering this and decided to force them to comply via programming in the database.

My first thought was to create a validation rule for the field in the table, which would be:

Like “####” Or Like “####-####”

But I wanted to stop the OnClick event and return the user to the date field on the form while being certain that nothing was saved to the table (I use unbound controls on my forms).

Adding the validation rule to the date text box would accomplish this, but I need to add “Is Null” to the rule or Access would not allow a folder with no date, of which we have many:

Like "####" Or Like "####-####" Or Is Null

The one thing I did not like about this was that if the date in the text box was invalid you could not use any of the other controls on the form until you fixed the date. I can see wanting to use the “Close” or “Clear” buttons. So what I opted to do was put the validation in the VBA for the OnClick event behind the Save button.

Here is the code I inserted right after the value from the Date textbox is picked up:

     If strDate Like "####" Then

          'The date is a four-digit year,

          'continue with the OnClick event.

     ElseIf strDate Like "####-####" Then

          'The date is two four-digit years separated by a dash,

          'continue with the OnClick event.

     ElseIf strDate Like "" Then

          'No date, which I consider okay,

          'continue with the Onclick event.

     Else:

          MsgBox ("Please enter a 4-digit year or two 4-digit years separated by a dash")

          'The date is invalid, set focus to the date field and stop the OnClick event.

          Me.txtDate.SetFocus

          Exit Sub

     End If

Getting a Proper Title Sort Order in Microsoft Access

In our publications database I have my student workers enter the titles as they appear, which means that they often have an initial article. Unlike MARC Access does not have a built-in way to tell it to ignore the first four, or whatever, spaces before sorting, so all the titles beginning with “The” are grouped together and all the titles beginning with “An” are grouped together, as so on. I wanted the sort order to ignore any initial articles, since that is the way most users except a list of titles to be constructed. I had thought of adding an “non-indexing spaces” field and using that to control the sort order. This idea was rejected because it meant another data element that the student workers would have to enter, and they would have to think about it not just transcribe what was on the item. Instead, I decided to deal with the initial articles after the fact.

What I did was create a function which strips the initial articles from the titles as the list is being created and the output from the function is used to sort the list even though the full title is displayed. I put this function in a module I call BasicFunctionsRDM, which is where I park any function I might use in other databases, if I need it I just import the entire module into another database. You can place this code in any module you like.

Public Function Article(strTitle As String)

     'Debug.Print "Before: " & strTitle

     'First make sure there are no double spaces after the initial article

     'replace any double spaces with a single space.

     strTitle = Replace(strTitle, Chr(32) & Chr(32), Chr(32))

     'Strip off any initial double quotes

     If Mid(strTitle, 1, 1) Like Chr(34) Then

          strTitle = Mid(strTitle, 2)

     End If

     'Strip the initial article from the title by using the mid-string function.

     'Always include, and count, the space after the article, otherwise you will

     'end up stripping the first two characters from a title beginning "Always".

     If Mid(strTitle, 1, 4) Like "The " Then

          strTitle = Mid(strTitle, 5)

     ElseIf Mid(strTitle, 1, 2) Like "A " Then

          strTitle = Mid(strTitle, 3)

     ElseIf Mid(strTitle, 1, 3) Like "An " Then

          strTitle = Mid(strTitle, 4)

     'Strip off any initial spaces.

     ElseIf Mid(strTitle, 1, 1) Like Chr(32) Then

          strTitle = Mid(strTitle, 2)

     'Strip off any single quotes at the beginning of the title.

     ElseIf Mid(strTitle, 1, 1) Like Chr(33) Then

          strTitle = Mid(strTitle, 2)

     'Strip off any exclamation points at the beginning of the title.

     ElseIf Mid(strTitle, 1, 1) Like Chr(39) Then

          strTitle = Mid(strTitle, 2)

     ElseIf Mid(strTitle, 1, 3) Like "El " Then

          strTitle = Mid(strTitle, 4)

     'Use a single character wildcard to catch both "Le " and "La ".

     ElseIf Mid(strTitle, 1, 3) Like "L? " Then

          strTitle = Mid(strTitle, 4)

     ElseIf Mid(strTitle, 1, 3) Like "De " Then

          strTitle = Mid(strTitle, 4)

     ElseIf Mid(strTitle, 1, 3) Like "Di " Then

          strTitle = Mid(strTitle, 4)

     ElseIf Mid(strTitle, 1, 3) Like "Il " Then

          strTitle = Mid(strTitle, 4)

     ElseIf Mid(strTitle, 1, 3) Like "Un " Then

          strTitle = Mid(strTitle, 4)

     ElseIf Mid(strTitle, 1, 2) Like "l'" Then

          strTitle = Mid(strTitle, 3)

     'Use a single character wildcard to catch both "Uno " and "Una ".

     ElseIf Mid(strTitle, 1, 4) Like "Un? " Then

          strTitle = Mid(strTitle, 5)

     'Use a single character wildcard to catch "Les ", "Las ", and "Los ".

     ElseIf Mid(strTitle, 1, 4) Like "L?s " Then

          strTitle = Mid(strTitle, 5)

     ElseIf Mid(strTitle, 1, 4) Like "Gli " Then

          strTitle = Mid(strTitle, 5)

     'Use a single character wildcard to catch "Der ", "Dem ", "Den ", and "Des ".

     ElseIf Mid(strTitle, 1, 4) Like "De? " Then

          strTitle = Mid(strTitle, 5)

     ElseIf Mid(strTitle, 1, 6) Like "Einen " Then

          strTitle = Mid(strTitle, 7)

     ElseIf Mid(strTitle, 1, 5) Like "Eine " Then

          strTitle = Mid(strTitle, 6)

     ElseIf Mid(strTitle, 1, 4) Like "Ein " Then

          strTitle = Mid(strTitle, 5)

     ElseIf Mid(strTitle, 1, 4) Like "Die " Then

          strTitle = Mid(strTitle, 5)

     ElseIf Mid(strTitle, 1, 4) Like "Das " Then

          strTitle = Mid(strTitle, 5)

     End If

     'Strip off any double quotes left at the beginning of the title.

     If Mid(strTitle, 1, 1) Like Chr(34) Then

          Article = Mid(strTitle, 2)

     Else:

          Article = strTitle

     End If

     'Debug.Print "After: " & Article

End Function

It’s not perfect. I’m sure I’ve missed some articles in other languages and probably some for the languages I did try to cover, but it is very easy to add another article and this pretty well covers the ones most common in our database.

To use this function simply include it the “Order By” clause of your SQL query. So if you have a text box, txtKeyword, and a search button, cmdSearch, the code in the “On Click” event would look something like this:

Private Sub cmdKeyword_Click()

On Error GoTo Err_cmdKeyword_Click

     Dim strKey As String       'Variable for the keyword.

     Dim strSQL As String      'Variable for the SQL query to populate the results list.

     strKey = Nz(Me!txtKeyword, "")

     strKey = Authority(strKey)

     'Debug.Print strKey

     'Format the keyword with wildcards and single quotes.

     strKey = "'*" & strKey & "*'"

     'Debug.Print "strKey = " & strKey

     'This SQL query pulls the pamphlet ID, title, and date for any pamphlet with

     'the keyword anywhere in the title. It then sorts it by title, ignoring initial articles.

     strSQL = "SELECT tblPams.PamID, tblPams.Title, tblPams.Date FROM tblPams "

     strSQL = strSQL & "WHERE tblPams.Title LIKE " & strKey & " "

     strSQL = strSQL & " ORDER BY Article(tblPams.Title), tblPams.Date;"

     'Debug.Print strSQL

     'Display the search results in a list box.

     Me.lstPamList.RowSource = strSQL

     Forms!frmStartPage.Refresh

     'Display the search query in a text box, txtString, on the form.

     Me!txtString = "Results for Search: Keyword Anywhere LIKE " & strKey & ""

     'Clear the keyword text box.

     Me!txtKeyword = Null

     Exit_cmdKeyword_Click:

          Exit Sub

Err_cmdKeyword_Click:

     MsgBox Err.Description

     Resume Exit_cmdKeyword_Click

End Sub

Micosoft Access Front-End Security. Part 3: Authentication at the individual control level.

On some of the forms in our collection management database I have buttons that I want everyone to be able to use, some I only want a few to be able to use, and a few I only want one or two people to be able to use. I control these buttons by using the computer’s user name.

First I added a field called “Level,” to the users table, tblUsers. I used four levels, with level 4 being the administrator level, with no restrictions on what that person can do. Level 1 is the most restricted, users at this level can do little more than search and read data.

In the On Click event of each button that is not a level 1 control I wrap the event procedure in the following If-Then-Else logic:

    Dim intUser As Integer                  ‘This variable holds the number for the user level.

    ‘Pick up the user level by matching the computer’s username with the user ID in tblUsers

    intUser = DLookup("[Level]", "tluUsers", "[UserID] = '" & Environ("username") & "'")

    ‘For a level 2 control you want to allow all level 2, 3, and 4 users, so the code allows

    ‘for users greater than level 1.

    ‘For a level 3 control you want to allow only level 3 and 4 users, so the code allows only

    ‘users greater than level 2.

    ‘A level 4 control has code to allow only users greater than level 3.         

    If intUser > 3 Then

        ‘This is where you would put the code for your event procedure.

    Else:

        MsgBox ("Sorry, you are not authorized to access this procedure.")

    End If

Bound text boxes can be protected by putting this If-Then-Else logic in the Got Focus procedure. If the user level is too low to allow updating the data then reset the focus to another control. In this example level 1 users are not allowed to edit the collection title, but are allowed to edit the donor name.

    If intUser < 2 Then

        MsgBox ("Sorry, you are not authorized to edit the Collection Title")

        Me.DonorsName.SetFocus

    End If

Next Week: Back-End Security.

Microsoft Access Front-End Security. Part 2: Requiring Authentication to Open the Database

If you need, or want, to limit who can open your database there are at least two ways of doing so. One is easy and passive, it looks at the user ID for the computer and either opens the database, or not depending on whether or not you have allowed that user access. The other takes a little more work and requires input from the user.

Passive Authentication:
You can use this method if you are working in an environment that requires users to log onto their computers. Each computer stores the user ID and that can be picked up in the VBA code. An AutoExec macro triggering a user defined function and a table containing the allowed user IDs will do the trick. First you must define your function. To do this you can either put it in an existing module, or create a new module for it, either way we’ll call this function “Authentication()”.

Public Function Authentication()

'This function runs from the AutoExec macro when the database is opened.

Dim strUser As String          'This is the user ID picked up from the computer.

Dim intUser As Integer         'This is derived from the table of allowed users tblUsers.

'Pick up the user ID with the Environ() function.

strUser = Environ("username")

'Assign either a 1 or 0 to intUser by comparing strUser with the IDs in tblUsers.

'the DCount() function will count the number of times strUser appears in tblUsers.

'Be sure to user the null function Nz() to account for the null value returned when

'strUser does not match any value in tblUsers and convert it to a 0.
'This way any user in tblUsers will have a value of 1 and anyone not in the table
'will have a value of 0.

intUser = Nz(DCount(“[User]”, “tblUsers”, “[User] =” & strUser & “”), 0)

'If intUser = 0 the user ID is not in tblUsers and not allowed to open the database.

'In that case tell the user they are not authorized and close the application.

'Otherwise allow the database to open.

      If intUser = 0 then

          If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

              DoCmd.Quit acQuitSaveNone

          Else:

              'Correct log in opens whatever form you want your users to start with.
              DoCmd.OpenForm "frmStartPage"

          End If

     End If

End Function

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter RunCode and under “Arguments” enter Authentication(). Save the macro. Now whenever the database is opened the function Authentication() will run before anything else happens.


Interactive Authentication:
This requires the user to enter a password, which can be either unique to each user or the same one for everyone. In either case you will want to mask the password as it is entered, so you will need to use a form instead of a function. For this type of authentication your users table, tblUsers, will have two columns. One column for the user name or ID and one for the password, can be the same for everyone. The form will need three text boxes. One text box for the username, one for the password, and a third one, set not to display, for the counter. There also needs to be a “Log In” button. The OnClick event for this button will run the following code:

Private Sub cmdLogIn_Click()

Dim strUser As String                      'This is the user name entered by the user.

Dim strPassword As String              'This is the password entered by the user.

Dim strAuthenticate As String           'This is the password from tblUsers.

Dim intCounter As Integer                'This keeps track of number of log in attempts.

intCounter = Nz(Me.txtCounter, 0)

'Me.txtCounter refers to a control on the Log In with its “visible” property set to “No.”

strUser = Me.txtUser

strPassword = Me.txtPassword

strAuthenticate = Nz(DLookup("[Password]", "tblUsers", "[User] = '" & strUser & "'"), “None”)

If strAuthenticate = “None” Then

       'Is someone not included in the users table attempts to log in close the database.

       If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

       DoCmd.Quit acQuitSaveNone

       End If

Else:

       If strPassword = strAuthenticate Then

             'Correct log in opens whatever form you want your users to start with.

             DoCmd.OpenForm "frmStartPage"

             'Then close the Log In form.

             DoCmd.Close acForm, "frmLogIn"

       Else:

             Me.txtUser = strUser

             Me.txtPassword = ""

             Me.txtPassword.SetFocus

             intCounter = intCounter + 1

            Me.txtCounter = intCounter

            'After three incorrect log in attempts close the database.

            If intCounter = 3 Then

                 If MsgBox("Sorry, you are not authorized to use this database.") = vbOK Then

                      DoCmd.Quit acQuitSaveNone

                End If

            Else:

                MsgBox "Incorrect Log In. Please try Again"

           End If

      End If

End If

DoCmd.Close acForm, "frmLogIn"

End Sub

To mask the password as it is typed you will need an On Load event for the form:

Private Sub Form_Load()

       Me.txtUser.InputMask = "Password"

End Sub

If you would like the password to be case sensitive replace
If strPassword = strAuthenticate Then
With
If CBool(InStrB(strPassword, strAuthenticate)) Then

To run this code when the database is first opened create a new macro and name it AutoExec. Under “Action” enter OpenForm and under “Arguments” enter frmLogIn. Save the macro. Now whenever the database is opened the log in form will open before anything else happens.

Of course, a user could just close or minimize the log in form, but if you had locked down the front end as described in the last post they would not be able to open any forms or interact with any of the tables.

Next week: Part 3: Authentication at the individual control level.

Smart Fields on a Microsoft Access Form

I set up a Microsoft Access database for students to create folder lists for collections. The only data I needed was collection number, box, folder, folder title, date, and notes. The data entry form only needed those six fields. I used unbound text boxes for the fields and in the on-click in the VBA behind the form the values of the text boxes were picked up as variables. An SQL statement then wrote those values to the table. To minimize the amount of data entry the students had to do I used sticky and smart fields on the form.


The folder number is a smart field, and the box number is sometimes a smart field. As noted above when the “Next Folder in Box” button is clicked the folder number text box is not reset to null or zero-length string It is instead set to the folder number variable value plus 1. However if the “Next Box” button is clicked the folder number text box is set to 1 and the box number text box is set to the box number variable plus 1.

What this all means is that if you have

Collection: 5544
Box: 12
Folder: 14

And click “Next Folder in Box” the form will show

Collection: 5544
Box: 12
Folder: 15

However, if you click “Next Box” the form will show

Collection: 5544
Box: 13
Folder: 1

So, once those three fields are filled in at the beginning of a data entry session they never need to be touched again for the entire session, no matter how many folders or boxes are processed.

Here is the VBA code I used:

Dim intBox as integer             ‘Variable to hold the box number.

Dim intFolder as integer          ‘Variable to hold the folder number.

intBox = Nz(Me!txtBox, 0)             ‘Pick up the box number from the form,

                                                   ‘convert to a zero if null.

intFolder = Nz(Me!txtFolder, 0)       ‘Pick up the folder number from the form,

                                                   ‘convert to a zero if null.

‘Code to write the form data to a table would go here.

‘When all tasks are done, reset the form.

‘If “Next Folder in Box” was clicked, add one to the folder number.

intFolder = intFolder + 1

‘If “Next Box” was clicked, add one to the box number

‘and reset the folder number to 1.

intBox = intBox + 1

intFolder = 1

Me!txtBox = intBox              ‘Set the box number control’s value on the

                                           ‘form to intBox.

Me!txtFolder = intFolder        ‘Set the folder number control’s value on the

                                           ‘form to intFolder.

Sticky Fields on a Microsoft Access Form

I set up a Microsoft Access database for students to create folder lists for collections. The only data I needed was collection number, box, folder, folder title, date, and notes. The data entry form only needed those six fields. I used unbound text boxes for the fields and in the on-click in the VBA behind the form the values of the text boxes were picked up as variables. An SQL statement then wrote those values to the table. To minimize the amount of data entry the students had to do I used sticky and smart fields on the form.

The collection number is a sticky field, and the box number is sometimes a sticky field. After the data is entered in the form if the “Next Folder in Box” button is clicked the value of each field is picked up as a variable and written to the table. Then the text boxes on the form for collection number and box number are set to the value picked up as the variable for the field. The other text boxes, except folder number, are set to either null or zero-length string.

Here is the VBA code I used:

Dim strColl as string               ‘Variable to hold the collection number.

strColl = Nz(Me!txtColl, "")   ‘Pick up the collection number from the form, 
                                          'convert to zero length string if null.

‘Code to write the form data to a table would go here.

‘When all tasks are done, reset the form.

Me!txtColl = strColl               ‘Set the collection number control’s value on the form 
                                           'to strColl.