| HOME MS Access Professionals File Archive |
A.D. is in a class of his own -- profound, perceptive, personable, and prolific. Links to all 185 of AD's samples from RogersAccessLibrary are on this very long page. AD also posted a few samples directly to MS Access Professionals.
Thanks to
185 Zip Files
A (7) B (3) C (12) D (2) E (11) F (67) H (1) I (4) L (3) M (7) N (1) P (3) Q (17) R (36) S (7) T (6)
| Name and Description | Category | Size | Version | Posted+SourceLink |
|---|---|---|---|---|
| A | GoTo Top | |||
| AddDataAtTop.mdb | Form | 126K | Access 2000 | 10 Jul 2008 at 1:58am |
|
This database demonstrates three styles of data entry at top of a subform - (a) Continuous Form Display (Using Command Button) - Data is entered via unbound text boxes in subform header, using command button on the parent form. Freshly added Record is highlighted in green, while other recent records added in current session are shown in orange. (b) Datasheet Display (Using Command Button) - On clicking the command button on parent form, fresh blank record (highlighted in green) is presented at the top of subform for enabling data entry. Recent records added in current session are shown in orange. (c) Datasheet Display (Auto - No command button) - On opening the main form, user is presented with fresh blank record (highlighted in green) at the top of subform. As soon as data entry commences in this record, its back color changes to yellow, while another fresh blank record (in green) is offered at top. All other recent records added in current session are shown in orange.Download Access 2000: uploads/37/AddDataAtTop.zip |
||||
| AnchorRowInDataSheet | Form | 58K | Access XP & 2K | 10 Jul 2008 at 1:56am |
|
This sample db demonstrates highlighting of desired row in a datasheet. This can be useful in a large datasheet, where, while editing a particular record, the user is required to move to some other record (for reference or copying etc) and then move back to the original record for continuing the editing action. On double clicking any field in the datasheet, the corresponding row gets highlighted. Any other row that might have been highlighted earlier, reverts to normal state (only one row remains highlighted at any given stage). Once a row is highlighted, it remains highlighted till some other row is double clicked. (If existing highlight on a row is to be removed without highlighting another one, simply double click it again). Versions - Access XP & 2K (In Access 2000 File Format) References - DAO 3.6Download Access XP & 2K: uploads/37/AnchorRowInDataSheet.zip |
||||
| AppendAsPerFieldPos.mdb | Form | 117K | Access XP & 2K | 10 Jul 2008 at 1:55am |
|
This sample db demonstrates append action from one table to another, using field positions (instead of field names). The technique can be handy for a set of tables having large number of fields, where data types across the tables are similar but field names are not. The main subroutine accepts five arguments (name of source table, name of target table, desired start column in source table, desired start column in target table and number of columns to be covered in the range). Last three arguments are optional. If omitted, all columns (except the one for auto- number field) get appended.Download Access XP & 2K: uploads/37/AppendAsPerFieldPos.zip |
||||
| AppendSequence.mdb | QTblFunc | 297K | Access 2K & XP | 10 Jul 2008 at 1:54am |
|
This sample demonstrates appending of sequential values in continuation to those existing in the target table (Reference ADO 2.1). Following situations are covered (separately for batch mode as well as data entry mode- (a) Straight primary sequence of numbers. (b) Parallel sequences of strings - datewise. (c) Parallel sequences of strings - yearwise. (d) Parallel sequences of strings - yearmonthwise In addition, data entry mode is demonstrated for (i) Alphanumeric sequential values (multiple parallel series) (ii) Linked sub-sequential values (iii Formatted sequential numbers In Batch Mode, the above objectives are achieved through append queries (marked by faster execution as compared to stepping through recordset loops). Caution - The append queries make use of global variables through user defined functions. While such an approach works nicely for single pass action queries (as in current case), it is not recommended for display oriented queries (e.g. select query etc.) as the calculated values are highly volatile (mere navigation through the column will keep disturbing the values).Download Access 2K & XP: uploads/37/AppendSequence.zip |
||||
| AppointmentsAlert.mdb | General | 228K | Access 2000 & XP | 10 Jul 2008 at 1:53am |
|
Description - This sample db demonstrates an appointments planner featuring audiovisual alarm for events becoming due. The Planner opens with appointment grid pre-selected for today's date and the cursor moves to current time slot. If any other date (not older than yesterday) is selected in the Date Picker control, fresh appointment grid for that date is presented (if not already existing). When put into standby mode, the utility goes into minimized state and keeps scanning the status of scheduled appointments at specified time intervals. As & when an event becomes due, an audio alarm is sounded accompanied by a pop-up form displaying the current status of various appointments. The time in advance of an event for sounding the alarm, as well as the frequency of scanning is amenable to customization by the user. Audio alarm is governed by the *.wav file located in the same folder as that containing the sample db. If it is desired to use a different sound, simply replace this file by the one desired. The zip file contains two versions (Access 2000 file format), developed as follows - (a) Access 2002 installed on Windows XP (b) Access 2000 installed on Windows 98 Caution - (a) An activex control named 'Microsoft Date And Time Picker Control' has been used in this database. This control is required to be registered in the access installation on user's computer, before attempting to open & use the sample db. (b) For registration - file mscomct2.ocx should be available in System32 folder of windows operating system. Open any access database other than this db (even a blank one will do), click Tools -> Activex Controls -> Register. Navigate to the location of above .ocx file and click OK. Versions - XP & 2K (both in Access 2000 file format) References - (a) XP Version - Visual Basic For Applications Microsoft Access 10.0 Object Library Microsoft Office 10.0 Object Library DAO 3.6 (a) 2K Version - Visual Basic For Applications Microsoft Access 9.0 Object Library Microsoft Office 9.0 Object Library DAO 3.6 Download Access 2000 & XP: uploads/37/AppointmentsAlert.zip |
||||
| ArchivesManager.mdb | General | 158K | Access 2000 | 10 Jul 2008 at 1:52am |
|
This sample db demonstrates management of personal knowledge base (e.g. notes / articles / extracts etc). Copies of important messages from various discussion groups can also be stored. Self generating cascaded lookup lists for category / subject / topic facilitate data entry as well as search & retrieval through preview or print. The db is in Access 2000 file format, developed on Access-XP installation. Download Access 2000: uploads/37/ArchivesManager.zip |
||||
| Autonumbers_Reclaim.mdb | QTblFunc | 60K | Access XP & 2K | 10 Jul 2008 at 1:49am |
|
This sample db demonstrates retrieval of missing autonumbers resulting from - (a) Aborting of newly entered record (which has already been made dirty). (b) Deletion of contiguous records towards the end (upto & including the last record). The process is implemented in real time (at data entry stage itself) and is not dependent upon Compact & repair action (in this context, compact & repair is found to be effective only in versions up to Access 2K). While reclaiming the autonumbers, data integrity rules do not get compromised. Download Access XP & 2K: uploads/37/AutoNumbers_Reclaim.zip |
||||
| B | GoTo Top | |||
| BackEndsToggled.mdb | Multi-User | 117K | Access XP & 2K | 10 Jul 2008 at 1:41am |
|
This sample db enables the user to set up temporary copies of Back End db's and re-link the tables accordingly. At the end of session, the links get re-set to original BE's. Multiple Back Ends as well as those with db password are handled. Copies of temp BE's are placed in respective folders containing the original BE's. On opening the form F_ToggleLinks, temp BE's get created, tables get re-linked and the current status gets displayed. Whenever desired, the user can toggle table links between Temp and Original BE's. (Each time a fresh toggle is done, existing temp copy of BE, if any, gets over- written) On closing the form F_ToggleLinks, user is prompted for deletion of temp copies of BE's. On closing the form F_ToggleLinks, all links get automatically restored to original BE's. How To Use This Sample: (a) Simply import the form F_ToggleLinks and table T_Link into your working FE. (b) Go to VBA window (Alt + F11) and make sure References are set up for DAO 3.6 and Microsoft Scripting RunTime (c) Save, compile and then come out of VBA window (Alt + Q) Versions - Access XP & 2K (both in Access 2000 File Format) References - DAO 3.6 and Microsoft Scripting RunTime Download Access XP & 2K: uploads/37/BackEndsToggled.zip |
||||
| BackUpManager.mdb | General | 299K | MDB | 10 Jul 2008 at 1:40am |
|
BackUpManager (Updated on 26-May-2009) Explanatory Notes This is a utility for backing up all types of files and folders. In case of a folder, all the subfolders and files contained therein also get backed up. Multiple profiles can be created, suiting varying backup needs. These profiles can group the available files / folders into any manner as desired e.g. importance, periodicity of backup, relevance to a particular project etc. For a given file or folder, there is no limit to the number of different profiles in which it can be included. If the selection happens to include any Access database files, compaction gets carried out simultaneously, depending upon user's choice. Compaction is performed on a temporary copy of the file, eliminating the scope for any adverse effect on the original file. Multiple Files can be selected at a time for insertion as source files. Paths for destination files and folders are assigned automatically, using the drive farthest removed from the source one (Destination paths can be modified by user if desired). BackUp copies get date time stamped as well, depending upon user's choice. In XP version, FileDialog object is used for activating Files & Folder Picker dialog boxes, while in 2K version, API calls are used. (Both versions are in Access 2000 File Format) It is recommended that the additional notes for user, as given below, are carefully gone through, before commencing use of main program. Versions: Access 2K and XP (Both in Access 2000 file format) References: (a) XP version - Microsoft Scripting Runtime Microsoft Office 10.0 Object Library (a) 2K version - Microsoft Scripting Runtime =============================== Additional Notes For User: 01 - To Insert New Source Files - Double Click Source File Field. This Will Open The File Picker Dialog Box. Multiple Files Can Be Selected For Insertion At A Time. 02 - To Insert New Source Folder Alone (w/o Files), Double Click Source Directory Field. This Will Open The Folder Picker Dialog Box. 03 - Default Values For Destn Directory/File can be altered by user if desired (New Directory Will Be Created If Not Existing). 04 - To Select Specific Destination Directory - Double Click Destination Directory Field. 05 - Clicking Of Option Button (For Date Time Stamping) At Top Updates The Existing Value For Destn File Accordingly (Only If The Check Box Is In Selected State). 06 - To Select/Deselect All Check Boxes At Once - Double Click Last_BkUp Column. 07 - Display Is Arranged As Per Source Directory And FileName (Ascending) 08 - Selection Of Checkbox Inserts Default Values For Destn Directory And File (Only If Empty) 09 - Grey Color For Last_BkUp Indicates The Date Of Last Back Up is Earlier Than The Current Date. 10 - If Only Source Directory Is Entered (i.e. Source File Is Blank), The Source Folder (Including All It's SubFolders And Files) Will Get Copied To The Destn Folder - The Destn Folder Will Get Date Time Stamped (If appropriate option button is in selected state) 11 - Files/Folders Freshly Added In Current Session Are HighLighted In Maroon Color. 12 - Failure In Making BackUp Is Indicated By Pink Color In Last_BkUp Field. 13 - For Changing The Destination Drive (Of Items Where CheckBox Is In Selected State) To The Value Selected In Combo Box At Left, Click The Command Button At Top Right. 14 - For linking the files / folders to various profiles, click the command button named Edit Profile. In the profile editor, select the desired file / folder in the datasheet at left and enter the desired profile string in the datasheet at right. A given file or folder can be included in as many different profiles as desired. 15 - Any Access Database File If Selected For BackUp Gets Compacted As Well (If so desired by user). Compaction is performed on a temporary copy of the file, eliminating the scope for any adverse effect on the original file. Note: Occasionally, on the first run, if the check boxes have been selected indirectly (by double click in Last_BkUp column), a message "Data was changed" might appear instead of copy action. Simply try again - it will be OK. Download >>: uploads/37/BackUpManager.zip |
||||
| BlinkingLabels_XP.mdb | Form | 69K | Access XP | 10 Jul 2008 at 1:38am |
|
The sample db demonstrates blinking labels (cyclic control of brightness) in various styles as follows. (a) Conditional blinking based upon the date displayed in a text box. (b) Temporary blinking triggered by mouse move. (c) Continuous blinking. For each of the above, following sub-styles are covered (synchronizing with the blink status). (a) Fixed over-all size of label. (b) Dynamic size and font of label Note - This sample is meant for use with Access XP and higher.Download Access XP: uploads/37/BlinkingLabels_XP.zip |
||||
| C | GoTo Top | |||
| Charts_AccessAndExcel.mdb | Chart | 151K | Access 2K/XP/2K3 | 10 Jul 2008 at 1:37am |
|
This sample db demonstrates management of chart objects on forms and reports. Display of Excel chart on Access form based upon Access data, is also covered. Examples covered pertain to the readings of Blood Pressure and Pulse for patients. Each individual spell of stay in the hospital is identified by unique InPatient_ID. Important tips for handling the chart object are also included. Version - Access 2K/XP/2K3 (Access 2000 File Format) References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) Microsoft Graph - (Appropriate Version) (d) DAO 3.6Download Access 2K/XP/2K3: uploads/37/Charts_AccessAndExcel.zip |
||||
| Charts_PieTypeOnReports.mdb | Chart | 50K | Access 2K/XP/2K3 | 10 Jul 2008 at 1:36am |
|
This db demonstrates four alternative methods of depicting pie charts (on access reports), showing total scores for various codes("G", "R", "Y") pertaining to different group leaders. (a) Style A - RowSource for the chart is filtered as per run time value of key field (GrpID). This is done via user defined function. (b) Style B - RowSource for the chart is fed run time values of total scores for different codes for the given key field (GrpID). This is done via user defined functions. (c) Style C - Mini-Query serving as RowSource for the chart is built at run time, directly using values of total scores for different codes for the given key field (GrpID). (d) Style D - Child field (GrpID) in the chart is linked to master field (GrpID) located in the group header section.Download Access 2K/XP/2K3: uploads/37/Charts_PieTypeOnReports.zip |
||||
| ComputeAcrossFields.mdb | QTblFunc | 109K | Access 2000 & XP | 10 Jul 2008 at 1:34am |
|
This sample db demonstrates computation of Minimum / Maximum /Sum / Average / Median values for identified fields in the same record. All that is needed is to put a common identifier string as pre-fix to the name of each field that is required to be included for such calculation. The identifier string and primary Key value are passed as arguments to various user defined functions. Versions - XP & 2K (both in Access 2000 file format) References - Visual Basic For Applications Microsoft Access 9.0 Object Library DAO 3.6 Download Access 2000 & XP: uploads/37/ComputeAcrossFields.zip |
||||
| ConductExams.mdb | General | 178K | Access 2K | 09 Jul 2008 at 11:15pm |
|
This sample db demonstrates various aspects of conducting examinations, namely: (1) Student registration for various courses & generation of confirmatory eMails to respective students, conveying user ID's & passwords. (2) Building up of question bank (3) Setting up Test Papers: (a) By manual selection from question bank. (b) By automatic random selection from question bank. (4) Setting up Exams based upon available test papers and generation of confirmatory eMails to respective students. (5) Taking of exams by students (6) Automatic evaluation of performance. (7) Generation of report displaying performance results. Note - When a student logs in for taking a test, the command button for actual commencement of test becomes enabled not earlier than one minute before scheduled date & time of exam. While the test is in progress, flashing warning gets activated 5 minutes before expiry of allowed time period. The test form closes on expiry of allowed period even if the student fails to do so. Actual start & finish times also get recorded.Download Access 2K: uploads/37/ConductExams.zip |
||||
| ContFormsKeyNavAndHighLightMultiSelect.mdb | Form | 40K | Access XP & 2K | 09 Jul 2008 at 11:14pm |
|
This sample db demonstrates a continuous form featuring: (a) Key Navigation (Datasheet Style) (b) Graded Highlighting Of Multiple Selected Records Pattern of Highlighting: (a) Current record (whether fully selected or not) - Light Blue. (b) Other records, which form part of current selection block - Orange Yellow. (c) Other records, which were selected at any time earlier in current session - Light Purple. (d) All highlights other than that for current record can be undone by dbl clicking any row. 4.3 - Selection of records: (a) Single record - Click on record selector. (b) Multiple records at a time - Click on record selector of top desired record and drag the mouse to record selector of bottom desired record. (Alternatively, you can hold down shift key and then click the record selector for bottom desired record, or use Dn Arrow key to expand the selection suitably). Note - (a) Using Up/Dn arrow keys alone, when a group of records is in selected state, moves the whole selection mask accordingly (b) List of primary key values for records that have been selected so far (even if not in current selection) is stored in form's global variable named SelectList. (c) For a given file (Access 2000 file format), performance of dynamic conditional highlighting is found to be much better in Access 2003 installation. In case of Access 2K & XP installations, there is noticeable time lag for final rendering of conditional colors to take effect. (In the mean time, for a brief transient period, all existing colors appear to get lost temporarily). (d) Form's KeyPreview property should be set to Yes.Download Access XP & 2K: uploads/37/ContFormsKeyNavAndHighLightMultiSelect.zip |
||||
| ContFormUnboundControl.mdb | Form | 66K | Access XP & 2K | 09 Jul 2008 at 11:12pm |
|
The sample db demonstrates display of unbound control on a continuous form - for the current record only. Download Access XP & 2K: uploads/37/ContFormUnboundControl.zip |
||||
| ConvertPics_OLEtoExternal.mdb | General | 859K | Access 2000 | 09 Jul 2008 at 11:10pm |
|
This sample db offers remedial action for Access databases suffering heavy bloat on account of direct embedding of images in OLE fields. For carrying out the conversion, click the top button in main Switchboard to open the form F_PicsEmbeddedInOLE. On this form, click the command button for conversion. This will convert all images embedded in OLE fields into separate .jpg files and store the same in a newly created folder named PicFolder (in the same directory that holds the sample db). Simultaneously, the paths of these externally stored files get recorded in the field named ImageFile (in the source table named T_Pics). For slide-show display based upon paths of externally stored images (created as above), click the second command button on the main Switchboard. For handling an existing table having images embedded in OLE Fileds, import the table into sample db. Rename the OLE field as Pic, ensure an Autonumber primary key named ID_No, and add a text field named ImageFile. Rename the table as T_Pics and proceed as above. (When satisfied with external storage, existing OLE field in the table can be deleted - for eliminating Bloat) CAUTION - (a) This program uses Microsoft Photo Editor. It may please be ensuredthat correct path for file PHOTOED.EXE is specified in click event of CmdCopy in code module of form F_PicsEmbeddedInOLE . (b) This sample db as well as the accompanying Blank.jpg, should be kept in the same folder. 5 - Versions - Access 2000 References - OLE Automation, DAO 3.6, MS Scripting RunTimeDownload Access 2000: uploads/37/ConvertPics_OLEtoExternal.zip |
||||
| CreateNewControls.mdb | Form | 93K | Access XP & 2K | 09 Jul 2008 at 11:09pm |
|
This sample db demonstrates creation (or deletion) of an array of four command buttons at run time. Controls can be created or deleted only when the form is in design mode in design mode. Use of temporary auxiliary forms makes it look as if the creation (or deletion) of controls is taking place directly on clicking of appropriate command buttons on the form itself.Download Access XP & 2K : uploads/37/CreateNewControls.zip |
||||
| Crosstab_CalendarStyleTaskPlanner.mdb | General | 74K | MDB | 09 Jul 2008 at 11:08pm |
|
Crosstab_CalendarStyleTaskPlanner Updates: 22-Jun-2012: Next / Prev buttons provided for year / month 21-Jun-2012: Initial focus on current day if year month is current - otherwise first day of month. Explanatory Notes: This sample db demonstrates a task planner. On selection of person, year and month, a wall calendar matching the month/year gets displayed. The user can enter planned tasks directly into this calendar. As the space available in each date cell of the calendar is limited, supplementary remarks can be entered in a memo field displayed in a large text box at bottom of calendar. As the user moves into a date cell, control source for this memo field text box gets dynamically re-assigned so as to match the selected date. Active date cell gets highlighted in light yellow back color, matching that of the remarks text box. As and when desired, latest status of data entry in this calendar, which is in crosstab format, can be saved back to the source table in normalized manner, by clicking the Save button which becomes enabled whenever the form gets dirty. For the given person and month/year, calendar report gets generated by clicking the appropriate command button. This report displays the status of task planning for the selected month/year just like a wall calendar. While individual date cells in the calendar show brief mention of planned tasks, date-wise supplementary remarks are displayed at the bottom of report. Note: While entering data in date cells, it would be preferable to keep it brief so as to fit in the limited display area. Supplementary remarks for each date can be entered in the large text box (representing a memo field) at bottom. Version: Access 2000 File FormatReference: DAO 3.6Download File >>: uploads/37/Crosstab_CalendarStyleTaskPlanner.zip |
||||
| CrossTabAndReverse.mdb | QTblFunc | 74K | Access 2000 | 09 Jul 2008 at 11:06pm |
|
This sample db demonstrates data entry in crosstab format while the original records are stored in a normal manner. Interaction between data entry form and the main data table involves reverse crosstab approach so as to update the original records. Important details are explained in the accompanying Readme file. The sample is in Access 2000 file format developed on Access XP installation.Download Access 2000: uploads/37/CrossTabAndReverse.zip |
||||
| CrossTabDemo_2_A2K.mdb | QTblFunc | 29K | Access 2000 | 09 Jul 2008 at 10:59pm |
|
This database demonstrates generation of sales report summarizing the results in 12 columns corresponding to individual months in a year, grouping the rows as per calendar years and salespersons. (Subtotals across columns are also shown)Download Access 2000: uploads/37/CrossTabDemo_2_A2K.zip |
||||
| CrossTabDemo_2K.mdb | QTblFunc | 79K | Access 2000 (ADO) | 09 Jul 2008 at 10:58pm |
|
General - This database demonstrates data entry and report generation for an attendance register in spreadsheet style - featuring staggered dates (i.e. working days are restricted to two nominated weekdays) Data Entry - In order to retain normal structure of the table storing basic data (i.e. student_ID, date and Yes/No field for attendance), at the same time presenting spreadsheet style interface where attendance checkboxes are displayed in a row across each student name, the checkbox controls on the subform are bound to an interim table. Values entered get pushed to the corresponding checkboxes (for particular student_ID and date)in the main table. Before commencing entries for a new month, the user has the option to set the nominated weekdays. Report Generation - The report shows attendance markings arranged horizontally against each student. Headings for WeekdayNames & Dates for each attendance day are also displayed. For generating the reports, temporary table is populated on the fly , using crosstab query based upon data filtered as per start and end YearMonths selected by user. Version - Access 2000 References - Visual Basic For Applications Microsoft Access 9.0 Object Library OLE Automation ADO 2.6Download Access 2000 (ADO): uploads/37/CrossTabDemo_2K.zip |
||||
| D | GoTo Top | |||
| DatasheetDynamicColumns.mdb | Form | 116K | Access XP & 2K | 09 Jul 2008 at 10:56pm |
|
This sample db demonstrates manipulation of datasheet columns (their numbers, headings, control-source, column order, width etc) so as to suit run-time change of record-source. On opening the main form, it grabs the names of all available tables and queries, and loads them in the row-source for the combo box. At this stage the main subform has five columns on display, all unbound, with generic headings. There is no record- source yet, for the subform. As soon as a source table or query is selected in the combo box, the subform at right (serving as field list), gets populated with names of fields in the selected table/query. Out of these, the user can click the check boxes for selecting the fields required to be loaded in the main subform. On clicking the update button, the subform gets loaded with appropriate record- source and the controls get bound to respective fields. The columns are displayed in proper order, with headings matching the field names. If the number of fields selected in the field list is less than the number of controls available, the unused columns remain unbound and get hidden. Widths of displayed columns get adjusted automatically, so as to match the overall width of subform control. References - DAO 3.6 Download Access XP & 2K: uploads/37/DatasheetDynamicColumns.zip |
||||
| DentalAppointments.mdb | General | 153K | Access 2000 | 09 Jul 2008 at 10:55pm |
|
This sample db demonstrates Appointments Planner for a dental clinic having two chairs. The Planner opens with appointment grid for today's date pre-selected and the cursor moves to first available free time slot. If any other date is selected in the Date Picker control, fresh appointment grid for that date is presented (if no entry is already existing for that date). Default values of timeslots can be altered (e.g. longer slots might be needed for some tasks) - provided it does not intrude upon an already booked timeslot. The db is in Access 2000 file format, developed on Access-XP installation. Caution - (a) An activex control named 'Microsoft Date And Time Picker Control' has been used in this database. This control is required to be registered in the access installation on user's computer, before attempting to open & use the sample db. (b) For registration - file mscomct2.ocx should be available in System32 folder of windows operating system. Open any access database other than this db (even a blank one will do), click Tools -> Activex Controls -> Register. Navigate to the location of above .ocx file and click OK. Requires DAO 3.6Download Access 2000: uploads/37/DentalAppointments.zip |
||||
| E | GoTo Top | |||
| ExcelHyperLinksToAccess | Automation | 66K | 23 Jan 2009 at 2:38am | |
|
ExcelHyperLinksToAccess Sample Db - Explanatory Notes This sample db demonstrates import of excel data including hyperlinks if any. Direct import of specified data block from excel, using DoCmd.TransferSpreadsheet command is not able to pull the hyperlink details. To do so, it is necessary to convert the face value of hyperlink cells into full information suitable for proper interpretation in access table's hyperlink type field. Source excel file is placed in folder ExcelFile. This folder is located in the home folder (ExcelHyperLinksToAccess) that contains the sample db. It should be ensured that in the excel workbook, the sheet having source data is the active sheet (Open the workbook, select the sheet having source data, save and close). First row of data block should carry field names matching those in access table named T_Imported. On opening the main form, excel source sheet is displayed in an unbound OLE control. Through auto detection, available data block is shown enclosed in a solid border. If there is any column within this data block that carries hyperlinks, it gets highlighted in special color. Row and column numbers of first cell (i.e. top left cell) of excel data block, as well as column number of hyperlink column, get displayed in text boxes. If found necessary, the user can edit these values. After verifying that excel data block is displayed correctly, clicking of Import button carries out the import into table T_Imported, after converting the face value of cells in hyperlink column so as to suit the requirements of hyperlink type field in access table. If data is required to be imported from more than one worksheet in a given workbook, the process can be repeated by making different sheets active in turn (Open the workbook in folder ExcelFile, select the desired sheet, save and close). Important: It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to sticking of excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of DoCmd.TransferSpreadsheet has been carried out independently, after execution of automation code modules. References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6 Version - Access 2000 File Format Download >>: uploads/37/ExcelHyperLinksToAccess.zip |
||||
| ExcelToAccess.mdb | Automation | 65K | MDB | 09 Jul 2008 at 10:54pm |
|
1 - This sample demonstrates transfer of data from excel workbook to access db, accompanied by updating of data in local table of access db. 2 - Both techniques, i.e. Link as well as Import are covered. 3 - Data blocks can be located anywhere on any of the worksheets, so long as it is ensured that each such block comprises of specified number of columns (as per global constant) and first row represents the column headings. 4 - For transferring any particular data block to access db, select the worksheet on which it is located. Shortcuts for execution are: (a) Via Link to access: Ctrl + k (b) Via Import into access: Ctrl + t 5 - As soon as any of the above shortcuts is applied, data block on active worksheet gets identified automatically, accompanied by assignment of dynamic range name to it (as per specified global constant). In case of (b) above, the cell denoting heading of first column of data block should be in selected state, before using the short cut. In case of (a) above, even this is not necessary. First column heading, wherever it is, gets identified automatically. 6 - Based upon linked or imported data, updating of local table in access db takes place on the following lines: (a) If PartNum already exists in the local table, the date & stock get updated as per fresh data. (b) If fresh data has records with PartNum values not yet existing in local table, corresponding fresh records get appended to the local table. 7 - After each execution of data transfer, the access db gets displayed adjacent to excel workbook. Records freshly appended get highlighted in light blue, while those freshly updated are shown in dark yellow. Simultaneously, in excel workbook too, the data rows responsible for causing changes in access local table (update/append) get correspondingly highlighted. 8 - There is complete flexibility regarding the worksheets. Any worksheet (even a freshly inserted one) can be used, provided it is the active sheet. 9 - Even the name of workbook or the range name assigned dynamically to the data block on active sheet, do not matter, so long as it is ensured that the workbook is housed in the same folder that contains the access db. 10 - It is desirable that the same computer should not have multiple versions of Excel installed. Moving amongst different versions of excel on same computer in the same session can lead to unexpected results. 11 - The sample has been tested with Excel 2000 workbook in conjunction with Access Db in Access 2000 file format in various installations of access, i.e. 2K, XP and 2003. Note - All user action is via excel workbook. Access Db need not be opened directly. References: (a) Excel Workbook - Access 9.0 Object Library & DAO 3.6 (b) Access Db - DAO 3.6Download Excel 2K & Access 2K: uploads/37/ExcelToAccess.zip |
||||
| ExcelToAccessBrokenRanges | Automation | 90K | 25 Aug 2011 at 11:37pm | |
|
ExcelToAccessBrokenRanges Sample Db - Explanatory Notes This sample db demonstrates import of scattered data from excel involving horizontally fragmented data blocks - into a single access table. In such a situation, assigning the data blocks to a common range name, followed by straightforward use of DoCmd.TransferSpreadsheet command does not work. Each data block has to be imported individually, for eventual amalgamation into a single table T_Imported. Sample excel file (Orders.xls) is located in the home folder that contains this sample db. First row of each data block should carry field names. On opening the main form, and clicking the command button captioned "Load Excel File", the user gets the option either to load the sample excel file already available, or browse to and select any other excel file. The list box at right gets populated with the names of defined ranges, along with addresses of respective data blocks in selected excel file. Simultaneously, a text box located below the list box, gets populated with the cell addresses for respective data blocks. This text box is editable allowing the user to carry out any modification if desired. An option group provides a choice for the mode to be adopted for import action (i.e. whether as per pre-defined ranges or as per user specified addresses). Note: Names collection in excel workbook covers the defined ranges. Each element of this collection returns a Name object whose default property is Value. This default property returns range address. For example if the range object named R_DataBlock_1 covers cells C4:D7 on sheet DataBlock_1, Name object's value will return =DataBlock_1!$C$4:$D$7. For getting actual name of the range, we have to use Name property of Name object. User defined address string using comma as separator between adjacent blocks (no spaces) serves as one of the arguments for subroutine P_ImportFromExcelBrokenRanges. Either of the three sample styles given below can be used (Orders_01 is the name of worksheet): (a) Range Names: "R1,R2, -- etc" or (b) Address By Cells: "Orders_01!B2:C12,Orders_01!E3:G13, -- etc" or (c) Address By Columns: "Orders_01!B:C,Orders_01!E:G, -- etc" Note: In (b) & (c) above, avoid absolute addresses (Don't use $ sign anywhere) If style (c) is used, first row of spreadsheet (i.e. row 1) should carry field names. This style takes care of last populated row in the specified columns - automatically. Clicking of Import button carries out the import of different data blocks into temp tables followed by amalgamation into a single table T_Imported. The imported data gets displayed in the subform. Important: It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to sticking of excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of DoCmd.TransferSpreadsheet is best carried out independently, after execution of automation code modules and closing of excel. References: (a) DAO 3.6 (b) Microsoft Scripting RunTime Version - Access 2000 File Format Download File >>: uploads/37/ExcelToAccessBrokenRanges.zip |
||||
| ExcelToAccessMultiSheets.mdb | Automation | 53K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:51pm |
|
This sample db demonstrates importing of excel data (having first row as column headings). Any desired data block from any desired worksheet can be imported. A common dual-purpose query is used for appending / updating data in local table T_Local as per data imported into table T_Imported. Fresh records get appended to T_Local, while those for which matching primary key value already exists, get updated as per latest imported data. As added convenience, the excel workbook serving as data source is displayed within a web browser control on import form, while the results (appended/updated records) are displayed alongside in a subform. The imported data is highlighted in separate colors depending upon whether the records are appended or updated. Before clicking the import button, top left column heading cell of desired data block on desired worksheet should be selected in the excel workbook displayed in browser control on the form. The area pertaining to this data block is detected automatically, gets highlighted in color and is assigned a dynamic range name. Import process continues further only when user is satisfied that correct data block has been highlighted. Multiple import of various data blocks in different worksheets can be conducted, one after the other. Note: (a) The source excel file can be of any name, but should be in the same folder as the one containing the sample db. It is detected automatically and all further action takes place on a temp copy, so as to avoid any interference to the original. (b) On the import form, labels have been used instead of command buttons in order to prevent freezing of web browser, which otherwise takes place when some excel cell is clicked by user prior to clicking of command button. (c) It is observed that with use of browser control for opening the excel workbook, Excel process may not terminate at the end of session, even though Quit command has been used and all object variables set to nothing. After the access db is finally closed, it would be desirable to cross check via task manager and terminate the Excel process if still running. Caution: Before trying this sample db, please ensure that activex control named "Microsoft Web Browser" is registered in your access installation. For this, file named shdocvw.dll should be available in System32 folder of windows operating system. Click Tools > Activex Controls to get the dialog box for Activex controls and check whether this control appears in the list. If not, click Register button, locate the file in System32 folder and do the needful. 5 - Versions - Access 2K/XP/2K3 (Access 2000 File Format) References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6Download Access 2K/XP/2K3: uploads/37/ExcelToAccessMultiSheets.zip |
||||
| ExcelToAccessMultiSheetsStyle2 | Automation | 223K | 28 Nov 2011 at 11:12am | |
|
ExcelToAccessMultiSheetsStyle2 Sample Db - Explanatory Notes This sample db demonstrates import of data from excel, spread over multiple sheets. Two styles are covered: (a) Excel workbook with pre-defined range names. (b) Excel workbook without pre-defined range names. Sample excel files (WithRangeNames.xls and WithoutRangeNames.xls) are located in the home folder that contains this sample db. First row of each data block carries field names. On opening the main form, and clicking the appropriate command button, the user gets the option either to load the sample excel file already available, or browse to and select any other excel file. In style (a), information pertaining to data blocks belonging to different named ranges as fetched from source file, is displayed in the subform at left. In style (b) similar information is displayed, based upon programmatic detection of data blocks in excel workbook, matching the first field name provided by the user. For greater precision, if desired, the user can also optionally enter column count and name of last field. Amongst data blocks displayed in the subform, those required to be imported can be identified by clicking check boxes in column headed "Include". For items selected in this manner, the text box at right gets populated with a comma separated list of data blocks earmarked for import. Based upon user choice in the option group, this list is made up either of range names (applicable to style (a) only), or cell addresses or column addresses. The list is editable, allowing the user to carry out any modification if desired. An option group provides a choice for the mode to be adopted for import action (i.e. whether as per pre-defined ranges or as per user specified cell or column addresses). On clicking the appropriate command button, data blocks covered by above list get imported into access. For added convenience, command buttons have been provided for viewing the Imported data as well as the excel file - if desired. Supplementary Notes: 1 - Names collection in excel workbook covers pre-defined ranges. Each element of this collection returns a Name object whose default property is Value. This default property returns range address. For example if the range object named R_DataBlock_1 covers cells C4:D7 on sheet DataBlock_1, Name object's value will return =DataBlock_1!$C$4:$D$7. For getting actual name of the range, we have to use Name property of Name object. 2 - User defined address string using comma as separator between adjacent blocks (no spaces) serves as one of the arguments for import subroutine. Either of the three sample styles given below can be used (Orders_01 is the name of worksheet): (a) Range Names: "R1,R2, -- etc" - (Applicable to style (a) only) or (b) Address By Cells: "Orders_01!B2:C12,Orders_01!E3:G13, -- etc" or (c) Address By Columns: "Orders_01!B:C,Orders_01!E:G, -- etc" Note: In (b) & (c) above, for smooth execution of DoCmd.TransferSpreadsheet method, avoid absolute addresses (Don't use $ sign anywhere) 3 - Important: It is observed that DoCmd.TransferSpreadsheet action, if carried out as an integral part of automation code, can lead to hung excel application (excel refuses to quit despite explicit quit command and setting the object variables to nothing). For this reason, implementation of DoCmd.TransferSpreadsheet is best carried out independently, after execution of automation code modules and closing of excel. References: (a) DAO 3.6 (b) Microsoft Scripting RunTime (For excel application, late binding has been used) Version - Access 2000 File Format Download File >>: uploads/37/ExcelToAccessMultiSheetsStyle2.zip |
||||
| ExcelToAccessTransposed.mdb | Automation | 55K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:50pm |
|
This sample db demonstrates importing of excel data, where contents of individual fields are arranged down the rows (as against the conventional layout of fields across the columns). Any desired data block from any desired worksheet can be imported. After importing into access, the fields are transposed into normal layout via a cross tab query. This is done in accordance with the list of desired field names as contained in table T_Hdg. A common dual-purpose query is used for appending/updating data in local table T_Local as per data imported into table T_Imported. Fresh records get appended to T_Local, while those for which matching primary key value already exists, get updated as per latest imported data. As added convenience, the excel workbook serving as data source is displayed within a web browser control on import form, while the results (appended/updated records) are displayed alongside in a subform. The imported data is highlighted in separate colors depending upon whether the records are appended or updated. Before clicking the import button, top left cell of desired data block on desired worksheet should be selected in the excel workbook displayed in browser control on the form. The area pertaining to this data block is detected automatically, gets highlighted in color and is assigned a dynamic range name. Import process continues further only when user is satisfied that correct data block has been highlighted. Multiple import of various data blocks in different worksheets can be conducted, one after the other. Note: (a) The source excel file can be of any name, but should be in the same folder as the one containing the sample db. It is detected automatically and all further action takes place on a temp copy, so as to avoid any interference to the original. (b) On the import form, labels have been used instead of command buttons in order to prevent freezing of web browser, which otherwise takes place when some excel cell is clicked by user prior to clicking of command button. (c) It is observed that with use of browser control for opening the excel workbook, Excel process may not terminate at the end of session, even though Quit command has been used and all object variables set to nothing. After the access db is finally closed, it would be desirable to cross check via task manager and terminate the Excel process if still running. Caution: Before trying this sample db, please ensure that activex control named "Microsoft Web Browser" is registered in your access installation. For this, file named shdocvw.dll should be available in System32 folder of windows operating system. Click Tools > Activex Controls to get the dialog box for Activex controls and check whether this control appears in the list. If not, click Register button, locate the file in System32 folder and do the needful. 5 - Versions - Access 2K/XP/2K3 (Access 2000 File Format) References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6Download Access 2K/XP/2K3: uploads/37/ExcelToAccessTransposed.zip |
||||
| ExportAccessToExcelAsPivotTable.mdb | Automation | 50K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:49pm |
|
This sample db demonstrates export of access queries to excel. If desired, pivot tables based upon exported data, also get created in the destination file (The destination files are stored in a folder named ExportedExcelFiles, located within the parent folder containing the sample db). Queries meant for export are displayed in multi-select list box. If multiple selection is made, bulk export takes place, data from each selected query going to a separate sheet in destination workbook. For queries where pivot table view is also required at excel end, names of pivot fields (row field, column field, data field) appear in appropriate columns in the list box. For other queries (not requiring pivot table view) these columns appear blank. If multi-selection includes a mixture of both types of queries, data from all queries gets exported, while pivot tables at excel end get created only for queries having distinct values for pivot fields as displayed in the list box. Depending upon user's choice, optional template can be used for creating the destination excel file. For this demonstration, the template file is located along with sample db in the same home folder. While suitable titles are inserted programmatically for all pivot table sheets, similar action for sheets holding exported data is implemented only if no template has been used. All utilized sheets (data as well as pivot table) get renamed as per respective exported queries. Exported data blocks are highlighted in color & provided with suitable borders. References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6Download Access 2K/XP/2K3: uploads/37/ExportAccessToExcelAsPivotTable.zip |
||||
| ExportAccessToExcelMultiSheets.mdb | Automation | 39K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:47pm |
|
This sample db demonstrates exporting of access data to excel workbook. The excel workbook serving as data destination is displayed within a web browser control on the export form, while the access data meant to be exported is displayed alongside in a subform. Before clicking the export button, the cell meant to serve as top left column heading cell of exported data block on desired worksheet should be selected (in the excel workbook displayed in browser control on the form). Multiple export of various queries to different worksheets can be conducted, one after the other. The destination worksheet in each case gets re-named as per access source query. Note: (a) The destination excel file is copied from template excel file if available, and is placed in folder named ExportedExcelFiles. This folder and the template excel file are placed along the sample db in the same parent folder. ExportedExcelFiles folder and or template excel file, if not already existing, get created programmatically. The user can replace the sample template file by any other desired excel file. However, it should be ensured that the name of this file is always Template.xls (b) On the export form, command buttons and the combo box have been placed in a subform in order to prevent freezing of web browser, which otherwise takes place when some excel cell is clicked by user prior to clicking of command button. (c) It is observed that with use of browser control for opening the excel workbook, Excel process may not terminate at the end of session, even though Quit command has been used and all object variables set to nothing. After the access db is finally closed, it would be desirable to cross check via task manager and terminate the Excel process if still running. Caution: Before trying to use this sample db, please ensure that activex control named "Microsoft Web Browser" is registered in your access installation. For this, file named shdocvw.dll should be available in System32 folder of windows operating system. Click Tools > Activex Controls to get the dialog box for Activex controls and check whether this control appears in the list. If not, click Register button, locate the file in System32 folder and do the needful. References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6Download Access 2K/XP/2K3: uploads/37/ExportAccessToExcelMultiSheets.zip |
||||
| ExportToExcelNamedMultiSheets | Automation | 54K | 17 Sep 2008 at 5:43am | |
|
ExportToExcelNamedMultiSheets Author: A.D. Tejpal Description: This sample db demonstrates a drastically simplified method for exporting multiple access tables/queries to custom named worksheets in the given excel workbook. No automation code is needed. If the user is not very particular about pre-defining ancillary content in the destination file and positioning of starting cell for exported data, DoCmd.TransferSpreadsheet method can be used directly. (DoCmd.OutputTo method is not convenient as it is object specific and overwrites preceding export - if any). Names of tables / queries meant to be exported are placed in a table named T_ExportList, having fields QueryName (name of table or query required to be exported) and DestnSheetName (desired name of worksheet to which you wish to export the table or query). User can select the items to be exported by clicking the Yes/No type field named DoExport. Sample subroutine P_ExportToExcelMultiSheet() is used to export all selected tables / queries to different sheets in destination excel file. Respective sheets in excel file get named as per those prescribed in field DestnSheetName. This procedure uses sample subroutine named P_ExportToExcelNamedSheet. It accepts three arguments, i.e. SourceQueryName, ExcelSheetName and ExcelFilePath. The subroutine creates a temporary copy of table/query as per the name of destination worksheet, does the export and then deletes the temporary copy. Note - If DestnSheetName is left blank, the target worksheet gets named as per the table / query that has been exported to it. Version: Access 2000 File Format References: (a) Microsoft Excel Object library (version 9.0 or later) (b) Microsoft Scripting RunTime (c) DAO 3.6 Download >> uploads/37/ExportToExcelNamedMultiSheets.zip |
||||
| ExternalDbAsLibraryRef.mdb | External | 120K | MDB | 09 Jul 2008 at 10:45pm |
|
ExternalDbAsLibraryRef (Updated on: 12-Oct-2010) Explanatory Notes This set of db's named LOCAL.mdb and External.mdb respectively, is meant to demonstrate display of forms and reports belonging to external db, within local db by setting up library reference to it, in the local db acting as host. It covers (a) Display of external db forms loaded with external db data, (b) Display of host db forms loaded with external db data, (c) Display of external db forms loaded with host db data The external db is located adjacent to local db in the same folder. Both db's are in Access 2000 file format. Two alternative styles are covered for displaying the external reports and forms. In style 1, the selection forms used for picking up the reports / forms belong to external db itself. In style 2, these selection forms belong to local db. SQL statements serving as row source for respective list boxes in selection forms are based upon tables based in external db. In load events of selection forms, these tables get populated afresh with the names of form and reports in external db. Two alternative styles for opening a form belonging to external db and loading it with data from host db are also demonstrated. In one case, the task is accomplished without using IN clause, while the other makes use of IN clause. In local db, correct library reference to external db is set up programmatically during load event of main switchboard. This is based upon external db (External.mdb) being housed in the same folder that contains the local db. If there is any change in this set up, function Fn_ExternalDbPath() in general module of local db, used for getting the path of external db, will need suitable revision. Note: (a) In local db's module, name of library reference to external db has to be used as qualifier for calling subroutines or functions belonging to external db. (b) Within the module of external db (when serving as library reference), CodeDb and CodeProject represent external db while CurrentDb and CurrentProject represent the host db. Version: Access 2000 file format. References: External Db - DAO 3.6 Local Db - Nothing extra Download >>: uploads/37/ExternalDbAsLibraryRef.zip |
||||
| ExternalDbAutomation.mdb | External | 169K | Access XP & 2K | 09 Jul 2008 at 10:43pm |
|
The sample set consists of two mdb files, representing local and external db's respectively. Access XP version incorporates programmatic insertion of password argument for opening the external db (In earlier versions, password argument is not available for OpenCurrentDatabase method). So long as both db's are in the same folder, the local db will automatically detect the path of external db Clicking the command button captioned "Go To External Db", on switchboard form in local db, hides the local access application and results in either of the following - (a) If external db is not yet open, a new instance of access application is created, in which external db is opened and its switchboard form is displayed. (b) If external db is already open, it gets un-hidden and its switchboard form is displayed. In external db, opening of form F_Reports results in loading of all available report names (from AllReports collection) in the list box, facilitating print/preview. In external db, clicking the go-back button (or closing the switchboard form) results in hiding of external db and un-hiding of local db. Any of the following actions in local db ensures closure of external db (if still open) - (a) Click command button for closing external db. (b) Close switchboard form of local db. (c) click command button for closing local db. References - DAO 3.6Download Access XP & 2K: uploads/37/ExternalDbAutomation.zip |
||||
| F | GoTo Top | |||
| FirstInFirstOut.mdb | General | 71K | Access XP & 2K | 09 Jul 2008 at 10:41pm |
|
This sample db demonstrates (a) Monitoring of orders covered against stock available - on first in first out basis. (This is relevant when the total quantity as per orders booked is in excess of total stock). (b) Monitoring of stock consumed against orders received - on first in first out basis. (This is relevant when the total quantity as per orders booked is less than total stock).Download Access XP & 2K: uploads/37/FirstInFirstOut.zip |
||||
| FixMixedQuotes.mdb | QTblFunc | 101K | MDB | 09 Jul 2008 at 10:40pm |
|
FixMixedQuotes (Updated On 14-Dec-2009) Explanatory Notes: This sample db demonstrates handling of embedded single and / or double quotes in VBA strings Typically, the situation arises when contents of a text box, containing embedded quotes in miscellaneous combinations (single, double or both), are sought to be used in any of the following: (a) Append query. (b) Update query. (c) Where clause of a query - Exact Match. (d) Where clause of a query - Partial Match (Like operator). (e) Filter string for form - Exact Match. (f) Filter string for form - Partial Match (Like operator). For handling embedded quotes, alternative styles are demonstrated as follows: 1 - Avoid fixing any quotes - By using direct reference to values held by form controls: 1.1 - This is an extremely useful method, based upon form control reference embedded within the SQL string. Major advantages are: (a) No need to fix any embedded quotes, whether single or double or a combination of both. (b) No need to worry about specific delimiters while concatenating values for various data types (e.g. ' for text type and # for date type data). (c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US date format (mm/dd/yyyy). (d) There is no problem if the value held by any of the form controls happens to be Null (provided destination table is in a position to accept Null value in the field concerned). 1.2 - Three styles of direct reference to values held by form controls are demonstrated: (a) Normal style (Forms!FormName!ControlName embedded within SQL string) - This requires use of DoCmd.RunSQL method. In this case, CurrentDb.Execute method if used, would attract error 3061 (Too few parameters. Expected 1). (b) Enclosing form reference within Eval() function embedded within SQL string. This is amenable to use of CurrentDb.Execute method. (c) Fetching form control value via wrapper function embedded within SQL string. This is amenable to use of CurrentDb.Execute method. 2 - Fixing of embedded quotes prior to concatenation. This involves substitution of each occurrence of a given quote by twin occurrence of the same. Two styles are demonstrated: (a) Fixing of embedded single quotes only. While concatenating the result, outer single quotes are to be used as per normal practice. (b) Fixing of embedded double quotes only. While concatenating the result, outer double quotes are adequate (outer single quotes are not to be used). Note: When fixing embedded quotes, only one type (either single or double - Not Both) is to be fixed as per 2 (a) or 2 (b). Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/FixMixedQuotes.zip |
||||
| Form_A2K10_MultiValueFields | Form | 148K | 08 Nov 2011 at 10:40am | |
|
Form_A2K10_MultiValueFields Explanatory Notes This sample db demonstrates handling of multi-value fields (MVF) on access forms. Three aspects are demonstrated as follows: 1 - Adding / Replacement of MVF value elements in target subform as per source subform. 2 - Applying Form Filter on multi-value field as per reference multi-value field. 3 - Sorting by multi-value field (bound column or displayed column as desired) Version: accdb file format developed on Access 2010 References: Nothing extra Download File >>: uploads/37/Form_A2K10_MultiValueFields.zip |
||||
| Form_A2K7_Attachments | Form | 77K | 25 Feb 2010 at 5:17am | |
|
Form_A2K7_Attachments Explanatory Notes This sample db demonstrates a user friendly approach for handling attachments in Access 2K7, without invoking the dialog box for attachment management. As the user navigates to a given record on the sample form, names of documents held by attachment type field get displayed in a list box. Simultaneously, total count of attached documents gets displayed adjacent to the attachment control. Appropriate command buttons provide following features: (a) Insertion of new documents: On clicking this command button, file open dialog box gets displayed, enabling selection of one or more files, which get added to the attachment field. Simultaneously the status of list box as well as attachment counter text box gets updated. (b) Removal of attached documents: Based upon selection of one or more (or all) documents in the list box, the selected items get removed from the attachment field. Simultaneously the status of list box as well as attachment counter text box gets updated. (c) Extraction of source files from attached documents: Based upon selection of one or more documents in the list box, source files for the selected items get extracted into a temporary folder named ZZZTemp. This folder gets created if missing and is placed in the parent folder holding the db. The status of extracted documents gets displayed in a list box adjacent to the one showing attached documents. Note: Even if the temporary folder contains large number of extracted files pertaining to various records, only those relevant to current record get displayed in the list box. (d) Deletion of extracted files: Based upon selection of one or more (or all) extracted files in the list box, the selected items get deleted from the temporary folder. Simultaneously the status of list box gets updated. (e) Opening of extracted file: Based upon selection in the list box, the extracted file gets opened in its parent application. Version: Access 2K7 accdb File Format Reference: (a) MS Office 12.0 Access database engine Object Library (b) Microsoft Office 12.0 Object Library (c) Microsoft Scripting RunTime Download >>: uploads/37/Form_A2K7_Attachments.zip |
||||
| Form_AnimatedWavesAndShapes | Form | 96K | 12 Jan 2009 at 12:19am | |
|
Form_AnimatedWavesAndShapes Sample Db - Explanatory Notes This sample db demonstrates programmatic animation of waveforms and nested shapes. Five styles of waveforms are covered (The user can select the speed of animation e.g. Low / Medium / High): (1) Spiral - Simple (2) Spiral - Oscillating X axis (3) Sine wave - Three components in series (4) Sine wave - Three components in phase (5) Sine wave - Three phase (typical of electric power supply) Nested shapes cover various combinations of controls radiating outwards within a given box. Animation speed continues to cycle through normal / fast / faster. The size of display can be altered by clicking anywhere within the outermost rectangle. Bottom right corner of display area shifts to the clicked location while the top left corner remains constant. References: Nothing extra Version - Access 2000 File Format Download >>: uploads/37/Form_AnimatedWavesAndShapes.zip |
||||
| Form_CascadedCombosOnDatasheets | Form | 54K | 29 Mar 2011 at 12:07am | |
|
Form_CascadedCombosOnDatasheets (Updated On: 07-Apr-2011) Explanatory Notes In a typical arrangement of cascaded bound combo boxes, the first column (bound one - typically the foreign key value) of slave combo box is hidden. As a result, use of filtered row source for the slave combo box on a datasheet or continuous form suffers from the limitation that whenever its row source gets altered as a result of changed value held by master combo box, the displayed values for this column in other records disappear - if the existing real values do not feature in the bound column of lookup list. In order to overcome the problem of blank display in slave combo, the traditional method usually involves one of the following: (a) Placement of a bound text box on top of slave combo box. This technique is applicable only to a continuous form (not feasible in datasheet). (b) Always retain all items in the look up list, ensuring that only those matching master combo's current value are selectable and are placed at top of dynamic sort order. This method suffers from the drawback that the user gets burdened with a bulky look up list, carrying redundant items. This sample db demonstrates an entirely new approach for smooth management of cascaded bound combo boxes on a datasheet or continuous form, without suffering from any of the limitations associated with either of the conventional alternatives mentioned above. There is no need to place any overlapping control on slave combo or to display redundant items in its look up list. This is achieved by binding the slave combo to display field of look up table and storing the foreign key value (fetched through this combo) in a hidden field / column. While such an arrangement exposes the displayable look up field to potential edit action in user interface, care is taken that in reality there is no interference to the values stored in the look up table. User selection in slave combo is permitted only after having done so for master combo. As long as the master combo is blank, the slave remains locked and highlighted in color. Although the sample demonstrates the arrangement for a datasheet form, the solution is equally applicable to continuous form view. Note (For update of 07-Apr-2011): A blank dummy record has been placed in the look up table so as to serve as a transient place holder. This way, the need to perform form's undo action on a freshly dirtied new record gets eliminated, thereby preventing skipping of autonumbers as encountered in earlier version. Form F_Sub, when opened, looks for the existence of blank dummy record in look up table and if not found, inserts the same. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Form_CascadedCombosOnDatasheets.zip |
||||
| Form_ComboAndListLoopedLookUp.mdb | Form | 40K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:39pm |
|
The sample db demonstrates a simulated combo box where the selected item is always displayed in the middle row of drop down list. A list box acts as the drop down list, while a text box combined with command button is used in lieu of combo box. This feature can be useful where the user desires to see at a glance, the available choices just before the selected item as well, without having to scroll back. (In a normal combo box, selected item appears at the very top, hiding the available choices prior to it). The drop down list has a loop feature as well, so that even if the selected item happens to be at the very end, the display is extended by continuing with first few items after the last item (or last few items before the first item), giving the effect of an endless display. Auto-expand and Not-In-List features (permitting the user to add new items if desired) are also provided. During auto-expand action, matching selection continues to occupy the middle row of drop down list. Note: (1) If Not-In-List feature is to be disabled, set form level global constant named ForceInList to "N". (2) This sample has 9 displayable rows in the list box. If this value is required to be changed in view of different combination of height and font for the list box, value of public constant named RowsMax in general module should be modified suitably. (3) Stand-alone list box with endless looped list, always positioning the selected item at the middle row, is also displayed.Access Versions 2K/XP/2K3 (Access 2K File Format) Download Access 2K/XP/2K3: uploads/37/Form_ComboAndListLoopedLookUp.zip |
||||
| Form_ComboCancelDropDnFirstClick.mdb | Form | 20K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:37pm |
|
This sample db demonstrates canceling of drop down action for combo box on the first click (only in datasheet view - if there is already an existing value). A warning message notifies that from next click onwards, drop down list will become active. This feature is meant to prevent inadvertent changes in combo box column, while in datasheet view. Toggling of subform between datasheet and form views is also demonstrated.Download Access 2K/XP/2K3/2K7: uploads/37/Form_ComboCancelDropDnFirstClick.zip |
||||
| Form_CommonMouseMoveFunction.mdb | Form | 32K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:35pm |
|
This sample db demonstrates common functions for MouseMove, GotFocus and LostFocus over multiple controls on a form, for temporarily highlighting the control having mouse over, apart from special highlight for control actually having focus. MouseMove over a given control does not necessarily imply that the control in question is the active control. This rules out the use of an omnibus function based upon ActiveControl. Instead, it becomes necessary that even if a common function of generic nature is used for all controls, name of each individual control making the call has to be passed as an argument. Evidently, conventional approach in calling such a function from MouseMove events of multiple controls involves tedious work by the developer. Repetitive entries of function name (with control name as argument) are needed either in VBA code in MouseMove event for each of the controls in question, or directly as similar entries against OnMouseMove property on Event tab in properties dialog box of each such control. An interesting alternative has been demonstrated here that makes the whole process remarkably simpler, overcoming the above drawbacks. As the form loads, OnMouseMove event property of all text/combo boxes and labels gets set to function Fn_MMove(), correctly passing the name of control as argument in each case. Similar action takes place for OnGotFocus and OnLostFocus properties, loading functions Fn_GotFocus()and Fn_LostFocus() respectively, correctly passing the name of control as argument in each case. Mouse move over text boxes, combo boxes and labels leads to following response (controls with transparent back style are kept exempt from this behavior, presuming that the user would like such controls to remain steady): (a) Back color for text or combo box becomes pink while that for label becomes golden. (b) Special effect - The control appears raised. (c) Simultaneously, previously highlighted control, if any, reverts to its original state of back color and special effect. This is done by calling subroutine named P_ClearHighLightPrevControl() in Fn_MMove() as well as MouseMove event of command button named CmdClose. (d) Control having focus is always highlighted in green. It over-rules any highlight that might result from mouse move. For example, if a text box not having focus is currently highlighted in pink because of mouse move and the user sets focus on that text box either by clicking or tabbing, it gets highlighted in green. Note: (a) Particulars of previously highlighted control are stored in form level global variables. Whenever function Fn_MMove() is called, original status of the control in question gets stored in these variables, before proceeding with the task of temporary highlighting. (b) Form's MouseMove event has the limitation that it fires only in the dead area of form window (where form canvas is absent). For this reason, assured restoration of highlighted control to its original status takes place when the next control gets highlighted via MouseMove. (c) If it is desired to restore currently highlighted control to its original status, without having to highlight another one, simply hover the mouse over the button captioned "Close". Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format) Download Access 2K/XP/2K3/2K7: uploads/37/Form_CommonMouseMoveFunction.zip |
||||
| Form_ContinuousCopyPasteColumns.mdb | Form | 43K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:34pm |
|
This sample db demonstrates selection of a column in source subform and updating the selected target column in destination subform accordingly. Two styles are covered: (a) Full Column. (b) Part Column. Full Column Mode: (a) In full column mode, dbl clicking anywhere in the desired column in source subform selects the entire column. It is a simulated selection, highlighting the column in light blue color. (b) Similarly, dbl clicking anywhere in the desired column in destination subform selects the entire column, highlighting it in yellow color. (c) On clicking the copy/paste command button on parent form, contents of destination column get updated as per the source column. In confirmation of completion of this process, the color of destination column changes to orange. Part-Column Mode: (a) In part-column mode, dbl clicking on any cell in the desired column in source subform selects the cell as start of data block. Dbl clicking any other cell (as end cell) in the same column results in simulated selection of all cells from start cell to end cell. This block of cells gets highlighted in light blue color. (b) Dbl clicking any cell in the desired column in destination subform identifies that cell as starting point for target data block. This cell gets highlighted in yellow color. (c) On clicking the copy/paste command button on parent form, contents of destination data block get updated as per the source data block. In confirmation of completion of this process, the color of destination data block changes to orange.Download Access 2K/XP/2K3/2K7: uploads/37/Form_ContinuousCopyPasteColumns.zip |
||||
| Form_ContinuousSimulated | Form | 113K | 06 Oct 2011 at 7:38am | |
|
Form_ContinuousSimulated Explanatory Notes This sample db demonstrates a simulated continuous form having unbound controls, facilitating row-wise display of unlimited colors. It displays 12 records at a time. Full coverage of data is provided via suitable navigation buttons. Apart from being able to edit the records, the user can add new records or delete the current record by clicking appropriate command buttons. In addition, two alternative modes for search have been provided: (a) By record number or (b) By ID number. All these actions are feasible directly on the form. Current record remains identified by special highlight in first column. First two columns (locked) display the record number and TrainID. Next two columns, holding TrainCode and ColorValue, are editable. Last column serves as ColorStrip, each row displaying the color represented by ColorValue in the previous column. There is two way synchronization between ColorValue and ColorStrip. Any value entered in the former gets reflected as corresponding color in the latter. On the other hand, double click on ColorStrip invokes the color dialog box, where the user has unlimited choice and the color finally selected gets displayed in the color strip. Simultaneously, appropriate value gets assigned in ColorValue column. Note: The modules for color dialog box have been taken from Access 2002 Desktop Developer's Handbook. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Form_ContinuousSimulated.zip |
||||
| Form_ControlDefaultValues | Form | 82K | 21 Mar 2011 at 11:57pm | |
|
Form_ControlDefaultValues Explanatory Notes This sample db demonstrates programmatic setting of default values for form controls, covering different data types (Date, Text and Number). Two methods are illustrated as follows: 1 - Concatenation of intended default value into a string assigned as control's default value property. 2 - Generation of intended default value through embedded reference within the string assigned as control's default value property. For each of the above styles, two alternative modes are demonstrated as follows: (a) Set defaults as per current record (On editing or double clicking pertinent controls). (b) Set defaults as per last record (On editing or double clicking pertinent controls - only if the cursor is on last record). Double click on first column (ID) clears default values for all controls. At each stage, the latest record whose values have been used for setting up the defaults, gets highlighted in distinct color. Simultaneously, the new record is highlighted in similar color so as to provide visual correlation. If the current record happens to be other than these two records, it gets highlighted in a separate color. For implementing style 1, intended default values are processed through a user defined function so as to take care of appropriate enclosing delimiters for date and text data types, apart from fixing embedded quotes (if any) in text type values. In addition, date type values are properly formatted before concatenation so as to ensure consistent results, despite differing regional settings for short date format. This approach is preferred over the oft adopted simple expedient of merely enclosing the intended default values within outer quotes, irrespective of the data type (as in such a case, it leads to a default value that is not a true live date or number, though the aberration gets masked in case of bound controls). Style 2 mentioned above, represents an entirely new approach. It provides appropriate data type in case of dates or numbers, at the same time eliminating the need for worrying about delimiters for date or text type data. Moreover, there is no need for fixing embedded quotes in text type data or for pre-formatting of dates to take care of local regional settings for short date format. This is achieved through an embedded reference within the string assigned as default value. In this context, form level public collection of control values is utilized. Note: It would have been much simpler to use embedded reference to the control for setting its default value. However, it is observed that such a technique works only if the reference happens to be to a control other than the one whose default value is being set. For example: MyControl.DefaultValue = "[SomeOtherControl]" Version: Access 2000 file format. References: Nothing Extra. Download >>: uploads/37/Form_ControlDefaultValues.zip |
||||
| Form_ControlGroups | Form | 93K | 17 Nov 2008 at 8:12pm | |
|
Form_ControlGroups Sample Db - Explanatory Notes This sample db demonstrates handling of form controls in distinct groups in such a way that actions meant for a given group get performed across all controls belonging to that group, without having to process any loop for tackling individual controls. This functionality is achieved via class module named C_ControlGrps. Grouped controls get assigned to a collection, as instances of this class. Following styles of grouping are shown - covering two distinct groups in each case: (a) Permanent grouping via control's tag value assigned at design stage. (b) Dynamic grouping allowing the user to add / remove controls to any of the groups as desired, at run time. For current illustration, following actions performed on the groups at run time, are demonstrated: (a) Manipulation of default values group-wise. (b) Locking / unlocking of controls group-wise. (c) Restricting tabbing to a particular group as desired. (d) Hiding / un-hiding of groups as desired. (e) Manipulation of back colors group-wise, via slider bars for R/G/B color components. (f) Special effects - This involves a continuous cycle of smooth transition of shades for the back color, interspersed with periodic swapping of back colors between the two groups. Note: (a) Although the sample demonstration covers two distinct groups of controls, the class C_ControlGrps permits creation of as many separate groups of controls as required. Controls belonging to a particular group need not be contiguous. (b) Simultaneous action across all controls in a given group, without having to perform control-wise looping, is made feasible by invoking relevant events within the class module. Reference required - Nothing extra. Version - Access 2000 file format Download >> uploads/37/Form_ControlGroups.zip |
||||
| Form_ContWithCascadedCombo.mdb | Form | 37K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:30pm |
|
This sample db demonstrates management of cascaded combo boxes on a continuous form, where first column (bound one) is hidden. Normally, use of filtered row source for a combo box on a continuous form suffers from the limitation that whenever the row source is altered for any fresh entry, the displayed values for this column in other records disappear - if the existing real values do not feature in the bound column of lookup list. The above limitation is overcome by using a union query as the row source. Lookup list based upon this query has three parts: (a) All values relevant to filtered row source are displayed at top, showing "OK" in the Validity column. (b) A separator row (c) Other values (not matching the filter criteria) are displayed at the bottom. Validity column for such values displays "XX" If the user attempts to select a row in lookup list, not having "OK" in the validity column, it is rejected with a suitable message asking for selection to be made only from OK items. Simultaneously, the combo box gets highlighted in color. In order to prevent un-matched values, if the value in master combo (CboClient in this sample) is altered, existing value (if any) in the slave combo (CboContact in this sample) gets cleared (unless it happens to be valid in the updated row source as well). If the user attempts to enter the slave combo while the master combo is blank, the slave gets locked and highlighted in color. A message box comes up, asking master value to be entered first and the focus gets sifted to master combo. Note - Implementation of highlighting colors is found prompter and more effective in Access 2K3 as compared to Access 2K.Download Access 2K/XP/2K3/2K7: uploads/37/Form_ContWithCascadedCombo.zip |
||||
| Form_CustomAutoComplete | Form | 71K | 20 Aug 2009 at 12:38am | |
|
Form_CustomAutoComplete Explanatory Notes This sample db demonstrates customized auto-completion of data entry as per area of interest. Based upon desired profile (i.e. area of interest, e.g. Aviation, InfoTech, Medical etc) as selected in the combo box, reference phrases meant to be used for auto-completion get displayed in the subform at left. The auto-complete feature comes into play as soon as typing commences on a new line in the text box in subform at right. Appropriate portion of text from closest match in reference phrases gets displayed next to the cursor, shaded in selected state, as suggested continuation. If the user wishes to accept the suggested continuation, pressing right arrow clears the shading, converting it into accepted text. On the other hand, if the user continues typing further, the shaded portion of suggested continuation continues to shrink correspondingly, so long as there is no deviation from reference text. As soon as there is a deviation, new suggested text, as per next closest match with reference phrases, gets displayed, duly shaded - and so on. At all stages, currently matching reference text is shown highlighted in special color, on the subform at left. If no match is found within the selected profile, a search for matching reference text in other profiles is conducted. If found, the particular phrase is used for auto-completion. Simultaneously, its linkage to current profile is implemented. If desired, the user can also type fresh reference text directly in the subform at left. Records for reference text, freshly added during current session under a given profile get highlighted in special color. If newly entered data contains completely new sentences (marked by line breaks) not present in reference table, these are added to this table after obtaining user's consent via form's BeforeUpdate event. Simultaneously, appropriate linkage to current profile is established by inserting necessary entries in bridging table T_Link (many to many relationship). Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Form_CustomAutoComplete.zip |
||||
| Form_CustomSeriesByDefaultProperty | Form | 46K | 02 Jul 2009 at 6:05am | |
|
Form_CustomSeriesByDefaultProperty Updated On 28-Jul-2012 (Plain number series added) Explanatory Notes This sample db demonstrates a novel approach involving generation of value sequence via a common function embedded as default value property of the bound control carrying such values. As the data entry progresses, the default value keeps incrementing as required, without having to undertake any special manipulation via form's current event. Moreover, it is not necessary to use even other form events like Dirty / Before or After Insert / Before or After Update etc. Two styles are covered: 1 - Plain Number Series. 2 - Alpha-Numeric Series (Number series with text prefix) The prefix is selected via combo box on the main form. Whenever, a new prefix is selected, the newly displayed default value on the subform gets set 1 higher than the existing highest in alpha-numeric sequence pertaining to the selected prefix. If there is no existing entry with the given prefix, the default value gets set to prefix followed by 000001. Type of prefix can be selected via the option group as follows: (a) Prefix by combo box selection. (b) Prefix as per current year (yyyy) (c) Prefix as per current year month (yyyymm) Version: Access 2000 file format. Reference: Nothing extra. Download >>: uploads/37/Form_CustomSeriesByDefaultProperty.zip |
||||
| Form_DatasheetHighLightStyles | Form | 45K | 04 May 2011 at 12:45pm | |
|
Form_DatasheetHighLightStyles Explanatory Notes This sample db demonstrates an interesting approach to conditional highlighting of datasheet forms. It is remarkably generic, using a set of functions in general module and does not depend upon any primary key field. In fact, no field name or form name is used and hardly any code is needed in the form module (except for style (b) below). Following styles for highlighting the records are covered: (a) Highlight current record, First record, new record. (b) Flag desired row or rows by dbl clicking (Dbl click again to remove the flag). Once a flag is set for a record, and unless it is removed subsequently by user action (another dbl click on flagged record), it remains in force for current database session, even if the form is closed and then re-opened. (c) Highlight odd rows. (d) Highlight even rows. (e) Highlight every third row. (f) Highlight top - mid - last row. (g) Highlight top 2 - mid 2 - last 2 rows. Note: The solution is equally applicable to continuous forms. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Form_DatasheetHighLightStyles.zip |
||||
| Form_DragDropResizeArrange | Form | 314K | 27 Jun 2012 at 3:52am | |
|
Form_DragDropResizeArrange Explanatory Notes: This sample db demonstrates various methods for dragging / re-sizing / arranging of controls by user action on access form at run time. In each style, before closing the form, user has the option to save latest status of each control regarding its position and size. On next opening of form, controls will get depicted as per such saved status. For a control to become eligible for drag and/or resize action, its tag property should include the words Drag and/or ReSize. At any stage, the user can undo the last action by clicking the UnDo command button. As and when desired, all such controls can be made to revert back to their original design settings by clicking the command button at bottom left. This command button also serves to toggle between design settings and last saved user settings. Following alternative styles are demonstrated: 1 - Style A - It covers the following features: 1.1 - Drag / re-size by direct use of control's mouse events via class C_ControlsDDR. This class has an interesting feature: WithEvents functionality for different types of controls has been incorporated into a single class. 1.2 - Grouped controls manipulation. As the user drags the mouse (with left button in pressed state) around a set of controls, a red rectangle gets drawn accordingly and all controls falling within this rectangle are treated as a group. This group of controls can then be subjected to any of the following actions as desired: (a) Drag the group of controls. (b) Align the grouped controls Left / Right / Top / Bottom. (c) ReSize the group as per Widest / Narrowest / Tallest / Shortest control. (d) Make horizontal or vertical spacing amongst grouped controls equal. 1.3 - All controls enclosed by red rectangle drawn by the user (para 1.2 above) become subject to grouped action, even if not carrying the words Drag or ReSize in their tag values. For controls required to be kept immune to grouped action, the tag value should carry the word Exclude. 2 - Style B: 2.1 - It demonstrates induced manipulation of controls via drag/re-size handles - without depending upon mouse events of these controls. Three types of controls are illustrated, i.e. web browser, subform and image. Out of these, web browser and subform control do not have any mouse event of their own. 2.2 - As the user clicks upon the target control, drag handle appears at top left while resize handle appears at bottom right. Dragging on top left handle (with left button pressed) moves the control while similar action on resize handle leads to resizing. 3 - Style C: 3.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of controls without depending upon any of their event. While three types of controls are illustrated, i.e. web browser, subform and image, this approach is universally applicable to all types of controls (including lines which do not have any event at all). 3.2 - As the user clicks anywhere on blank space of detail section near top left of target control, it is accompanied by automatic detection of that control. In confirmation, a red marker appears at top left of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of control identified by the marker. 3.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target control, it is accompanied by automatic detection of that control. In confirmation, a red marker appears at bottom right of the control. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing of control identified by the marker. 4 - Style D: 4.1 - It demonstrates a completely non-intrusive approach towards induced manipulation of lines (lines do not have any event at all). 4.2 - As the user clicks anywhere on blank space of detail section near top left of target line, it is accompanied by automatic detection of that line. In confirmation, a red marker appears at top left of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced movement of line identified by the marker. 4.3 - Similarly, when the user clicks anywhere on blank space of detail section near bottom right of target line, it is accompanied by automatic detection of that line. In confirmation, a red marker appears at bottom right of the line. Dragging the mouse (with left button pressed) on form detail section leads to induced resizing/rotation of line identified by the marker. 4.4 - Rotating action on a line can be carried out in either direction (clock-wise or anti clock-wise). There is no limit to the angle of rotation. So long as left mouse button is kept pressed, the user can continue rotating the line in complete circles in either direction. Note: It is observed that combo box and list box do not behave in a completely satisfactory manner while being dragged or re-sized through direct use of mouse events of these controls. There is no such problem with induced drag/re-size approach, as demonstrated in styles B to D. Interestingly, styles C & D are completely non-intrusive and do not depend upon any event of target control. Version: Access 2000 file format. References: (a) DAO 3.6, (b) Microsoft Internet Controls Note: Reference (b) gets established automatically when web browser control is inserted in a form Download File >>: uploads/37/Form_DragDropResizeArrange.zip |
||||
| Form_DsContSetDisplayPos | Form | 57K | 06 Oct 2010 at 10:03am | |
|
Form_DsContSetDisplayPos (Updated On: 02-Nov-2010) Explanatory Notes This sample db demonstrates positioning of selected row or block of rows in desired manner in the display window. It is applicable to continuous as well as datasheet forms. Two styles are covered as follows: (a) Returning back to existing position of current record in display window, after requery. (b) Option to set start position of target record at top, middle or bottom of display window. Both styles are equally applicable to continuous form as well as datasheet. As an added feature, datasheet style navigation across records in a continuous form, using up and down arrow keys is incorporated in style (a). Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Form_DsContSetDisplayPos.zip |
||||
| Form_DynamicGrpLevels | Form | 44K | Access 2000 File Format | 09 Jul 2008 at 10:28pm |
|
This sample db demonstrates incorporation of dynamic group levels and hiding of duplicates on a form in a manner similar to that for reports. Group headers and group footers as well as overall footer (like report footer) are also included. The demonstration covers a book list. Default outermost group level (i.e. first group level) is based upon Topic, while the inner group level is based upon author. The set of headers and footers for topic (first level group) are highlighted in matching color. Similarly the headers and footers for author (second level group) are highlighted in matching color different from that of first group. Overall footer is highlighted in another color. Group footers show the total number of books for the pertinent groups while the overall footer shows the grand total. Double click on either of the first two columns (Topic or Author) toggles the grouping level. For example if Topic is first group and Author is second group, the arrangement gets reversed: Author becomes the first group while Topic becomes the second group. Simultaneously, the order of columns also gets re-adjusted so that the first column always displays the first group level. The demonstration covers continuous form view as well as datasheet view. Toggling between datasheet and continuous form views (in the subform) is carried out by clicking a command button on the parent form. Note: (a) RecordSource for the form is made up of union queries and it is non-editable. (b) Visible property for column heading labels (located in detail section) meant for datasheet view is set to No. This does not hinder their display in datasheet view. At the same time, such labels remain hidden in continuous form view, where the purpose is met by labels located in form's header section. (c) Conditional formatting of controls, as required, is carried out via subroutine P_SetFormat() in subform's module. Download Access 2000 File Format: uploads/37/Form_DynamicGrpLevels.zip |
||||
| Form_DynamicSizingOfSubform | Form | 98K | 12 Dec 2008 at 5:20pm | |
|
Form_DynamicSizingOfSubform Sample Db - Explanatory Notes This sample db demonstrates run time manipulation of subform control height. It covers both views for the subform, i.e. continuous form as well as datasheet. The views can be toggled from one to the other by clicking a command button. Depending upon the layout for a specific form, it is preferable to specify the maximum number of rows upto which the subform height should expand. This figure can be entered in text box named TxtMaxLines. Default value for max lines is set at 6. While stepping through the records on main form, if the records in linked subform happen to exceed MaxLines, further increase in the height of subform is kept in abeyance. At this stage, vertical scroll bar is inserted. As and when the subform records are again within limits, the vertical scroll bar is removed and the height of subform control adjusts suitably. In case of datasheet view, care has been taken to dynamically adjust the column widths so that no awkward blank space is visible at right in lieu of scroll bar when it is not needed. Two alternative ways are available for navigating through records on the main form. The user can either use the normal navigation buttons or the unbound combo box for jumping to any desired record. The combo box is suitably synchronized so that even while using normal navigation buttons, the value displayed in combo box matches the current record. Reference required - Nothing extra. Version - Access 2000 file format Download File >>: uploads/37/Form_DynamicSizingOfSubform.zip |
||||
| Form_FreezeDataSheetColumnsAndSize.mdb | Form | 43K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:26pm |
|
This sample db demonstrates two styles for datasheets: (a) Freezing desired columns (b) Forcing fixed widths for desired columns References: DAO 3.6Download Access 2K/XP/2K3/2K7: uploads/37/Form_FreezeDataSheetColumnsAndSize.zip |
||||
| Form_GanttChart | Form | 76K | 09 Feb 2010 at 6:20am | |
|
Form_GanttChart Explanatory Notes This sample db demonstrates depiction of Gantt charts on forms. Two styles are covered: (a) Style A: Using Bound controls (b) Style B: Using Unbound controls Style A is editable. As the user enters start and end dates for various tasks, the chart string gets updated automatically. Simultaneously, the month / year markers at top get updated, duly taking into account the overall time span from start of earliest task to finish of last task. Version: Access 2000 File Format Reference: DAO 3.6 Download >>: uploads/37/Form_GanttChart.zip |
||||
| Form_HighLightUpdatedFields.mdb | Form | 27K | Access 2K/XP/2K3 | 09 Jul 2008 at 10:25pm |
|
This sample db enables the user to identify at a glance as to which fields in which records have been updated in the current data entry session. The most demanding scenario is that of a datasheet form. The objective is achieved through run time manipulation of format conditions. The db incorporates following features: (a) On opening the form, all display is normal - no highlights yet. (b) Fresh records added in current session get highlighted in light blue back color, with bold font in black forecolor. (c) Any cell (i.e. given field on given record) updated for the first time in current session, gets highlighted in light orange back color, with bold font in dark red forecolor. (d) Any cell (i.e. given field on given record) updated more than once in current session, gets highlighted in light pink back color, with bold font in dark red forecolor. (e) At any stage, if so desired, all highlights can be removed (signifying the start of next session), by clicking the command button captioned "Remove Highlights" (f) On closing the form, it starts with a clean slate (i.e. no highlights), when opened next time. Fresh highlights take effect progressively, as and when fields are updated in the new session. Download Access 2K/XP/2K3: uploads/37/Form_HighLightUpdatedFields.zip |
||||
| Form_LastViewedAndCurRecToggle | Form | 41K | 12 Sep 2011 at 1:33pm | |
|
Form_LastViewedAndCurRecToggle Explanatory Notes This sample db demonstrates toggling between last viewed record and the current record on subform in datasheet view, via a command button on parent form. Navigation through subform records is conducted through a combo box on the parent form. Alternatively, the user can click on the desired record directly on the subform. The current record gets highlighted in light green while the last viewed record is highlighted in light grey. The user can also flag one or more records by double clicking any of the columns on desired record. Last column of records flagged in this manner gets highlighted in pink color. These flags remain in force for the current session of access, even if the form is closed and then re-opened. Repeat double click on a flagged record will remove the flag. To remove all flags, command button captioned "Clear All Flags" can be clicked. Version: Access 2000 file format. References: Nothing extra. Download File >>: uploads/37/Form_LastViewedAndCurRecToggle.zip |
||||
| Form_LogIn.mdb | Form | 43K | Access 2K/XP/2K3/2K7 | 09 Jul 2008 at 10:23pm |
|
This sample db demonstrates a log in form with the following options: (a) As Administrator. (b) As User. Max three attempts are allowed. If the correct combination of user ID and password are not supplied within the max allowed attempts, the database closes down. If this happens in non-administrative mode, user's account gets blocked. To start with, the text boxes for entering user Id & password have light blue back color. If the first attempt fails, a message box regarding invalid combination of user ID and password comes up. Simultaneously, the back color of text boxes (user ID & password) changes to light orange, accompanied by a label display (in light orange back color) notifying that it is the second attempt. If the second attempt fails, a message box regarding invalid combination of user ID and password comes up. Simultaneously, the back color of text boxes (user ID & password) changes to light pink, accompanied by a label display (in light pink back color) notifying that it is the third attempt. If the third attempt also fails, the database closes down after displaying appropriate message box. If this happens in non-administrative mode, the user is notified that the particular account stands blocked and the administrator should be contacted. Simultaneously, the following entries get recorded against this user's account in the relevant table: (a) Field named Blocked (Yes/No type) gets set to True. (b) Current date / time gets entered in field named DtLastBlocked. (c) Cumulative count of such blockages for this user is entered in field named BlockCount. This helps identify repeated defaulters if any. If a user with blocked account tries to log in without clearance from the administrator, he/she is faced with the following as soon as the user Id is entered: (a) Text box for entering password gets locked. (b) Command button captioned "OK" gets disabled. (c) Back color of both text boxes changes to light pink. (d) A label with light pink back color gets displayed, notifying that the account stands blocked. (e) Focus moves to command button captioned "Cancel". Clicking this command button results in closure of database. On successful log in, appropriate switchboard form (Administrator or User) gets opened. Particulars of User_ID, LogStatus and ID (PK) get stored in hidden text boxes on these forms - for further use by the developer as found necessary. Facility for change of password is available on both the switchboards (Adm / User). The Adm switchboard provides the additional facility for blocking / unblocking of user accounts. Note - If the user tries to close the log in form prematurely, by clicking the close box at top right of the form, it results in closure of database. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format) Download Access 2K/XP/2K3/2K7: uploads/37/Form_LogIn.zip |
||||
| Form_MoveTabControl | Form | 23K | 23 Mar 2009 at 8:47pm | |
|
Form_MoveTabControl Sample Db - Explanatory Notes This sample db demonstrates movement of tab control to any desired location, along with all its controls located on various pages. Conventional approach to moving a control does not suit a tab control, as the controls contained in it stay put at their current locations. As a result, one can land up with a badly deformed tab control, in an effort not to let go of any control. A workaround for ensuring correct movement of tab control, along with all its controls, without resorting to custom built collection or array, is demonstrated in this sample db. Version: Access 2000 File Format Reference required: Nothing Extra Download File >>: uploads/37/Form_MoveTabControl.zip |
||||
| Form_MSCalGetDtByYrMnChange | Form | 32K | 07 Nov 2010 at 12:59pm | |
|
Form_MSCalGetDtByYrMnChange Explanatory Notes In MS Calendar Control, when the user selects a new value for the month or year, corresponding date is not returned by the control unless day of the month is also clicked. This sample db demonstrates a way to make the Calendar Control respond directly to change of month or year. Two styles are covered as follows: (a) Hyper Mode - Date returned by calendar control responds directly to month/year change by user. (b) Normal Mode - New month/year selection has to be followed by clicking day of month so as to get appropriate date. The user can select either of the above styles via an option group adjacent to the calendar control. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Form_MSCalGetDtByYrMnChange.zip |
||||
| Form_MsgBoxAutoClick.mdb | Form | 24K | Access XP (in A2000 format) | 09 Jul 2008 at 10:21pm |
|
This sample db demonstrates automatic closing of message box after a specified time delay (as desired), if the user fails to respond in the mean time. Two methods are covered, one using windows API and the other based upon SendKeys. Note - Windows API method is the preferred one (SendKeys method is included mainly for academic interest). Download Access XP (in A2000 format): uploads/37/Form_MsgBoxAutoClick.zip |
||||
| Form_MtoMViaCheckBoxArray | Form | 93K | 28 Dec 2010 at 12:31pm | |
|
Form_MtoMViaCheckBoxArray Explanatory Notes This sample db demonstrates use of check box array for populating a junction table serving many to many relationship between clients and training courses. Data is stored in normalized manner in tables T_Clients, T_Courses and T_ClientCourses. No temporary table is needed. Two styles of user interface are demonstrated: 1 - Normal Style (Using Virtual Records Matrix): ------------------------------------------------- 1.1 - Client particulars are displayed in the parent form while all available course options are displayed in a subform, as a matrix of virtual new records. 1.2 - As and when the user clicks a check box into selected state against desired course in the subform, the row in question gets converted into a freshly entered actual record in the junction table. 1.3 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted. This is accompanied by a virtual new record getting displayed in lieu of the recently deleted actual record (At all times, the subform matrix continues to display all available course options). 1.4 - Total number of courses opted for current client as well as the grand total for all clients are also displayed in the parent form 2 - Spreadsheet Style - Datasheet View (Using Crosstab Query): ----------------------------------------------------------------- 2.1 - Adequate number of check boxes along with child labels are inserted in the form at design stage. This is a one time exercise, using the subroutine P_AddControls(). 2.2 - Each check box column of this datasheet form (used as a subform) represents an individual course. A crosstab query, based upon Cartesian join between tables T_Clients and T_Courses, serves as the record source. 2.3 - As and when the user clicks a check box into selected state in desired course column, a record with appropriate values for ClientID and CourseID gets added to the junction table. 2.4 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted. 2.5 - First three columns display client name, list of courses opted and total number of courses opted respectively. These three columns are frozen so as to always remain in view while the user moves across check box columns representing individual courses. 2.6 - Third column depicts total number of courses opted for each client. Bottom cell of this column shows the overall total courses opted for all clients. Overall total is also depicted within parenthesis in header caption for this column, so as to always remain in view despite vertical scrolling. 2.7 - For each course, total number of clients who have opted for it, is depicted within parenthesis in the caption for respective column header, so as to always remain in view despite vertical scrolling. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Form_MtoMViaCheckBoxArray.zip |
||||
| Form_MultiColumn.mdb | Form | 87K | Access 2000 | 09 Jul 2008 at 4:13am |
|
This sample db demonstrates multi-column layout in forms. This could be convenient in situations where only a small number of fields are involved. Adoption of multi-column layout (similar to across and then down layout in multi-column reports), where records are arranged across the screen, can facilitate optimum utilization of viewing area on continuous form or datasheet. Two alternative styles of multi-column display are covered (either style can be selected via an option group): Style 1 - Complete transposition: 1.1 - Field names appear at left, arranged in a vertical strip. Individual records appear across the screen as a series of vertical strips. For example, in a three column layout, there would be three data strips opposite the fieldname strip. 1.2 - If it were a blank form to start with, only the first data strip would be available as a new record (adjacent to field name strip). As soon as data entry in this strip is initiated, a fresh data strip (as a new record) becomes available at the right of current strip. 1.3 - When the last data strip at right is dirtied, a fresh strip of field names along with one blank data strip (representing new record) becomes available below the existing set of strips. Style 2 - Repetition of fields horizontally: 2.1 - The set of columns representing a conventional record gets repeated across the screen. For example, if three fields F1, F2 and F3 are involved and three column layout is desired, the column headers will be like F1 F2 F3 F1 F2 F3 F1 F2 F3 2.2 - To start with, one horizontal strip spanning first set of fields will be available as new record. As soon as data entry in this strip is initiated, a fresh data strip (as a new record) becomes available at the right of current strip, under the next set of fields. 2.3 - When the data strip under last set of field names is dirtied, a fresh blank data strip (representing new record) becomes available below the existing data strip under the first set of field names. For each style, both view are shown (continuous form as well as datasheet). Toggling between the two views takes place on clicking the pertinent command button. In datasheet view, there is certain wobble effect while conditional formatting is updated. No such problem is experienced in continuous form view Data strip representing new record is highlighted in light blue. Blank cells (if any) beyond this new data strip, are colored black, not available for use. Any attempt to enter such forbidden strip deflects the focus back to new record strip. Deletion of records can be carried out by double clicking on the pertinent data strip. Additional details are included in accompanying file Notes.txt Download Access 2000: uploads/37/Form_MultiColumn.zip |
||||
| Form_PivotTableSimulated | Form | 339K | 17 Apr 2012 at 12:45am | |
|
Form_PivotTableSimulated Explanatory Notes Updated On 26-Apr-2012: Cell-wise graded collapse or expansion by double clicking incorporated. This sample db demonstrates a simulated pivot table, displayed on access subform in datasheet view. It is based purely upon native access queries and VBA, getting rid of traditional dependence upon OWC i.e. Office Web Components (slated for deprecation). 2 - Incorporation of features typical of excel pivot table: -------------------------------------------------------- An attempt has been made to incorporate basic features considered typical of an excel pivot table as listed below: 2.1 - Being a datasheet, column headings remain in perpetual view, affording convenient vertical scrolling. 2.2 - Suitable number of grouping columns at left are frozen so as to facilitate convenient horizontal scrolling. 2.3 - Uncluttered display (suppressing duplicate contents in grouping columns). 2.4 - Full choice of filter fields (equivalent to page fields in excel) 2.5 - Appropriate cross tab columns if a field is selected as column field. (Only one field can be selected as column field) 2.6 - Toggling of data orientation between column and row styles as per user choice. 2.7 - Manipulation of ordinal position of columns - as desired by user - by clicking up or down command buttons in field list. 2.8 - Run time insertion of unlimited number of calculated field columns - as desired by user. Calculated field names and the underlying expressions can be edited as and when desired. The results of any such action get displayed promptly in the pivot table. 2.9 - Run time setting / editing of formatting for different data fields - as desired by user. The results of any such action get displayed promptly in the pivot table. 2.10 - Run time hiding / un-hiding of grouping and data fields as desired by user (By clicking Hide check box in fields list). 2.11 - Fields List and Filter Check-List can be hidden un-hidden via command button. The filter list gets displayed only if IsFilter check box against one or more fields is in selected state. 2.12 - If desired by the user, filter field columns get displayed in the pivot table as well. 2.13 - Cell-wise graded collapse / expand action in the pivot table can be performed by double clicking on a column PRIOR to the last one amongst grouping columns. Note (see item 2.5 above): -------------------------- Column field is meant to be the last one amongst grouping fields. In the fields list, when the user selects IsColumn check box, the field in question, if not already the last one, moves to last position amongst non-data fields and gets highlighted in maroon color. If any other field has its IsColumn check box in already selected state, the same becomes de-selected. 3 - Some Interesting Extra Enhancements: ------------------------------------------ While building the simulated pivot table certain interesting enhancements have been incorporated as follows, so as to make it still more versatile and user friendly: 3.1 - Generic Data Source: --------------------------- The arrangement for depicting the pivot table is completely generic. The developer is not required to drag any fields on to the form. All that needs to be done is identification of data source (which can be the name of a table or saved query). This is done conveniently via look-up list on a combo box. On selection of new data source, corresponding pivot table gets displayed via unbound controls on the subform. Adequate number of such controls have been provided so as to suit up to 300 columns. 3.2 - Column-wise auto-expand and auto-collapse: --------------------------------------------------- Step by step sequential auto-expand or auto-collapse of pivot table, column-wise, can be carried out by clicking pertinent command button. (Non-sequential random hiding / un-hiding of pivot table columns can be carried out by clicking Hide check box in fields list). Note: Cell-wise graded collapse or expansion can be done by double click (See item 2.13 above). 3.3 - Flexible Run Time Grouping By Date Type Fields: -------------------------------------------------------- Source data originally entered as simple dates can be depicted in various grouping styles, e.g. ByYear, ByQuarter, ByMonth etc. The user can play with date grouping style, selectable via combo box at bottom right. The results of any such action get displayed promptly in the pivot table. (For this feature to be in force, Calc column against date type field in fields list subform should carry the value "DtGrp") 3.4 - Color Highlights For Pivot Table Rows Depicting Sub-totals: ------------------------------------------------------------------ For added convenience, sub-total rows in pivot table are highlighted in color. Wherever more than one such rows happen to be adjacent, alternate color highlights are provided - for improved legibility. 3.5 - Color Highlights In Fields And Filter List: ----------------------------------------------- In fields list, distinct color highlights are provided for fields identified for hiding, filtering or to serve as column field. In filter check list, excluded items are highlighted. 3.6 - Reminder Content In Outer Columns: ---------------------------------------------------- During vertical scrolling in traditional pivot tables, the topic displayed in outer-most column at left can go out of view, depending upon the range of contents in grouped columns at right. For better convenience, pivot table in this sample db provides for repeat display of outer column content, whenever there is change of content in last but one grouped column. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Form_PivotTableSimulated.zip |
||||
| Form_Resize | Form | 120K | 21 Sep 2010 at 1:33am | |
|
Form_Resize (Updated On: 16-Jun-2011) Explanatory Notes This sample db demonstrates resizing of access forms so as to suit current screen resolution. In addition, user has the option to carry out custom resizing - if desired. Six styles of demo forms are included as follows: (a) Simple controls - all free to float and resize. (b) Simple controls - with certain controls having tag property settings for locking their position and / or size. (c) Combo box and list box. (d) Nested subforms - Continuous. (e) Nested subforms - Datasheet. (f) Tab control having (i) Nested subforms and (ii) Option group. Tag property settings for steering the behavior of individual controls are as follows (more than one setting (separated by ;) can be included in the tag string): (a) LockLeft: The control retains a fixed distance from left edge of the form. (b) LockRight: The control retains a fixed distance from right edge of the form. (c) LockTop: The control retains a fixed distance from top edge of the form. (d) LockBottom: The control retains a fixed distance from bottom edge of the form. (e) LockLeftRight: The control expands in such a manner that its left edge retains the original distance from left edge of the form, while its right edge retains the original distance from right edge of the form. (f) LockTopBottom: The control expands in such a manner that its top edge retains the original distance from top edge of the form, while its bottom edge retains the original distance from bottom edge of the form. (g) LockSize: The control retains its original size. (h) LockWidth: The control retains its original width. (i) LockHeight: The control retains its original height. Demo forms have all been designed for a screen resolution of 800 x 600. On opening any of these forms, it resizes automatically to suit the screen resolution currently in force (if it happens to be different from the designed one). Custom Resizing: If the user wishes to fine tune the displayed size of a given form even further, it can be done by double clicking any blank area on the main form (outermost parent form in case there are subforms). Depending upon user's choice in the resultant pop up form, the form in question will resize up or down by 5 percent. The user is presented with the option to store the custom resized status of the form in question. If accepted, the form will henceforth open at the customized size, irrespective of screen resolution in force. At any stage, the user can alter the default opening style of a form by double clicking on its blank area, and then selecting any of the following options (the intended change takes effect from next opening of the form onwards): (a) Resize to suit current screen resolution - even if custom resizing had been in force so far. (b) Resize to customized zoom factor, irrespective of screen resolution in force. (c) Retain the original design size - irrespective of current status of screen resolution. Note: In this sample db, whenever a demo form is opened, a message box comes up showing the status (e.g. design resolution, current screen resolution, whether the form has been resized as per current screen resolution or whether it conforms to custom zoom level as set by the user). In regular use, such a message on each opening of the form might seem too intrusive. If desired, the opening message can be skipped by de-selecting the check box in main switchboard form (i.e. the start up form). Usage: Form resizing is accomplished via a self contained class named C_ReSizeForm. Import this class along with form F_ZoomMaster into your db. Also import table T_Zoom and clear its contents. Sample code in VBA module of main form (this code is not to be placed in VBA module of a subform) is as follows (in this sample case, the form is originally designed for a screen resolution of 800 x 600): '============================= ' Declarations section Private fm As C_ReSizeForm '-------------------------------------------- Private Sub Form_Open(Cancel As Integer) Set fm = New C_ReSizeForm fm.P_Init Me, 800, 600 ' Note: Above statements should be placed at the very ' beginning (before any other code) in form's open event. ' Caution: Filteration code if any (e.g. Me.Filter = "FilterString" ' and Me.FilterOn = True) MUST NOT be placed in form's ' open event. Form's load event should be used instead. End Sub ' Note: For proper clearance of circular reference ' do not set fm to nothing in any event of real form. ' Let it be taken care of by VBA's garbage collection ' process. (Also see note in mfm's close event in ' class C_ReSizeForm) '============================== Note (For Best Results): (a) AutoResize property of each form and subform should be set to No. (b) For proper effect, independent forms should be displayed in their restored status - not maximized. Statement DoCmd.Restore can be placed in form's Activate event. (c) As an abundant precaution, each label should be independent (Not captive to any other control). (d) For optimum display, preferably use Times New Roman Font. (e) For bulk removal of custom resize settings for all forms, simply delete the contents of table T_Zoom. With this step, all forms (where resize class has been used) on opening, shall automatically resize to suit the screen resolution currently in force. (f) Datasheet forms must have necessary code in the load event, specifying the row height, font height and column widths as applicable at design stage resolution. This is because, any run time manipulation of these properties tends to stick over to next opening, leading to snowballing effect. Sample code in form's load event is given below: '---------------------------------------------- Me.RowHeight = 300 Me.DatasheetFontHeight = 11 Me.Control1.ColumnWidth = 1000 Me.Control2.ColumnWidth = 1500 '---------------------------------------------- Version: Access 2003 file format. References: DAO 3.6 Download >>: uploads/37/Form_ReSize.zip |
||||
| Form_RowNumsWithoutPK | Form | 47K | 14 Nov 2011 at 8:34am | |
|
Form_RowNumsWithoutPK Explanatory Notes This sample db demonstrates generation of row numbers on a form via calculated control. Row numbers get displayed in keeping with the prevailing sort order and do not need any primary key to be available for the purpose. If form's sort order is changed during user interaction, row numbers as displayed, get updated automatically so as to suit the new sort order in force. Version: Access 2000 file format References: Nothing extra Download File >>: uploads/37/Form_RowNumsWithoutPK.zip |
||||
| Form_Search.mdb | Form | 39K | Access XP (A2K format) | 08 Jul 2008 at 11:08pm |
|
This sample db demonstrates search results with following interpretations of values entered in unbound cascaded combo boxes: (a) " ALL" No filter for the field in question. (b) Some specific value Show all records where the field in question has exact match for this value. (c) Some specific value having Wild card prefix and / or suffix. Show all records after applying Like operator with wild cards, for finding matches with the field in question. The results are displayed in a subform and get updated as soon as a new value is entered in any of the combo boxes. It takes advantage of the fact that the outcome of Like operator without wild cards is equivalent to that of = operator. When making a direct entry in the combo box (by typing), if the previous existing value happens to be " ALL", make sure to overwrite the complete contents, including the space prefix.Download Access XP (A2K format): uploads/37/Form_Search.zip |
||||
| Form_SearchAllFields | Form | 71K | 21 Sep 2009 at 12:39am | |
|
Form_SearchAllFields Updated On 13-Jan-2012 Explanatory Notes This sample db demonstrates fast search across whole data source, covering all fields and all records. The search is conducted on all columns in "Across and then Down" style. Based upon space separated search words entered by the user, advanced search capability is provided as follows: (a) Match field values having any of search words. (b) Match field values having all of search words, but not necessarily in the same order as entered in search box. (c) Match field values having all of search words, in the same order as entered in search box. (d) Exact match of field values against full contents of search box.. As the user types into the search text box, equivalent criteria string gets displayed at bottom of form. Simultaneously, the total number of matches found across the data source gets displayed. In addition, all matching fields get highlighted in distinct back color. Various search options are selectable via an option group. Appropriate command buttons enable the user to find the first or next matching record as desired. A special subroutine ensures that the found record gets displayed at mid-position of the subform window. Note: (a) For optimum speed, search across all fields is carried out in a single pass through form's recordset, by using a master criteria string combining the conditions applicable to different fields. (b) This method eliminates the need for concatenated strings combining the fields on each record, prior to execution of search action. (c) Search action takes care of text as well as number type data. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Form_SearchAllFields.zip |
||||
| Form_SearchByMultipleListBoxes | Form | 75K | 18 Aug 2008 at 6:00am | |
|
Description: This sample db demonstrates application of search criteria based upon selections in three multi-select list boxes. For a given list box, the search criteria is deemed True if the field value happens to match any of the selected items. Overall criteria is determined by application of And operator between the outcomes pertaining to different list boxes. For sake of illustration, three different data types are covered by the three list boxes in this sample, namely date, text and number. In case of text type, handling of embedded quotes if any, within the field values, has also been demonstrated. Three different styles of displaying the contents of list boxes have been shown as follows: (a) No cascading - The full list is always under display. (b) Simple cascading (hierarchical) - LstDate controls the contents of LstType and LstSupp, while LstType controls the contents of LstSupp. (c) FreeStyle cascade - Whichever list box happens to be updated, causes filtering of contents of other two list boxes accordingly. For user's convenience, facility for selecting or de-selecting the whole list box has been provided. Three different styles of applying the search criteria are demonstrated for each style of cascading: (a) Permanent saved query with embedded criteria clause. (b) SQL built at run time. (c) Filter string applied at run time. Selection results for each list box are stored in corresponding unbound text boxes as comma separated strings. For text type data, each element of such a string is enclosed within outer single quotes, after having replaced each embedded single quote if any, by double single quotes (As the outer enclosing character is single quote, embedded double quotes if any, don't require any special treatment). For date type data, each element of such a string is enclosed within outer hashes. This arrangement eliminates the need for repeated cycling through the list boxes while testing the field values. The value is simply checked against the contents of corresponding unbound text box, using IN operator. In case of permanently saved query with embedded criteria clause, EVal() function is used for facilitating interpretation of superstring having IN operator. Version: Access 2000 File Format References: Nothing extra DownLoad >> uploads/37/Form_SearchByMultipleListBoxes.zip |
||||
| Form_SearchByYearMonthRange.mdb | Form | 46K | Access 2K/XP/2K3 (2K format) | 08 Jul 2008 at 11:07pm |
|
This sample db demonstrates Filtration of records based upon start and end years & months. Two styles are covered: (a) Filter applied directly on form. (b) Filtration via source query, using form based parameters in the WHERE clause.Download Access 2K/XP/2K3 (2K format): uploads/37/Form_SearchByYearMonthRange.zip |
||||
| Form_SetPermissionLevels | Form | 61K | 03 May 2010 at 5:20am | |
|
Form_SetPermissionLevels Explanatory Notes This sample db demonstrates setting of permission levels (e.g AllowAdditions, AllowEdits etc) for various forms as applicable to different user groups. On selecting (or entering - if new) the name of desired form in bound text box on form F_SetPermissions_Main, a matrix of virtual records is presented in the subform, displaying settable properties (e.g AllowAdditions, AllowEdits etc) against each group ID. Each GrpID in table T_Grps is represented by one virtual record in this matrix. As and when any of the properties is set by clicking the check box in pertinent column, the erstwhile virtual record gets converted into an actual record in table T_Permissions. This way, the table is not burdened with redundant records (e.g. groups for which no permission has been set). At the same time, full matrix (of virtual plus actual records) is always available for convenient setting or editing of permissions for the given form, group-wise. During the process of setting permissions, GrpID column of each record gets conditionally highlighted, depending upon the numbers granted as against overall total. Demonstration of using the permissions set as above, is provided via form F_LoadForms. On selecting an employee from the combo box, followed by entering of correct password and clicking the log-in button, the user can select the form to be loaded - via the adjacent combo box. The selected form, with prescribed permissions duly enforced, gets loaded as a subform. For ready appreciation the permission status gets displayed at top. Text boxes representing various permissions get highlighted in appropriate color, depending upon YES / NO status. Version: Access 2000 File Format Reference: DAO 3.6 DownLoad File >>: uploads/37/Form_SetPermissionLevels.zip |
||||
| Form_SplitFormSimulated | Form | 242K | 13 Aug 2012 at 3:06am | |
|
Form_SplitFormSimulated Updated On 15-Aug-2012 -- Made suitable for Access 2010 also Updated on 25-Oct-2012 -- Enabled to function as linked subform Explanatory Notes: This sample db demonstrates a simulated split form. It has two adjacent subforms, one below the other, separated by a divider bar. Top subform serves as single form while the bottom one represents datasheet portion. There is two way synchronization between single form and datasheet. Comparative vertical space available for the two subforms can be adjusted at run time by dragging the divider bar up or down. Single form portion functions as the prime mover. Some of the salient features are listed below: 1 - Ease of adaptation: The developer wishing to use this sample db for his own needs has to simply assign his single form as source object for the top subform in the main form included here. Everything else will get taken care of automatically. Based upon generic template, datasheet portion of split form gets generated programmatically in the bottom subform, displaying columns matching bound controls in the single form above. It is like a plug & play feature. 2 - Consolidation of code in a wrapper class: Necessary code for integrated coordination between main form as well as its two subforms is consolidated in a wrapper class, instantiated in open event of main form. This class has pointers to both the subforms as well as the main form. 3 - No added burden for data loading: Datasheet subform uses the recordset already loaded for single form, thus avoiding any additional burden. 4 - Divider bar: (a) Divider bar can be dragged for dynamic expansion / shrinkage of datasheet and single form heights at run time. (b) On opening the split form, divider bar assumes the position last held in previous session. 5 - Re-sizing of nominated controls on single form: For added convenience, certain controls on single form, e.g. text box bound to memo field or even an image control, can be slated for vertical re-sizing so as to best utilize the available space resulting from divider bar movement. Tag property of such controls should include the word ReSize. In the sample db, such a behavior is demonstrated for control named Synopsis, bound to memo field. 6 - Hiding / Un-hiding of datasheet columns: On opening, datasheet columns matching memo fields are in hidden state. The user can hide any other column by double clicking the same. Similarly, any column can be un-hidden by double clicking the matching bound control on single form. 7 - Auto adjustment of datasheet column widths: (a) At any given stage, the width of displayed columns gets adjusted automatically, so as to ensure optimum utilization of available width of datasheet window, duly taking into account the latest status of hidden / un-hidden columns. (b) For a given single form, on opening for the very first time, datasheet columns assume equal width, suiting available space. Thereafter, if the user has manually adjusted the column widths, any further automated adjustment of column widths in response to hiding / un-hiding of columns is carried out in such a manner as to retain relative proportion of column widths. (c) On click of a command button, the user has the option to reset all column widths equally distributed, as if it were the first opening of form. 8 - Highlights: (a) When a datasheet column is in hidden state, corresponding control in single form gets highlighted in light grey. As and when the column is un-hidden, matching control on single form reverts to its normal color. (b) Current row in datasheet gets highlighted in light green. (c) As the user tabs from one control to the other on single form, matching cell on current row of datasheet gets highlighted in distinct color (say light maroon). Note: Flicker effect on datasheet, due to conditional formatting, seems to be more pronounced in Access 2010 as compared to Access 2003. 9 - Search Action - Positioning of destination row in datasheet window: Based upon search action performed via suitable controls (like combo box etc) on the single form, the destination row on datasheet gets positioned at middle of display window. This takes into account dynamic height of datasheet window, resulting from movement of divider bar. In Access 2010, use of search box on navigation bar, too, results in similar positioning of destination row at middle of datasheet window. Wrapper Class Usage: Integrated wrapper class C_SplitForm is used in VBA module of main form F_Main as follows: (1) Following statement is in F_Main's declarations section: Private mfm As C_SplitForm (2) Following code is in F_Main's open event: Set mfm = New C_SplitForm mfm.P_Init Me, Me.SF_A, Me.SF_B, Me.SF_Ref, Me.LbDiv Note: (a) Subform control at top, i.e. SF_A holds the single form while SF_B holds the datasheet form. Single form is the prime mover. (b) Hidden subform control named SF_Ref holds a single record subform for storing the position of divider bar as well as name of single form - at close of session. (c) Label LbDiv serves as the divider bar. It can be dragged up & down as desired, for adjusting relative heights of single form & datasheet portions at run time. (d) Setting of form object to Nothing is carried out in close event of mfmMain in wrapper class C_SplitForm, so as to ensure proper termination of the class. (Care should be exercised Not To set mfm to Nothing in real form's module, for example in its close event, as that would interfere with smooth termination of wrapper class. - Also see remarks in close event of mfmMain in this class). (e) Class C_SplitFormControls is a child class of wrapper class C_SplitForm. (f) Generic datasheet form used in bottom subform control (SF_B) has no code in its VBA module. It has 200 unbound text boxes along with corresponding attached labels so as to cater up to 200 columns. Version: Access 2000 file format. References: DAO 3.6 A special note of thanks to John W. Colby (of Colby Consulting) for kindly providing valuable insights and willingly sharing his vast expertise in the field of classes. Download File >>: uploads/37/Form_SplitFormSimulated.zip For multiple split forms on tab pages, see other sample: Form_SplitFormSimulatedMultiple |
||||
| Form_SplitFormSimulatedMultiple | Form | 271K | 01 Sep 2012 at 4:43am | |
|
Form_SplitFormSimulatedMultiple Updated on 25-Oct-2012 -- Enabled to function as linked subform Explanatory Notes: This sample db demonstrates simulated multiple split forms on tab control pages. The split form has two adjacent subforms, one below the other, separated by a divider bar. Top subform serves as single form while the bottom one represents datasheet portion. There is two way synchronization between single form and datasheet. Comparative vertical space available for the two subforms can be adjusted at run time by dragging the divider bar up or down. Single form portion functions as the prime mover. Some of the salient features are listed below: 1 - Ease of adaptation: The developer wishing to use this sample db for his own needs has to simply assign his single form as source object for the top subform in the main form included here. Everything else will get taken care of automatically. Based upon generic template, datasheet portion of split form gets generated programmatically in the bottom subform, displaying columns matching bound controls in the single form above. It is like a plug & play feature. 2 - Consolidation of code in a wrapper class: Necessary code for integrated coordination between main form as well as its two subforms is consolidated in a wrapper class, instantiated in open event of main form. This class has pointers to both the subforms as well as the main form. 3 - No added burden for data loading: Datasheet subform uses the recordset already loaded for single form, thus avoiding any additional burden. 4 - Divider bar: (a) Divider bar can be dragged for dynamic expansion / shrinkage of datasheet and single form heights at run time. (b) On opening the split form, divider bar assumes the position last held in previous session. 5 - Re-sizing of nominated controls on single form: For added convenience, certain controls on single form, e.g. text box bound to memo field or even an image control, can be slated for vertical re-sizing so as to best utilize the available space resulting from divider bar movement. Tag property of such controls should include the word ReSize. In the sample db, such a behavior is demonstrated for control named Synopsis, bound to memo field. 6 - Hiding / Un-hiding of datasheet columns: On opening, datasheet columns matching memo fields are in hidden state. The user can hide any other column by double clicking the same. Similarly, any column can be un-hidden by double clicking the matching bound control on single form. 7 - Auto adjustment of datasheet column widths: (a) At any given stage, the width of displayed columns gets adjusted automatically, so as to ensure optimum utilization of available width of datasheet window, duly taking into account the latest status of hidden / un-hidden columns. (b) For a given single form, on opening for the very first time, datasheet columns assume equal width, suiting available space. Thereafter, if the user has manually adjusted the column widths, any further automated adjustment of column widths in response to hiding / un-hiding of columns is carried out in such a manner as to retain relative proportion of column widths. (c) On click of a command button, the user has the option to reset all column widths equally distributed, as if it were the first opening of form. 8 - Highlights: (a) When a datasheet column is in hidden state, corresponding control in single form gets highlighted in light grey. As and when the column is un-hidden, matching control on single form reverts to its normal color. (b) Current row in datasheet gets highlighted in light green. (c) As the user tabs from one control to the other on single form, matching cell on current row of datasheet gets highlighted in distinct color (say light maroon). Note: Flicker effect on datasheet, due to conditional formatting, seems to be more pronounced in Access 2010 as compared to Access 2003. 9 - Search Action - Positioning of destination row in datasheet window: Based upon search action performed via suitable controls (like combo box etc) on the single form, the destination row on datasheet gets positioned at middle of display window. This takes into account dynamic height of datasheet window, resulting from movement of divider bar. In Access 2010, use of search box on navigation bar, too, results in similar positioning of destination row at middle of datasheet window. Wrapper Class Usage: Integrated wrapper class C_SplitForm is used in VBA module of main form F_Main as follows: (1) Following statement is in F_Main's declarations section: Private mfm As C_SplitForm (2) Following code is in F_Main's open event: Set mfm = New C_SplitForm mfm.P_Init Me, Me.SF_A, Me.SF_B, Me.LbDiv, "T_ZZZRef" Note: (a) Subform control at top, i.e. SF_A holds the single form while SF_B holds the datasheet form. Single form is the prime mover. (b) Reference table named T_ZZZRef holds divider bar position and columns widths from last session. (c) Label LbDiv serves as the divider bar. It can be dragged up & down as desired, for adjusting relative heights of single form & datasheet portions at run time. (d) Setting of form object to Nothing is carried out in close event of mfmMain in wrapper class C_SplitForm, so as to ensure proper termination of the class. (Care should be exercised Not To set mfm to Nothing in real form's module, for example in its close event, as that would interfere with smooth termination of wrapper class. - Also see remarks in close event of mfmMain in this class). (e) Class C_SplitFormControls is a child class of wrapper class C_SplitForm. (f) Generic datasheet form used in bottom subform control (SF_B) has no code in its VBA module. It has 200 unbound text boxes along with corresponding attached labels so as to cater up to 200 columns. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Form_SplitFormSimulatedMultiple.zip |
||||
| Form_SubformAsListBox.mdb | Form | 63K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 11:05pm |
|
This sample db demonstrates use of subform as list box. Two alternatives are covered: (a) Continuous subform type list box. (b) Datasheet subform type list box. For each alternative, three styles of list box simulation are demonstrated as follows: (a) Multi-Select - Extended (b) Multi-Select - Simple (c) Single Select Selection behavior in each case is similar to that of typical normal style for the pertinent list box. Extraction of information regarding selected items is relatively more convenient as compared to a conventional list box. Subform based list box affords the added facility of convenient formatting & alignment. In datasheet based alternative, the user can even adjust the row height if required, so as to suit multi-line content. Note: (a) Overall performance regarding prompt rendering of highlight colors (based upon conditional formatting) is found to be best under Access 2003 (even better than Access 2007). (b) For multi-select (extended) list box based upon datasheet subform in versions other than A2K3 (i.e. A2K, XP, A2K7), highlight colors representing multi- selection take complete effect only when Shift key is finally released. (c) For multi-select (extended) list box based upon continuous subform, the performance in versions other than A2K7 is found to be OK (though it appears to be best in A2K3). In Access 2007 however, there is a slight time lag in rendering the highlights, though it does not wait till the shift key is finally released. Download Access 2K/XP/2K3/2K7: uploads/37/Form_SubformAsListBox.zip |
||||
| Form_SubformHighLightCurrentRow.mdb | Form | 19K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 11:04pm |
|
This sample db demonstrates highlighting of current row on a continuous subform, in special color. When the user moves out of the subform, to any other control in the parent form, the subform row that was last current, continues to remain highlighted. Toggling of subform between datasheet view and continuous form view is also demonstrated. Highlighting of current row, persists from one view to the other. There is no dependence upon any primary key - for identifying the current row.Download Access 2K/XP/2K3/2K7: uploads/37/Form_SubformHighLightCurrentRow.zip |
||||
| Form_SubformSaveUndo.mdb | Form | 27K | Access 2K/XP/2K3 | 08 Jul 2008 at 11:03pm |
|
This sample db demonstrates user control over Save / Undo actions on a subform. There are two command buttons (CmdSave and CmdUndo) on the subform. It is convenient to place these buttons on the subform itself, as the subform gets updated as soon as the focus shifts to the main form. These command buttons are in disabled state to start with. As soon as the record gets edited and the form becomes dirty, both the buttons become enabled and the user can take appropriate action (Save or Undo). As long as the record remains dirty, a warning label remains displayed at top. If the user tries either to move to a different record or to exit the subform while the record is still dirty, the action is trapped via form's BeforeUpdate event, for prompting the user either to save or reject the changes. If, however, the change is saved / rejected through direct click on the Save / Undo buttons, the prompt is suppressed and the desired action gets carried out straightaway. Note - This solution is equally applicable to an independent form.Download Access 2K/XP/2K3: uploads/37/Form_SubformSaveUndo.zip |
||||
| Form_SynchronizedSubforms | Form | 116K | 16 Jun 2011 at 2:52pm | |
|
Form_SynchronizedSubforms Updated On: 29-Nov-2011 (Tweaked to suit Access 2010 as well) Explanatory Notes This sample db demonstrates synchronized scrolling of two subforms (both in datasheet view). Two modes are covered: (a) One way synchronization: Top subform always functions as the master while the other one serves as the slave. (b) Two way synchronization: Whichever subform happens to be the active one, functions as the master while the other one serves as the slave. Note: For ready identification, the subform currently serving as the slave, has a darker back color as compared to the master. For each of the above modes, three alternative styles of scroll synchronization are demonstrated: (a) Synchronize horizontal scroll only. (b) Synchronize vertical scroll only. (c) Synchronize both horizontal and vertical scroll. Sample data depicts student's scores in phase 1 (top subform) and phase 2 (bottom subform). For each student, wherever the scores in these two subform happen to differ, the same get highlighted as follows: (a) Top subform: Light grey. (b) Bottom subform: If value is greater than that in other subform, it gets highlighted in light green. On the other hand, if value is less than that in other subform, it gets highlighted in light pink. Note: The above highlights get suitably updated promptly on editing of data in either of the two subforms. Version: Access 2000 File Format Reference: Nothing Extra Note: This sample db makes use of certain functions in module modGet-SetScrollInfo contained in Stephen Leban's sample db named SetGetScrollbars. Download File >>: uploads/37/Form_SynchronizedSubforms.zip |
||||
| Form_TimeLineDisplay | Form | 112K | 25 Aug 2008 at 2:07am | |
|
Form_TimeLineDisplay Author: A.D. Tejpal Description: This sample db demonstrates display of scheduled time slots as a TimeLine graph. Basic data is held in normalized manner in table T_Plan. In its simple form, this table has fields PDate (date type), Task (text type), StartTime and EndTime (both Date/Time type). Table T_Time, populated with 15 minute time slots (strings in format "hh:nn") covering the 24 hours in a day, serves as the master template. Query Q_TimeLine_1 uses a Cartesian join between tables T_Plan and T_Time. Its output is filtered so as to show only those records where the time slot in table T_Time happens to intersect the scheduled booking in table T_Plan (StartTime to EndTime) Crosstab query Q_TimeLine_2, using Q_TimeLine_1, provides the final output, showing the columns for each individual master timeslot. Booked time slots carry the value 1. This feature is used to highlight the booked slots via conditional formatting. Bookings falling between 06:00 to 22:00 Hrs are shown in light blue while those falling outside this period are shown in pink. As the user enters start and end times, TimeLine graph representing the up-to-date status gets displayed in the subform at bottom. For ready reference, the gist of bookings for the given date and task is also displayed. In order to facilitate overall view of time scheduling for various tasks on different dates, Following reports are also included: (a) R_TimeLine_SortedByDateTask (b) R_TimeLine_SortedByTaskDate Note - If a crosstab query is used as record source for a subform having Master/Child linkage, it is necessary to explicitly set the Column Headings property of the query (comma separated list of desired field names - each within double quotes). Version: Access 2000 File Format References: DAO 3.6 Download File >> uploads/37/Form_TimeLineDisplay.zip |
||||
| Form_ToolTipsForDatasheet | Form | 50K | 26 Jul 2012 at 9:25am | |
|
Form_ToolTipsForDatasheet Explanatory Notes: This sample db demonstrates a simplified implementation of simulated tool tip feature for datasheet forms. Two styles are demonstrated. Both quite straight forward. Style A is completely free of any API call and displays the tool tip at top left corner of the form. Style B makes use of API GetCursorPos() and shows the tool tip on form body. Note: For best results, it is desirable that the user should wait for current tool tip to disappear, before attempting to evoke the tool tip for any other control/column. Version: Access 2000 file format. Reference: Nothing Extra. Download File >>: uploads/37/Form_ToolTipsForDatasheet.zip |
||||
| Form_TrackBulkSelectionsInDataSheet.mdb | Form | 29K | Access 2K/XP/2K3 | 08 Jul 2008 at 11:02pm |
|
This sample db demonstrates tracking of multiple selected rows in a datasheet subform. This can be convenient when large number of rows are involved, as there is no need for the user to click the check box in each individual row. It marks the selected blocks of rows by automatic setting of bound check box to true in each of the selected rows. The selected rows get highlighted in a distinct color. This approach permits selection of rows even in scattered blocks (i.e. there is no compulsion for the selected rows to be contiguous, in one monolithic group). Even when focus moves away from subform to the parent form, the selection status is not lost, as check boxes for all such records continue to hold True value. Visual imprint of multiple selections made so far, also continues to get displayed via color highlighting. Any desired operations on the records marked for selection can be carried out by clicking appropriate command buttons on the parent form. Action queries if any, can use the status of Yes/No field in the criteria clause. At any stage, if desired, the user can clear all selections by clicking appropriate command button on the parent form.Download Access 2K/XP/2K3: uploads/37/Form_TrackBulkSelectionsInDataSheet.zip |
||||
| Form_Treeview | Form | 172K | 29 Sep 2009 at 8:09pm | |
|
Form_Treeview Explanatory Notes This sample db demonstrates treeview control with two way synchronization visa-vis conventional form. Two styles of treeview control are covered as follows: 1 - Employees organization chart: 1.1 - Treeview at left shows the organizational set up in hierarchical style, department-wise, as per chain of command, while the adjacent subform at right displays the same information in conventional style. 1.2 - Name of employee in current row of subform is highlighted in light green. Name of top boss for this employee is highlighted in light maroon while all intermediate superiors in the chain of command reaching up to current employee are highlighted in light orange. 1.3 - In synchronization, corresponding nodes in treeview (current employee, top boss and intermediate superiors of current employee) get highlighted in colors matching those in 1.2 above. 1.4 - Treeview - Drag and Drop feature: Any employee node can be moved to another employee or department node. The resulting change in command structure gets reflected promptly in the treeview as well as the subform. If an employee node is dragged to empty space within treeview control (instead of specific destination node), a new department gets added, with the dragged employee designated as top boss in that department. 1.5 - Complete two way synchronization between treeview and the subform (Navigation / Editing): For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa. 2 - Student grading: 2.1 - Treeview at left displays classes and students while the adjacent treeview shows subject-wise grades for current student. 2.2 - Subject-wise grades can be entered / edited conveniently just by clicking the pertinent check boxes. 2.3 - For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed. 2.4 - Complete two way synchronization between treeview and the subform (Navigation / Editing): For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa. Note: For editing the label caption for employee or student nodes in the above treeviews, select the node by clicking on its label. Thereafter, click again so as to make the contents editable. Version: Access 2000 file format. References: DAO 3.6 Microsoft Windows Common Controls 6 (SP 6) - (This is file MSCCOMCTL.ocx - normally in System 32 folder of Windows directory) [Special Note Of Thanks to Drew Wutka for his outstanding series of instructive posts on AccessD discussion group (Database Advisors), and to Arthur Fuller of AccessD, for his excellent article in Database Advisors on the subject] Download >>: uploads/37/Form_TreeView.zip |
||||
| Form_TreeView_BOM | Form | 181K | 19 Jul 2010 at 4:34am | |
|
Form_TreeView_BOM Updates: 30-Aug-2010: SQL mode incorporated - for extracting product chain. 04-Mar-2012: Subroutine P_ExtractProductChainByCode in general module modified so as to identify base parts in first iteration itself. Explanatory Notes This sample db demonstrates extraction of complete hierarchical chain of sub-assemblies and components for the selected item. The user can select the desired item either by selection via combo box or by using the navigation buttons on the master subform at top left, bound to the master table T_PartMaster. Fields PartName and UnitPrice are available for user interaction (i.e. editing or new entry). UnitPrice is to be entered only for base parts (items which are not meant to have any child assemblies or components). There is no limit to the number of nested levels permitted. Two alternative modes for extraction of product chain are demonstrated, i.e. either via SQL or via code. Traditional approach using SQL for extracting product chain is known to suffer from certain limitations as follows: (a) Fixed number of maximum nested levels permitted. (b) Inherent limitation of access in maximum number of table instances allowed in a query. (c) For heavily nested structure, the output can be quite unwieldy and clumsy - involving extra sets of columns for each nested level. (d) The output is in de-normalized form. Adoption of a novel technique in this sample db, involving cascaded execution of SQL, has not only overcome all the limitations mentioned in previous para, but also ensured normalized output, with no limit as to the number of nested levels permitted. This approach represents a major breakthrough and is expected to provide significant gain in performance for large data sets. For the selected item, complete hierarchical chain of sub-assemblies and components gets displayed in the lower subform at right. For base parts, total quantity as well as total price are also shown in respective columns. Summary information (like maximum nesting levels, number of distinct assemblies and base parts as well as total cost of base parts needed for the selected item) gets depicted in label caption at top of this subform. For convenient analysis, the user can select any of the following styles of display, via option group below this subform: (a) All parts (Complete hierarchical chain). (b) Assemblies only - arranged as per relevant nesting level. (c) Assemblies only - Straight. (d) Base parts only - arranged as per relevant nesting level. (e) Base parts only - Straight. Simultaneously, the hierarchical chain of sub-assemblies and components gets depicted as tree view. Total cost of base parts (i.e. items which have no child assemblies or components) also gets depicted at bottom of tree view. Contents of tree view remain in step with the latest selection (out of five listed above) in option group. Also, there is two way synchronization between tree view and the lower subform at right. If the user selects any node in tree view, corresponding record in subform becomes the current record. Conversely, if the user navigates to any record in the subform, corresponding node in tree view assumes selected state. PartID of matching record in the subform gets highlighted in a manner similar to the shade signifying selected node in treeview. As the user navigates through master subform at top left, the contents of tree view as well as the two subforms at right keep getting updated as per current selection (treating current PartID on master subform as the top item for which hierarchical chain is to be extracted). The upper subform at right enables the user to build new assemblies or edit the contents of existing ones. The three editable fields are: ParentPartID, PartID and Qty. Depending upon user preference, as per selection in the option group below this subform, contents of junction table get displayed in one of the following styles: (a) Hide all nesting levels beyond the top one. This provides the least cluttered view. (b) For nesting levels other than the top one, show only the assemblies (keeping base parts hidden). (c) Show all items - assemblies as well as base parts (base parts are items which do not have any child assemblies or components). Certain features as listed below have been incorporated as a help towards proper data entry or editing in the above subform: (a) If no data has yet been entered, default value of ParentPartID gets set as per current PartID in master subform at top left. (b) RowSource for ParentPartID combo box grows progressively in such a manner as to ensure continuity of hierarchical chain. (c) PartID can be entered only if ParentPartID for the given record has already been entered. (d) RowSource for PartID remains such as to prevent circular dependencies. (e) On entry of PartID, a super check is conducted against resultant circular dependency - if any. (f) Duplicates of a given combination of PartID and ParentPartID are not allowed. If the user happens to make such an entry, a friendly message is provided and the combination is not accepted. Note: (a) In table T_PartMaster, PartID is the primary key. Junction table T_ProductKits as well as transit table T_ProductChain make use of composite primary key made up of PartID and ParentPartID. (Values for PartID as well as ParentPartID are both derived from PartID of table T_PartMaster). (b) The sample db is designed in such a manner as to suit both data types for PartID (i.e. number or text). (c) PartName of top item as per master subform at top left is highlighted in distinct color. PartName of all sub-assemblies (not base parts) in the two subforms at right is highlighted in another distinct color. Corresponding nodes in tree view are highlighted in matching color. (d) For fetching various types of totals pertaining to current product chain, a hidden subform bound to a totals query is used. This arrangement is considered more efficient than multiple computations via functions etc. (e) While extracting hierarchical chain of components into transit table T_ProductChain, the process gets terminated as soon as any circular dependency is detected. This avoids the need for arbitrary limit to the number of recursive cycles for guarding against such a situation (thus eliminating any redundant recursive cycle). Version: Access 2000 file format. References: DAO 3.6 Microsoft Windows Common Controls 6 (SP 6) - (This is file MSCCOMCTL.ocx - normally in System 32 folder of Windows directory) [Special Note Of Thanks to Arthur Fuller, Drew Wutka, Jim Dettman, Rocky Smolin and Shamil Salakhetdinov (mentioned in alphabetical order) for the valuable inputs kindly provided by them in AccessD discussion group (Database Advisors)] DownLoad File >>: uploads/37/Form_TreeView_BOM.zip |
||||
| Form_TreeView3TierCascaded | Form | 96K | 14 Oct 2010 at 7:47am | |
|
Form_TreeView3TierCascaded Explanatory Notes This sample db demonstrates three tier cascaded treeview control for student tests and grades, with two way synchronization visa-vis associated subforms. Treeview at left displays classes and students while the second treeview shows different levels of tests for various subjects - for the class in question. Third treeview displays test results for current student, showing date of current test and marks obtained. Grades can be entered / edited conveniently just by clicking the pertinent check boxes. For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed. For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa. Version: Access 2000 file format. References: DAO 3.6 Microsoft Windows Common Controls 6 (SP 6) - (This is file MSCCOMCTL.ocx - normally in System 32 folder of Windows directory) Download >>: uploads/37/Form_TreeView3TierCascaded.zip |
||||
| Form_UnBound_UnDoReDo | Form | 56K | 17 Mar 2009 at 2:08am | |
|
Form_UnBound_UnDoReDo Sample Db - Explanatory Notes General: This sample db demonstrates Undo / Redo features while entering / editing data through an unbound form. Use of collections for storing control and field values respectively, facilitates the process. Adding a new record: Clicking the Add command button presents a simulated new record in distinct back color (indicative of new record). As soon as it is made dirty, Save and Undo command buttons become enabled. Clicking Save adds the record to source table and back color changes confirming the process. If, instead of Save, Undo is clicked, the existing entries get ignored, and fresh blank new record is presented. Editing an existing record: Based upon selection in combo box, the controls get populated with the contents of desired record, with a back color distinct from that for new record. As soon as the record is made dirty (by making change in any of the controls), Save and Undo buttons get enabled. Clicking the Undo button restores the original values and simultaneously, Redo button gets enabled (accompanied by disabling of Undo button). Clicking the Redo button restores the values back to recently edited ones. Simultaneously, Undo button gets enabled (accompanied by disabling of Redo button). The user can shuttle between the Undo & Redo buttons as often as desired. Different back colors confirm Undo / Redo actions. Clicking Save button saves the record as edited. Deleting records: Clicking the Delete button deletes the selected record. At the same time, autonumber for source table gets reset suitably so that next one will get generated in sequence, without leaving any holes. Version: Access 2000 File Format Reference required: DAO 3.6 Download File >>: uploads/37/Form_UnBound_UnDoReDo.zip |
||||
| Form_VirtualRecordsMatrix | Form | 110K | 30 Nov 2009 at 12:13am | |
|
Form_VirtualRecordsMatrix Explanatory Notes This sample db demonstrates generation and display of a matrix of virtual new records. This arrangement has the advantage that while the user can see at a glance all the available data entry slots, no redundant record actually gets inserted in the source table until some data is actually entered in it. For example, task planner for December would show 31 virtual records, one for each day of the month. If the user makes entries only for 5th and 20th December, only these two records will actually get inserted in the table, while records pertaining to all the 31 days continue to get displayed. Three styles have been demonstrated in this sample db: (a) Task planner as per each day of the given month. (b) Task planner as per half hourly time slots on the given day. (c) Bike sales: For each batch of bikes received, a matrix having number of virtual records matching the batch size (number of bikes received in the given batch) is displayed. As and when a bike gets sold, ticking the check box inserts the record in source table with automatic filling in of sale date as well as sale stamp fields. Note: Styles (a) and (b) above use a modified version of calendar form originally developed by Allen Browne. Following features have been incorporated while implementing the adaptation: (a) Use of calendar form as a subform. (b) Mild highlighting (persistent) of today's date - This is in addition to strong highlighting of selected date. (c) Two way synchronization between calendar subform and task planner subform. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Form_VirtualRecordsMatrix.zip |
||||
| Form_VirtualTextForNull | Form | 32K | 07 Jun 2010 at 10:00am | |
|
Form_VirtualTextForNull Explanatory Notes In certain situations, it might be considered desirable to display a meaningful text in a form control in case of Null value, without compromising the Null value status (which would otherwise stand vitiated by assignment of a default value). This sample db demonstrates a simple technique for achieving the above objective. This is done by manipulation of control's format property and has the advantage that the required information gets displayed without disturbing the value actually held by the control (i.e. Null or otherwise). Either of the following styles can be used for assigning the format property: (a) Direct Entry against format property on format tab in control's property sheet via user interface at design time: @;"[Text To Be Displayed If Null]" (b) Programmatic assignment via code in form's VBA module: Me.MyCombo.Format = "@;[Text To Be Displayed If Null]" With the above approach, whenever the control happens to be blank, it will display the desired virtual text. In this state, if the user clicks into the control, the virtual text disappears, making it very convenient to make a new entry. In case of a combo box where first column is not hidden, direct assignment as per (a) above is found adequate. However, very often, the first column width is kept zero. In that case, for proper effect, it is found necessary to manipulate the format property via code. In this sample db, demos are provided for the following: (a) Combo box with first column hidden. (b) Combo box with first column on display. (c) Text box meant for entering dates: This involves toggling of formats. Virtual text message ([Enter Target Date]) gets displayed if the text box is blank. On the other hand, when a date is entered, the format gets switched to long date with a meaningful prefix. Typical display: "Target Dt: December 31, 2010". It is to be noted that inclusion of an informative prefix in this manner does not disturb the value held by the control in question. It continues to be a proper date. (d) Subform displaying meaningful virtual text on the new record. Version: Access 2000 File Format Reference: Nothing Extra Download >>: uploads/37/Form_VirtualTextForNull.zip |
||||
| FormAppendSelectedRecord.mdb | Form | 72K | Access XP & 2K | 08 Jul 2008 at 11:00pm |
|
The sample db demonstrates adding a fresh record that is a copy of desired selected record. This is done on double clicking the source record. Both the records (source record as well as the freshly added one) remain highlighted till the fresh record is saved.Download Access XP & 2K: uploads/37/FormAppendSelectedRecord.zip |
||||
| FormParametersForRecordsets | QTblFunc | 61K | 20 Oct 2008 at 5:54am | |
|
FormParametersForRecordsets Sample Db - Explanatory Notes SQL strings having embedded form based parameters do not readily lend themselves to creation of recordsets (Error 3061 - Too Few Parameters). Conventional approach involves concatenation of hard values represented by form controls into the SQL string. Concatenation of hard values into SQL string suffers from the following drawbacks: (a) Proper delimiters need to be used for non-numeric data (Single quote for text type data and hash (#) for date type data). (b) If text data has embedded quotes, these need to be fixed before concatenation. (c) For date type data, the hard value has to be converted into "mm/dd/yyyy" format before undertaking the concatenation. This is meant to prevent inconsistent results, if the regional settings for short date on the local computer happens to be not as per U.S. date format. (d) Care needed in handling Null values. The limitations brought out above, point to the desirability of devising a workable solution for utilizing SQL strings having embedded form parameters, as source for recordsets. Function Fn_FixFormParamsForRecordsets() is meant to fix such SQL strings, making them suitable for use with recordsets. This eliminates the problems outlined in preceding para. Sample db named FormParametersForRecordsets demonstrates the syntax for alternative styles of handling the embedded form parameters. These examples are based upon Allen Browne's ELookup() function which is meant to provide greater flexibility and other advantages over built-in DLookup() function. Three different modes of form controls are covered: (a) Controls directly on main form (Main form is named F_C). (b) Controls on subform. (Subform control on main form, holding this subform is named SF_Sub). (c) Controls on sub-subform i.e. subform within a subform. (Subform control on first subform, holding this sub-subform is named SF_SubSub). For each form mode above, four styles of lookup execution are demonstrated: (a) Using function ELookup_A() - This is a modified version of ELookup(), with built in capability to fix embedded form parameters (if any) in criteria clause. The process involves wrapping each embedded form reference in EVal() function after enclosing it in outer single quotes. So far as the user is concerned, the syntax of passing embedded form references is just similar to that used for DLookup(). (b) Using function ELookup() in its original form. Here, it is necessary for the user to wrap each embedded form reference in EVal() function after enclosing it in outer single quotes - before supplying the arguments. (c) Using custom function Fn_CV(), which accepts the form name and upto three control names as its arguments. Arguments for 2nd and 3rd control names are optional - needed only if the control is located on a subform or sub-subform. In such a case, control names other than the final one, represent subform controls. (d) Using in-built DLookup() function. This is meant to provide ready appreciation at a glance, as to how the syntax for passing arguments in three alternative methods in (a) to (c) above, compares with that for DLookup(). Note - Function ELookup_A() is self contained, incorporating the code for fixing embedded form parameters. Parallel function ELookup_B() has also been given in the general module. It is similar to ELookup_A(), with the only difference that auxiliary function named Fn_FixFormParamsForRecordsets() is used for fixing the form parameters. The syntax for passing arguments to ELookup_B() is same as that for ELookup_A(). The modified function also takes care of spaces if any, in names of forms and controls. For the selected modes of forms (main, sub, sub-sub) and functions, the suggested syntax is displayed in an unbound text box named TxtCode at bottom right. The purpose is to look up the ID matching the criteria determined by combined effect of various text boxes on the form as follows: (a) 1st text box (Txt_TargetFieldName) carries the name of field to be looked up (b) 2nd text box (Txt_TableName) has the name of data table or query. (c) 3rd text box (Txt_TextValue) has the text value to be compared. (d) 4th and 5th text boxes (Txt_TextFieldName1 and Txt_TextFieldName2) have names of text type fields meant for IN operation in criteria clause. (e) 6th text box (Txt_DateValue) has the date value to be compared. (f) 7th and 8th text boxes (Txt_DateFieldName and Txt_NumberFieldName) carry the names of date and number fields featuring in ORDER BY clause. The date field features in criteria clause also. Note - The set of eight text boxes each on the subform and sub-subform are named identical to those on the main form. Clicking the command button captioned "LookUp" executes the displayed code and shows the result (matching ID) in adjacent text box (TxtResult). Simultaneously, the matching record in subform at top right gets highlighted in light blue. The above arrangement has inter-active feature. The user can play with the code displayed in text box (TxtCode), tinker with the suggested syntax as if it is live code and see the results by clicking on command button. If at any stage, it is desired to revert back to the originally suggested syntax, just select a different option from any of the two option groups (Form / Function) and then re-select the original option. The correct syntax will re-appear in TxtCode. Important - Form references holding field names are not to be embedded. These are to be concatenated, without using any delimiters. This is equally applicable to DLookup() as well. Reference required - DAO 3.6 Version - Access 2000 file format Download >> uploads/37/FormParametersForRecordsets.zip |
||||
| Forms_CenterLabelsVertically.mdb | Form | 67K | Access XP & 2K | 08 Jul 2008 at 10:57pm |
|
This sample db demonstrates vertical centering of a label's caption text on a form. Need for such a feature typically arises when colored labels are sought to be used as command buttons. While horizontal centering can easily be implemented by setting the Text Align property to "Center", vertical centering needs precise setting of top and bottom margins (default value of both these margins is zero). Manually setting the two margins (top & bottom) equal to each other is not in itself adequate. These two values added to the text height should match the label height exactly. Otherwise, the caption might look truncated (if the sum of two margins plus text height exceeds label height) or off-center, towards top (if the sum is less than label height). The sample makes use of a sub routine that accepts two arguments (a) Name of the form containing target labels. (b) Tag value identifying labels on which vertical centering is to be implemented. The solution covers label captions spanning single as well as multiple rows. Note - This solution should be applied AFTER the font size and weight (for the labels) have been finally set.Download Access XP & 2K: uploads/37/Forms_CenterLabelsVertically.zip |
||||
| Forms_CheckReqdAndDuplicates.mdb | Form | 133K | Access XP & 2K | 08 Jul 2008 at 10:56pm |
|
The sample db demonstrates incorporation of following safeguards at data entry stage. (a) Ensuring that for a given record, mandatory fields get entered before permitting data entry in any other field. (b) Ensuring that in case of unique composite index, if data being entered in any of the fields involved in such a combination would lead to creation of duplicates, a friendly message is provided, and the irregular entry is un- done. Mandatory Fields (Property settings in source table: Required = Yes, Allow zero length = No) (1) Sole dependence upon form's Error event for generating a friendly message, is by itself not adequate (This event fires only when the user tries to save the record or move to another record). (2) In order to ensure that even while staying at a given record, data entry in other fields becomes feasible only after the mandatory ones have been filled in, further programmatic support has been provided, some of the salient features being - (a) Controls pertaining to mandatory fields are identified by setting their tag property = R (This facilitates super-check on blank/filled status of such controls). (b) Once the form gets dirty, mandatory controls in blank state get highlighted in a special color. (c) If any attempt is made to enter data in some other control while any of the mandatory ones is still blank, the focus moves back to the first blank mandatory control, after providing a suitable friendly message. (d) Two styles are demonstrated. (i) Tabbed form (mandatory controls on first page, other controls on subsequent pages). (ii) Datasheet form Preventing duplicates in fields comprising composite unique index or primary key. (1) Here too, sole dependence upon form's Error event for generating a friendly message, is by itself not quite adequate, for reasons stated against 4.2 (1) above. (2) The sample demonstrates provision of super-check in exit event of controls pertaining to composite unique index. If any entry is such as would lead to duplication of combined field value, the entry is un-done and the exit event gets cancelled, after providing suitable friendly message. The focus continues to remain on the control in question.Download Access XP & 2K: uploads/37/Forms_CheckReqdAndDuplicates.zip |
||||
| Forms_ReGroupRecords.mdb | Form | 42K | MDB | 08 Jul 2008 at 10:55pm |
|
This sample db demonstrates movement of records amongst groups. The example covers an electrical distribution system comprising of circuits & lamps. It shows how the user can re-assign selected lamps from one circuit to another. Subform A at left displays the lamps belonging to destination circuit (Circuit A), as selected in the combo box. Subform B at right displays all other lamps (not belonging to circuit A). Lamps selected in Circuit B (subform at right) get transferred to Circuit A (subform at left). The transferred records get inserted just after the current cursor position in subform A. The user can determine the precise insertion point by suitable placement of cursor in this subform, before clicking the command button for moving the records. Freshly moved lamps get highlighted in special color in suform A. Lamp in newly inserted records get renumbered by suffixing .001, .002 etc just after the lamp number below which the insertion takes place. Overall numbering for the current circuit in subform A, can be dressed up by clicking the command button for renumbering. This gets rid of the extra decimal suffix and renumbers all the lamps in proper sequence, retaining their intended sort order. Three categories of lamp numbering are demonstrated as follows: (a) Pure digits (four digits format). (b) Prefix "FL-" followed by four digits - denoting fluorescent lamps. (c) Prefix "SV-" followed by four digits - denoting sodium vapor lamps. Note: (a) If it is anticipated that the number of lamps per circuit might exceed 9999, the length of format string ("0000") used in re-numbering module, can be increased suitably. (b) Freshly moved lamps acquire the existing prefix (if any) for the current record in subform A, below which the insertion takes place. (c) On renumbering in subform A, digital sequence for each group of lamps (as identified by the prefix), starts at "0001".Download: uploads/37/Forms_ReGroupRecords.zip |
||||
| Forms_TabWithinMemoField.mdb | Form | 61K | Access XP & 2K | 08 Jul 2008 at 10:54pm |
|
This sample db demonstrates use of tab key within a memo field. Normally, pressing the tab key causes the cursor to move to next control. However, in large memo fields, it is sometimes more convenient that tab key should result in suitable indent within the memo field, rather than moving out of the control. Specific tab value (number of spaces by which the cursor should move on pressing the tab key) can be set by the user if desired. Default value is 5.Download Access XP & 2K: uploads/37/Forms_TabWithinMemoField.zip |
||||
| FormSetReqdFields.zip | Form | 112K | Access XP & 2K | 08 Jul 2008 at 10:52pm |
|
This sample db demonstrates user setting of required fields at data entry stage. The field list is built up, based upon the record source for the form. If the FieldList table is empty, it gets populated automatically, on opening of form F_DataMain. For fields to be pinpointed as mandatory, the adjoining check box should be selected (in the list displayed on Set Required Fields tab). During data entry, form's Before Update event checks whether any of such mandatory fields is blank. If so, suitable warning message, listing such blank fields, is displayed and update action gets cancelled. Versions - Access XP & 2K (In Access 2000 File Format) References - DAO 3.6 Download Access XP & 2K: uploads/37/FormSetReqdFields.zip |
||||
| FormsSelectedBlockInDatasheet.mdb | Form | 45K | MDB | 08 Jul 2008 at 10:51pm |
|
This sample db demonstrates tracking of selected columns and rows in a datasheet (subform). Particulars of columns and rows coverd in the selection block, are displayed in a text box on the main form. The sample incorporates an interesting feature. Even when the focus moves away from the subform (containing the datasheet), an imprint of last selected block of columns and rows continues to remain highlighted in a distinct color.Version: Access 2000 File FormatReference: DAO 3.6Download File: uploads/37/FormsSelectedBlockInDatasheet.zip |
||||
| FormsSubformsReference.mdb | Form | 198K | Access XP & 2K | 08 Jul 2008 at 10:50pm |
|
This sample db demonstrates execution of following types of tasks while simultaneously displaying the code involved: -Referring to controls of one group of nested subforms from another group of nested subforms: (a) There are two adjacent sets of nested subforms, each three levels deep. (b) Dbl clicking any text box identifies it as Target Control. Thereafter, Dbl clicking any other text box identifies it as Base Control. (c) Code in the subform containing Base Control, acts upon Target control and also sets focus on to it. Simultaneously, this code gets displayed at bottom of main parent form. (d) For each situation, two alternative styles of VBA code are shown (with and without use of bang (!) operator). (e) Extreme case involves Base Control in innermost nested subform at left, acting upon Target Control in innermost nested subform at right (i.e. going up-hill across three levels of parent forms and then down-hill across three levels of subforms). 4.3 - (i) Query using values represented by controls on forms / nested subforms, (ii) Referring to subroutines (contained in form's modules) across various levels of nested subforms: (a) Here too, two adjacent sets of nested subforms, each three levels deep are used. (b) Dbl clicking any text box on main parent form or various nested subforms at left, causes its value to be picked up by the query, results of which, are displayed in the innermost nested subform at right. (c) Simultaneously, the SQL used in source query, gets displayed at bottom of parent form. (d) VBA code used for calling a subroutine contained in the module of innermost nested subform at right, from various levels of nested subforms at left, is also displayed. Note - (a) In a query, reference to form controls is feasible only by using bang (!) operator. (b) In VBA code, parenthesis can be used in lieu of bang (!) operator and are preferred. (c) Thumb rule: Use Dot for one to many situation. Use bang (!) or parenthesis for many to one situation e.g member of a collection.Download Access XP & 2K: uploads/37/FormsSubformsReference.zip |
||||
| FormsSynchronized.mdb | Form | 127K | Access XP & 2K | 08 Jul 2008 at 10:49pm |
|
The db demonstrates synchronization of slave form with the master form. Two styles are covered: (a) Two subforms (Master & Slave) (b) Two normal forms (Master & Slave) As the user navigates through the records in master form, those in slave form remain synchronized in such a manner that matching rows in the two forms are always in alignment. Current record in slave form matches the current record in master form and is highlighted in distinct color. With the exception of vertical scroll bars, all the normal means of navigation are available for moving through the master form. These include subform's navigation buttons, PgDn & PgUp keys, Tab key, Ctrl + End, Ctrl + Home, Ctrl + DnArrow and Ctrl + UpArrow. (For the key strokes to be effective, the cursor should be in the master subform). As an added feature, the user can step through the records in master form (which is a continuous form) in datasheet style, simply by pressing Dn or Up arrow keys. The user can double click on any row to move it to top of display area.Download Access XP & 2K: uploads/37/FormsSynchronized.zip |
||||
| FormSubf_ReOpenAtCurrRec | Form | 39K | 01 Nov 2010 at 7:11am | |
|
FormSubf_ReOpenAtCurrRec Explanatory Notes: This sample db demonstrates re-opening of main as well as subform at the respective records which were current when last closed. If the current position on last closure was a new record, the form in question will re-open at new record. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/FormSubf_ReOpenAtCurrRec.zip |
||||
| FreezeDatasheetRows.mdb | Form | 68K | Access 2000 DAO | 08 Jul 2008 at 10:48pm |
|
This sample db demonstrates freezing of nominated rows in datasheet view. Any combination of rows can be selected for this purpose (not necessarily adjacent to start with). The selected rows are displayed at the top and remain in perpetual view, while navigating through the datasheet. The db is in Access 2000 file format, developed on Access-XP installation. Requires DAO 3.6Download Access 2000 DAO: uploads/37/FreezeDatasheetRows.zip |
||||
| Function_TopXfromY | QTblFunc | 56K | 26 Jul 2012 at 4:02am | |
|
Function_TopXfromY Explanatory Notes: This sample db demonstrates extraction of a specified block of records (X numbers) from the source query, starting at a given position (record number Y). It is observed that in case of large data, a query having ORDER BY clause, becomes unacceptably slow, if TOP predicate is included. The method shown in this sample db gets the results promptly. Moreover, the user can specify the starting point (Y), while fetching the block of X records. Version: Access 2000 file format. Reference: DAO 3.6 Download File >>: uploads/37/Function_TopXfromY.zip |
||||
| H | GoTo Top | |||
| HighLightDataSheetRows.mdb | Form | 139K | Access XP & 2K | 08 Jul 2008 at 10:46pm |
|
This sample db demonstrates highlighting of datasheet rows in following styles Current Row Alternate Rows Certain Special features Anchor Row - In both alternatives (a) & (b) above, user can pinpoint any row as an anchor row (if desired), by double clicking on it. The anchor row gets highlighted in a special color and continues in this state, till some other row is pinpointed as the new anchor row. If any existing Anchor row is to be put back to normal (without pinpointing a new one), simply double click it again. Over-riding conditional highlighting of individual cells (if desired) as per specified criteria. In the sample db, Stock field gets special highlight if the value falls below 100. This special highlight holds good even when the row as a whole is highlighted as current/alternate/anchor row. In case of alternate row highlighting, the form, on opening, has color on odd rows. For replacing it with color on even rows, simply double click on any even numbered row. For reverting back to color on odd numbered rows, double click on any odd numbered row. Note - The technique used is completely non-obtrusive. It does not need any special field (e.g. Yes/No or counter) to be added to the source table. No updating or editing of source data is involved. No label or unbound text box is used. Only requirement is that the source table should have a field holding unique values. In this db, ID is such a field (autonumber type). Download Access XP & 2K: uploads/37/HighLightDataSheetRows.zip |
||||
| I | GoTo Top | |||
| ImageManager.mdb | Form | 330K | MDB | 08 Jul 2008 at 10:44pm |
|
ImageManager (Updated on 28-Dec-2008) Sample Db - Explanatory Notes Two versions are contained in the zip file (both are in Access 2000 file format) - (a) ImageManager_XP.mdb - for Access XP installation or higher, using Application FileDialog object from MS Office 10.0 Object Library - for selecting image files. (b) ImageManager_2K.mdb - for Access 2000 installation - using API for invoking the common dialog - for selecting image files. These sample db's demonstrate various aspects of handling linked images and each version provides choice of following modes - (a) Thumbnail View (Carousal Style) (b) Thumbnail View (Continuous Form Style) (c) Slide Show (Manual) - The user can advance the images step by step. (d) Slide Show (Auto Play) - Time interval and AutoRepeat (Yes/No) can be specified by the user. For both modes of slide show (Manual & Auto), following sorting options are available - (1) Normal Sort (2) Random Sort (3) No SortReferences: (a) For both versions - DAO 3.6 (b) For XP version only - Microsoft Office 10.0 (or higher) Object LibraryFile Format (Both Versions) - Access 2000 File FormatDownload >>: uploads/37/ImageManager.zip |
||||
| InputMask_Dynamic.mdb | Form | 281K | Access 2000 | 08 Jul 2008 at 10:43pm |
|
This sample db demonstrates dynamic setting of Input Masks at run time - with partly pre-filled values as desired. Three cases are covered - (a) Phone Numbers - with area codes pre-filled (b) Dates pre-filled with desired month and year part. (c) Text string prefixed/suffixed with current month-year. (d) Text string prefixed with any desired text string. The sample is in Access 2000 file format Download Access 2000: uploads/37/InputMask_Dynamic.zip |
||||
| InputMaskDateValidation.mdb | Form | 117K | MDB | 08 Jul 2008 at 10:41pm |
|
The sample db demonstrates incorporation of suitable safeguards, taking into account certain peculiar features associated with input masks for date type controls, as detailed below. (a) - Mask violation (e.g. skipping of a digit that is mandatory as per mask) is trapped in form's error event through DataErr 2279. (b) - Date violation (i.e. a string that can not be interpreted as a valid date, even by juggling all possible combinations of day/month/year parts) is trapped in form's error event through DataErr 2113. This error has nothing to do with input mask and gets triggered for invalid date even when there is no mask. (c) - Above error values are not to be confused with conventional error. Even when DataErr 2279 or 2113 gets precipitated, the value returned by Err.Number remains zero. (d) - DataErr 2279 takes precedence over 2113. (e) - When either of DataErr 2279 or 2113 is encountered, control's BeforeUpdate event does not get a chance to fire. (f) - Input mask for date type control must have zero as its second argument (i.e. just after the first semicolon). Otherwise, it will attract DataErr 2113 even for a valid date. For example, use 00\-00\-0000;0;_ instead of 00\-00\-0000;;_ (g) - Absence of any error (2279 or 2113) is no guarantee that the date value is going to be as intended by the format in force. For example an entry 22-04- 2005 would get stored as 22-Apr-2005, even when the format for data entry is mm-dd-yyyy. Such anomalies need to be trapped through a super-check in control's BeforeUpdate event. (h) - If the format for data entry is in conflict with that for regional setting for short date on the particular computer, there is another pitfall. The value as entered, even though free of any error and despite being found valid as per intended format (via super-check in control's BeforeUpdate event), would still get interpreted and stored as per the local system settings on the computer (unless the day part is greater than 12 and can not be interpreted as month part). For example, if the format for data entry is mm-dd-yyyy, while the system setting for short date is dd-mm-yyyy, 07-04-2005 will get stored as 07-Apr-2005 (instead of 04-Jul-2005). Remedial action becomes necessary in control's AfterUpdate event. (i) - On entering a date type control for editing existing data, its format property, if in conflict with the system setting of format for short date, gets over-ruled by the latter. On exiting the control, the format property specified for it governs data display (even if in conflict with the system setting).Download :uploads/37/InputMaskDateValidation.zip |
||||
| InsertRows.mdb | Form | 133K | Access XP & 2K | 08 Jul 2008 at 10:40pm |
|
1 - This sample db demonstrates - (a) Insertion of desired number of new rows in a datasheet - at the selected position. (b) Movement of selected rows to a different desired position in the datasheet (Equivalent to Drag & Drop). 2 - Newly inserted/moved rows are highlighted in a distinct color. 3 - If the user wishes to renumber any SubLines(decimals permitted), there is provision for automated re-numbering in a sequence of whole numbers and displaying the results sorted accordingly. Versions - Access XP & 2K (In Access 2000 File Format) References - DAO 3.6Download Access XP & 2K: uploads/37/InsertRows.zip |
||||
| L | GoTo Top | |||
| Labels_ScrollingText.mdb | Form | 135K | Access XP & 2K | 08 Jul 2008 at 10:38pm |
|
The sample db demonstrates scrolling of text in labels, in following alternative styles (a) Simple vertical scrolling (Single sweep as well as continuous sweep). (b) Mixed scrolling (Lateral followed by vertical scroll, before each line occupies its final place) - Single Sweep. (c) Mixed scrolling (Lateral followed by vertical scroll, before each line occupies its final place) - Continuous Sweep. Note - XP version, run on XP installation, is found to give more pleasing results as compared to 2K version Download Access XP & 2K: uploads/37/Labels_ScrollingText.zip |
||||
| LicenseLock | General | 250K | 08 Jul 2008 at 10:36pm | |
|
LicenseLock Explanatory Notes: This sample db is meant to secure Licensee particulars (Or date of expiry for free trial) in a tamper-proof manner. Often, such information is stored using registry settings and/or hidden tables. With sufficient skill and motivation, contents of registry or hidden/system tables can be tampered with. For utmost security, the sample db demonstrates storage of such information in hard coded form directly in the code module of an auxiliary Mde file called LockFile.mde. (As far as known, it is not possible to get into the code module of an Mde file). Creation of this lock file is automated for versions up to Access 2003 (See note below). To start with, the main application file (say MainFile.mde) is shipped to the client along with LockFile.mde (having hard coded information for expiry date of free trial). This lock file is very small in size and does not have any tables or queries. Whenever the main file is opened, it grabs necessary information from the lock file (placed in the same folder as the main file) and can function normally only if expiry date for trial is not yet over. As & when the client becomes eligible, new lock file containing his Licensee status can be sent to him. Now the main file will be able to work smoothly (without any limit to expiry date), displaying the name of licensee prominently. LockFileMaker.mdb is used for automated generation of lock files. Step by step guidelines are given in the accompanying file LicenseLock_ReadMe.htmNote: For Access versions up to 2003, programmatic conversion from .mdb to .mde format is found feasible via SysCmd argument 603. It is an undocumented argument and is no longer found effective in Access 2007 onwards. For these later versions, manual conversion to .accde format can be done. Caution: The package contains separate versions for Access_XP and Access_2K. Since creation of Mde files is involved, it is important that Access_2K version is not attempted to be used on Access_XP installation (Access_XP can handle Mde conversion only if the mdb file is already in XP format i.e. Access 2002). Of course, the finished product (LockFile.mde and MainFile.mde) generated by 2K version of this package, can be used on XP installation as well. Versions - Access XP & 2K References - OLE Automation, MS Scripting RunTime Download File >>: uploads/37/LicenseLock.zip |
||||
| ListFoldersAndFiles | General | 174K | 14 Aug 2011 at 11:57pm | |
|
ListFoldersAndFiles Explanatory Notes This sample db demonstrates listing of folders and files contained within the selected top directory, as per desired file specifications. Based upon user's choice, subfolders can be included or ignored. If desired, more than one types of files can be included in a single file spec in the form of a comma separated string - e.g. : "*.htm,*.pdf,*.txt" etc. Process Mode For Listing Folders and files: Two alternative methods for listing of folders and files, under a given top folder, are covered as follows: (a) Non-Recursive mode - using Dir() function. Apart from being faster than (b), it has the advantage that there is no extra strain on memory resources (otherwise associated with recursive approach), thus avoiding the risk of potential hang up in case of very large and deep directory tree. (b) Recursive mode - using FileSystemObject Display Of Listed Folders And Files: Each run for listing of folders and files is logged in table T_ProcessLog. On the viewing form, for the selected ProcessID, path and other details of topmost folder are displayed at top of the form. Similar details for the current subfolder are displayed just below the information for top-most folder. Subfolders and their files are displayed in adjacent subforms. For the current file, its details (like file type, size, attributes, DtCreated / DtLastModified / DtLastAccessed) are also displayed, apart from a hyperlink to the file itself. The hyperlink label becomes active only for permitted file types. The user can edit the contents of table T_AllowHyperLink for setting such permissions. Three alternative styles of display are provided as follows: Style A - View Folders and Files In Hierarchical Chain: The user can drill down the directory tree by expanding any of the subfolders which then assumes the role of current main folder, resulting in display of subfolders and files held by the erstwhile subfolder. This can be done indefinitely, till the last subfolder at deepest nesting level is reached. Similarly, by pressing a command button, the user can move up the directory tree. The process can be repeated till the current main folder becomes identical to the top folder (i.e. the original top most folder for which the listing was generated). Style B - View All Folders At A Glance - And Their Files: For convenient viewing, the folders are sorted as per nesting level and path. The top-most folder (nesting level: zero) is highlighted in distinct color. For other folders, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other. Style C - View All Files At A Glance: For convenient viewing, the files are sorted as per nesting level and path. Files in top-most folder (nesting level: zero) are highlighted in distinct color. For other files, nested groups are shaded alternately in light and dark grey so as to facilitate visual transition from one nesting level to the other. General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca. Note: (a) While using file system object, all types of folders and files get covered (including System, Volume, Hidden, ReadOnly etc). On the other hand, while using Dir() function, such types don't get covered unless relevant arguments are explicitly supplied. (b) Using Dir command via DOS command prompt, listing of folders and files can be saved to a text file, which can then be imported into access table. Such a listing is quite fast and one might be tempted to try this route. However, there is a pitfall associated with this approach. If any special characters are present in the folder or file name (say in internet files), the same might not come through faithfully. For example ® is found to come across as r - resulting in corrupted path. Version: Access 2000 file format. References: (a) DAO 3.6 , (b) Microsoft Scripting Runtime Download File >>: uploads/37/ListFoldersAndFiles.zip |
||||
| M | GoTo Top | |||
| MatchCombinations_2K.mdb | General | 87K | Access 2000 | 08 Jul 2008 at 10:34pm |
|
This is an exercise in matching largest possible combinations of positive and negative values for a given field in the database so that their values offset each other. This information is relevant to certain systems of book keeping. In order to achieve best results, at the same time avoiding unaffordable slowing down in processing each and every possible combination (the number of theoretically possible combinations can become astronomically huge as the total number of records in the database increases), following sort sequence is adopted for positive and negative values before computing their running sums, which in turn are subjected to cartesian comparison - (a) Pos Asc, Neg Asc (b) Pos Asc, Neg Desc (a) Pos Desc, Neg Asc (a) Pos Desc, Neg Desc (a) Pos Random, Neg RandomDownload Access 2000: uploads/37/MatchCombinations_2K.zip |
||||
| MatchPipeSequence.mdb | General | 38K | Access 2000 format | 08 Jul 2008 at 10:34pm |
|
This sample db deals with raw data where each record shows a PrevPipe connected to the NextPipe through a Joint. There are different installation groups, each containing a number of pipe sets with joint in-between as mentioned. Goal: Re-arrange the records in such a manner that for each installation group, the pipes are in sequence, i.e. NextPipe in one record is the PrevPipe in next record. For each sequence set pertaining to a given Instal Group, the very first pipe at left of joint and the very last pipe at right of joint are highlighted in special colors. For testing the sample, try to put the raw data in most severely disorganized manner and then click the command button captioned "<< Update Pipe Sequence >>". The subform at right will now display the pipe sets re-arranged in correct sequence. In the report also, very first pipe (left of joint) & very last pipe (right of joint) in the sequence set for each Instal Group have been highlighted in distinct colors. Versions - Access 2000 File Format (Developed on Access 2003. Also tested on Access XP & 2K) Download Access 2000 format: uploads/37/MatchPipeSequence.zip |
||||
| MatchSimilarAddress | General | 47K | 08 Jul 2008 at 10:32pm | |
|
MatchSimilarAddress (Updated On 07-Mar-2010) Explanatory Notes This sample db demonstrates matching of similar addresses resulting from inconsistent data entry. For example, the following items might relate to the same address (and are therefore required to be identified as similar) - (Address Field) (Zip Field) 2001 S. STATE ST. 84190 2001 S. STATE ST. S 84190 2001 SOUTH STATE ST 84190 The demo form in sample db performs dual role, both for data entry as well as for identifying similar addresses. Similar addresses, covering sets of records pertaining to the same basic address, are grouped together and such clusters of duplicate addresses are highlighted in alternate colors. Version: Access 2000 File Format Reference: DAO 3.6 Download >>: uploads/37/MatchSimilarAddress.zip |
||||
| MedicalDiagnosis.mdb | General | 124K | Access XP & 2K | 08 Jul 2008 at 10:30pm |
|
The sample db demonstrates an interesting presentation of many to many relationship between symptoms & diseases. Some of the interesting features covered in this db are outlined below. Diagnosis As the user selects various symptoms in the subform at left, matching diseases (that exhibit all the selected symptoms) get displayed at right. With the selection of more & more symptoms, list of diseases gets correspondingly narrowed down. Data Entry (a) Integrated interface for data entry facilitates smooth population of junction table bridging the many to many relationship between diseases & symptoms. (b) After entering the disease name on main form, the user can select/enter corresponding symptom in an unbound combo box. If it matches one of the existing ones in the symptoms table, corresponding Symptom_ID gets entered directly in the junction table. (c) If the symptom entered in the combo box is a new one, it gets appended to the symptom table and newly generated value of Symptom_ID gets entered in the junction table. (d) In either case (b or c above), newly added symptom for the disease in question is displayed duly highlighted in the subform, in appropriate sort order.Download Access XP & 2K: uploads/37/MedicalDiagnosis.zip |
||||
| MedicineIntakeMonitor.mdb | General | 53K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:29pm |
|
This sample db demonstrates monitoring of actual intake by patients against the course of various medicines prescribed by respective doctors. For a given patient, the subform at top displays the medicine courses as prescribed by the doctors. It includes the name of medicine, name of doctor, date of prescription, duration (days) and doses (number of times per day). There is a field for doctor's notes as well. The other subform displays a grid of shadow new records for each dose on each day of the prescribed course for each medicine. To confirm intake of a particular dose of given medicine, the user clicks in a check box named Done. The date and time for such confirmation gets automatically inserted in field named DtStamp. As soon as the user clicks the Done check box, the current shadow record gets converted into an actual record in table T_MedTrack. This way, there is no redundant addition to records, till confirmatory action is actually implemented. Also, if the user attempts to click the check box for any of the future dates (i.e. beyond today's date), a friendly message preventing such an entry comes up. As added convenience, highlighting has been provided as follows: (a) When the user is on any record in the compliance subform (the lower one), corresponding record in prescription subform (the upper one) gets highlighted in yellow. This way, the user can readily see the doctor's and other particulars relevant to the current dose of current medicine. (b) When compliance for a given dose is confirmed and the date stamp matches due date, the record gets highlighted in light blue if the due date is same as today's date. Later, when this due date becomes older than current day's date, the highlight color for such a record changes to orange. (c) Records where compliance date stamp does not match the due date, are highlighted in pink. (d) Records older than current day's date, where compliance has not been confirmed (via selection of check box), are also highlighted in pink. The sample includes report generation for the selected settings, providing compliance status for various prescriptions. Records where compliance is missing or delayed beyond due date, are highlighted in color. Summary of the incidence of missed or delayed compliance is given in the group footer. Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/MedicineIntakeMonitor.zip |
||||
| ModulesAddIn_BulkImportExport | General | 88K | 14 Oct 2008 at 3:23am | |
|
ModulesAddIn_BulkImportExport Explanatory Notes This utility enables bulk import of .bas, .cls or .txt files as general or class modules, or export of existing modules to destination folder as .bas / .cls files. Requirements Microsoft Access 2000 or later Microsoft Windows 98 or later References: DAO 3.6 Microsoft Scripting RunTime Visual Basic for Applications Extensibility 5.3 Note: It is not necessary for the host db to have the above references. Installation: The folder ModulesAddIn_BulkImportExport should be put directly on the C: drive, so that the path for .mda file is - C:\ModulesAddIn_BulkImportExport\ModulesAddIn_BulkImportExport.mda. (This is important, so as to establish the correct registry key) Open any database in Access and click on Tools > Add-Ins > Add-In Manager. Click on Add New. Browse to the folder and file at C:\ModulesAddIn_BulkImportExport\ModulesAddIn_BulkImportExport.mda Click OK. This will copy the .mda file to Microsoft's default Add-in folder for Access. You have to do this only once. Use: To use ModulesAddIn_BulkImportExport from within any database, click Tools > Add-Ins > ModulesAddIn_BulkImportExport Imp: Before invoking the AddIn, it should be ensured that the host db is free of any compile error. ======================================================== Technical Notes: Add-In named ModulesAddIn_BulkImportExport, when invoked in the host db, displays a list box at right showing existing modules. On clicking the command button captioned "Get Folder", the user can browse to the folder containing .bas, .cls or .txt files required to be imported as modules. As soon as the folder is selected, the list box at left displays the names of files contained in this folder, along with type of module and line count. Selection of one or more files in the list box, followed by clicking of button captioned "Import", imports the selected files as new modules. Sorting is devised in such a manner that freshly imported modules get displayed prior to those already existing. Similarly existing modules as selected in the list box at right, can be exported as .bas / .cls files to destination folder. This folder, named ExportedModules, gets created in the same directory where the host db is located. Certain interesting factors pertaining to Add-Ins in general, are mentioned below: 1 - For code contained in modules belonging to the Add-In: (a) If the AddIn is opened independently, CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively (all refer to AddIn itself). (b) If the AddIn is activated in host db, CurrentProject / CurrentDb refer to host db while CodeProject / CodeDb refer to AddIn itself. (c) Host applications AllForms / AllReports / AllModules collections can be accessed by code in AddIn, using CurrentProject. (d) Tables / Queries are not covered by collections like those in prev para. (e) While the AddIn is in activated state, any query / table / form / report, whether in host db or in the AddIn, as well as modules in host db can be opened directly by name, using DoCmd statement in AddIn code, without having to use any special qualifier. (f) If any pair of similar objects exists both in the AddIn as well as the host db, code in AddIn module will act upon the object located in the AddIn itself. (g) For code in AddIn module, when the AddIn is in activated mode, queries / recordests based upon CurrentDb do not recognize the tables in AddIn itself, unless referred via IN clause, giving full path of the AddIN. This path, returned by CodeProject.FullName, refers to the original location of AddIn mda file e.g. C:\ModulesAddIn_BulkImportExport\ModulesAddIn_BulkImportExport.mda and not the shadow AddIn file in AddIns folder in MyDocuments. There is no such problem if queries / recordsets are based upon CodeDb instead of CurrentDb. (h) Modules belonging to the AddIn can not be opened by DoCmd mehod or otherwise addressed, even by code in the AddIn. DoCmd.CopyObject command can however be used for copying modules from AddIn to host db. 2 - For code contained in modules belonging to the host db: (a) CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively, always referring to the host db, irrespective of the fact whether the AddIn is in activated state or not. (b) Whereas code in the AddIn can access all tables / queries / forms / reports, whether belonging to AddIn or the host db (see para 1 (e) above), code in host db can directly access only the objects belonging to host db itself. Note: General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca Download File >>: uploads/37/ModulesAddIn_BulkImportExport.zip |
||||
| MotelBooking.mdb | General | 183K | Access XP & 2K | 08 Jul 2008 at 10:26pm |
|
The sample db demonstrates reservation of rooms in a small motel. Some of the interesting features covered in this db are outlined below. Status of bookings at a glance (Style 1) Latest status of room bookings is displayed with rooms appearing across columns. Period to be viewed can be selected by entering the start & end dates in the text boxes provided for the purpose. The display keeps changing in keeping with the period selected. Status of bookings at a glance (Style 2) Similar to Style 1 above, with the difference that dates are displayed across columns. At any given stage, the status for a period of one month w.e.f start date (as entered) is displayed. Reports are available for both styles (1 &2), in corresponding layouts. Data entry - In order to facilitate allotment of rooms, latest status of vacant rooms is displayed in the lower half of data entry form. As soon as the start & end dates of proposed stay are entered, free rooms available during this period get highlighted in the display below. More-over, the drop down list of rooms for the combo box is limited only to such rooms as are free. Versions - Access XP & 2K (both in Access 2000 File Format) References - DAO 3.6 Download Access XP & 2K: uploads/37/MotelBooking.zip |
||||
| N | GoTo Top | |||
| NotesHierarchical.mdb | General | 57K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:24pm |
|
This sample db demonstrates handling of performance notes in an organization. Persons in administrative position are assigned various levels. For example the top boss has level 1. Managers next in command, each have level 2. Supervisors next in command, each have level 3. General employees do not have any level assigned to them. Though three level administrative set up is shown in the db, it can be adapted to any number of levels, simply by assigning levels 4, 5 & so on. In order to identify clear chain of command, the immediate boss for each employee is identified via BossID. For top boss, BossID field is blank. Performance notes for a given employee as selected, can be recorded at various levels by his superiors in direct chain of command. A person in senior position can not alter the notes recorded by those below him (but can view the same). The row source for combo box meant for selecting employee is set dynamically in such a manner that it is confined only to those in direct chain of command under the selected noter. This involves recursive reference to BossID field in employees table, implemented via user defined function. Any person in administrative position, wishing to record a note, has to log in using a password. On successful log in, the noter has full read/write access to his own notes for the selected employee. At the same time, all the notes for this employee, as recorded by other noters (only those in the chain of command below the current noter) get displayed, but in read-only state. The notes table, has a Yes/No field named Sent. When Sent is set to true, the note in question can no longer be edited, even by the person who recorded it originally. More-over, once set to True, it can no longer be re-set to False and no back dated note can be created. For example, if note dated 31-Dec-2006 for a given combination of employee and noter has been marked sent, and we are now in year 2007, any new note with date prior to 31-Dec-2006 can not be created this noter for the given employee.Download Access 2K/XP/2K3/2K7: uploads/37/NotesHierarchical.zip |
||||
| P | GoTo Top | |||
| PagingAcrossReports | Report | 61K | 08 Jul 2008 at 10:23pm | |
|
PagingAcrossReports (Updated on 31-Jan-2010) Explanatory Notes This sample db demonstrates integrated sequential paging across a selected group of reports. The user can alter the sequence of printing as desired, by moving the report names up or down. Out of available reports, those required to be included in sequential printing are selected by ticking the check box adjacent to report name. Page numbers are displayed at bottom right of the report, in style "CurrentPage Of TotPages" where CurrentPage is the sequential page number across reports and TotPages is the total number of pages for all selected reports combined. Version: Access 2000 File Format Reference: DAO 3.6 Download >>: uploads/37/PagingAcrossReports.zip |
||||
| PoolCalcDynamicRates.mdb | General | 71K | Access XP & 2K | 08 Jul 2008 at 10:22pm |
|
This sample db demonstrates computation of pool game charges based upon dynamic hourly rates. For example, for off-peak periods, reduced hourly rates might apply. Similarly, the whole rate structure for week-ends might vary from that in force on normal days.Download Access XP & 2K: uploads/37/PoolCalcDynamicRates.zip |
||||
| PrintExternalFiles.mdb | General | 138K | Access XP & 2K | 08 Jul 2008 at 10:20pm |
|
This sample db demonstrates viewing / printing of external files by clicking a command button on an access form. Two methods are demonstrated: (a) FollowHyperLink method. (b) ShellExecute() API call. All the documents required to be printed are to be placed in a special folder named PrintJobs, located in the same folder as the sample db Two styles are covered: (a) Viewing / Printing of individual files, one at a time. (b) Batch printing of selected files. In single file mode, whenever, the pertinent form opens, paths & names of all files in folder PrintJobs (as well as all its sub-folders if any) get loaded in a table, thus populating the look-up list for the combo box. The selected file can be viewed / printed on clicking the Print button. In batch-print mode, the files table can be freshly re-loaded with paths & names of all files in folder PrintJobs (as well as all its sub-folders if any), by clicking a command button whenever desired. All selected files get printed on clicking the Print button. For the sake of demonstration, sample files with extension pdf, doc, rtf, txt, snp (snapshot) and prn have been included in various subfolders in the folder PrintJobs. The last type, i.e. .prn file gets created when PrintToFile option is used in print dialog box. The sample file was created via HP 610-C printer driver. (Batch-print mode is confined to pdf, doc, rtf and txt files). Note: (1) Prn files: (a) The last file type (.prn type) is included mainly for academic interest, as it is used comparatively rarely . These files (in printer language) are bulky in size and are created by choosing PrintToFile option in Print dialog box, for subsequent printing directly via DOS command (without needing to re- open the original document). Printing gets done faster and does not depend upon the parent application. Typical situations, meriting use of such files are excel worksheets (with special selection of ranges), or frequently needed prints of certain specific pages of other documents. (b) In the sample db, printing of .prn files is achieved by run-time creation of batch file and activation thereof for executing the print job. This will work only where the installed printer matches the printer driver through which the file was originally created. (2) After completion of printing, the user should check for any parent application still remaining open and close the same if desired.. References - DAO 3.6, MS Scripting RunTimeDownload Access XP & 2K: uploads/37/PrintExternalFiles.zip |
||||
| Q | GoTo Top | |||
| Query_A2K10_MultiValueFields | Query | 121K | 30 Oct 2011 at 9:33pm | |
|
Query_A2K10_MultiValueFields Explanatory Notes This sample db demonstrates query based approach to bulk appending / updating / make table actions involving multi-value fields in Access 2010, without resorting to use of recordset or recordset2 objects, thus overcoming a known limitation associated with such fields. In this arrangement, an extra field named TempID is provided in the destination table. The append query is executed in two stages. In first stage, primary key values from source table are appended to TempID field in destination table. In 2nd stage, value elements of MVF field get appended, using an inner join between the source and destination tables (PK of source table equals TempID of destination table). Three styles are covered as follows: (a) Bulk appending of selected records (having multi-value fields) from one table to another. (b) Bulk updating of multi-value field elements (addition or replacement of values) in destination table based upon values held in source table. (c) Make table action covering multi-value fields based upon selected records in source table. Version: Access 2010 accdb file. References: Nothing extra. Download File >>: uploads/37/Query_A2K10_MultiValueFields.zip |
||||
| Query_AppendValuesViaSelectClause.mdb | Query | 41K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:19pm |
|
This sample db demonstrates use of SELECT clause in preference to VALUES clause, for appending values held by form / subform controls into the destination table. Some advantages of using SELECT instead of VALUES clause are mentioned below: (a) No need to worry about specific delimiters while concatenating values for various data types (e.g. ' for text type and # for date type data). Moreover, there is no problem if the value held by any of the controls happens to be Null (provided destination table is in a position to accept Null value in the field concerned). (b) No need to fix any embedded quotes, whether single or double. (c) No risk of dates getting mis-interpreted (in the process of concatenation into SQL string) when local system settings for short date are not as per US settings (mm/dd/yyyy). Following three styles of SQL for append action are shown separately for values held by controls on an independent form as well as those on a subform. Three controls are involved in each case, holding text, date and number type data respectively: (a) Use form/subform values directly. (b) Use form/subform values via recordset. Function Fn_rsApp() is used. (c) Use form/subform values via array. Function Fn_arApp() is used. A dummy table named T_Dummy with single field single record is used for facilitating adoption of SELECT clause instead of VALUES clause. Contents of this table are of no significance. Form/subform controls and correspondingly appended record in destination table get highlighted in special color. Simultaneously, the SQL actually used for the selected append action is also displayed for ready reference. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/Query_AppendValuesViaSelectClause.zip |
||||
| Query_CompareTables.mdb | Query | 32K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:18pm |
|
This sample db demonstrates an interesting approach for comparison of data in two tables. Discrepancies between the contents of different tables can be identified directly via union query, without having to undertake a series of individual field-wise comparisons. Hitherto, the main hurdle in taking advantage of this feature of a union query (without ALL clause) has been the absence of a foolproof and positive method to identify the source table for each row displayed by the union query, irrespective of any order by clause. Inclusion of a source-identifier string converts erstwhile identical records from different tables into unique ones, causing all such records to show up, defeating the original objective. However, the above limitation can be overcome by utilizing an interesting feature of Access in such a manner that various source-identifier strings pertaining to different tables are treated as identical by the union query, while remaining uniquely identifiable through proper interpretation. This approach is based upon the fact that for a given letter of alphabet, while upper and lower case are treated alike by the query, their interpretation based upon character codes is different. Discrepancies in the contents of two tables (for records identified by combination of PtID and AdmDate) are displayed as follows: 1 - Non-Matching records: (a) These are displayed in the subform at right. For each row, the name of its source table is also shown. (b) Each row is highlighted (separate back colors, depending upon the source table name). (c) For the fields under comparison, only those values are shown where there is a discrepancy between the two tables. Other fields (having no deviation) appear blank. (d) Displayed non-matching values are highlighted (separate back colors depending upon which table the field belongs to). 2 - Missing records (Existing in one table but not the other): (a) These are displayed in the subform at left. For each row, the name of its source table is also shown. (b) Each row is highlighted (separate back colors, depending upon the source table name). Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/Query_CompareTables.zip |
||||
| Query_ComputeAcrossFields_CodeLess | Query | 38K | 15 Jun 2011 at 12:13am | |
|
Query_ComputeAcrossFields_CodeLess Explanatory Notes This sample db demonstrates computations across fields via pure SQL. In the first step, normalization of data is accomplished, using Cartesian join between the source table and an ancillary single field table holding the field names. Thereafter, a totals query provides the desired results. Two styles are covered: (a) Compute across all fields. (b) Compute across top 3 fields (i.e. fields holding top 3 values). Version: Access 2000 File Format Reference: Nothing Extra Download File >>: uploads/37/Query_ComputeAcrossFields_CodeLess.zip |
||||
| Query_ComputeAsPerRulesTable.mdb | Query | 68K | Access XP | 08 Jul 2008 at 10:16pm |
|
This sample db demonstrates computations as per formulae existing in a dedicated separate table. Three alternative styles are covered: (a) Permanent Query utilizing user defined function for the computations. The results are spread across records. (b) Dynamically generated query - Results spread across columns. (c) Dynamically generated query - Results spread across records. Report based upon 4.2(a) above, is also included. Versions - Access XP (in Access 2000 File Format) References - DAO 3.6Download Access XP: uploads/37/Query_ComputeAsPerRulesTable.zip |
||||
| Query_CriteriaStyles | Query | 52K | 18 Oct 2010 at 1:20pm | |
|
Query_CriteriaStyles Explanatory Notes This sample db demonstrates use of criteria string stored in a table as embedded criteria clause in a saved query. It can of special relevance where the situation demands use of saved query. If the developer is in a position to manage via SQL string in VBA code or programmatic application of filter on a form or report, the task is rather straight forward, as it involves routine concatenation of stored criteria string into the overall SQL string. However, if it is found necessary to use stored criteria in a saved query, careful handling become crucial to successful implementation. This is on account of the fact that when a stored criteria string is embedded in the SQL of a saved query, its elements acquire a dormant state. For example "[MyField] IN (50,100,150)" does not come across as a meaningful clause. The argument for IN clause remain frozen as string "50,100,150". For activating such an embedded string, it needs to be enclosed within Eval() function. Moreover, field names, wherever occurring within the criteria string, are required to be replaced by their respective current values, duly enclosed within appropriate delimiters as applicable. This is because, a string argument for Eval() function has to be intrinsically meaningful. Two styles are covered as follows: (a) Criteria string is complete, inclusive of field names and operators. (b) Criteria string is partial, excluding field names. Operators are included. In other words it represents the right hand portion of a criteria expression, including operators. Style (a) has the advantage that it is universal in scope, permitting complex criteria, involving interaction between multiple fields for unlimited what-if scenarios, while the SQL for saved query remains unchanged. On the other hand, style (b) is simpler, not requiring any pre-processing of criteria string before application of Eval() function. However, in this case, the expression is confined to only one field at a time, with separate saved query per criteria field of interest. For each of the two styles, two alternative methods for fetching the criteria string are demonstrated (either via form control or via DLookUp() function)' The sample has interactive feature, wherein the user can play with the originally stored criteria strings and watch the outcome. At each stage, SQL of saved query currently in force, also gets displayed. Whenever desired, original criteria strings can be restored on click of a command button. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Query_CriteriaStyles.zip |
||||
| Query_CrosstabAttendanceMarking.mdb | Query | 67K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:15pm |
|
This sample db demonstrates date time marking of employee's attendance sheet, in crosstab style, while retaining the original data in normalized state. Since no keyboard inputs are involved (date time stamping is done via mouse dbl click), the cross tab query serves directly as form's record source, without needing any interim table. For the selected year and month, the integrated output gives following display: (a) First header row - shows weekday names. (b) Second header row - shows day of the month (c) Third row onwards - show attendance markings (date/time) for respective employees. (Navigation to the two header rows (a and b above) is prevented programmatically) The over-all union query composed of three crosstab components (a to c above) is devised in such a manner that its output adapts to different year/month combinations without requiring run-time manipulation of control source property for various controls representing days of the month. Saturdays and Sundays in the header row are highlighted in grey. Date time marking of attendance is carried out by dbl click in the column for current date (on the row pertaining to given employee). Attendance marking is permitted only in the column matching current date. Once marked, it can not be overwritten. Total days actually attended is displayed in column titled TotInMonth, placed adjacent to EmpName column. The form is in datasheet view and these two columns are frozen, so as to remain in view, irrespective of navigation amongst the remaining columns (each of which represents the day of month). On opening the form, it displays the status for current year and month and the cursor gets positioned in the column representing current day of the month, on the first record where attendance has not yet been marked. If there is no employee awaiting attendance mark, the cursor goes to the first employee's record. While navigating through the records, EmpName and Current day's attendance column for the current record are highlighted (in light blue if attendance marking is pending, otherwise in light pink). Blank attendance sheets for the selected month / year can be generated via report preview, by clicking the appropriate command button. Note: Adoption of datasheet view (as done here) affords the advantage of freezing EmpName and TotInMonth columns so that these are always in view. However, as the user scrolls down, the contents of header rows go out of view. Column headings remain visible and do provide an indication of the day of the month involved (e.g. D01, D02, -- etc). The other alternative would be to adopt continuous form lay out. In that case, freezing of desired columns would not be readily available. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/Query_CrosstabAttendanceMarking.zip |
||||
| Query_DateSpansInDateRange.mdb | Query | 60K | Access 2K/XP/2K3/2K7 | 08 Jul 2008 at 10:13pm |
|
This sample db demonstrates monitoring of common intersections (number of days) between multiple instances of date spans (Leave availed by employees) visa- vis the selected date range. Such an intersection takes place when (Start date of date span is less than end date of selected date range) And (End date of date span is greater than start date of selected date range). Selection of date range (DtFrom and DtUpto) for which employee's leave status is to be viewed, is carried out via combo boxes on the main form. On opening the form, default values on these combo boxes cover the full date range reflected by available data. As per built in safeguard, the user can not have a value for DtFrom that is greater than DtUpTo. Similarly, it is not feasible to use a value for DtUpTo that is less than DtFrom. The subform at left displays the names of all employees whose leave periods overlap the date range (either partly or in full). Against each employee, the total number of days overlap is also shown. By user selection via an option group, this list can be sorted either as per employee name (asc) or total days overlap (desc), as desired. Current record is highlighted in special color, while the font for total days overlap is highlighted in dark red color. For the employee featuring in current record of above subform, details of individual spells of leave (StartDate / EndDate in each case) for that employee, get displayed in chronological order in an adjacent subform at right. Only such of the leave spells are shown as have a positive intersection with the selected date range. In each case, portion of leave period that actually falls in the selected date range is also shown, duly highlighted in color. Whenever the user alters the date range by making new selections in the combo boxes on the main form, the information displayed by the two subforms as mentioned above, gets updated simultaneously. Report Generation is provided in three styles as follows: Style 1 - All Employees: Sorted as per employee name. Style 2 - All Employees: Sorted as per total leave days overlap (Desc). Style 3 - Current Employee Only. Leave Chart (Employee-wise) - An interesting feature has been incorporated in the above reports. For each employee, individual leave spells overlapping the selected date range are depicted as a series of colored horizontal strips superimposed on a background bar representing the selected date range. Note - A common report is used for all three styles listed above. Filtration and sort order are tailored at run time, to match the combination of options selected by the user. As added convenience, the caption and fore color of the label at top right corner of the report are set to distinct values to mark different styles. Download Access 2K/XP/2K3/2K7: uploads/37/Query_DateSpansInDateRange.zip |
||||
| Query_FillMissingValues | Query | 42K | 07 Feb 2011 at 8:56am | |
|
Query_FillMissingValues Explanatory Notes: This sample db demonstrates filling up of missing values in a sequential series. Three types of source data are covered: (a) Number series. (b) Alpha-numeric series. (c) Date series. Subform on left shows the start and end values for each missing range of sequential values per PersonID. Subform at right depicts all the missing sequential values duly filled in. Each record representing start of a new sequence is highlighted in color. Note: A single field table (T_Ref) populated with sequential numbers from 0 onwards is used as the driver for eliciting the missing values. The number of records in this table should be such as to cover the maximum likely range of missing values. Version: Access 2000 file format. References: Nothing extra. Downloas >>: uploads/37/Query_FillMissingValues.zip |
||||
| Query_FindClusters | Query | 43K | 20 Nov 2011 at 11:41pm | |
|
Query_FindClusters Explanatory Notes This sample db demonstrates identification of clusters in value series as per given maximum allowed spacing between adjacent elements of a cluster. For this, sorted values are subjected to sliding comparison via unequal self outer join. Original data is displayed in the subform at left. Based upon user selection of SeriesID and maximum allowed spacing between adjacent elements of a cluster, the values qualifying as members of various clusters are displayed in the subform at right. Start value for each cluster is also displayed in a separate column. Simultaneously, matching value elements in in the subform at left get highlighted in distinct color. Version: Access 2000 File Format References: DAO 3.6 Download File >>: uploads/37/Query_FindClusters.zip |
||||
| Query_GrpSequentialsAndMissing | Query | 39K | 27 Jan 2011 at 8:49am | |
|
Query_GrpSequentialsAndMissing Explanatory Notes: This sample db demonstrates identification of data blocks as well as missing portions in a sequential series. Three types of source data are covered: (a) Number series. (b) Alpha-numeric series. (c) Date series. Subform on left shows all records, duly highlighting the start of each new block of sequential series. Subform at right depicts group-wise gist (for each PersonID) of sequential blocks as well as missing portions, duly indicating the start and end values for each set. Missing blocks are highlighted in light grey. Note: For identifying the start and end values for available as well as missing blocks in the given sequential series, an interesting technique has been used in this sample db, eliminating the need for any driver or temporary table. Instead, sliding comparison with projected next and previous values has been adopted through self joins based upon source data. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Query_GrpSequentialsAndMissing.zip |
||||
| Query_MonitorAnnualEvents | Query | 52K | 08 Jul 2008 at 10:12pm | |
|
Query_MonitorAnnualEvents (Updated: 11-Jan-2010) Explanatory Notes: This db demonstrates tracking of annual events like birthdays, marriage anniversaries etc Events Alert: Clicking first command button on main switchboard displays such of the events as are due within next 10 days or are already overdue by upto 3 days. Events Status At A Glance (All Events): Clicking 2nd command button on main switchboard displays all events at a glance. Dynamic sort order is applied in two alternative styles as per user preference: (a) Starting 3 days before today. (b) Starting with previous month. The month previous to current one is placed at top. For example, if current month is September, the one at top will be August, while the last one will be July. (In alternative (b), although first record starts at beginning of last month, it is ensured that if there are any events due within next 10 days or already overdue by upto 3 days, these get positioned at top in the available display window. If no event is currently due / overdue, records for the current month get positioned at top in the available display window). Distinct color highlights are provided as follows: (a) Overdue by upto 3 days - Pink. (b) Falling due on current date - Ochre. (c) Due within next 10 days - Light Blue. Note: Functions Fn_DaysAhead() & Fn_DaysPast() are used to detect whether an event is imminent or overdue as on current date. Functions Fn_DtRank() and Fn_MonthRank() are used for enforcing dynamic sort order as per style (a) or (b) above.Version: Access 2000 File FormatReference: DAO 3.6Download >>: uploads/37/Query_MonitorAnnualEvents.zip |
||||
| Query_Pedigree.mdb | Query | 143K | Access XP (A2K format) | 07 Jul 2008 at 11:13pm |
|
This sample db demonstrates tracking of pedigree for cats upto 8th generation level. Base data is contained in table named T_Cats. For convenience, identifier strings named ParentType are also generated. For example SS_2 means Sire > Sire, culminating in Gen Level 2. Similarly, DSDSD_5 means Dam > Sire > Dam > Sire > Dam, culminating in Gen Level 5. In order to overcome the table limit inherent in nested queries with multiple tables (leading to the message that the query is too complicated), interim tables (T_Ped_6_01 to 04) have been used to store the results of sixth generation queries. These tables have in turn been used as the base for 7th generation onwards. With each higher generation level, additional columns get created in geometric progression. For proper presentation of final results and for smooth report generation, the output of these queries has been normalized (converting the columns into records) and appended to table T_Ped. Although it does not take too long to process the existing data of 1175 records simultaneously without any filters, it is recommended that while generating the report, suitable range of ID's is selected. Otherwise, the unfiltered report containing pedigree information for all cats in source data will span more than 1800 pages. In the results displayed immediately after processing of data, the record for original cat in each case is shown high-lighted in distinct color, while the records for parents are highlighted in separate colors for Male/Female. Similar pattern has been adopted for the report. Progress bar has been provided on data processing form. Existing 1175 records translate into an output of more than 20,000 records, taking approx. 26 secondsDownload Access XP (A2K format): uploads/37/Query_Pedigree.zip |
||||
| Query_RowNumbersAndSumLargeData | Query | 70K | 09 Oct 2008 at 4:44pm | |
|
Query_RowNumbersAndSumLargeData Sample Db - Explanatory Notes Subqueries (or user defined functions based upon recordsets) for calculation and display of row numbers and running sum, are found to be slow in case of large data sets. Use of increments to global variables offers a faster alternative. This method is preferably used through an action query, as calculated values directly displayed via select query tend to be volatile (the results keep on changing as one navigates up and down the records. Two alternative methods based upon incrementing global variables are demonstrated as follows. In each case, two styles are covered, namely (a) Straight simple sequence and (b) Group-wise sequence. If it is simple sequence, the user defined functions have provision for specifying the starting number or sum - if desired: 1 - Row number and running sum - via update action. 2 - Row number and running sum - via make table action. For academic interest, depiction of row numbers through generation of autonumbers has also been demonstrated as follows (this approach is however not as fast as that involving increments to global variables): 3 - Row number - via append action.. 4 - Row number - via make table action. Note: (a) Use of incrementing global variables (items 1 and 2 above), apart from being the fastest approach, has the added benefit that it is not dependent upon availability of primary key. (b) Four user defined functions utilized in implementing this method are given in the general module. (c) Use of these functions in WHERE clause of pertinent queries is meant to initiate the sequence in desired manner. (d) If filter has been applied to displayed data, generation of row number and running sum gets implemented on the filtered data, as demonstrated in the sample db. Reference required - DAO 3.6 Version - Access 2000 file format Download >> uploads/37/Query_RowNumbersAndSumLargeData.zip |
||||
| Query_SplitToMultiRowsReMergeSorted | Query | 36K | 17 Jun 2009 at 6:03pm | |
|
Query_SplitToMultiRowsReMergeSorted Explanatory Notes This sample db demonstrates splitting of comma (,) or star (*) separated string elements into multiple rows followed by re-merging of these elements duly sorted, back into strings. It is a virtually codeless solution, but for use of two wrapper functions related to built in Split() function. For splitting the strings into multiple rows, a single field table (T_Ref) populated with numbers 0 to 10 is used in Cartesian join with the table holding source data. The sorted output is appended to table T_Jobs_Split, which in turn is used to generate the crosstab query followed by final select query, re-merging the sorted elements back into strings. Note: (a) If the maximum number of elements in one string is likely to exceed 10, additional numbers can be added to table T_Ref. At the same time, pivot clause of crosstab query and calculated field of final select query (based upon crosstab query) will need o be expanded to accommodate additional columns. (b) Use of number reference table (as compared to master job table) in Cartesian join with source table for generating multiple rows has the advantage that it eliminates the risk of inconsistent results arising out of partial match using Like operator. Moreover, it does away with the need of preparing such a table and keeping it up-to-date, apart from ensuring that there is no inadvertent discrepancy in its contents. Version: Access 2000 file format. Reference: DAO 3.6 Download >>: uploads/37/Query_SplitToMultiRowsReMergeSorted.zip |
||||
| Query_TopsPerSubGrpLargeData.mdb | Query | 103K | Access 2000 format | 07 Jul 2008 at 11:10pm |
|
Description: 1 - This sample db examines various alternatives for getting Top values (as per date) so as to identify the most efficient method in the context of large data. Main data table has test results recorded for various patients over various dates. The objective is to find (Top result means latest result as per date): (a) Top 3 Results per test per patient. (b) Top 2nd and 3rd Results per test per patient. Note - Task (b) involves extra processing load as compared to (a) 2 - As a query acting directly on the whole data at a time is found to be impractical (it is extremely slow or hangs up altogether in case of large data size), five alternative methods for handling each of the objectives mentioned at 1 (a) & (b) are demonstrated. 3 - For facilitating experimentation with test data, form F_FillTables enables programmatic population of data tables as per the size desired. For this task, two alternative styles are demonstrated: (a) Based purely upon recordsets (Very slow). (b) Combination of recordsets and append query (Extremely fast). Note - For building up test data consisting of 1200,000 records (1000 patients, 20 tests per patient, 60 readings per test), method (a) takes 4,605 seconds while method (b) does it in just 210 seconds. 4 - Performance of methods using form interface (A, AA and B to E, F, FF and G to K ) for getting the top results gets automatically logged in table T_Log. 5 - For academic interest, sample queries acting directly upon the whole data, are also given (in series Q_Z_L to Q_Z_R). These can be tested on comparatively small sized data on account of slow speed and tendency to hang up if data size is large. 6 - Conclusions: Results of certain trials are contained in table T_Conclusions. Conclusions based upon these are summarized below: 6.1 - Piecemeal processing of data in convenient packets, through saved queries with Top clause, using filter criteria based upon global variables fed via user defined functions, and storage of output in an interim table (T_Result), is found to be fastest (Form options A, B, F and G). 6.2 - Using reference to form controls (Form options AA and FF) instead of user defined functions based upon global variables as in 4.6.1 above for performing the same job, results in noticeable slow-down. For instance, using option A or B, a result set having 40,000 Top 2nd and 3rd results based upon data having 1200,000 records gets displayed in 581 sec, whereas, using option AA, it takes 798 sec. 6.3 - Action similar to 6.1 above, but using SQL string built at run time (Form options D and J), is found to be slower than that using saved queries with filtering via global variables, though faster than saved queries with filtering via reference to form controls. For instance, using option D, a result set having 40,000 Top 2nd and 3rd results based upon data having 1200,000 records gets displayed in 630 sec as compared to 581 sec for options A or B, and 798 sec for option AA. 4.6.4 - Action similar to 6.1 above, but using saved query with a count subquery (Form options C and H), is found to be very slow. For instance, using option C, a result set having 4,000 Top 2nd and 3rd results based upon data having 120,000 records takes 1,082 sec to get displayed, as compared to mere 17 sec for options A or B. 6.5 - Using a supplementary table (T_Temp) to store the filtered piecemeal records (Form options E and K), before processing the same and appending to results table (T_Result), does not offer any advantage. Instead, it takes almost double the time for options A and F. 6.6 - Queries acting directly upon the whole data at a time are all found very slow and prone to hang up in case of larger data size. For academic information, some of the comparative results, based upon data consisting of 24,000 records are furnished below: 6.6.1 - Top 3 results - 1200 records in result set. (This task, if performed by form option F, takes MERE 2 Seconds). (a) Query: Q_Z_L_Top3_TotalsQry_TopInHaving - 150 Sec (First screenfull appears in 5 Sec). (b) Query: Q_Z_M_Top3_TotalsQry_TopInWhere - 140 Sec (All Results at completion only). 6.6.2 - Top 2nd and 3rd results - 800 records in result set. (This task, if performed by form option A, takes MERE 3 Seconds). (a) Query: Q_Z_N_Top2nd3rd_PlainQryWithCountSubQuery - 212 Sec (All Results at completion only). (b) Query: Q_Z_O_Top2nd3rd_TotalsQryCountInHaving - 217 Sec (First screenfull appears in 10 Sec) (c) Query: Q_Z_P_Top2nd3rd_TotalsQryCountInWhere - 210 Sec (All Results at completion only). (d) Query: Q_Z_R_Top2nd3rd_PlainQryWithUDF - 130 Sec (All Results at completion only). Versions - Access 2000 File Format (Developed on Access 2003. Also tested on Access XP & 2K) Download Access 2000 format: uploads/37/Query_TopsPerSubGrpLargeData.zip |
||||
| QueryWithSubTotals.mdb | Query | 90K | Access XP & 2K | 07 Jul 2008 at 11:07pm |
|
This sample db demonstrates interesting use of union query, so as to display sub-totals embedded in a normal select query. As a result, even in datasheet view of a form, it becomes feasible to get an output incorporating sub-totals in report style.Download Access XP & 2K: uploads/37/QueryWithSubTotals.zip |
||||
| R | GoTo Top | |||
| RemoteDb.mdb | External | 155K | Access XP & 2K | 07 Jul 2008 at 11:06pm |
|
This sample db demonstrates handling of objects and data in an external db, without setting up any library reference or table link. This feature allows flexibility in deployment, without having to worry about missing references or links. Following tasks are covered (using the Local db). (a) Preview a report belonging to remote db. (b) Background printing of a report belonging to remote db. After completion of print job, the remote db gets closed automatically. No distraction to the user of local db (c) Open a form in remote db. (d) Run a local query using data from table existing in remote db. Note - In Access 2000, the start up option for displaying database window should be kept enabled for smooth execution of (a) to (c) above. References - DAO 3.6Download Access XP & 2K: uploads/37/RemoteDb.zip |
||||
| Report_CustomFields.mdb | Report | 38K | Access 2K/XP/2K3/2K7 | 07 Jul 2008 at 11:05pm |
|
This sample db demonstrates report generation as per user selection of fields (including the order in which the same are to be displayed). Such a feature can be useful for printing labels. CanGrow property of text boxes in such cases is set to No. User can identify fields for inclusion in (or exclusion from) the report by selecting / de-selecting the check box against each field name displayed in a subform. The order in which the selected fields will get finally displayed can also be manipulated by clicking the Up / Dn command buttons adjacent to the subform so as to re- position the selected row as desired. In the subform, field names where the check boxes are in de-selected state are highlighted in a different back color and get positioned at bottom. In report module, any of the two alternative methods mentioned below could be adopted: (a) Provide necessary number of unbound text boxes in the detail section and assign the control source selectively in report's open event. Hide and re- position the redundant controls suitably. (b) Place all potential fields as bound controls and manipulate their visibility and position as required. Since manipulation of visibility & position of controls is involved in both cases, the sample is based upon alternative (b) above, eliminating the extra step involving assignment of control source. For better speed, manipulation of controls (visibility and position) is carried out in report header's format event, instead of using similar event pertaining to the detail section. More-over, it is ensured that the process is confined only to the first pass of formatting. Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/Report_CustomFields.zip |
||||
| Report_CustomGroupSort | Report | 72K | 07 Jul 2008 at 11:02pm | |
|
Report_CustomGroupSort (Updated on 20-Jan-2010) Explanatory Notes This sample db demonstrates dynamic grouping & sorting of access reports at run time, as per desired fields, in desired order. In a simulated sorting & grouping subform, the user can click various check boxes so as to specify the fields to be earmarked as group headers, apart from desired sort orders for all available fields. Fields earmarked for group headers do not get displayed in detail section. The position of displayed controls in detail section is adjusted suitably so as match the order specified in sorting & grouping subform. At the same time, width of control named Title is manipulated suitably so as to make full use of available space. Note: Controls in detail section have been provided with a light border so as to facilitate convenient visual indication regarding manipulation of their position and width. Version: Access 2000 File Format Reference: Nothing Extra. Download >>: uploads/37/Report_CustomGroupSort.zip |
||||
| Report_DynamicCaptions | Report | 70K | 15 Apr 2010 at 9:53am | |
|
Report_DynamicCaptions Explanatory Notes This sample db demonstrates printing of multiple reports with dynamic assignment of caption via report's open event. The user can alter the sequence of printing as desired, by moving the report names up or down. Out of available reports, those required to be included in sequential printing are selected by ticking the check box adjacent to report name. It is observed that if a report is printed directly by opening in normal view, the caption assigned programmatically in report's open event does not get properly captured in the print queue of the printer. This is explained by the fact that caption of active window tends to get captured for the above purpose. Depending upon the local set up, direct opening of report might not project its window presence adequately. For absolute certainty, it is therefore necessary that the report is first opened in preview mode, followed by PrintOut and Close actions. It would be interesting to note that you can't get away by opting for preview in hidden mode. In that case, you might even end up with the caption of the calling form getting depicted in print queue. The defining requirement is that the report should be the visible active window (even if for a very brief duration) when requisite information is getting passed to the printer. Time delay (in clock cycles) as included between various stages (e.g. opening in preview mode, print out and closing), as a measure of abundant precaution, is meant for stabilization between one step and the next. Version: Access 2000 File Format Reference: DAO 3.6 Download >>: uploads/37/Report_DynamicCaptions.zip |
||||
| Report_FixedRowsPerPgOrGrp | Report | 53K | 02 Sep 2009 at 4:27pm | |
|
Report_FixedRowsPerPgOrGrp Explanatory Notes This sample db demonstrates printing of fixed number of rows per page or group in an access report. For each case, two alternative methods are shown: (a) Solution based upon VBA code in report's module. This does not call for any interference to report's record source. (b) Solution based upon SQL of report's record source, minimizing the need for VBA code. SQL based solution as per (b) above makes use of a driver table having a single field populated with sequential numbers from 1 onwards. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Report_FixedRowsPerPgOrGrp.zip |
||||
| Report_GrpPaging_XofY_WithTOC | Report | 176K | 07 Jul 2008 at 10:59pm | |
|
Report_GrpPaging_XofY_WithTOC Explanatory Notes: The sample db demonstrates display of sub-page numbers, group-wise (for top level group), in addition to normal page numbers for the report as a whole. Table of Contents (TOC) showing a summary of group names with their start & end pages has been provided at the beginning of report. Same report can be opened in TOC or Non-TOC mode, as per user's choice. In Non-TOC mode, the table of contents gets bye-passed. The zipped folder contains two versions (2K & XP). The XP version has a string of bridging dots in table of contents. This feature has been avoided in 2K version as the results achieved through TextWidth property in 2K are not found quite perfect. (On the other hand, the behavior in XP onwards is observed to be satisfactory). For depicting sub-page numbers for each individual group, it is necessary to have two cycles of formatting, before commencement of print-preview. The first cycle populates the array, while the subsequent one assigns values to the unbound text box used for depicting sub-page numbers group wise. In order to force this behavior, it is necessary that a calculated control containing [Pages] as a part of its expression is present in the report. Two alternative methods for storing information regarding group-wise page details are demonstrated. First method uses an array for storing the particulars, while the other does it via a dedicated table. In the sample, group page particulars are displayed at top (just below group header content) while overall page numbers for the report as a whole, are shown at bottom. The placement of relevant text boxes can be altered suitably, if so desired. Version: Access 2000 File Format Reference: DAO 3.6 Download >>: uploads/37/Report_GrpPaging_XofY_WithTOC.zip |
||||
| Report_HideDupsConditional.mdb | Report | 25K | Access 2K/XP/2K3/2K7 | 07 Jul 2008 at 10:56pm |
|
This sample db demonstrates conditional hiding of duplicates in access reports. Two styles are covered: (a) For each item in 2nd column, hide duplicates in 1st column (b) For each item in 1st column, hide duplicates in 2nd columnDownload Access 2K/XP/2K3/2K7: uploads/37/Report_HideDupsConditional.zip |
||||
| Report_HideGrpHdrsIfNoDetail | Report | 85K | 02 Dec 2008 at 11:09pm | |
|
Report_HideGrpHdrsIfNoDetail Sample Db - Explanatory Notes This sample db demonstrates run time hiding of group headers and footers in access reports, if there are no longer any printable records for the group as a result of conditional cancellation of detail section's format event. This functionality is achieved by using a set of two classes. The main class (C_CondHeaders_Main) determines the maximum existing number of group levels and report sections, apart from building up a list of unique group keys. This class also builds up a collection of objects conforming to sub-class (C_CondHeaders_Sub). Each instance of the subclass represents a group header or footer section. Instead of trying to pass report section object as an argument, simply the section index is supplied, thereby circumventing the hurdle otherwise faced. Corresponding report section object (WithEvents) is generated within the sub-class. List of unique group keys generated in ShowDetal method of main class gets pushed to the sub-class via the latter's ShowGroups property. Action for hiding the group header / footer as applicable, is carried out in the sub-class. With the combination of two classes as outlined above, the arrangement becomes completely generic, capable of handling any number of grouping levels. At the same time, the code becomes drastically shorter and compact. IMPORTANT: 1 - The report must have a calculated text box having [Pages] as part of its expression. 2 - Each group header section should have a text box bound to that level's ControlSource field 3 - If there are any sorting/grouping levels above the last header which do NOT have headers, then for each such level, the report must include a textbox (may be hidden) located in Detail section, bound to the field serving as group's ControlSource. USAGE - In Report Module: =============================== Declarations Section: Private cRh As New C_CondHeaders_Main In Report_Open: Set cRh.ReportObject = Me In Report_Close: Set cRh = Nothing Last statement in existing Detail_Format: If Cancel = False Then cRh.ShowDetail =============================== Reference required - DAO 3.6 Version - Access 2000 file format Download File >>: uploads/37/Report_HideGrpHdrsIfNoDetail.zip |
||||
| Report_HighLightChanges.mdb | Report | 34K | Access 2K/XP/2K3 | 07 Jul 2008 at 10:55pm |
|
This sample db examines the values on report with current data, as compared to the one with old data, and highlights any changes in bold red. If current data contains any fresh records, over and above the old data, these get highlighted in light blue back color. The objective is achieved through generic code in report's module, without needing any specific conditional formatting of individual controls. Important: (a) The report must have a bound control named TxtPk, representing the primary key uniquely identifying a record. This control can be kept hidden if so desired. (b) Name of query representing old data must be the same as that of query serving as record source for current report, but with a suffix "_OLD". For example, if the record source for current report is named, "Q_A", the query representing old data will be named "Q_A_OLD" Download Access 2K/XP/2K3: uploads/37/Report_HighLightChanges.zip |
||||
| Report_MultiCopiesCustomLegends | Report | 111K | 05 Jul 2009 at 5:55pm | |
|
Report_MultiCopiesCustomLegends Explanatory Notes This sample db (meant for Access 2K3 and later) demonstrates printing of multiple copies of a report, each with distinct legend in page footer, as specified by user. The desired legend and its fore color for various copies can be entered / edited in a subform. Out of various planned copies, those required to be included in current print session can be selected via corresponding the check boxes. It is observed that timing plays an important role while opening the report multiple times for generating the desired number of copies. Simple straightforward placement of a series of opening commands for a given report in same code block runs the risk that report instances other than the first one might not get printed. This is because - unless the existing instance is completely closed, any attempt to open a fresh instance is liable to get suppressed. Trying to open the next instance via close event of existing one runs similar risk. As an abundant precaution and in order to circumvent the above pitfall, close event of existing open instance of report is used to activate the timer of calling form. The timer event of the form opens the next instance of report and at the same time, de-activates itself (by setting TimerInterval to 0). These steps continues repeating till all selected copies get printed. In order to facilitate preparatory work with the printer before commencement of each fresh copy, the user is presented with a message box with Yes / No / Cancel option. Clicking Yes prints the copy in hand while clicking Cancel results in cancellation of all remaining copies. If the user is not quite ready, clicking No will defer printing and timer will get reset to present this same message box at regular intervals depending upon print mode (2 sec for preview mode and 4 sec for hard print mode). As and when a given copy gets done, its date and time is displayed in last column of the subform. If the job has been performed <= 8 Hrs ago, it gets highlighted in light green back color, otherwise grey. Page numbering group-wise in x of y style is also demonstrated. This is in addition to the normal overall numbering (Page of Pages). Note - The sample is meant for Access 2K3 and later (as report's OpenArgs feature is not available in Access 2000). Version: Access 2002-2003 file format. References: Nothing extra. Download >>: uploads/37/Report_MultiCopiesCustomLegends.zip |
||||
| Report_PrintLabelsCustomStart | Report | 48K | 28 Aug 2009 at 5:24am | |
|
Report_PrintLabelsCustomStart Updated On 10-Sep-2011 (Conditional format on PK column skipped - for improved stability) Explanatory Notes This sample db demonstrates printing of labels in such a manner that starting position of first label as well as different number of copies for each label can be specified by the user. Out of available source data, only the labels selected by user will actually get printed. The report used for this demo has three columns. Two methods are covered as follows: (a) Everything is managed via VBA code. No interference to report's record source is needed. (b) The query serving as report's record source is devised in such a manner that it takes care of specified start position for first label to be printed as well as different number of copies for each label as desired. VBA code is minimal, just for hiding the label control when blank. Method (b) above makes use of driver table T_Ref having a single field RefNum populated with numbers 1 to 200. If the number of copies for any label are likely to exceed this number, contents of table T_Ref can be expanded suitably. Source data consisting of three fields holding top middle and bottom lines for the intended label, is displayed in a subform along with a column displaying the desired number of copies in each case. The last column has bound check boxes enabling the user to select which of the labels are to be printed. Sample label pertaining to current record in the subform is displayed on the parent form. Any editing of contents in the subform gets reflected promptly in the sample label. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Report_PrintLabelsCustomStart.zip |
||||
| Report_PseudoGraphs | Report | 99K | 30 Jul 2009 at 1:18am | |
|
Report_PseudoGraphs Explanatory Notes This sample db demonstrates simulated graphs on access reports, using a single text box for a given series. The following situations are covered: 1 - Monthly salary as per scales notified from time to time. For a given month and year, the most recent scale notification upto that month year becomes applicable. Whenever there is a change in salary from one month to the next, different back color is assigned so as to facilitate visual appreciation. 2 - Room bookings. 3 - Score sheets for star war games depicting the initial count of units for engine impulse, engine warp-1, engine warp-2 and superstructure. Depending upon field values in source table, series of numbered boxes are depicted. Note: (a) Simulation of graph using a single text box is implemented by suitable manipulation of report's MoveLayout property. (b) In form F_Salary, the technique for programmatically positioning specific block of records appropriately in the display window of subform control showing notification of salary scales, is also demonstrated. (c) In order to keep sample data for salary scales current with respect to the present year, update query named Q_SalaryMaster_UpDt is executed via load event of form F_SwitchBoard. While undertaking practical adaptation of this sample db, this statement should be disabled. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Report_PseudoGraphs.zip |
||||
| Report_RepFooterformultiColumns | Report | 33K | 12 May 2009 at 1:04am | |
|
Report_RepFooterForMultiColumns Sample Db - Explanatory Notes This sample db demonstrates handling of report footer for a multi-column report with Down and then Across (D&A) layout in such a manner as to prevent its spill-over to a redundant extra page. In a multi-column report with D&A layout, spill-over of normal report footer section to an extra page, despite adequate space being available at the end of last column, is attributable to the fact that page footer has already been hit by the first column. Once that happens, there is no longer any room for the report footer, which has to move over to next page. Two alternative styles are demonstrated in this sample db: 1 - Style A - Use the page footer in dual role: 1.1 - This work-around involves placing the controls meant for report footer content also in the page footer. It is to be noted that an actual report footer can not be afforded as it will result in spill-over to extra page if the detail section content on last page is in excess of one column. 1.2 - If any calculated values (e.g. Count(*) etc) pertaining to report footer are needed, these can be grabbed via a calculated control in outermost dummy group header. This control can then be referred in the page footer control meant to show report footer content. 1.3 - Using page footer's format event, visibility of controls can be manipulated in such a manner that upto the last but one page, only the controls meant for page footer get displayed. On the last page, only the controls meant for report footer get displayed. Certain controls needed in both cases get displayed permanently. For convenient identification, following tag values can be used: (a) Page Footer only - PP (b) Report Footer only - RR (c) Required for both - PR 1.4 - Using page header's format event, height of page footer section can be manipulated in such manner that it is at its normal value for pages upto last but one. On the last page, height of this section gets increased to accommodate the requirement for report footer. Simultaneously, it is necessary to manipulate the top and height properties of report footer controls. The task is aided by including normal height of each report footer control as second element of comma separated string serving as its tag value. 1.5 - On other than last page, controls meant for report footer content are set to some arbitrary low height, apart from adjusting their top values - so that section height of page footer section is governed by page footer controls only. On the last page, Top positions & heights of report footer controls are restored to normal values. 1.6 - In page header's format event, the height of page footer section is kept sufficiently large to start with. After the tops and heights of individual controls have been set, an interesting alternative is available for snapping back the height of page footer section to a tight fit over the modified size of controls. At the end, simply set the page footer section height to zero. 2 - Style B - Place the report footer in an outermost dummy group footer: This is an extremely simple solution, not requiring any code. It does have the limitation that the width of report footer is confined to that of one column. If this restriction is not acceptable, style A as described in 1 above, can be adopted. Version: Access 2000 File Format Reference required: Nothing Extra Download File>>: uploads/37/Report_RepFooterForMultiColumns.zip |
||||
| Report_ReportFooterOnTopOfPageFooter.mdb | Report | 32K | Access 2K/XP/2K3/2K7 | 07 Jul 2008 at 10:51pm |
|
This sample db demonstrates placement of report footer on top of page footer. It caters for variable heights of report headers / footers (CanGrow property = Yes). If there is going to be any potential blank space between report footer and the page footer, it is moved above the report footer so that the bottom of report footer sits on top of the page footer. The desired objective is achieved by inserting an outermost dummy group footer whose height is manipulated at run time so as to serve as dynamic blank spacer block. Two stage opening of report is undertaken because dynamic grown heights of report sections are available only in print event. On the other hand, adjustment of section heights is permitted only in format event. Hence the need for extra cycle of report opening. Stage A - First cycle of opening the report, stores dynamic values of Report Footer bottom and Page Footer top in unbound text boxes on form F_SwitchBoard Stage B - Re-opens the report, duly adjusting the height of outermost dummy group footer, so as to force the report footer bottom to move close to page footer top. Note - In the sample db, 4 lines of text in report header as well as footer are inserted programmatically in unbound text boxes, just to cover grown height features of sections concerned. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download Access 2K/XP/2K3/2K7: uploads/37/Report_ReportFooterOnTopOfPageFooter.zip |
||||
| Report_SendInMailBody.mdb | Report | 84K | MDB | 07 Jul 2008 at 10:49pm |
|
Report_SendInMailBody (Updated on 18-Dec-2008) Sample Db - Explanatory Notes This sample db demonstrates sending an access report by placing it in the body of eMail message. Though sending a report as an attachment, using DoCmd.SendObject command is pretty straightforward, including it in the body of eMail itself, requires some extra measures. Word and Outlook objects are used in this approach and should therefore be available on sender's computer. Two styles are covered, one deals with a single report at a time, while the other provides for sending multiple reports as selected in a list box. Temporary output files are stored in folder named ReportOutput located in the same folder as the sample db. This folder gets created programmatically, if not already existing. An important feature is the display of horizontal lines in final HTML output by using line markers (*^*) in the report. This overcomes a well known limitation otherwise faced in transferring lines from access reports to HTML format. Even though labels with string of hyphens as caption, could be used as replacement for lines, the final results in that case are not quite satisfactory. When an access report is converted to HTML format, it translates to one file per page. While concatenating the contents of these files, care has been taken to eliminate unwanted hyperlink labels (First Previous Next Last) appearing at the bottom of each page. In each case, two alternative modes for transferring the report to mail body are demonstrated: (a) Output the report pages to HTML files (one file per page), read these files, consolidate the content and assign the same to HTMLBody property of outlook application's mail item object. (b) Output the report to word file, save it in HTML format, read this file and assign the content to HTMLBody property of outlook application's mail item object. This method is slower than (a) above, mainly on account of the time taken in opening the word application. Moreover, the text alignment and rendering of horizontal lines (through line markers) is not completely satisfactory. This method is given from an academic point of view only. Note: Each time Outlook is used for sending messages programmatically, the user is required to clear a warning message (informing that some program is trying to send a message through Outlook). If manual intervention in this regard is to be circumvented, third party utility like ClickYes could be used. Reference required: (a) Outlook Object Library (b) Word Object Library (c) Microsoft Scripting RunTime Version - Access 2000 file format Download File >>: uploads/37/Report_SendInMailBody.zip |
||||
| Report_SendPartsByGrpLevel.mdb | Report | 49K | Access 2K/XP/2K3/2K7 | 07 Jul 2008 at 10:41pm |
|
This sample db demonstrates outputting of selected access report group-wise in HTML format. For each different value of the group header, portion of report pertaining to that group value gets saved as a separate html file, in folder named ReportParts, located in the parent folder housing the sample db. If no such folder exists, it gets created programmatically. User selection of report is carried out via list box at left. If desired, copies of report parts group-wise can also be sent as attachments to eMail. User can select destination eMail addresses in subform at right, by selecting the check box named SendMark. If more than one address is selected, eMails with attachments get sent to all the selected addresses. Three different reports are included, grouping books in following alternative styles: (a) Grouped by author (b) Grouped by publisher (c) Grouped by topic Code in report's open event as well as group header's format event is of generic nature. It uses the preliminary formatting cycle to build an array of group values, which are later used to filter the report whole sending / saving the group-wise parts, via subroutine named SendReportPartsByGrpLevel() in general module. This subroutine takes two arguments (report name and eMail address (optional)). If second argument is not supplied, no eMail gets sent (though report parts group- wise are still saved in ReportParts folder, in HTML format). Name of each saved file as well as report part caption reflect the group value as per which the report has been filtered. Spaces in group value, if any, are replaced by underscores, before being concatenated into file name. Important: (a) Make sure that a control bound to the grouping field and bearing the same name is present in the group header section. (b) A calculated control having [Pages] as part of its expression must be present in the report. This is required for ensuring complete preliminary round of formatting, which is used to build up an array of group values. Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format) References: DAO 3.6, Microsoft Scripting Runtime Download Access 2K/XP/2K3/2K7: uploads/37/Report_SendPartsByGrpLevel.zip |
||||
| Report_ShowNoDataSubReports.mdb | Report | 33K | Access 2K/XP/2K3 | 07 Jul 2008 at 10:40pm |
|
The sample db demonstrates display of subreport header even when there is no matching data in the subreport. It involves use of a wrapper query incorporating a left join between the master & child linked fields. Care is also needed to suppress unwanted portions (e.g. detail section as well as the column header labels / lines etc in the header section) of a subreport with no matching data. If page break after the subreport is required, it would be desirable to implement it via VBA code instead of depending upon the setting in properties dialog box, so as to prevent redundant blank page at the very end. This feature too is covered in the sample db.Download Access 2K/XP/2K3: uploads/37/Report_ShowNoDataSubReports.zip |
||||
| Report_SortAsPerGrpCalc | Report | 28K | 02 Dec 2009 at 3:59am | |
|
Report_SortAsPerGrpCalc Explanatory Notes This sample db demonstrates two alternative styles for sorting between groups as per total sum for each group, as follows: (1) Report R_SortByGrpSumDesc_A makes use of calculated expression in Sorting and Grouping (S&G) dialog box. The report is grouped as per publishers and these groups are sorted in descending order as per total group amount (Stock * UnitCost) for each publisher. The expression used directly in S&G dialog box is: =CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0)) (2) Report R_SortByGrpSumDesc_B makes use of calculated field named GrpAmount in the source query. This field name is used directly in S&G dialog box. The expression in the source query (design grid) is: GrpPrice: CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher = '" & [Publisher] & "'"),0)) Note: (a) For (2) above, use of subquery (instead of DSum()) for arriving at the total value won't suit, attracting an error message while trying to run the report. This is because subqueries are not amenable to direct use as source fields for group levels in an access report. (b) For consistent sorting results, output of Nz() function, when used in a query or S&G dialog box, is required to be converted to desired data type. Imp: (a) Name of a calculated control can not be used directly in Sorting and Grouping dialog box, which only accepts either a field name featuring in the record source or an expression. (b) User defined function deriving its value by referring to the calculated control itself, is also not found effective in Sorting and Grouping dialog box. This could be attributable to the fact that values held by various controls are not yet exposed in report's open event, whereas field settings for sorting and grouping are required to be enforced at this stage itself. (c) For a solution independent of source query, an expression based upon either a built in or a user defined function can be used in the Sorting and Grouping dialog box, as demonstrated in report style A in this sample db. Version: Access 2000 file format. References: Nothing extra. Download >>: uploads/37/Report_SortAsPerGrpCalc.zip |
||||
| Report_SpellCheck | Report | 47K | 15 Nov 2009 at 7:36am | |
|
Report_SpellCheck Explanatory Notes This sample db demonstrates spell check on a report prior to its opening. Two alternatives are covered: (a) Record source is updateable. (b) Record source is non-updateable. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/Report_SpellCheck.zip |
||||
| Report_SubRepSetMaxRowsPerPg | Report | 57K | 27 Sep 2011 at 12:01pm | |
|
Report_SubRepSetMaxRowsPerPg Explanatory Notes This sample db demonstrates custom setting of maximum number of rows per page for one or more subreports. Setting of forced page breaks in subreport's detail section, though effective, suffers from the drawback that the subreport control on parent report gets forced to full page height. As a result blank space on main report goes waste. Though not recommended, this method has been demonstrated as the last option, just for academic interest. Note: While using forced page break in subreport, it has to be ensured that CanGrow property of subreport control on parent report is set to Yes. Otherwise the subreport does not get displayed beyond first page. In fact what happens is that as a result of forced page break, the subreport control with CanGrow as Yes, expands suitably so as to span multiple pages as needed. Following styles are demonstrated in the sample db: 1 - A1: Single SubReport Style 1: The desired effect is achieved by conditional cancellation of detail format of subreport for records not falling in the target block determined by parent report's current record number. It is like obtaining a filtered output matching target block of sequential numbers. The number of pages on parent report is restricted to the minimum required for the subreport. 2 - A2: Single SubReport Style 2: Similar to A1. However, the number of pages on parent report is not restricted. It can exceed the minimum required for the subreport. 3 - B1: Two SubReports Style 1: Similar to A1 above, but with two subreports, each with its own setting for max rows per page. 4 - B2: Two SubReports Style 2: Similar to A2 above, but with two subreports, each with its own setting for max rows per page. 5 - C: Single SubReport With Forced Page Break: Not recommended. Included for academic interest only. Version: Access 2000 file format. References: Nothing extra. Download File >>: uploads/37/Report_SubRepSetMaxRowsPerPg.zip |
||||
| Report_TrainControlChart | Report | 74K | 24 Jan 2012 at 8:16am | |
|
Report_TrainControlChart Explanatory Notes This sample db demonstrates time distance charting of trains on a double line route. Time axis, covering 24 hours is depicted horizontally, while various railway stations are displayed along the vertical axis. Vertical lines across chart area identify half hour time intervals. For convenient legibility, time lines at two hour intervals have darker shade. Positioning of stations is in proportion to their respective distance from terminal station as compared to overall length of route. Major stations have adequate loop lines, while the minor ones have no facility for overtaking. Each pair of trains is assigned a distinct color (in table T_Trains). T1/T2 (Red) is a slow train, stopping at all stations. T3/T4 (Blue) is faster and stops only at major stations en-route. T5/T6 (Green) is a super fast pair, stopping at only at some of the major stations en-route. Halts of slower trains are devised in such a manner as to permit un-impeded overtake by faster ones. Two local trains (L1 and L2) performing multiple U trips are also demonstrated. Start and end points of each train have been made prominent by placement of a marker (a small circle - looks like a big dot) in a color matching that of the train in question. Train control chart can be viewed in three alternative styles as follows, depending upon user's choice via option group: (a) Whole route at a glance. (b) Zoomed view of first half of route. (c) Zoomed view of second half of route. Note: (a) If more stations get added (in table T_Stations), proportionate inter-station spacing in the chart will get adjusted automatically so as to suit the available chart height. (b) Chart height is set as 8100 twips via report level constant mTotChartHt. This is found to work fine in Access 2003 on Win XP (Paper size: A4, Default printer: HP Laserjet M1005). If, depending upon local set up, it is found that the report tends to spill over beyond one page, the value of this constant can be adjusted downwards suitably. For complete chart to be depicted, it is important that report (R_TrainControlChart) remains confined to a single page. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Report_TrainControlChart.zip |
||||
| Report_WithColumnHeaders.mdb | Report | 41K | Access 2K/XP/2K3/2K7 | 07 Jul 2008 at 10:38pm |
|
This sample db demonstrates provision of headers for each column of multi-column reports. Four styles are covered as follows: (a) R_MultiCol_AcrossAndDown - Natural group header (Column layout: Across And Down) (b) R_MultiCol_DownAndAcross - Natural group header (Column layout: Down And Across) (c) R_MultiCol_AcrossAndDown_DummyGrpHdr - Dummy group header (Column layout: Across And Down) (d) R_MultiCol_DownAndAcross_DummyGrpHdr - Dummy group header (Column layout: Down And Across) For displaying meaningful dynamic information in column headers, styles (a) and (b) above should suit. If no natural grouping is feasible or if merely static information is to be displayed in column headers, styles (c) and (d) can be adopted. Some additional notes: In a multicolumn report, following alternatives could be considered for ensuring presence of headers for each column. 1 - DA layout (Down, Then Across) This will provide the appropriate heading (as per group header) for each column. However, when available data is less than the space available on a page, the last populated column won't be balanced. A new column commences only when the previous one gets filled to its full height. 2 - AD layout (Across, Then Down) This gets you balanced columns but group heading appears only for the first column. The situation can be remedied by adopting a work-around. This involves creation of redundant group headers on the field meant to serve as column heading. In a report with three columns, three identical group headers are required to be created. Certain points that need to be kept in view, while dealing with group headers meant to serve as column headings, are reiterated below. (a) In sorting and grouping dialog box, KeepTogether property of the group should be kept as No. (b) RepeatSection property of group header section should always be kept as Yes. The implications of NOT DOING SO are. (i) In DA layout, the column heading will appear only for the first column on first page. None of the columns on subsequent pages will carry any heading. (ii) In AD layout, if adequate number of group header sections have been inserted, the column headings will appear for all columns on the first page. Subsequent pages will not carry any column headings. 5 - Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format) Download Access 2K/XP/2K3/2K7: uploads/37/Report_WithColumnHeaders.zip |
||||
| ReportBorders.mdb | Report | 127K | MDB | 07 Jul 2008 at 10:36pm |
|
ReportBorders Updated On 18-Dec-2011 Explanatory Notes This sample database demonstrates a simplified approach for creation of Inner & Outer borders for Access reports. Insertion of blank rows after last record, is also demonstrated. Following styles are covered: (a) Report With Outer Borders (b) Report With Border Grid - Spreadsheet Style (Vertical and horizontal inner borders along with outer border) (c) Blank Rows After Last Record - Fixed Height of Report Header / Footer (d) Blank Rows After Last Record - Dynamic Height (Can Grow = Yes) of Report Header / Footer Important - For style (b) above: (a) KeepTogether property of detail section should be set to Yes, so as to prevent the possibility of missing top detail line at start of a new page (caused by spill over of detail section across two pages). (b) It has to be ensured that report header section has a discreet height (more than zero). Otherwise, report header print event won't fire. Note: Style (d) above, involves two stage opening of report and makes use of Stephan Leban's function fTurnPage(). Version: Access 2000 File Format Reference: Nothing Extra Download File >>: uploads/37/ReportBorders.zip |
||||
| ReportBottomAligned.mdb | Report | 67K | MDB | 07 Jul 2008 at 10:34pm |
|
ReportBottomAligned Updated On: 27-Dec-2011 Explanatory Notes This sample db demonstrates bottom alignment of text in various text boxes in an access report. It involves dynamic adjustment of top margins in such a manner that for each record, grown height of each text box in the detail section matches that of the tallest one, at the same time ensuring uniform bottom alignment. Dynamic height (grown height) of a control can be captured only in the print event. However, direct manipulation of control's size or position is not allowed in this event. For consistent results, necessary adjustment in top margin should be made in the format event. In the sample db, this objective is achieved by two stage opening of report. In the first stage, grown heights of controls, as exposed during print event are stored in a temporary table (T_Temp). These values are then used to adjust the heights and top margins of controls during second stage opening of report. Two styles of report with bottom alignment of text are covered: (a) Simple - Without grid lines. (b) With grid lines - Spreadsheet style Except for the name of row counter text box, generic code is used so as to automatically suit any number and nomenclature of controls. While drawing vertical grid lines in style (b) above, the locations are set programmatically in such a manner as to fall mid-way between adjacent controls. For optimum effect, it has been ensured that top margin of first control displaying row numbers is dynamically kept identical to that of next adjoining control. The following alternatives stand rejected: (a) If top margins were to be manipulated within print event itself, corresponding effect on grown height would have been out of sync, getting manifested only upon firing of next print event. (b) Padding up the shorter control with suitable number of vbcrlf's in print event, so as to match its grown height with the taller one is also found to work. The rows of text in each control are calculated by taking into account the TextWidth property as compared to control's width. However, it runs into a snag. The wrapped text does not leave uniform side margins (visa vis the control's borders) on each line. This detracts from precise calculation of the number of rows occupied by the contents of different controls. As such, if this alternative were to be adopted, the results might not always measure up to the desired level of perfection. Note: This update overcomes the need to navigate through each page of report so as to fetch grown heights of controls as revealed in print event, thereby eliminating dependence upon either SendKeys method or Stephen Leban's API based page turner function. It is understood that SendKeys stands broken in Windows 7 while Leban's function fails to work in Access 2010. Instead, Setting report's MoveLayout property to false in first stage opening of report, as used in this version is found most dependable as it shrinks the report to a single page, ensuring that print events for all records get fired without having to move away from first page. Version: Access 2000 File Format Reference: DAO 3.6 Download File >>: uploads/37/ReportBottomAligned.zip |
||||
| ReportCalendarStyle.mdb | Report | 126K | Access XP & 2K | 07 Jul 2008 at 10:32pm |
|
The sample db demonstrates monthly report in calendar style layout, showing appointments / tasks scheduled for various dates. No space is wasted in the data table for storing records for dates where no appointment or task is scheduled. However, the report shows all days of the month in question, arranged in calendar style. Days having appointments / tasks are highlighted in distinct color. The report is based upon an interesting variation of multi-column layout, avoiding the need for cross tab query. It also includes data entry interface, with two way synchronization between the calendar and data entry subforms: (a) As the user picks any date on the calendar subform, data entry subform moves to the record pertaining to this date. If there is no existing record for this date, the form moves to a new record, having the selected date as its default value. (b) While in data entry form, if the user steps through various records, date shown on the calendar keeps changing in synchronization with the current one on data subform. The calendar used for data entry is an Access subform, thus avoiding use of any OCX control for this purpose. Versions - Access XP & 2K (both in Access 2000 File Format) References - DAO 3.6Download Access XP & 2K: uploads/37/ReportCalendarStyle.zip |
||||
| ReportGrpPgTrack.mdb | Report | 79K | Access 2000 | 07 Jul 2008 at 10:30pm |
|
This database demonstrates the display of page numbers for group headers on a report, while retaining natural page sequence of the main report. Three options exist for the type of output - (a) Normal report with the added feature that for groups spanning over more than one page, start & end page numbers for the group are also displayed along with the group header, which is repeated on all pages falling in the range (along with continuation Suffix). (b) Summary Report listing just the group headers and their page particulars. This can be useful supplement for very large reports. (c) Comprehensive report - In addition to covering (a) above, it contains a subreport at the end summarising the groups with their page particulars. Note - Two alternative methods have been used for navigating through the pages - (i) Method A - Using SendKeys (ii) Method B - Using function fTurnPage() taken from Stephan Leban's site. Download Access 2000: uploads/37/ReportGrpPgTrack.zip |
||||
| ReportPageWiseTotals.mdb | Report | 102K | Access XP & 2K | 07 Jul 2008 at 10:29pm |
|
This sample db demonstrates display of cumulative totals on each page and is pertinent to long Detail sections. Following alternatives are covered. (a) Total displayed in detail section itself (at the end of last detail) - Style - 1 (uses NextRecord property of report and visible property of controls in print event of detail section). (b)Total displayed in detail section itself (at the end of last detail) - Style - 2 (uses visible property of controls in format event of detail section). - This is simpler than (a). (c) Total displayed in page footer - This is the simplest, with minimal Code. Download Access XP & 2K: uploads/37/ReportPageWiseTotals.zip |
||||
| Reports_AccessToExcelAndWord.mdb | Report | 92K | Access 2000 | 07 Jul 2008 at 10:24pm |
|
This sample db demonstrates export of Access reports to Excel and Word, with proper alignment and formatting, including lines and back colors. Reports sent directly from Access to Excel are known to suffer from the following shortcomings, making the output quite un-acceptable: (a) Substantial loss of alignment and formatting. (b) Labels and lines are lost. (c) Unwanted depiction of actual names of controls, cluttering up the overall display. (d) Group footers do not get transferred properly. There is partial or total loss of content. (e) Back colors (if any) of controls and sections are lost. This sample db overcomes the above limitations by adopting the following approach: (a) Access report is first sent to Word. This facilitates retention of features other than lines and back colors. (b) The report now in Word document, is copied to Excel. (c) In Excel, the report is dressed up and formatted, complete with lines and back colors (Excel is well suited to perform such task). (d) Final version of formatted report in Excel is copied back to Word and saved in word document format (Normally, when an access report is sent to word via DoCmd.OutPutTo method, it always gets saved in rtf format, even if .doc extension is used for destination file. For subsequent conversion to word document format, explicit File > SaveAs action is needed). (e) With the steps outlined above, access report, duly formatted, complete with lines and back colors becomes available in Excel as well as Word. Note: (a) In the sample db, lines have been provided for column headers, group footers and report footer. Back colors have been provided for group and report footers. (b) Since individual page numbers do not serve any purpose in Excel, the reports have been designed without page header and footer. (c) Two styles of reports are demonstrated: (i) R_Sales - This report has single row per record (CanGrow property of various controls is set to No) (ii) R_Books - This report allows multiple rows per record (CanGrow property of various controls is set to Yes). In order to preserve column-wise alignment on transfer to Excel, programmatic padding up of individual columns by place holders (dots) has been adopted so as to ensure that grown height of each control in a record matches that of the tallest one. Subsequently, during the dressing up process in Excel, these placeholder dots get eliminated. (d) Excel and Word versions of reports as created above, are stored in a separate folder named REPORTS. This folder is housed in the parent folder containing the sample db and gets created if not already existing. 5 - Versions - Access 2K References: (a) Excel 9.0 Object Library (b) Word 9.0 Object Library (c) DAO 3.6 (d) Microsoft Scripting RunTime Download Access 2000: uploads/37/Reports_AccessToExcelAndWord.zip |
||||
| Reports_AddIn.mdb | Report | 47K | MDB | 06 Jul 2008 at 11:14pm |
|
This AddIn, when invoked in the host db, displays the names of all reports in host db on a list box on a form belonging to AddIn. Selected report can be opened by clicking a command button on this form. Installation and use guidelines are given below: Installation: The folder Reports_AddIn should be put directly on the C: drive, so that the path for .mda file is - C:\Reports_AddIn\Reports_AddIn.mda. (This is important, so as to establish the correct registry key) * Open any database in Access and click on Tools > Add-Ins > Add-In Manager. * Click on Add New. Browse to the folder and file at: \Reports_AddIn\Reports_AddIn.mda * Click OK. This will copy the .mda file to Microsoft's default Add-in folder for Access. You have to do this only once. Use To use Reports_AddIn from within any database, click Tools > Add-Ins > Reports_AddIn Technical Notes: These are contained in accompanying file ReadMe_Reports_AddIn.htm Version: Access 2K/XP/2K3/2K7 (Access 2000 File Format)Download 2K/XP/2K3/2K7: uploads/37/Reports_AddIn.zip |
||||
| Reports_AddIn_2 | Report | 34K | 17 Mar 2009 at 8:21pm | |
|
Reports_AddIn_2 Sample Add-In - Explanatory Notes General: This Add-In is meant to utilize a set of common reports belonging to the Add-In, in various db's. Queries in host db are used as record source for such reports. On activation of the Add-In, names of reports belonging to the Add-In are shown in a list box at left, while names of queries available in host db are displayed in list box at right. For a given report selected in left list box, the user can select an appropriate query in right list box. The selected report will open, using selected query as its record source. Version: Access 2000 File format Reference Required: DAO 3.6 Installation: The folder Reports_AddIn_2 should be put directly on the C: drive, so that the path for .mda file is - C:\Reports_AddIn_2\Reports_AddIn_2.mda. (This is important, so as to establish the correct registry key) Open any database in Access and click on Tools > Add-Ins > Add-In Manager. Click on Add New. Browse to the folder and file at C:\Reports_AddIn_2\Reports_AddIn_2.mda Click OK. This will copy the .mda file to Microsoft's default Add-in folder for Access. You have to do this only once. Use: To use Reports_AddIn from within any database, click Tools > Add-Ins > Reports_AddIn_2 For a practical demonstration, import table TA_Books and queries QA_Books_1 to 3 from Add-In to host db. Rename the imported queries as desired, say Q_Books_1 to 3. Now, run the Add-In and open the report RA_Books (belonging to the Add-In) with different queries of host db (Q_Books_1 to 3, one by one) as its record source. Technical Notes: Certain factors having a bearing on use of CodeProject / CodeDb are placed below, for ready reference: 1 - For code contained in modules belonging to the AddIn: (a) If the AddIn is opened independently, CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively (all refer to AddIn itself). (b) If the AddIn is activated in host db, CurrentProject / CurrentDb refer to host db while CodeProject / CodeDb refer to AddIn itself. (c) Host applications AllForms / AllReports collections can be accessed by code in AddIn, using CurrentProject. (d) Tables / Queries are not covered by collections like those in prev para. (e) While the AddIn is in activated state, any query / table / form / report, whether in host db or in the AddIn, can be opened directly by name, using DoCmd statement in AddIn code, without having to use any special qualifier. (f) If any pair of similar objects exists both in the AddIn as well as the host db, code in AddIn module will act upon the object located in the AddIn itself. (g) For code in AddIn module, when the AddIn is in activated mode, queries / recordsets based upon CurrentDb do not recognize the tables in AddIn itself, unless referred via IN clause, giving full path of the AddIN. This path, returned by CodeProject.FullName, refers to the original location of AddIn mda file e.g. C:\REPORTS_ADDIN_2\REPORTS_ADDIN_2.MDA and not the shadow AddIn file in AddIns folder in MyDocuments. (h) For a report belonging to the Add-In, direct assignment of query name belonging to host db won't do. It will not be recognized. Objects in the Add-In can not be directly mixed those in the host db. The solution lies in using the IN clause with full path of host db. (i) Run time assignment of record source in report's open event is found more dependable than doing so via an argument in DoCmd.OpenReport. 2 - For code contained in modules belonging to the host db: (a) CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively, always referring to the host db, irrespective of the fact whether the AddIn is in activated state or not. (b) Whereas code in the AddIn can access all tables / queries / forms / reports, whether belonging to AddIn or the host db (see para 1 (e) above), code in host db can directly access only the objects belonging to host db itself. Download File >>: uploads/37/Reports_AddIn_2.zip |
||||
| ReportsMerge.mdb | Report | 75K | Access XP & 2K | 06 Jul 2008 at 11:11pm |
|
This sample db demonstrates merging of two reports so that the combined report prints the records from each original report alternately in continuation (in an interleaved manner). Two styles for the merged report are shown - (a) Style 01 - Two records per page (one record from each report). (b) Style 02 - One record per page (First record of first report on page 1, first record of second report on page 2, and so on). Download Access XP & 2K: uploads/37/ReportsMerge.zip |
||||
| ReportTurnPages.mdb | Report | 52K | Access XP & 2K | 06 Jul 2008 at 11:09pm |
|
The sample db demonstrates navigation through pages of access reports in following alternative styles (all equally effective). (a) Custom tool bar - simple code (b) Pop-up form - simple code (c) Custom tool bar - elaborate code - This option is based upon function fTurnPage() at Stephan Leban's web site (contained in file ControlReports.zip)Download Access XP & 2K: uploads/37/ReportTurnPages.zip |
||||
| ReportTwoColBalanced.mdb | Report | 86K | Access XP & 2K | 06 Jul 2008 at 11:07pm |
|
This sample demonstrates a two column report where the columns are balanced despite the layout being down & then across. Both of the following situations are covered - (a) Report shorter than one page. (b) Report longer than one page. (Vertical Border Between Columns & Horizontal Border At Bottom, Provided) Download Access XP & 2K: uploads/37/ReportTwoColBalanced.zip |
||||
| ReportWithCommentsAtEnd.mdb | Report | 20K | Access XP (2000 format) | 06 Jul 2008 at 11:06pm |
|
This db demonstrates Consolidation of comments pertaining to different records and their display at the end of report. Such an arrangement can be quite convenient where only some of the records carry comments and / or adequate width is not available to display full contents of comments in the assigned column in main part of the report. Cross reference is provided via serial numbers assigned to each comment. Subreport placed in report footer is used for this purpose. Access XP (in Access 2000 File Format)Download Access XP (2000 format): uploads/37/ReportWithCommentsAtEnd.zip |
||||
| RunningAccount.mdb | General | 114K | Access 2000 & XP | 06 Jul 2008 at 11:02pm |
|
This sample db demonstrates running account (debit / credit / balance) - in passbook style. Data Entry as well as Report generation (for individual account holders) are covered. Separate samples have been provided for - (a) Number type Customer ID (b) Text type Customer ID Current Row is highlighted (data entry stage). Reference - DAO 3.6Download Access 2000 & XP: uploads/37/RunningAccount.zip |
||||
| RunningCountSum_2K.mdb | QTblFunc | 72K | Access 2000 | 06 Jul 2008 at 11:01pm |
|
This database covers display of running count and sum of records on continuous forms. Two alternative methods are demonstrated - Method A (Form Based) - Values are computed locally in calculated controls on the form. There is no dependence on any primary key or particular sort order. (Records Remain Editable) Method B (Query Based) - Values are computed in the source query. Though primary key is required, there is no compulsion to sort on it (Records Remain Editable) Note - Typically, record counter in a query involves use of either a subquery or DCount function. Both require (i) A primary key and (ii) Sorting to be in force - on the primary key field. This can pose a limitation where main sorting is desired on a field other than that holding primary key. Moreover if subquery method is used, the records are no longer editable. The sample database is free from such limitations Version - Access 2000 References - Visual Basic For Applications Microsoft Access 9.0 Object Library OLE Automation ADO 2.6 DAO 3.6 Download Access 2000: uploads/37/RunningCountSum_2K.zip |
||||
| S | GoTo Top | |||
| ScheduleTracker.mdb | General | 129K | Access 2000 | 06 Jul 2008 at 10:59pm |
|
1) This sample db tracks following types of events - (a) Recurring (Birthdays, Marriage Anniversaries etc) (b) Others - (i) Scheduled Appointments/Court Cases etc (ii) Periodical Project Reviews Etc 2) Whenever the database is opened, there is an automatic pop up display if any event happens to fall in the Alarm Zone. Criteria for inclusion in Alarm Zone is as follows - (a) For recurring type - From one day before current date, upto a week ahead. (b) Others (only where the check box for RemStatus has been selected)- All due dates already crossed as well as those falling within a week ahead of current date. In case of scheduled appointments, if any advance notice (in days) has been specified, the due date is computed accordingly. For periodical reviews, if any reminder interval (in days) has been specified, the next due date (which may fall on or after the current date) is calculated accordingly. Note - Email messages can be sent to the persons concerned by double clicking the Email Address. Provision has been made for multiple profiles, covering different persons to whom various tasks pertain. 3) If no event happens to fall in the alarm zone, the user is taken directly to the main switchboard (without any pop up alarm). At any stage, the present status of events can be viewed by clicking the appropriate command button. 4) The sample is in Access 2000 File Format - developed on Access XP installation. References - Visual Basic For Applications, Microsoft Access 9.0 Object Library, ADO 2.1 Imp - In due course, sample data for other than recurring events would tend to get obsolete. For the sake of demonstration, on each opening of db, table T_EventsOthers gets populated via action queries in open event of form F_Check. (For regular use, this part of code should be disabled).Download Access 2000: uploads/37/ScheduleTracker.zip |
||||
| SelfSaveAddIn | General | 139K | Access 2000 | 06 Jul 2008 at 10:57pm |
|
SelfSaveAddIn Version 1.4 (Updated on 04-Jan-2011) Explanatory Notes This utility enables creation of periodic backup copies of the running database (as well as all its back ends if any - if so desired). Interval of auto alerts (in minutes) can be set by the user as desired. At auto alert stage, the backup form pops up, with a message box asking the user to close all activity, ensure there is no other user, get out of VBA window and click the backup button. (The message box comes up even when the user is in VBA window). If the user closes the backup form without actually performing the backup, auto alert period gets reset to one minute, causing persistent reminders every minute. Once the backup is done and the form closed, auto alert interval reverts back to normal value originally set by the user. If desired, the backup copies can be compacted before backup, apart from getting stamped with current Date or DateTime. If the user has selected DoCompact check box, the AddIn creates a compacted back up file with "_CpBk_" embedded in its suffix. If, for any reason, the back process is not successful, normal copy gets done with "_Bk_" embedded in its suffix. By looking at the name of backed up file, the user can determine whether compaction was a success (Presence of "Cp" in the suffix). Note: Safe compaction technique is used, so that even if the process fails, the original file remains un-affected. In such an eventuality, the backup gets done without compaction. If DoCompact option is selected and the host db has a password, it will be needed as one of the arguments in compaction statement. The user will have to enter this password only once during a given session. If the host db is linked to one or more back ends, and some or all of these are password protected, all the back ends will get compacted and backed up, using the passwords extracted from relevant connection strings. Old backup files in excess of the maximum limit set by the user, get deleted simultaneously. Backup folder can be prescribed by the user. After each backup, latest count of backup files, apart from particulars of last backup, is displayed. On click of a button, the user can see the list of all files existing in backup folder. Files <= 8 Hrs old are highlighted in light green, those > 8 Hrs and <= 24 Hrs old are in orange color while others ( > 24 Hrs old) are shown in grey color. Version: Access 2000 file format. References: DAO 3.6, Microsoft Scripting RunTime Installation: The folder SelfSaveAddIn should be put directly on the C: drive, so that the path for .mda file is - C:\SelfSaveAddIn\SelfSaveAddIn.mda. (This is important, so as to establish the correct registry key) Open any database in Access and click on Tools > Add-Ins > Add-In Manager. Click on Add New. Browse to the folder and file at C:\SelfSaveAddIn\SelfSaveAddIn.mda Click OK. This will copy the .mda file to Microsoft's default Add-in folder for Access. You have to do this only once. Use: To use SelfSaveAddIn from within any database, click Tools > Add-Ins > SelfSaveBackUp Technical Notes: Certain interesting factors pertaining to Add-Ins in general, are mentioned below: 1 - For code contained in modules belonging to the Add-In: (a) If the AddIn is opened independently, CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively (all refer to AddIn itself). (b) If the AddIn is activated in host db, CurrentProject / CurrentDb refer to host db while CodeProject / CodeDb refer to AddIn itself. (c) Host applications AllForms / AllReports / AllModules collections can be accessed by code in AddIn, using CurrentProject. (d) Tables / Queries are not covered by collections like those in prev para. (e) While the AddIn is in activated state, any query / table / form / report, whether in host db or in the AddIn, as well as modules in host db can be opened directly by name, using DoCmd statement in AddIn code, without having to use any special qualifier. (f) If any pair of similar objects exists both in the AddIn as well as the host db, code in AddIn module will act upon the object located in the AddIn itself. (g) For code in AddIn module, when the AddIn is in activated mode, queries / recordests based upon CurrentDb do not recognize the tables in AddIn itself, unless referred via IN clause, giving full path of the AddIN. This path, returned by CodeProject.FullName, refers to the original location of AddIn mda file e.g. C:\SelfSaveAddIn\SelfSaveAddIn.mda and not the shadow AddIn file in AddIns folder in MyDocuments. There is no such problem if queries / recordsets are based upon CodeDb instead of CurrentDb. (h) Modules belonging to the AddIn can not be opened by DoCmd mehod or otherwise addressed, even by code in the AddIn. DoCmd.CopyObject command can however be used for copying modules from AddIn to host db. 2 - For code contained in modules belonging to the host db: (a) CurrentProject / CurrentDb are identical to CodeProject / CodeDb respectively, always referring to the host db, irrespective of the fact whether the AddIn is in activated state or not. (b) Whereas code in the AddIn can access all tables / queries / forms / reports, whether belonging to AddIn or the host db (see para 1 (e) above), code in host db can directly access only the objects belonging to host db itself. Note: General module named basCommDlg, an adaptation from Access Developer's Handbook, has been kindly provided by Bill Mosca ============================================================= Download Access 2000: uploads/37/SelfSaveAddIn.zip |
||||
| SubFormDynamicView.mdb | Form | 51K | Access XP & 2K | 06 Jul 2008 at 10:56pm |
|
This sample db demonstrates toggling of subform view at run time (between datasheet & continuous form).Download Access XP & 2K: uploads/37/SubFormDynamicView.zip |
||||
| SubReport_GroupHdrAcrossColumns.mdb | Report | 33K | Access XP (2000 format) | 06 Jul 2008 at 10:52pm |
|
This sample db demonstrates display of group header across all the columns in a multi-column report (Column layout: Across, then Down). This feature can be useful for displaying page header content of subreports. Page headers for subreports do not get displayed. The work-around is to include the contents of page header in a dummy group header (with its Repeat Section property set to Yes). However, a multi-column report faces the limitation that a group header can cover only one column width. The solution lies in including a sub-subreport in a dummy group header of the subreport, to serve as surrogate page header. The contents to be displayed are preferably included in report header section of this sub-subreport. Caution: In order to prevent distorted display of columns, suitable additional number of blank dummy group headers should be inserted so as to ensure that their total number equals the number of columns. Repeat Section property of each such group header should be set to Yes.Download Access XP (2000 format):uploads/37/SubReport_GroupHdrAcrossColumns.zip |
||||
| SubReportTwoColBalanced.mdb | Report | 167K | Access XP & 2K | 06 Jul 2008 at 10:50pm |
|
The sample db demonstrates balanced display of two columned subreport, despite adoption of "Down And Then Across" (D&A) layout. Normally, "Across And Then Down" (A&D) layout is more convenient for getting balanced columns. However, in such an arrangement, there can be awkward blank spaces in one column if the contents of adjacent one overflow into multiple rows. This can be remedied by adopting D&A layout. Direct adoption of D&A layout in a subreport does not work as the first column itself continues to expand when CanGrow property of the container control is set to "Yes". Remedy lies in setting CanGrow property of the subreport control to "No" and manipulating its height at run time. This is done by two stage opening. In stage 1, subreport alone is opened and required heights are stored in an array. These values are then used for regular opening of main report in stage 2.Download Access XP & 2K: uploads/37/SubReportTwoColBalanced.zip |
||||
| T | GoTo Top | |||
| Table_NormalizeMethods | QTblFunc | 131K | 14 May 2012 at 12:45am | |
|
Table_NormalizeMethods Explanatory Notes: This sample db demonstrates various methods for un-pivoting repeating columns of a table into rows. Following alternatives are covered: 1 - Progressive Append (One column at a time). 2 - Union Query. 3 - Query using Choose() function (It makes use of Cartesian join with single field driver table, populated with sequential numbers). 4 - Recordset Method (Looping through records). For each of the above methods, two alternative styles are shown: (a) Appending the output to an existing table or (b) Making a new table. SQL for various queries is built through generic code in VBA module. For academic interest, query based method using DLookUp() function has also been included in VBA module. It makes use of Cartesian join with single field driver table, populated with names of pivot columns. Such a query can be built easily, without needing any VBA. However, the performance is comparatively slow, making it unsuitable for large data size. Tests conducted on Access 2003 desktop (Win XP) show that method 1 (progressive append) is most efficient while method 4 (recordset) is the slowest. For large data sets, execution time for recordset method can be as much as 9 times that of progressive append method. The other two methods: (3) - Union query and (4) - Cartesian join query using Choose function, though much faster than recordset method, are found significantly slower than progressive append method. Moreover, in make table mode involving large data sets, these two methods tend to attract system warning 3035 (System Resources Exceeded). Making a new table with normalized output is found comparatively faster than appending the output to an existing table. One of the contributory factors appears to be the fact that deletion of records in a table is much slower than deletion of the table itself. For facilitating comparative testing of various methods in a convenient manner, source data can be enlarged by clicking a command button. For each click, data size gets doubled by padding up with dummy data (self append). Execution time in each case can be recorded in the log table (depending upon the status of toggle button for logging). The log table can be displayed or hidden as desired by user. As and when it is felt necessary to revert back to original sample data, it can be done with the click of a command button. Some of the test results with different data sizes, as ascertained during development of this sample db, are included in table T_Log. Version: Access 2000 file format. References: DAO 3.6 Download File >>: uploads/37/Table_NormalizeMethods.zip |
||||
| TableNormalizationByPureSQL | QTblFunc | 27K | 18 Jan 2011 at 12:10pm | |
|
TableNormalizationByPureSQL Explanatory Notes: This sample db demonstrates pure query based solution for normalization of data held by a non-normalized table (T_Source) and posting the converted contents to destination table (T_Normalized). Steps: 1 - Create the empty destination table T_Normalized with a structure identical to that of table T_Source, but without the non-normalized fields (e.g. Red, Green, Blue, Yellow in this sample). 2 - Add two new fields to the newly created table T_Normalized. One meant for holding the names of non-normalized fields and the other for holding corresponding values. In the current sample, these two fields are named Color and Stock respectively. 3 - Create an auxiliary table named T_SourceFieldsConverted having a single field meant for holding names of non-normalized fields. In the current sample, this field is named Color. Populate this table with the names of non-normalized fields (i.e. Red, Green, Blue, Yellow in this sample). 4 - Execution of append query Q_AppNormalized will populate destination table T_Normalized with normalized data, duly converted from source table T_Source. This query is based upon Cartesian join between tables T_SourceFieldsConverted and T_Source. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/TableNormalizationByPureSQL.zip |
||||
| TableNormallization | QTblFunc | 31K | 06 Jul 2008 at 10:46pm | |
|
TableNormallization (Updated On: 31-Oct-2010) Explanatory Notes: This sample db demonstrates programmatic normalization of data in a non-normalized table and posting the rectified contents to a new table, using a generic subroutine. Version: Access 2000 file format. References: DAO 3.6 Download >>: uploads/37/TableNormallization.zip |
||||
| TrackMissingAndDuplicates.mdb | QTblFunc | 232K | Access XP & 2K | 06 Jul 2008 at 10:44pm |
|
This sample demonstrates monitoring of sequential values so as to keep track of missing as well as duplicate values. The status form displays the following information (side by side) - (a) Overall Position as existing. (Records where next required sequential value is missing are highlighted. In addition, records carrying duplicate values are also highlighted in different color). (b) Gist of missing sequential values. (c) Gist of duplicate sequential values. Update: The scope has now been expanded so as to cover - (a) String values with formatted numeric suffix. (b) String values with un-formatted numeric suffix. (c) Pure dates. (d) Pure numbers.Download Access XP & 2K: uploads/37/TrackMissingAndDuplicates.zip |
||||
| TrackNameChange.mdb | General | 138K | Access Version XP&2K | 06 Jul 2008 at 10:40pm |
|
This sample db demonstrates tracking of name changes spread over different records. Basic data is contained in a table having three fields (one primary key - autonumber type, and two text fields named OldName & NewName respectively). For each OldName, there is a NewName & vice versa. As an illustration, one typical set of records might be - OLDNAME NEWNAME CocaCola CocaColaClassic CocaColaClassic CherryCoke CherryCoke CherryCokeClassic CherryCokeClassic DietCoke The sample db enables correlation of any OldName with the last NewName in the sequence. Similarly, it correlates any NewName with the first OldName in the sequence. Data entry incorporates autocomplete feature for OldName field (filling in the closest match from the recently filled NewNames). Versions - Access (In Access 2000 File Format) References - ADO 2.1 Download Access Version XP&2K: uploads/37/TrackNameChange.zip |
||||
| TrialSet | General | 114K | 06 Jul 2008 at 10:27pm | |
|
TrialSet Updated On: 01-Jun-2012 Sample Db - Explanatory Notes General This sample demonstrates the process of preparing a front end db for trial use, by laying down the following settings - (a) Number of permitted uses. (b) Trial expiry date. Out of (a) & (b) above, whichever falls earlier, displays a suitable message and causes the db to become unavailable for further use. Count of uses is stored in a hidden table named T_Status. Even if the user gains access to this table, and tries to edit it, the overall settings are not easily disturbed as the criteria is linked to autonumber value which is controlled programmatically. Expiry date, start number and max permitted uses get embedded in VBA code - and therefore free from user interference, once the file is converted to mde. Some interesting features for making the demo tamper-proof: 1 - Essential values like TrialMode (Y/N), expiry date, max permitted uses, start value of autonumber, camouflaging offsets (for autonumber and date of last use) are hard coded in VBA module, which is not accessible to user in mde format. 2 - Count of uses: (a) Count of uses is tracked by autonumber value in table T_Status. It is a single record table and the record gets re-inserted after each use. (b) Five digit starting value of this autonumber is generated on random basis and gets hard coded in VBA module. (c) The columns showing expiry date, max count and count of uses so far are redundant (mere diversionary role). Tampering of these values has no effect. (d) If the user tries to interfere with autonumber value by re-creating the table, and the new autonumber happens to be less than the hard coded value of starting autonumber, the trial validity stands forfeited. 3 - Additional safeguards: 3.1 - A superstring made up of 55 digits is used to keep track of latest values of autonumber and the last use date. This string is generated by using random numbers. The autonumber and year / month / day parts of last use date are embedded within this string at different locations. As a further precaution, camouflaging offsets are added to the real values before implementing the embedment. These offsets, generated by using random numbers, are hard coded in VBA module. As a result, different copies of trial db, generated by using form F_LockSet, will have different values of offsets. Successful tampering of such a superstring is expected to be virtually impossible 3.2 - The above super-string is stored in four separate ways as follows: (a) Database property (b) Project property (c) Registry key (d) Hidden system file in hidden system folder 3.3 - On first run of trial db (signified by autonumber of table T_Staus being equal to hard coded value of starting autonumber coupled with the absence of database property, project property, registry key and special system hidden file), items 3.2 (a) to (d) above get created, duly populated with the superstring. 3.4 - On each closing of form F_SwitchBoard, existing value of autonumber in table T_Status is incremented by one and along with current date, is embedded in freshly generated 55 digit superstring. 3.5 - On each opening of form F_SwitchBoard, validity check is performed, covering: (a) Current autonumber in table T_Status should not be lower than the hard coded value of starting autonumber. (b) None of the special settings (database property, project property, registry key and special system hidden file) should be missing. (c) None of the four superstrings, stored in 3.2 (a) to (d) above, should show any sign of tampering and all the four superstrings should be identical. (d) Autonumber values as extracted from the superstrings from four different sources should all match the one in table T_Status. (e) Last use dates as extracted from various superstrings should equal each other and should never be higher than current date. This is meant to guard against re-setting of system clock backward. (f) Current autonumber should not exceed the value computed by adding max permitted uses to starting autonumber (both these settings are hard coded in VBA module). (g) Current date should not exceed the expiry date (hard coded in VBA module). Usage: Before distributing an application with such features, following steps should be taken - 1 - In your db, open VBA window and set reference to Microsoft Scripting RunTime. For versions up to Access 2003, set reference to DAO 3.6 as well. (This reference is not required for Access 2007 onwards as it is already built in). Come back to database window. 2 - Import all objects from sample db into your db. These items are: (a) - Table T_Status (b) - Forms F_LockSet, F_Next and F_Switchboard (c) - General modules M_LockCheck and M_LockSet 3 - Use form F_LockSet for making the desired settings for expiry date and max number of uses permitted. This will generate appropriate hard coded entries in VBA module M_LockSet. 4 - Delete the form F_LockSet. 5 - IMPORTANT: Right click table T_Status, open its properties window and make it hidden by setting its attributes to Hidden. 6 - Make sure that no form other than F_Switchboard is permitted to open independently. All forms should open only through F_SwithBoard and should also incorporate validity check for trial permissions. This is done by including suitable code in form's open event as demonstrated in sample form F_Next. 7 - Set F_SwitchBoard as the start up form. 8 - Go to VBA editor (press Alt + F11), save & compile (Debug > Compile). 9 - Come out of VBA window and convert to mde (or accde) format as applicable. Conversion to mde or accde format will succeed only if the following conditions are satisfied: (a) All objects (tables, forms etc) are in closed condition. (b) The db is in a format matching the application. For example, if it is Access 2003 installation while the db is in access 2000 file format, it has first to be converted to Access 2003 file format. Similarly, on Access 2007 or 2010, the db should be in accdb format before attempting conversion to accde format. Note: As and when the db is to be made available for unrestricted use, create a fresh copy, using F_LockSet to generate the hard coded settings, after selecting "N" as TrialMode. Now, when the user opens form F_SwitchBoard, there will be no validity check and on closing of this form, all the special settings mentioned in para 3.2 (a) to (d) above (i.e. database property, project property, registry key and special system hidden file) will get deleted. Version: Access 2000 File Format References: DAO 3.6 for versions up to Access 2003 (For Access 2007 onwards, this reference is already built in) Microsoft Scripting RunTime Download File >>: uploads/37/TrialSet.zip |
||||