Directory — Software — Blog Details for "LEARN MS-ACCESS TIPS AND TRICKS"

LEARN MS-ACCESS TIPS AND TRICKS

LEARN MS-ACCESS TIPS AND TRICKS
Learn 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.

RSS Feed Homepage

Articles: 1, 2, 3, 4, 5, 6

Articles

Opening External Data Sources
2008-07-18 09:02:00
Linking of external data sources like dBase, Excel or Tables from another Microsoft Access Data base, in a Back-End, Front-End scenario, is the most common and efficient method to work with data, besides Tables from within the Database. If such Data Sources are linked with Access database then it is easy to build queries on them to process and prepare customized Reports, or Design Forms to Add/Modify Data from within Access. The Source data can remain in the parent Application for updating information. We can work with the linked Tables with the same ease of using another Table of MS-Access except modifying the structure of the linked Table.To link a Table manually, highlight Get External Data in File Menu and select Link Tables? from the displayed Menu. The Common Dialog Box will be displayed and you can browse to the location of another MS-Access Database, dBase File or Excel File and select it. They will expose Objects that can be linked to the MS-Access Database.If the sourc...
More About: Links , Opening

PIE CHART OBJECT AND VBA
2008-07-10 20:42:00
This is the continuation of a discussion on working with Chart Object in VBA. We have created a small Table and a Report with a Chart Object on it, in the earlier Post Working with Chart Object in VBA and gone through few simple Property changes on the Chart Object to get prepared for the Demo of the VBA Programs. Please go through Steps 1 to 7 explained in the earliest Post, by following the link above, and continue.You may have a look at the Column Chart and VBA topic at your convenience as well.If you have already tried out the Code given in the earlier Posts then you are familiar with most of the Code Segments that you see here and will have no difficulty in understanding them. The difference is only in the formatting method of individual PIE Slices and few property changes also. In this example we will try three types of Pie Charts: 1. 3D Pie, 2. 3D Pie Exploded, 3. Pie of Pie. Copy and Paste the Code given below into a Global Module in your Database.Public Function PieC...

Column Chart and VBA
2008-07-04 08:24:00
In our earlier example, Working with Graph Chart in VBA, we have made preparations to try out Charts with VBA and did sample Runs for three Types of Charts viz. Column Clustered, Line Chart and Pie Chart. If you have landed on this page for the first time you may go to the earlier Post by clicking on the Link above. Go through the simple preparations explained there, by following Steps 2 to 7, to try out the Code given here.Earlier, we could change the Chart type through Code to one of three Types with common property settings that can be applied to all the three. But we have conditionally skipped some properties not relevant to PIE Chart, like Axis Titles. We will deal with PIE Chart separately later and will see how to format individual Pie Slices and change other settings as well.Here, we will try only one Category of Chart viz. Column Type and will run the Code for 4 different designs. This type of Chart with vertical Bars is the most commonly used Charts and loosely referr...

Working with Chart Object in VBA
2008-06-26 22:21:00
Working with Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it is very useful and can be used across Applications to prepare Graph Charts with the same look and feel in minutes. There are several Chart Types, Chart Areas and numerous Object Groups and Objects to work with. The Range of values that can go into them, are too many. There are Chart Type specific properties and value-ranges, like the Auto Scaling property that can be used only for 3D Charts. I did some searching, experimenting and created a VBA Routine that can modify the Graph Chart elements with beautiful formatting to my liking. I hope you will like it too. The sample run result image is given below.Resizing of Graph Chart and formatting of several elements, like Chart Title, Axis Titles, Chart Area, Plot Area and Chart Bars with Gradient color, Legends, Grid Lines, Data Labels and alignment of Data Labels are done with VBA Code. Placing a Chart Object on the Report wi...
More About: Working

Linking with IBM AS400 Tables
2008-06-19 17:13:00
We can convert IBM iSeries (AS400) DB2 Table into dBase Format, download and Link or Import into MS-Access Databases. The downloading procedure must be created and run from AS400 Menus. If it requires several steps before the output file being transferred to local drive then it can be automated with Macro Scripts. The key strokes can be recorded and modified in such a way that the target file goes to a specific location on the Local Machine with a predefined name that remains linked with the Microsoft Access Database. If you have sufficient Access Privileges to iSeries (AS400) Main Frame Tables you can Link them directly into your MS-Access Database. We are going to look into this method with ODBC based procedure shortly.Normally, Reports are generated from AS400 and provided to Users as Hard Copies, or converted into Report Spool File in Text Form, if Soft Copy is requested for. We can download this File either in Text form or upload it into Microsoft Excel (may not Parse the dat...
More About: Links , Linking

REPAIRING COMPACTING DATABASE WITH VBA
2008-06-12 17:28:00
As far as Microsoft Access is concerned compacting the Database is an essential function to keep the Database size to the minimum. When you work with the Database MS-Access creates temporary work-objects within the Database resulting the file size to expand. We can display some of these temporary objects with VBA Code. Copy and paste the following Code into the Global Module of a Database that was not compacted recently. Press Ctrl+G to open the Debug Window (Immediate Window), click somewhere within the code and press F5 to execute it.Public Sub tmpObjects()Dim ctr As Container, doc As DocumentDim cdb As Database, itemSet cdb = CurrentDbSet ctr = db.Containers("tables")For Each item In ctr.Documents If Left(item.Name, 1) = "~ " Then Debug.Print item.Name End IfNextSet db = NothingSet ctr = NothingEnd SubYou will find a listing of temporary work-files, similar to the sample lines given below, in the Debug Window:~sq_cBRVISIT~sq_cBR~sq_cBRVISIT~sq _cBRV...

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...

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

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

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

More articles from this author:
1, 2, 3, 4, 5, 6