Hook'D on Access


A few samples

Miscellaneous Samples

. This Access 2000 file has several samples.
  • Continuous form with varying background colors but doesn't use conditional formatting.
  • Data Menu interface using a tall vertical command button much like multiple letter option buttons. Also has find as you type functionality.
  • Query By Form applet which is an older version of this handy utility.
  • Calendar demo for using calendar for date entry.
  • Line graph report that doesn't use a chart control.
  • Report that plots height vs weight without using a chart control.
  • Bar graph report that allows varying width bars. Again, doesn't use a chart control.
  • Form that allows the user to drag a control and drop it elsewhere on the form.
  • Calendar format report. A more complete sample is listed below.
  • Form with a rectangle the user can move and size with very limited utility ;-)

Calendar Reports sample

. This Access 2000 sample shows how to create several different calendar style reports. There is a doctor/patient schedule that displays a week-at-a-glance with dates across the top and times along the left (sample). Each appointment is "drawn" with the proper size and position. A similar report places multiple doctors across the top for a single day schedule.

A standard monthly calendar is also included (sample). This report uses copies of the same subreport for each day. A gantt type report (sample) is included which displays time bars across longer periods of time.

Crosstab Report with dynamic column headings sample

. This Access ACCDB demonstrates how to create a report from a crosstab query where the column headings will not be known until run-time. The report will accomodate any number of columns with extra columns "wrapping" under the first set of columns. This type of report could be used to display salesperson orders and dates as row headings and customers as column headings. The method I developed will dynamically create columns to match each different salesperson. If a salesperson has 2 or 20, the report will accommodate them all. This method runs fairly quickly and requires only about 20 lines of code.

Annual Calendar Report sample

. This is a simple annual calendar report that displays some useful techniques.

Annual Calendar Report sample showing days off

. This is the same annual calendar report with X'd days off.

Query By Form

The DH QBF is a complete query by form applet that can be easily integrated into any existing Access application. Typically, the functionality provided by DH QBF can replace many "canned" reports. The developer imports several forms, tables, a query, and a report from the DH_QBF.mdb, creates some master queries, and deploys.

The developer creates one or more master queries that join tables, alias field names, create calculated columns, etc. The users can then select a master query (datasource) from a drop-down and then select up to 30 fields from the master query. Users can define sorting and criteria as well as grouping and totaling. All of this "design" information is stored in two tables for re-use.

The results of the queries are displayed in a datasheet subform contained in a main form. The main form has options to send/export the records to print, Word table, Word merge, Excel, HTML, CSV, Merge to Report, or a graph. Most formats allow he user to automatically open the target application. The Word merge process will open a new Word document and link to the merge fields.

Multi-Select Listbox Generic Function

This sample demonstrates how a multiselect listbox can be used as the criteria in a query. A generic function is used to return a True or False depending on if a field value is selected in a listbox on a form.

There are two list boxes in the demo: one is bound to a numeric field and the other a text field.

While this solution works great for smaller tables, it may not be the best solution for tables with 1000s of records.

Report with chart using 2 link fields

This report shows how to create a chart on a report that links to 2 fields in the report's record source without displaying either of the fields on the chart. Both fields must be in the record/row source of the object.

The report is grouped on Company and then Mth. The chart displays sales for the company and month by category. Neither the Company or Month values display in the chart but are used in the Link Master/Link Child properties.

Report that displays two columns using code rather than page setup

This report shows how to use some code in a report to build multiple columns without using Page Setup. This solution may offer greater flexibility of formatting than the traditional 2-column report.

| Contact | ©2007 Duane Hookom