How To's and Articles

	User Level Security Not Behaving? How to Fix It
	Leading Dot List
	Combo Box List Shows Description Only but Text Area Shows Key
	Code That Macro
	SaveAsText/LoadFromText
	Build String From MultiSelect ListBox
	Controlling The Controls On A Custom Toolbar

User Level Security Not Behaving? How to Fix It

Newbee:
But something went dreadfully wrong. I followed the Security wizard, took away "Admin's" rights and gave that user a password. I took away all the "users" user group rights. I set the permissions for each object type for each group. But for some reason, now every time I open any Access database I have to log in.

Here is what might have happened:
You used the default system workgroup file on your PC when you set up security. Because all installations of Access create a default system.mdw, everyone will have a work group file different from yours even though the files have the same name. Theirs will not have a password for "Admin", and Admin will still have full permissions to the database. as will the Users default group.

How to fix it:

Go back into the database. Click on Tools>Security>User and Group Accounts. Select Admin. Clear his password. Next, put him back in the Admins group. Now close the database and re-open it. You won't be prompted for a log-in now because Admin is the default user. You might as well go ahead and remove all the custom user groups and user accounts you set up. The only groups you want to retain are Admins and Users. The only account should be Admin who is a member of Admins and Users groups.

Now your system.mdw file is back to normal which is what you want. Now you can do it the right way. Find where it is and write down the path. You will need to find it again to re-join it later. Mine is in C:\Documents and Settings\BMosca\Application Data\Microsoft\Access. In case you forgot, it's named System.MDW

Open the Workgroup Administrator. If you are using Access 97, there should be a shortcut on your Start menu in the Office 97 menu group. If you are using Access 2000, you'll have to hunt for it. Look in C:\Program Files\Microsoft Office\Office\1033 for a file named WRKGRADM.EXE. Double-click it to open it. If you are using Access 2003 you got lucky again because you can find it in Access. Open Access 2003 and click on Tools>Security>Workgroup Administrator.

Create a new workgroup security file (.MDW). I usually give it the same name as the database because I deal with hundreds of different databases all with their own MDW file. And it should be placed on the server in a shared folder that your users have Create/Edit permissions.

Make sure you write down the user name, file name, and workgroupID. You will need that information should you have to recreate the MDW file. The last step should bring you back to the Create/Join box of the workgroup administrator wizard. Select Join.

Now open Access and start up the security wizard again. When it asks you if you want to create a new one or modify the current one, select the current one. Next, choose "I want to create a shortcut to open my database". That is the important part. That is what will prevent that nagging login for every database you open whether secured of not.

Now go through the steps of securing the database. I won't go into that here because Microsoft's Security FAQs article at http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Faccess%2Fcontent%2Fsecfaq.asp does that for me. Read it before starting over. You might even want to download a copy of the article from http://support.microsoft.com/kb/207793/en-us  and keep it handy.

Now your database is secured and the users have to be in your workgroup security file to use it. But you are not quite finished. Open the workgroup administrator again and browse to the default System.mdw. Join it. Close the workgroup administrator. Create a shortcut to your database. It should have a Target that starts with Access and includes the /wrkgrp switch. It will look something like this (all one line and quotes around paths that contain spaces):

"C:\Program Files\Microsoft Office\Office11\msaccess.exe"  /wrkgrp \\MyServerName\MyFolder\MyWrkgrpFileName.MDW

The Start In line is your Office folder:

"C:\Program Files\Microsoft Office\Office11"

The /wrkgrp switch forces the database user to temporarily join the workgroup. When he closes Access, he will be back in the default System workgroup file and won't have to log in to unsecured databases.

-top- 

I occasionally get a request to print out a list of phone numbers from a
database. The format is:

Company Contact...............................................(123)555-1234
Company2 Contact2............................................(555)555-2222

The company name and contact are left-justified; the phone number is right-justified. Since the company and contact strings vary in length there is a varying number of dots between them and the phone number. You could use a few lines of code to determine the number of dots needed by getting the length of the 2 other strings, but you really don't have to do it that way.

Place a text box in the detail section near the right margin. That's going to be our phone number. Then place a text box that stretches from the left margin to the left edge of the phone number box. Put this as the ControlSource:
=[CompanyName] & " " & [Contact] & String(120,".")

The String function will put 120 dots concatenated to the rest of the string (Company and contact). Set the can grow and can shrink properties for this box to no. All the dots will print, right up to the right edge of the text box. Any extra dots will not show since the box cannot expand.

Play around with the number of dots you need. Depending on the length of the line, you might not need as many or perhaps more.

-top-

 -top-

If you are new to Access development, you probably rely heavily on macros. While they are easy to create and run, they are also had no way to trap errors until Access 2007 came along.

Up to and including Access 2003, if a macro runs up against an error, the best it can do is come to a stop and show a dialog box to the user. At that point, the user has to decide if he/she should skip the step or stop the rest of the macro from running. How is the user supposed to know what to do?

The best method is to use VBA which gives you a way to trap errors and handle them for the user.

