With Access 2007, Microsoft introduced Layouts. You apply a layout under the Arrange tab, in the Table group. Layouts in Access are an emulation of HTML tables, which may explain why the Access team refers to them as Layouts but put them in a ribbon group called Table. And let's not even discuss the confusion between a Layout and Layout View.
A layout provides a grouping method for controls on a form or report. The two default layout types are Tabular (labels in form header, controls in detail), and Stacked (labels to the left, controls to the right, all in detail). Regardless of which layout you choose, you can edit it afterward to work any way you please.
If you are building an Access Web app to post to a Sharepoint server, your forms must use a layout, according to Microsoft's Access 2010 Help page for controls and layouts (http://office.microsoft.com/en-us/access-help/introduction-to-controls-HA010352440.aspx#BMunderstandcontrollayouts). This makes sense, since the layouts create HTML tables that Sharepoint server can use in its data-driven model.
When it comes to a desktop database, however, Microsoft gives no compelling reason for using the layouts, noting only that "layouts are optional if you are building a desktop database that will only be opened by using Access" and suggesting that they are useful in creating a uniform look for your form. Microsoft completely fails to note the power of a layout coupled with the Anchor properties in a sizable form.
Anchors are one of the more recent innovations in Access. Until Access 2007, a control's position was set by the Top and Left measurements and remained static throughout the life of the form, unless changed in code. With the introduction of Anchors, the developer could choose how a control moves when a form is resized. Controls are anchored both vertically and horizontally, so a Close button at the bottom-right corner of a form can remain always in the bottom-right corner, while the header stays in its customary position at the top left. Anchors can also make a control grow and shrink with the form, by setting the Anchor properties to Both. With the anchor at Both, the control holds its initial Top and Left settings, but increases or decreases in size as the form is resized, holding the distance between the control and the bottom and/or right sides of the form static.
This is great, but then when the developer sets an Anchor property to Both for two controls on the same level, there's a problem; if the user stretches the form, the controls overlap each other as each holds to both its constraints. The way to solve this problem? Layouts!
When you put your controls into a layout, they are constrained within the table outlined by the layout. This means that, no matter how many controls you have lined up top-to-bottom and left-to-right, you can select the entire layout and set the Anchor properties to Both without any overlap. As the controls shrink and grow within the layout, the layout shrinks and grows with them, justifying each control to the table. Et Voila! Thanks to your layout, you now have a form that resizes at the user's demand, without any complex coding on your part.
Monday, April 14, 2014
Friday, November 8, 2013
Use a Tab Control to Simulate a Subform
Subforms are wonderful for keeping a lot of related data together. They allow you to duplicate functionality across multiple forms, and also to pull data from different tables without the trouble of joining them in queries. Another function, which may seem incidental at first, is the ability to show, hide, enable, and disable a group of controls simply by setting the value on the subform control.
Once you become comfortable with using subforms, you may be tempted to create a subform just for the "incidental" purpose of grouping the controls. This practice, however, will quickly crowd your object explorer and clog up your system memory. Rather than using a subform, repurpose a tab control. Insert a tab control into your form and give it only one page. Then, set the "Style" property to "None." Now you have a page that behaves like a single-use subform: you can fill it with controls that you can move or hide at will, with a single command.
Once you become comfortable with using subforms, you may be tempted to create a subform just for the "incidental" purpose of grouping the controls. This practice, however, will quickly crowd your object explorer and clog up your system memory. Rather than using a subform, repurpose a tab control. Insert a tab control into your form and give it only one page. Then, set the "Style" property to "None." Now you have a page that behaves like a single-use subform: you can fill it with controls that you can move or hide at will, with a single command.
Monday, October 21, 2013
Library Databases - The What and Why
Do a Google search for "MS Access Library Database," and what you're going to see is a lot of suggestions for writing databases that manage libraries, one of the initial databases designed by beginning Access developers. This article is not about coding a library management database, it is about MS Access's intrinsic ability to use another MS Access database as a code library.
In the VBE, go to Tools -> References, click Browse, then hit the drop-down under "Files of type:" Along with .dll's and ActiveX controls, you'll discover that you can link any of several MS Access file types as a Reference. Now, normally I'm against references, but in this case I make an exception.
Say you have 10 interconnected MS Access front-ends running as part of a modular system. You probably want them to have a similar look and feel, with the same basic functionality. This means that a lot of the code procedures used in each database are going to be the same. Now, you could import and export modules across each database, but this has the potential to introduce serious version issues. Instead, create a code library database.
The library database is a convenient place to put all of the code that you will share across the databases you build - whether it be a password-masking procedure, the hash code you use for encrypting the passwords, or something as simple as a custom function for putting a Select Case statement onto a single line (like an If-Then to an IIf). Whenever you have code that you will want to reuse, put it into the library database. Then, instead of hunting multiple db modules for the right function to import, simply attach the library database as a Reference and - voila! - instant access to all of your custom classes and procedures.
A word of caution, however - library databases are very sensitive to environment changes. If you rename a library database or move it to a different folder, you will break each reference. If your database is accessed by multiple users, follow best practice and have them run local copies, with the library database in the same folder on the users' systems as the front ends, in order to mitigate that issue.
And a further note - even though some of Microsoft's documentation appears to hint that you can open objects, such as forms and reports, in a library database using code in the library database, the fact is that you can't. The application environment simply doesn't allow for it. If you do want to create a forms or reports library that is accessible from all of your other databases, you can handle it by using an Access automation object.
In the VBE, go to Tools -> References, click Browse, then hit the drop-down under "Files of type:" Along with .dll's and ActiveX controls, you'll discover that you can link any of several MS Access file types as a Reference. Now, normally I'm against references, but in this case I make an exception.
Say you have 10 interconnected MS Access front-ends running as part of a modular system. You probably want them to have a similar look and feel, with the same basic functionality. This means that a lot of the code procedures used in each database are going to be the same. Now, you could import and export modules across each database, but this has the potential to introduce serious version issues. Instead, create a code library database.
The library database is a convenient place to put all of the code that you will share across the databases you build - whether it be a password-masking procedure, the hash code you use for encrypting the passwords, or something as simple as a custom function for putting a Select Case statement onto a single line (like an If-Then to an IIf). Whenever you have code that you will want to reuse, put it into the library database. Then, instead of hunting multiple db modules for the right function to import, simply attach the library database as a Reference and - voila! - instant access to all of your custom classes and procedures.
A word of caution, however - library databases are very sensitive to environment changes. If you rename a library database or move it to a different folder, you will break each reference. If your database is accessed by multiple users, follow best practice and have them run local copies, with the library database in the same folder on the users' systems as the front ends, in order to mitigate that issue.
And a further note - even though some of Microsoft's documentation appears to hint that you can open objects, such as forms and reports, in a library database using code in the library database, the fact is that you can't. The application environment simply doesn't allow for it. If you do want to create a forms or reports library that is accessible from all of your other databases, you can handle it by using an Access automation object.
Subscribe to:
Posts (Atom)