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
Database Open Close Event Alerts
2008-06-05 16:39:00 Your Application is installed on a Network and shared by different Users. You would like to monitor the activities of this Database , like which User Open s or Close s it and at what time? Or your database is not a Secured one (which means anybody can open it) and installed on Network for the benefit of few trusted Users. You already knew their Workstation IDs/Network IDs, but you would like to know some one else opens it to explore and when they do catch them red handed. Warn the unauthorized intruder immediately by sending a message to his Workstation informing that he is not in the permitted zone and request him to close it immediately. He will know that you are on watch over your Database and will not attempt a second time, when you are around!. If he responded positively by closing the Database then you will get an Alert message from the Database transmitted by the Closing Event .You are doing some maintenance work on the Database and you would like to know if some one opens it ...
List Box and Date Part Two
2008-05-29 21:27:00 Continued from LIST BOX AND DATE PART ONEWe have seen that when working with date values in List Boxes we have to convert the selected value into one form or the other before they can be used for data processing tasks. We will try two more examples here with different date expressions. In all these cases the output is same but data selection parameters are used differently. We can Filter Data using Data Range parameters i.e. by entering Start-Date in a Text Box and End-Date in another, on a Report Parameter Screen, or by entering these values directly into Parameter Queries to filter data from the Source Table or Other Queries. But here we are trying to do it differently and get some familiarity in working with Date related expressions.Open the Form LISTBOXDATE that we have created in the earlier example in Design View.Make a copy of the List Box and Paste it in the same area of the Form. Drag and place it to the right side of the Combo Box. See the Sample image given below:Pla... More About: Part
List Box and Date Part One
2008-05-22 14:58:00 One of our regular readers from South Africa, Mr. Nick Els, has made a suggestion to bring out an Article on usages of List Boxes with Date related settings. This is the First Part of a two part Series on this particular Topic. You may go through the earlier Posts: Selected List Box Items and Dynamic Query and Create List from Another List Box.Descriptive names of Months or its Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in a variety of ways for comparing with Date Field values in Queries for filtering data. I mentioned variety because all methods require creating expressions at Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are being compared. We will split this Article into two parts instead of overcrowding with all of them here.List Boxes can be created on Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports or on Report Parameter Forms for using in Queries for Data...
List Box and Date Part One
2008-05-22 14:58:00 One of our regular readers from South Africa, Mr. Nick Els, has made a suggestion to bring out an Article on usages of List Boxes with Date related settings. This is the First Part of a two part Series on this particular Topic. You may go through the earlier Posts: Selected List Box Items and Dynamic Query and Create List from Another List Box.Descriptive names of Months or its Numeral form combined with Year Values from Combo Boxes or List Boxes can be used in a variety of ways for comparing with Date Field values in Queries for filtering data. I mentioned variety because all methods require creating expressions at Query level or in Text Boxes on Forms to reformat the values into a compatible form before they are being compared. We will split this Article into two parts instead of overcrowding with all of them here.List Boxes can be created on Data Entry Forms, Main Switchboards (Control Screen) for opening Forms or Reports or on Report Parameter Forms for using in Queries for Data...
Create List from another Listbox.
2008-05-15 18:12:00 stumble it!If you have not tried the earlier example Selected List Box Items and Dynamic Query please visit that page and try it out before continuing with this. The Table and Queries created for that example are used here also.I will give you briefly what you should do before continuing. Import the Orders Table from the Northwind.mdb sample database. Create two Select Queries by copying and pasting the following SQL String into the SQL editing window and save the Queries with the names as suggested below.Query : OrdersinQSELECT Orders.*FROM OrdersWHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));Query : OrdersOUTQSELECT OrdersINQ.*FROM OrdersINQWHERE (((OrdersINQ.OrderID) In (11067)));In the earlier example we have used a method by highlighting items and using them as criterion for the output Query. Here, we are using different methods for the same result in a variety of ways.When you create a Form with Microsoft Access Form Wizard it will give you a list of Fie...
Create List from another Listbox.
2008-05-15 18:12:00 stumble it!If you have not tried the earlier example Selected List Box Items and Dynamic Query please visit that page and try it out before continuing with this. The Table and Queries created for that example are used here also.I will give you briefly what you should do before continuing. Import the Orders Table from the Northwind.mdb sample database. Create two Select Queries by copying and pasting the following SQL String into the SQL editing window and save the Queries with the names as suggested below.Query : OrdersinQSELECT Orders.*FROM OrdersWHERE ((([Orders].[OrderDate]) Between #5/1/1998# And #5/31/1998#));Query : OrdersOUTQSELECT OrdersINQ.*FROM OrdersINQWHERE (((OrdersINQ.OrderID) In (11067)));In the earlier example we have used a method by highlighting items and using them as criterion for the output Query. Here, we are using different methods for the same result in a variety of ways.When you create a Form with Microsoft Access Form Wizard it will give you a list of Fie...
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...
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
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
More articles from this author: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 1, 2, 3, 4 |
|
 |
|
 |