You can turn a macro into code rather painlessly even if you don't know a thing about VBA. Select the macro in the database window. Click on Tools>Macro>Convert Macros to Visual Basic (for Acc 2007, select the macro, click the Database Tools tab on the Ribbon, then Convert Macros to Visual Basic. You will have the options to include error handling and comments. I suggest you opt for both. After all, we're doing this to handle errors.

A new module will be created named something like "Converted Macro-". The code window will automatically open when the conversion is finished, and you will see the code. If not, look for the module in the database window or Navigation Pane and double-click it to view the code. The new function will have the same name as the macro. At this point you should rename either the macro or the function. It's bad practice to give 2 or more objects the same name.

To run the code, create a command button on a form and set its On Click property to the name of the new function. It should look something like this:

=Delete_All_Data()

Another method is to type [Event Procedure] on the On Click property line and then click the little builder button to the right of the line.  That will take you to the button's Click event. The Sub will look like this:

Private Sub Command276_Click()

End Sub

Type a call to the function between those two lines of code so it looks like this:

Private Sub Command276_Click()
    Call Delete_All_Data

End Sub

Granted, the code created during the conversion uses old syntax, and there might be a better way to write it, but it gives you an idea as to how to write VBA. With a bit of reading and asking questions in Access groups like mine, you will soon be able to write your own "macro code". 

SaveAsText/LoadFromText

There is a couple of undocumented methods belonging to the Application object that can come in very handy: SaveAsText and LoadFromText. Both of these are hidden methods so they do not show up in the IntelliSense context menu unless you unhide them.

To unhide these methods, open the object browser in the code editor by pressing F2. Right-click anywhere in the browser and select Show Hidden Members. That will expose them not only in the browser but in the IntelliSense menu as well.

Now close the browser and open the Immediate Window by pressing Ctrl+G or clicking the icon in the toolbar.

The arguments for Application.SaveAsText() are:

<the object type>

<the object name>

<the file path and name to save as>

So if I had a form named frmCustomers I could save it to my folder Databases on my C: drive in the Databases folder like this:

Application.SaveAsText acForm,"frmCustomers","C:\Databases\frmCustomers"

To load it into any database, all you need to do is open the database and use the LoadFromText method like this:

Application.LoadFromText acForm,"frmCustomers","C:\Databases\frmCustomers"

Note the parameters are the same, except this time the file is the existing file.

"So what's the big deal?" you ask. I'm glad you asked. When you save a form as text, it instantly becomes very portable. You can load it into another database or save it on a USB stick or disk (it will be very small as compared to saving it in an Access database). And there is one more feature that makes these methods very useful. As you develop a form or report, the code page gets more and more fragmented as you edit code, add or delete controls, rename controls, etc. By the time you are finished, that code page is like Swiss cheese and just begging to corrupt. SaveAsText effectively removes the holes. All you need to do is save the object as text, delete the original object, compact your database and then use LoadFromText to bring it back into the database.

That method for cleaning a semi-corrupt object is much safer than decompiling the entire database (another undocumented practice that can just as likely do harm as good). And guess what! There is a very handy add-in made by Dimitri Furman that makes all this even easier. All you do is open the add-in within the database and select the object(s) you want to save as text. Then click a button and it's done. You can just as easily load the objects with the same add-in. Go to my Misc Downloads and Links page and look for Objects As Text to find a direct link to the file.

-top-

Built A String From A MultiSelect ListBox

Using a Listbox to filter a form or report or even a recordset for data manipulation is pretty straight-forward. Same as using a combo box, right? Yes, unless you want to use the MultiSelect property of the ListBox to filter for more than one value. To do that, you will need to build a string from the selected items in the list.

Here is a simple example of using a MultiSelect ListBox to filter a form. I'll use Northwind's Employees form. Any version of Northwind will work, including the Access 2007 accdb file.

Place a listbox in the Form Header section. Name the ListBox lstEmployees. Set the MultiSelect property to Extended. You could use Simple, but Extended has become the most common type in Windows programs. It lets you use the Ctrl key to select multiple items not in sequence, plus you can use the Shift key to select a series of items. You can even use a combination by selecting a series and then non-sequential items. Truly a wonderful feature! (Boy! I gotta get out more.)

Next put a command button named cmdFilter in the Header right next to the ListBox. This is the button we will use to run the code to filter the form.

Now we're ready to write the code. Actually, I'll write it. You'll just copy and paste my work. If you don't have a standard module in your database add one now. Call it whatever you want as the name is unimportant..to me anyway.The function can be pasted into your standard module or your form's class module as a private function. I prefer using a public function so all forms can use it. You never know when you might have another form that will need this functionality.

This is the function that will do all the work:

Option Compare Database
Option Explicit

Public Function BuildList(ctrl As Control, _
        intColNum As Integer, _
        strFieldName As String, _
        strDelimiter As String, _
        Optional bolNoFieldName As Boolean) As String
