LEARN MS-ACCESS TIPS AND TRICKSLEARN MS-ACCESS TIPS AND TRICKSLearn Mail Merge in MS-Access. Learn how to enhance your Microsoft Access Forms with 3D Headings and Animated Controls. Step by step tutorial to prepare Graph Charts in Access. Use of Office Assistant in Access. Easy to implement Examples and Codes. 
Articles
Selected List Box Items and Dynamic Query
2008-05-08 22:03:00 Billing Department of Northwind Traders processes their Orders selectively for shipment of materials. An Order Selection Screen has been provided with a List Box on which they can highlight the Order Numbers, Filter the selected Orders from the main file and prepare Customer Invoices and Shipping Documents. They must be able to process all the Orders appearing in the list as well.The items appearing in the List Box themselves are records selected from within a Date-Range from the Main Order File. By creating two text controls for start-date and end-date values we can control the selection of records for the list box, if needed. But for now, we will work with items selected from the List Box, for simplicity.We need the Orders Table from the Northwind.mdb sample Database for our example.Import the Orders Table from the sample Database NorthWind.mdb, if it is not already done in our earlier examples. If you are not sure about the exact location of this Database on your PC, visit the ... More About: Items , Dynamic , Query
Selected List Box Items and Dynamic Query
2008-05-08 22:03:00 Billing Department of Northwind Traders processes their Orders selectively for shipment of materials. An Order Selection Screen has been provided with a List Box on which they can highlight the Order Numbers, Filter the selected Orders from the main file and prepare Customer Invoices and Shipping Documents. They must be able to process all the Orders appearing in the list as well.The items appearing in the List Box themselves are records selected from within a Date-Range from the Main Order File. By creating two text controls for start-date and end-date values we can control the selection of records for the list box, if needed. But for now, we will work with items selected from the List Box, for simplicity.We need the Orders Table from the Northwind.mdb sample Database for our example.Import the Orders Table from the sample Database NorthWind.mdb, if it is not already done in our earlier examples. If you are not sure about the exact location of this Database on your PC, visit the ... More About: Items , Dynamic , Query
Database Daily Backup
2008-05-01 21:27:00 If your Database is installed on a Network then a regular server backup is done on a Daily /Weekly/Monthly/Quarterly basis by the Network Team and kept in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deletion by mistake etc., you can always send a request to the Computer Department giving details of location, filename and safe backup date from which you would like to restore from. This may take a few hours to few days to get it done because the backup tapes or other mediums must be transported back from its storage location before they is able to complete your request.If your Project have its own backup procedure and does it on a regular basis you don't have to go after others with requests and resultant delay in restoring the Database. This will also ensure that your Application?s down time is very minimal.You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but ... More About: Backup
Database Daily Backup
2008-05-01 21:27:00 If your Database is installed on a Network then a regular server backup is done on a Daily /Weekly/Monthly/Quarterly basis by the Network Team and kept in Fire-proof Cabinets away from the Computer Center. If something happens to your database, like database corruption or deletion by mistake etc., you can always send a request to the Computer Department giving details of location, filename and safe backup date from which you would like to restore from. This may take a few hours to few days to get it done because the backup tapes or other mediums must be transported back from its storage location before they is able to complete your request.If your Project have its own backup procedure and does it on a regular basis you don't have to go after others with requests and resultant delay in restoring the Database. This will also ensure that your Application?s down time is very minimal.You can secure the Database Objects (Forms, Queries, Tables, Reports etc.) from within the Database but ... More About: Backup
Days in Month Function
2008-04-24 15:20:00 Function to Calculate Number of Days The User-Defined Function DaysM() given below can be used in calculations that involves the number of days of a particular month. Copy and Paste the following Code into a Global Module and save it in your Project.Public Function DaysM(ByVal varDate) As IntegerDim intYear As Integer, intmonth As IntegerOn Error GoTo DaysM_Err If Nz(varDate) = 0 Then DaysM = 0 Exit FunctionEnd IfintYear = Year(varDate)intmonth = Month (varDate)DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)DaysM_Exit:Exit FunctionDaysM_Err:MsgBox Err.Description, , "DaysM()"DaysM = 0Resume DaysM_ExitEnd FunctionSyntax: X = DaysM(varDate)Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X. The Parameter value can be a valid Date, a Date in Text format like "15-02-2008" or in its corresponding numeric value 39493. If you would like to re-write the Function differently by adding few extra lines of code ... More About: Function
Days in Month Function
2008-04-24 15:20:00 Function to Calculate Number of Days The User-Defined Function DaysM() given below can be used in calculations that involves the number of days of a particular month. Copy and Paste the following Code into a Global Module and save it in your Project.Public Function DaysM(ByVal varDate) As IntegerDim intYear As Integer, intmonth As IntegerOn Error GoTo DaysM_Err If Nz(varDate) = 0 Then DaysM = 0 Exit FunctionEnd IfintYear = Year(varDate)intmonth = Month (varDate)DaysM = Day(DateSerial(intYear, intmonth + 1, 1) - 1)DaysM_Exit:Exit FunctionDaysM_Err:MsgBox Err.Description, , "DaysM()"DaysM = 0Resume DaysM_ExitEnd FunctionSyntax: X = DaysM(varDate)Replace the varDate parameter with a valid Date. The Number of Days for the Month will be returned in Variable X. The Parameter value can be a valid Date, a Date in Text format like "15-02-2008" or in its corresponding numeric value 39493. If you would like to re-write the Function differently by adding few extra lines of code ... More About: Function
Finding Consecutive Workdays with Query
2008-04-17 16:36:00 How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into this problem and solution I will present an example below.Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking week-end breaks they will be rewarded with special incentives besides their daily wages.The employees joined for work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.After completion of the Project the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.I know it sounds like a Question in an Examination Hall. But, we are faced with difficult Questions very often, while considering data proc... More About: Query
Finding Consecutive Workdays with Query
2008-04-17 16:36:00 How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into this problem and solution I will present an example below.Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking week-end breaks they will be rewarded with special incentives besides their daily wages.The employees joined for work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.After completion of the Project the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.I know it sounds like a Question in an Examination Hall. But, we are faced with difficult Questions very often, while considering data proc... More About: Query
Finding Consecutive Workdays with Query
2008-04-17 16:36:00 How do we determine that the date values within a certain date-range are consecutive or intermittent? To bring some clarity into this problem and solution I will present an example below.Assume that a Company hires some temporary employees on daily wages to complete a Project within 15 days time. The Company informs the temporary hands that if they work 8 hours a day and for 7 days continuously without taking week-end breaks they will be rewarded with special incentives besides their daily wages.The employees joined for work on different dates and not all of them could work for 7 days without breaks. The attendance of the employees is logged date-wise into a Table with Employee Codes.After completion of the Project the task was given to us to prepare the list of employees who worked continuously for 7 days for awarding their declared incentives.I know it sounds like a Question in an Examination Hall. But, we are faced with difficult Questions very often, while considering data proc... More About: Query
Transparent Command Button
2008-04-11 08:26:00 Command Button s can be kept hidden on Forms by setting their Transparent or Visible Property to No and make them visible to click, when certain condition is met or when a particular User opens the Form.For example, when a Form with Data is routed through Networks to different Users (in a Secured Database) for verification, Approval of the contents, different Command Buttons can be created and programmed for each user and make them visible only when the respective User opens the Form or subject to the verified status of Data at lower Rank of Users and so on.The more popular method in practice, in the above situations, is to disable the Command Button and keep it visible all the time and enable it when required. Making a Button visible or invisible is another way of doing the same thing. We cannot set the Command button's Enabled or Visible Property to No, when the Button is active. We must shift the Focus from the Button to some other Control before setting the above properties to N... More About: Command
Transparent Command Button
2008-04-11 08:26:00 Command Button s can be kept hidden on Forms by setting their Transparent or Visible Property to No and make them visible to click, when certain condition is met or when a particular User opens the Form.For example, when a Form with Data is routed through Networks to different Users (in a Secured Database) for verification, Approval of the contents, different Command Buttons can be created and programmed for each user and make them visible only when the respective User opens the Form or subject to the verified status of Data at lower Rank of Users and so on.The more popular method in practice, in the above situations, is to disable the Command Button and keep it visible all the time and enable it when required. Making a Button visible or invisible is another way of doing the same thing. We cannot set the Command button's Enabled or Visible Property to No, when the Button is active. We must shift the Focus from the Button to some other Control before setting the above properties to N... More About: Command
Transparent Command Button
2008-04-11 08:26:00 Command Button s can be kept hidden on Forms by setting their Transparent or Visible Property to No and make them visible to click, when certain condition is met or when a particular User opens the Form.For example, when a Form with Data is routed through Networks to different Users (in a Secured Database) for verification, Approval of the contents, different Command Buttons can be created and programmed for each user and make them visible only when the respective User opens the Form or subject to the verified status of Data at lower Rank of Users and so on.The more popular method in practice, in the above situations, is to disable the Command Button and keep it visible all the time and enable it when required. Making a Button visible or invisible is another way of doing the same thing. We cannot set the Command button's Enabled or Visible Property to No, when the Button is active. We must shift the Focus from the Button to some other Control before setting the above properties to N... More About: Command
Colorfull Command Buttons
2008-04-03 17:39:00 We have seen Double-Action Command Button in the earlier Post and Command Button Animation before and now it is time for adding some color to the Command Buttons . Take a look at some different Command Buttons in action on a Control Screen (Main Switchboard) of a sample Ms-Access Application.Like to use them on your Application? You may be expecting some lengthy lectures from me about them. Or may be expecting about hundred or more lines of VBA Code to copy from the bottom of this Article to paste into your Project. Or you are getting prepared to download some MS-Access Add-Ons from Internet and install them into your machine. Probably one or two Library Files to link with your Project too, right? No, you don?t have to do any of those things to create and put these Command Buttons on your Project. You can create one Button in two minutes, well, may be 3 minutes when you create it for the first time. You can create the variants of the same button in less than one minute?s time.I k...
Colorful Command Buttons
2008-04-03 17:39:00 We have seen Double-Action Command Button in the earlier Post and Command Button Animation before and now it is time for adding some color to the Command Buttons . Take a look at some different Command Buttons in action on a Control Screen (Main Switchboard) of a sample Ms-Access Application.Like to use them on your Application? You may be expecting some lengthy lectures from me about them. Or may be expecting about hundred or more lines of VBA Code to copy from the bottom of this Article to paste into your Project. Or you are getting prepared to download some MS-Access Add-Ons from Internet and install them into your machine. Probably one or two Library Files to link with your Project too, right? No, you don?t have to do any of those things to create and put these Command Buttons on your Project. You can create one Button in two minutes, well, may be 3 minutes when you create it for the first time. You can create the variants of the same button in less than one minute?s time.I k... More About: Colorful
Colorfull Command Buttons
2008-04-03 17:39:00 We have seen Double-Action Command Button in the earlier Post and Command Button Animation before and now it is time for adding some color to the Command Buttons . Take a look at some different Command Buttons in action on a Control Screen (Main Switchboard) of a sample Ms-Access Application.Like to use them on your Application? You may be expecting some lengthy lectures from me about them. Or may be expecting about hundred or more lines of VBA Code to copy from the bottom of this Article to paste into your Project. Or you are getting prepared to download some MS-Access Add-Ons from Internet and install them into your machine. Probably one or two Library Files to link with your Project too, right? No, you don?t have to do any of those things to create and put these Command Buttons on your Project. You can create one Button in two minutes, well, may be 3 minutes when you create it for the first time. You can create the variants of the same button in less than one minute?s time.I k...
Double-Action Command Button
2008-03-27 21:33:00 Double-Action doesn't mean that with one Click you can execute two different procedures. For that matter you can do several things at one click. Here, the emphasis is on programing the same Command Button for doing two different Actions i.e. when the Button is Clicked a second time it will execute a different action than it did for the first one.To make the point more clear, let us look into a real world example. When the user opens a Data Editing Screen we want the screen to be kept locked for safety of the data and to prevent users changing the field values accidently.But, when it is time to make some changes in one or more fields on the current record the user will Click the Command Button to unlock the Form for editing. After editing, the User will Click the same Button again to lock the screen. Let us try an Example. But, before starting with the design task let us note down the above points with more specifics so that you will have a general idea what we are trying to do, ... More About: Double
Double-Action Command Button
2008-03-27 21:33:00 Double-Action doesn't mean that with one Click you can execute two different procedures. For that matter you can do several things at one click. Here, the emphasis is on programing the same Command Button for doing two different Actions i.e. when the Button is Clicked a second time it will execute a different action than it did for the first one.To make the point more clear, let us look into a real world example. When the user opens a Data Editing Screen we want the screen to be kept locked for safety of the data and to prevent users changing the field values accidently.But, when it is time to make some changes in one or more fields on the current record the user will Click the Command Button to unlock the Form for editing. After editing, the User will Click the same Button again to lock the screen. Let us try an Example. But, before starting with the design task let us note down the above points with more specifics so that you will have a general idea what we are trying to do, ... More About: Double
Double-Action Command Button
2008-03-27 21:33:00 Double-Action doesn't mean that with one Click you can execute two different procedures. For that matter you can do several things at one click. Here, the emphasis is on programing the same Command Button for doing two different Actions i.e. when the Button is Clicked a second time it will execute a different action than it did for the first one.To make the point more clear, let us look into a real world example. When the user opens a Data Editing Screen we want the screen to be kept locked for safety of the data and to prevent users changing the field values accidently.But, when it is time to make some changes in one or more fields on the current record the user will Click the Command Button to unlock the Form for editing. After editing, the User will Click the same Button again to lock the screen. Let us try an Example. But, before starting with the design task let us note down the above points with more specifics so that you will have a general idea what we are trying to do, ... More About: Double
SENDING ALERTS TO WORKSTATIONS
2008-03-21 15:52:00 Let us look into a scenario to understand the importance of this method. MS-Access Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update information in this Table and complete their side of the work only after the main records are introduced by Application A Users. All records are not very urgent in nature but some of them are and needs immediate action from B & C Users.There must be a way to inform Application A Users to give priority for certain Data for updating in the System. When they do, B & C Users must get Alerts on the updated status of those records, so that they can open their Applications and act upon their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.A Functional Diagram of this method is given below:Solution:Create a new table with the name Alert_Param with similar fileds given below in the BE Database:Create Key fields like Supplier Code, Invo... More About: Sending , Query
SENDING ALERTS TO WORKSTATIONS
2008-03-21 15:52:00 Let us look into a scenario to understand the importance of this method. MS-Access Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update information in this Table and complete their side of the work only after the main records are introduced by Application A Users. All records are not very urgent in nature but some of them are and needs immediate action from B & C Users.There must be a way to inform Application A Users to give priority for certain Data for updating in the System. When they do, B & C Users must get Alerts on the updated status of those records, so that they can open their Applications and act upon their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.A Functional Diagram of this method is given below:Solution:Create a new table with the name Alert_Param with similar fileds given below in the BE Database:Create Key fields like Supplier Code, Invo... More About: Sending , Query
SENDING ALERTS TO WORKSTATIONS
2008-03-21 15:52:00 Let us look into a scenario to understand the importance of this method. MS-Access Applications A, B & C are linked to a common Table in a Back-End Database on the Network. Application B & C Users can update information in this Table and complete their side of the work only after the main records are introduced by Application A Users. All records are not very urgent in nature but some of them are and needs immediate action from B & C Users.There must be a way to inform Application A Users to give priority for certain Data for updating in the System. When they do, B & C Users must get Alerts on the updated status of those records, so that they can open their Applications and act upon their side of the work. It is not necessary to keep B & C Applications open to receive Alert Messages.A Functional Diagram of this method is given below:Solution:Create a new table with the name Alert_Param with similar fileds given below in the BE Database:Create Key fields like Supplier Code, Invo... More About: Sending , Query
Refresh Dependant Combo Box Contents
2008-03-13 20:12:00 Creating a Combo box on a Data Entry/Editing Screen is easy. Click on the Combo box Tool Button on the Toolbox, select the required Fields from the Source Table or Query on the Wizard, select the target field on the Form and you are through. Well, may be not quite, few more changes may require on the Property Sheet of the combo box to adjust the width of individual Columns of List, Total List Width property, List Rows Property, Limit to List and On Not in List Event Procedure to prevent entering values other than not defined on the combo box.There are instances of more than one combo box on a Form and it is likely that the contents of one combo box depends on the values of another one. In such situations limiting the contents of the second combo box with the data related to the current item selected on the first combo box is more appropriate, rather than displaying all of them always. The User can then select the item that he wants from a limited list instead of scrolling through a... More About: Contents , Refresh
Refresh Dependant Combo Box Contents
2008-03-13 20:12:00 Creating a Combo box on a Data Entry/Editing Screen is easy. Click on the Combo box Tool Button on the Toolbox, select the required Fields from the Source Table or Query on the Wizard, select the target field on the Form and you are through. Well, may be not quite, few more changes may require on the Property Sheet of the combo box to adjust the width of individual Columns of List, Total List Width property, List Rows Property, Limit to List and On Not in List Event Procedure to prevent entering values other than not defined on the combo box.There are instances of more than one combo box on a Form and it is likely that the contents of one combo box depends on the values of another one. In such situations limiting the contents of the second combo box with the data related to the current item selected on the first combo box is more appropriate, rather than displaying all of them always. The User can then select the item that he wants from a limited list instead of scrolling through a... More About: Contents , Refresh
Refresh Dependant Combo Box Contents
2008-03-13 20:12:00 Creating a Combo box on a Data Entry/Editing Screen is easy. Click on the Combo box Tool Button on the Toolbox, select the required Fields from the Source Table or Query on the Wizard, select the target field on the Form and you are through. Well, may be not quite, few more changes may require on the Property Sheet of the combo box to adjust the width of individual Columns of List, Total List Width property, List Rows Property, Limit to List and On Not in List Event Procedure to prevent entering values other than not defined on the combo box.There are instances of more than one combo box on a Form and it is likely that the contents of one combo box depends on the values of another one. In such situations limiting the contents of the second combo box with the data related to the current item selected on the first combo box is more appropriate, rather than displaying all of them always. The User can then select the item that he wants from a limited list instead of scrolling through a... More About: Contents , Refresh
Filtering Data for different Users
2008-03-07 08:24:00 In a Network based MS-Access Application several Users may involve in updating information into a common Master Table from different Locations or for different categories. When a particular User Logs in into the Application he expects to see or work with the records that belongs to his area only rather than struggling with the full size of the Table.It is always a good idea to save the User IDs of the Users and Data Entry/Editing Date and Time (Time Stamp) into the records when they are added or updated. This can help in so many ways when needed later. For example, a User may come up and say he made some changes to a wrong record but he doesn?t know which one. He needs to find and correct the record but he doesn?t know how to find it. With approximate date and time from him and with the aide of User ID and Time Stamp we can prepare a listing of records or filter records and this may help him to find the record for corrections. Please see my earlier Article on Who changed the Dat... More About: Query
Filtering Data for different Users
2008-03-07 08:24:00 In a Network based MS-Access Application several Users may involve in updating information into a common Master Table from different Locations or for different categories. When a particular User Logs in into the Application he expects to see or work with the records that belongs to his area only rather than struggling with the full size of the Table.It is always a good idea to save the User IDs of the Users and Data Entry/Editing Date and Time (Time Stamp) into the records when they are added or updated. This can help in so many ways when needed later. For example, a User may come up and say he made some changes to a wrong record but he doesn?t know which one. He needs to find and correct the record but he doesn?t know how to find it. With approximate date and time from him and with the aide of User ID and Time Stamp we can prepare a listing of records or filter records and this may help him to find the record for corrections. Please see my earlier Article on Who changed the Dat... More About: Query
Filtering Data for different Users
2008-03-07 08:24:00 In a Network based MS-Access Application several Users may involve in updating information into a common Master Table from different Locations or for different categories. When a particular User Logs in into the Application he expects to see or work with the records that belongs to his area only rather than struggling with the full size of the Table.It is always a good idea to save the User IDs of the Users and Data Entry/Editing Date and Time (Time Stamp) into the records when they are added or updated. This can help in so many ways when needed later. For example, a User may come up and say he made some changes to a wrong record but he doesn?t know which one. He needs to find and correct the record but he doesn?t know how to find it. With approximate date and time from him and with the aide of User ID and Time Stamp we can prepare a listing of records or filter records and this may help him to find the record for corrections. Please see my earlier Article on Who changed the Dat... More About: Query
Crosstab Union Queries for Charts
2008-02-28 22:19:00 For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked into specific locations on the Worksheets where summary figures are available. The arrangement of chart values are planned and entered in the desired order depending on the type of Chart we require. Charts are created out of them as the final step for taking print outs or presentation purposes. Most of the time this is a one-time exercise. If modifications like adding another month?s data, if it is a month-wise chart, then we have to modify the physical arrangement of the data to add another set and change the chart Data Series Range Addresses to make the new data appear on the Chart. If advance planning is made for all twelve months data area for the Chart and if the actual data appears only up to March then balance 9 months Chart Area will be empty.For preparing charts in MS-Access we need to work with Queries to shape the data into a form that can go directly into the Chart.... More About: Union , Query
Crosstab Union Queries for Charts
2008-02-28 22:19:00 For preparing Charts in Excel, the Source Data keyed directly into cells or source-data cells are linked into specific locations on the Worksheets where summary figures are available. The arrangement of chart values are planned and entered in the desired order depending on the type of Chart we require. Charts are created out of them as the final step for taking print outs or presentation purposes. Most of the time this is a one-time exercise. If modifications like adding another month?s data, if it is a month-wise chart, then we have to modify the physical arrangement of the data to add another set and change the chart Data Series Range Addresses to make the new data appear on the Chart. If advance planning is made for all twelve months data area for the Chart and if the actual data appears only up to March then balance 9 months Chart Area will be empty.For preparing charts in MS-Access we need to work with Queries to shape the data into a form that can go directly into the Chart.... More About: Union , Query
Union Query
More articles from this author:2008-02-20 19:50:00 Union Queries are useful for combining data from more than one Table or from other Queries while maintaining the original data physically separate. Assume that the data Tables from different branches (branch1, branch2 & branch3) are linked directly into our main system in separate files and our job is to combine the contents and prepare monthly Reports, Charts etc. We can use Append Queries and combine the data into a separate Table and prepare what we want. Then that is duplication of information. Instead we can combine the data with the help of a Union Query and use that as source for our further processing tasks. The source data files will remain physically separate, branch locations can continue updating information on them.We cannot place Tables or Queries on the Query Design surface, as we do with other type of Queries instead we have to write the SQL String manually on the SQL Window directly. The SQL Syntax is not that hard to memorize and with little practice you can writ... More About: Union 1, 2, 3, 4, 5, 6, 7 |
|
 |
|
 |