'Purpose  : Build an "IN" list for SQL Where clause.
'           Delimiter for text or dates.
'           Optional boolean used to determine if IN operator is needed.
'DateTime : 5/15/2008 15:06
'Author   : Bill Mosca
    Dim strList As String
    Dim varItem As Variant

    If ctrl.ItemsSelected.Count = 0 Then
        MsgBox "Please select at least one item.", vbInformation, _
                "No Items Selected"
        Exit Function
    End If

    For Each varItem In ctrl.ItemsSelected
        strList = strList & strDelimiter & ctrl.Column0, varItem) _

                & strDelimiter & ","
    Next

    'Trim last comma.
    If right(strList, 1) = "," Then
        strList = Left(strList, Len(strList) - 1)
    End If

    If bolNoFieldName = True Then
        BuildList = strList
    Else: BuildList = strFieldName & " IN(" & strList & ")"
    End If

End Function

Now we need the code to apply the filter built by our function. The function takes 4 arguments with an optional 5th argument:

1. ctrl As Control - This is our listbox
2. intColNum As Integer - The column we want to use numbered from 0
3. strFieldName As String - The field name. If you don't need a field name just pass an empty string("").
4. strDelimiter As String - The delimiter needed. Text would use double quote("), Dates a octothorpe (#) These need to be surrounded by double-quotes. For text, I prefer using Chr(34) so I don't have to surround the double-quote with double-quotes...just to hard to read.
5. [Optional] bolNoFieldName As Boolean

The Optional parameter in the function lets us include the string " IN(" . In this case we want to include that string so we set the argument to to False or leave it out since it will default to false. If we just wanted a comma-delimited string we would set the Boolean to True.

In our button's Click event, the variable strFilter is assigned to the return from BuildList. That variable is assigned to the form's filter and the filter is turned on.

Private Sub cmdFilter_Click()
    Dim strFilter As String

    strFilter = BuildListMe.lstEmployees, 0, "EmployeeID", "", False)
    Me.Filter = strFilter
    Me.FilterOn = True

End Sub

I've left out all error trapping in this example. I'll leave that drudgery to you. You would probably want to trap an empty string if bolNoFieldName was set to false.

-top-

Controlling The Controls On A Custom Toolbar

You can create a custom toolbar in Access 2003 or earlier for your users that has all the buttons on it but still control the users access to objects based on their ULS* user group. This adds another layer of security assuming you have taken away the user's access to the database window.

I have a table I use to determine which buttons to show depending on the current user's user group. It has 3 fields: MenuPK (autonumber - Primary Key), UserGrp(text) and ItemNum(long). The ItemNumber would be the index number of the item on the toolbar. The index starts with 1.

We'll do all this in the Startup form's Load event. First, we'll get the UserGroup for the current user. I always assign a usr to only one group. By planning your groups in a heirachy, you should never have to assign a user to more than one group. Then we will call the function that does the work. It determines which buttons the user should see in the toolbar.

The Load event:

Private Sub Form_Load()
    Dim strUserGrp As String

    strUserGrp = GetUserGroup
    Call ViewTbarBtns(strUserGrp, "tbrNavigate")

End Sub

The GetUserGroup function uses the workspace objects.

Public Function GetUserGroup()
    Dim CurrUser As String
    Dim UsrGrp As dao.Group
    Dim wks As dao.Workspace

    Set wks = DBEngine.Workspaces(0)

    For Each UsrGrp In wks.Users(CurrentUser).Groups
        If UsrGrp.Name <> "Users" Then
            GetUserGroup = UsrGrp.Name
            Exit Function
        End If

    Next

    Set wks = Nothing

End Function

Here is the function that shows or hides the buttons.

Option Compare Database
Option Explicit

Public Function ViewTbarBtns(strUserGroup As String, strToolbar As String)
'Purpose  : Hide or show button on tbrPrintRptSnp toolbar.
'DateTime : 2/4/2005 14:21
'Author   : Bill Mosca
    Dim tbr As Object
    Dim btn As Object
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strSQL As String
    Dim idx As Integer

    Set tbr = Application.CommandBars("tbrPrintRpt")
    Set db = CurrentDb

    'Get recordset for this group.
    strSQL = "SELECT ItemNum FROM tblMenu " _
        & "WHERE UserGrp = '" & strUserGroup & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

    For idx = 1 To tbr.Controls.Count
        rs.FindFirst "ItemNum=" & idx
        ' If NoMatch is false it means the itemnum is assigned
        ' to the user group so show it. otherwise hide it.
        tbr.Controls(idx).Visible = Not rs.NoMatch
    Next

    Set rs = Nothing
    Set db = Nothing
    Set tbr = Nothing

End Function

And of course you will want a function to set all buttons visible as you do your developing.

Public Function TbarViewAll()
'Purpose  : Reset Toolbar so all items show.
'DateTime : 2/4/2005 14:33
'Author   : Bill Mosca
    Dim tbr
    Dim idx

    Set tbr = Application.CommandBars("tbrPrintRptSnp")

    For idx = 1 To tbr.Controls.Count
        tbr.Controls(idx).Visible = True
    Next

End Function

 -top-