Owner: LEARN MS-ACCESS TIPS AND TRICKS URL:http://msaccessblog.blogspot.com Join Date: Fri, 14 Sep 2007 11:22:24 -0500 Rating:0 Site Description: Learn how to enhance your Microsoft Access Forms with 3D Headings and Animated Controls. Easy to implement Examples and Codes, Ms-Access Security Set Up, Ms-Access on Internet. Site statistics:Click here
Reminder POPUPs 2007-09-12 08:57:00 The Reminder
Popup is a Report that opens and displays its contents automatically and plays a background sound to remind about something that needs the User’s immediate attention. The Report is exported into an external independent file format (Access Snapshot File Format, need MS-Office 2000 or later) and opens it in an external Window.We can open and view Access Reports exported into Access Snapshot File Format independently without the use of Microsoft Access and easily transport the Report alone through e-mails and share with others.The reminder can be of any future event that falls between current date and within next 7 days or so that we need advance information about, like appointments, conferences, Birthdays etc.This action is programmed into the Control Screen module with the help of the Form_Timer() Event Procedure.We will try an example with the Employees Table from the Northwind.mdb database. Import the Employees Table from Northwind.mdb sample database. If you are not
MS-Access and Graph Charts2 2007-09-04 23:36:00 Continued...If you have landed straight on this page please go through the earlier Post MS-Access
& GraphCharts
and then continue.Sample Data for Pie-ChartPie_TableDesc Veh_Sales Parts_Sales Service_SalesTotal Sales 450000 645000 25000Create a Table with the above structure and data and save it with the name pie_Table.Open a new Report in design view. Select Object… from Insert Menu, select Microsoft Graph Chart and Click OK. A Chart Object with default values is inserted on the Report.Click outside the chart on the report to de-select the chart and to come out of Edit Mode. Click again on the chart to select it, display the property sheet and change the following values:Size Mode = ZoomRow Source Type = Table/QueryRow Source = Pie_TableColumn Heads = YesLeft = 0.3”Top = 0.3”Width = 6.0”Height = 4.0”Double Click on the Chart. The Chart Formatting Toolbar will appear on the top. Select 3-D Pie Chart from the Chart Type Toolbar Opt
MS-Access and Graph Charts 2007-08-29 07:19:00 Access Reports are excellent in presenting information in Numbers and Text. But, Charts
goes one step further in providing information in the form of pictures and a quick look at them says it all. If we know the simple rule that goes for a Chart then we can create Charts in Access. The simple rule is: show the change of an event over a period of time in the form of dots and lines in relation to the numbers that they represent rather than in numbers and text itself. You must know how to create MS-Access data into a form suitable for preparation of Graph
s. To get some familiarity we will look into some examples that uses only one set of values and the data format that is needed for the graph.Events like the daily change of day temperature or the change of body temperature. of patients in a hospital, to monitor their condition, or the change of Gold price over a period of time etc. can be plotted in the form of Line/Bar Charts on a Graph Paper manually. The Scale of the Chart can be dete Read more:Access
Reports - Page Border 2007-08-22 15:42:00 Drawing a Page Border
is very easy in MS-Word and it is part of the Formatting Options and different artistic styles are also available. But when it comes to MS-Access we have to depend on Code to draw a Page Border before printing the Document. An Example Document image is given below:The above Document is imported from Northwind.mdb Database (visit the Page Saving Data on Forms not in Tables for location reference of the Database) without the Page Border (Catalog Report attached to Categories Table) and drawn a double-lined Page Border with the help of Code given below. If you would like to try it out on the same Report then Import the Categories Table and the Catalog Report from Northwind.mdb database. The Code can be used on any Report that you wish to draw the Page Border and the border will be drawn within the Margin settings of the Page. Public Function PageBorder(ByVal strReportName As String)Dim Rpt As Report, lngColor As LongDim sngTop As Single, sngLeft As SingleDim sngwidt
Highlighting Reports 2007-08-16 08:20:00 Analysis of data stored in Tables and providing resultant information in the form of Reports to the outside world is an important function of any Database Management System.An Access Application may contain several Tables logically related but maintained separately for easier handling. When it is time to prepare Reports related tables are selected and joined on common information in them, filtered and placed the output on Reports. Meaningful and timely Reports play a very important roll in decision making.Report designing is an Art in its own merit, like Form designing. MS-Access is equipped with excellent Report Designing Tools and with little practice you can master it.We have been asked to prepare a report on the Company’s Sales Team, to review their monthly Sales Target Achievement. A mandatory Target Amount of $100,000 is fixed on each member of the Team and the Management wants a month-end Report to review his or her performance. The management focuses on the performance of t
SAVING DATA ON FORMS NOT IN TABLE 2007-08-09 06:48:00 We normally save data in Tables, even though we enter them on Forms. We cannot save data on Forms as we do on Tables. But, we can definitely save some useful information on the Form itself without the support of data Table to store the values.Let us look into a situation where we need such a facility for our day-to-day activities. Assume that we want to open the Employees Form in data editing mode (not in data entry mode) with the last record that we were working on during earlier session, to continue our unfinished work from there. To do this, we must save the key information of the last worked record on the Form before closing the Employees Form.Import the Employees Table and Employees Form from the NorthWind.mdb sample database located at C:Program FilesMicrosoft OfficeOfficeSamples (MS-Office 2000) or C:Program FilesMicrosoft OfficeOffice11Samples (MS-Office 2003). Make a copy of this File to your current working folder so that whenever you need information from this fil
Edit Data in Zoom-In Control 2007-08-02 06:02:00 While designing a Form, Report or Query certain properties of these objects we can Zoom in and edit the property values in a big window, like Record Source Property, Filter or Order By property etc. When you right-click on these properties a Shortcut Menu will open up with the Zoom... option in it (see the image below) and when clicked we can edit the property values comfortably in a big window.But this option is not available when editing data on Forms, for large Field Type like Memo or text fields that holds more data than it displays.The Zoom… Tool Button is available under Properties Category of Forms, Reports, Query and Index groups in the Built-in Shortcut Menus. We can make a copy of this button and paste it on our Custom Shortcut Menu or on the built-in Form View Control
Shortcut Menu, as we did for Animated Floating Calendar and use it on our Forms, if needed.But we will do it differently. We are going to design our own Zoom In Control and it will be interesting to explo
Custom Menus and Tool Bars2 2007-07-27 00:41:00 Continued from earlier Post: Custom Menus
and ToolbarsOpen your database where we have created the Custom Menu Bar with the name MyMenuBar. Select Customize from Toolbars sub-menu of View Menu. Or you can right-click anywhere on the Menu Bar or Toolbar above and select Customize from the displayed Shortcut Menu.We can add built-in menu options from the Customize Control also without copying/dragging from other menus as we did in step 11 in the earlier Post. Click on the Commands Tab on the Customize Control. When you click on any of the Menu items on the left (in categories list) the built-in commands pertaining to that Menu is displayed in the Commands List at the right window. From here you can click and drag (no need to press and hold the Ctrl key) any option and place it on your Custom Menu Bar or Toolbar to organize the Built-in Options for the User.12. We will concentrate on our totally independent Menu Options: Data Files, Process & Reports and create our own Programs/Macros
Custom Menus and Tool Bars 2007-07-20 07:17:00 We already had a formal introduction to Custom Tool Bar, Submenus & Shortcut Menus
through the earlier posts: Animated Floating Calendar & Calendar and Toolbars. We have seen how to reference individual Button to enable/disable it based on situations that demands it.Here, we will take a closer look at Custom Menu bars, Toolbars & Shortcut Menus. Since, it is a lengthy subject we are forced to split this into 2 or 3 Posts.MENU BARSWindows based Applications have a Menu bar at the top with titles like File, Edit, View, Insert etc. When we click on them a long list of Options like Sub-menus, Items with Description alone or with Button Images and Description appears in a drop down list. When we click on any of them the designated action defined on the menu option runs and we will get our work done. We can create a Custom Menu bar with our Application specific Menu Descriptions like Data Files to open Forms linked to Tables/Queries, Reports to preview and print Reports, Utilities for oth
Calendar and Toolbars 2007-07-13 06:11:00 In our earlier discussion on Animated Floating Calendar
, the method which we have used to display the Calendar Control needs refinement, as I have mentioned there. We will look into the following points and how to deal with them:Advantages & disadvantages of Custom Toolbar Button & Shortcut Menu Button for displaying the Calendar Control and whey we need both?When there is no need for a Calendar Control on a Form how to disable the Buttons?When the Target Control is not a Date type field how to prevent the Calendar from appearing?Do we need the Custom Toolbar or Shortcut Menu Button to display the calendar, is there any other method?Let us look into the above Questions, one by one.The Custom Toolbar Button creation method was easy to explain without going too deep into the Shortcut Menus. Shortcut Menus are Popup Class of Menus and different from Toolbars. We will learn more about creating Custom Menus, Toolbars & Popup Menus for our Applications later.To run our Calendar the easies
Animated Floating Calendar 2007-07-05 14:00:00 Calendar (the ActiveX Control) is a good piece of object for clicking and inserting a Date into a field instead of typing. There is only one problem, let us say more than one, it occupies lot of space on the form and it comes in the way of designing other elements of the screen as well. If you need to enter date in more than one field, each field requires different Calendar Controls and we may not be able to accommodate all those copies in one place. Finally, a convenient method become totally inconvenient and we may have second thoughts and decide to go by the traditional method: typing everything manually, after all somebody else is going to do that. We are going to use the Calendar Control and we don’t accept defeats and all those above reasons will not prevent us from using it. The method that we are going to try out here is kind of hard to implement for the first time. But it will be very easy on other Forms in the same Project. Open your Database with a Form that you Read more:Calendar
, Floating
Startup Screen Design 2007-06-29 09:55:00 A Startup
Screen is a good beginning for an Application and not only this gives the user a taste of your design abilities but also you can use this screen to launch some of your daily routine programs like Daily Backup of the database, checking the link status of the attached tables from external data sources etc., in the background. We will look at a simple design of a Startup Screen. The Completed design is shown below with animated 'Website Address Image' at the center:We will look at the Form level and control level settings individually. Click on the Image above to look at its design in a bigger window. NB: All Object specifications on this site is in U.S. measurements. Those who follow Metric System please convert them or change the Regional Settings to U.S. in the Control Panel on your PCs. Open a new Form and set the Detail Section & Form level Properties as given below: Detail Section Height = 2.85"Back Color = 16777215 (white)Form Width = 5.45"Defaul
Control Screen Menu Design 2007-06-20 05:08:00 We have seen in the earlier topic on Control
Screen Design that we can display several Menus at one place. Let us look into a simple design with 2 Menus and how they can be displayed at the same place interchangeably. We need two Tables, one for Data Files List and another one for Reports’ List. You can use the same Table that we have created for the Opening Access Forms topic. Make a copy of it and rename it as ReportList.Open a New Form in design View.Click on the Tab Control Button on the Toolbox and draw a Tab Control on the new form’s detail section as shown below:Click on the second page of the tab control where page2 is shown and display the property sheet. Type Reports in the Caption Property.Click on Page1 and Type Data Files in the Caption Property.Now we need to create a List Box. Before clicking on the Listbox button on the Toolbox ensure that the Control Wizard (the button next to the arrow button) is selected on the Toolbox. When you point the arrow on the Tab
Control Screen Design 2007-06-10 14:15:00 Computers were designed for serious business purposes, when they were costing a fortune, not for entertainment. But, this has changed in the 80s when Personal Computers were introduced and easily available programs were computer games. Owning a personal computer was a dream of everyone at that time (even now) and I bought mine too (brand name Spectra Video, Taiwan). Its processor speed was 1.xx something with 84K Memory, (you can call it a Keyboard rather than a computer) with connections to TV for Screen and to a Cassette Recorder for recording Programs(BASIC) or Data. Even now you can see these types of machines in Supermarkets in Toys section. IBM PCs with 4.77 Mhz. processor speed and 640K Main Memory, with cassette recorder and priced sky high were unreachable for many. 5¼” Floppies & 10MB Hard Disks came with later Versions of XT(Extended Technology), AT (Advanced Technology) Models. What I am trying to say here is, the plaything concept has not changed ever since an Read more:Control
Open Forms with Hyperlinks in Listbox 2007-05-25 10:11:00 We have seen in the previous example that all our Forms
can be organized in one place with the use of a ListBox and can Open the Form by Double_Clicking an item in the list. As a result, based on the value in the Type field of the control table, a Form opens directly or runs a macro to perform a sequence of operations to prepare the output before opening a Form with the processed data linked to the Form. The objective of this method that we are going to try out here is same, but we will do it differently. It is better to know more than one method, which we can use in our various Projects, rather than using the same thing everywhere. For this example we need to design a small form (not a listbox) and make it look like a listbox. First, create a table with two fields with the following field Types and sizes: Field Name Type Size Seq Number Integer Desc Hyperlink Save the table with
Opening Access Forms 2007-05-19 14:59:00 Using the same method that we have seen in the Closing Forms
topic, we can create Command Buttons on the Control Screen and link the Forms to each Command Button by setting the Form Name to the Hyperlink SubAddress Property. This is the easiest method to open forms from a central point, when you have several forms in your Application. But the problem with this method is that you may end up with several command buttons on your Control Screen.We will look into different methods to organize and open the Forms from the Control Screen. We will look into one of them here and we need little preparation for this method.First of all, create a Table with the following Fields:SRLField NameField TypeField Size1.IDNumberInteger2.DESCText35 Characters3.FORMText20 Characters4.MACROText20 Characters5.TYPENumberIntegerand save it with the name: DataFiles.An Image of a Table with sample data filled in is given below: Fill the table with your data files’ list for which you already have Forms designed f Read more:Access
Closing Forms 2007-05-18 06:09:00 For closing an active Access Form we can use several methods and apply certain tricks to enhance the overall look of the action used for it.The simplest method is to click the window close button X, which everybody knows. The On Close Event of the Form is also triggered on this action and the programmer can take care of any unfinished work like closing of other forms related to the main form etc. But, when more Forms
are open at the same time then there is a chance that the user may close the wrong Form by clicking on the wrong X button on the top.I prefer to create a Command Button on a convenient location on the Form, like at the Footer or Header of the Form and let the user click on it. Create a macro and link to the Command Button’s Hyperlink SubAddress Property. My favourite name for the Macro is Hlink_Close and it will contain only the Close Action leaving the ObjectType and ObjectName parameters blank.Click on the Hyperlink SubAddress Property of the Command Button and click
USING MS-ACCESS ON INTERNET 2007-04-20 07:14:00 MICROSOFT ACCESS AND INTERNETInstalling the Personal Web Server (PWS)or Internet Information Services (IIS)Create the Example ASP FilesCreate a DSN ConnectionConnect to the DatabaseModify Access Data OnlineInserting DataModifying DataDeleting DataData Insert FormMICROSOFT ACCESS HOW TOs>> Home
MS-ACCESS AND INTERNET 2007-04-19 23:32:00 The following article with examples illustrates how to use Microsoft Access Database in internet. The example ASP Codes (or your Project for Internet) can be tried out on Local machine itself after installing a Personal Web Server (Windows 98) or Internet Information Services (Windows 2000) on your PC.Courtesy: Susan Sales Harkins and Martin W.P.ReidInstall the local serverThe first step is to install Personal Web Server or Internet Information Services, so skip to the next page if a server is already installed. PWS is a desktop Web server that allows you to connect to a network and share data. The example ASP files will use your system’s server (PWS or IIS) to simulate downloading data from the internet, but that data will really be on your local system.To install PWS on Windows 98:Insert your Windows 98 install disc in your CD-ROM drive.Click the Start button on the taskbar and choose Run.Enter the statementx:add-onpwssetup.exewhere x represents your CD-ROM drive letter. Click
Example ASP Files 2007-04-19 16:19:00 Set up the server folder and ASP filesAfter installing PWS or IIS, you should create a new folder to store the database and ASP files that you’ll execute in order to retrieve data from that database. Create the new folder in inetpubwwwroot and name it sample.Then copy the Northwind sample database that comes with Access into this new folder. You’ll find the Northwind.mdb file in Program FilesMicrosoft OfficeOfficeSamples. If you can’t locate Northwind.mdb, use the Windows Find feature or insert your Office CD and install it.By default, a folder won’t share files. So after you’ve created the sample folder, right-click its icon in Windows Explorer, select Properties, then click the Web Sharing tab. Select the Share This Folder option and click OK. Now Windows will allow you to share the files within this folder on a network.Create the example ASP FilesThe three links below open three short ASP example scripts. To create these files, simply copy and paste each scrip Read more:Example
Create a DSN connection 2007-04-19 16:18:00 At this point you need to create a data link. When you execute an ASP file, Windows must know which database you’re connecting to. An ODBC Data source Name (DSN) simply stores the information(the name, the path, the user, and the password) required to connect to a specific database. OLE DB connection
s are superior to DSN because they’re quicker to connect, but you must include the specific connection string in the script, and that’s beyond the scope of this article. A DSN connection requires little know-how to set up. There are two types of DSN.System: A System DSN is available to all users on the system and is the one to use when you want others to access a DSN connection via the Web. For this article, it is vital that you use a System DSN created on your Web server or local development system.File: All users who have the appropriate drivers installed can share a File DSN. A driver is a bit of software that provides specific functionality, such as a connection to a data s
Connect to the database 2007-04-19 16:14:00 All the pieces are in place. Now you’ll use the System DSN you created to connect to the Northwind sample database in inetpubwwwrootsample the same directory where you stored the ASP example scripts. Open a browser and execute each ASP file by entering a URL in the formhttp://server/subfoldername/filename.aspwhere server is the name of your server, subfoldername identifies the folder within wwwroot containing the ASP file, and filename.asp is the name of the ASP file that you’re executing. In this case, the server should be localhost, so to run the first example file enterhttp://localhost/sample/example.aspThe result should be a list of names, last then first, of each employee in the Northwind Employees table. Let’s look at the actual scripts; all of the ASP code is enclosed in <% and %> tags. In example.asp, the first statement<%Set Conn = Server.CreateObject("ADODB.Connect
ion")Creates a new instance of the connection object named Conn. You can give ob
example1 asp 2007-04-19 15:56:00 Paste this code into a file and save it as example1.asp in your sample folder.<html><body><%Set Conn = Server.CreateObject("ADODB.Connection")Conn.Open "NorthwindDSN"sql = "SELECT LastName, FirstName FROM Employees"Set rst = Conn.Execute(sql)%><%Do While Not rst.EOF%><%=rst("LastName")%><%=rst("FirstName")%><br><%=rst.MoveNextLoopSet Conn = NothingSet rst = Nothing%></body></html>Click Next for example2.asp
example2 asp 2007-04-19 15:46:00 Paste this code into a file and save it as example2.asp in your sample folder.<html><body><%Set Conn = Server.CreateObject("ADODB.Connection")Conn.Open "NorthwindDSN"sql = "SELECT LastName, FirstName FROM Employees WHERE LastName = 'Reid' "Set rst = Conn.Execute(sql)If rst.EOF Then Response.Write "No employee records available" Conn.Close Set Conn = Nothing Response.EndEnd if%><%Do While Not rst.EOF%><%=rst("LastName")%><%=rst("FirstName")%><br><%=rst.MoveNextLoopSet Conn = NothingSet rst = Nothing%></body></html>Click Next for example3.asp
example3 asp 2007-04-19 15:39:00 Paste this code into a file and save it as example3.asp in your sample folder.<html><body><%Set Conn = Server.CreateObject("ADODB.Connection")Conn.Open "NorthwindDSN"Sql = "SELECT LastName, FirstName FROM Employees "Set rst = Conn.Execute(Sql)If rst.EOF Then Response.Write "No employee records available" Conn.Close Set Conn=Nothing Response.EndEnd If%><table border=1><caption align="top">Employee List from Northwind. Demo</caption><tr><th>Last Name</th><th>First Name</th></tr><%Do While Not rst.EOF%><tr bgcolor="#00FFFF"><td><%=rst("LastName")%></td><td><%=rst("FirstName")%></td></tr><%rst.MoveNextLoopSet Conn = NothingSet rst = Nothing%></table></body></html>Goto Main Page
MS-Access Object Documenter 2007-09-26 13:07:00 We normally prepare Reports on processed or raw data and take print outs to share the information with others.But, there are times that we need details of the database Object
s: Tables, Queries, and Scripts (Macros) or VB Modules and other objects. We can take detail listing of Objects, like Table Fields and their Properties, Data Types and Sizes or the Control Names on Forms/Reports and their properties, User-Level & Group-Level Permission Settings and so on.We will take a listing of a Table with Field Names, Data Type, Size of each field and the Index information. I have used the Employees Table for our example, but you can try this with any Table in your Database. The sample listing is given below:Select Tools -- > Analyze -- > Documenter.Click on the Tables Tab.Put Check Mark on any of your Table.Click on the Options… Button to open the Options Dialog Control.Change the settings as shown above and Click OK to close it.Click OK on the first Dialog control to format the Listing Read more:Access
Useful Report Functions 2007-09-19 11:15:00 Common Functions
that you can use in Report Header or Footer Sections, while designing Reports, are given below. Copy and Paste the VB Code into a Global Module of your database and save it.Write the Functions in the form of a Formula in Text Boxes as shown in the Syntax.Function to display formatted Page Numbers.Function : PageNo()Syntax : =PageNo([page],[pages])Result : Page : 1 / XXX where XXX stands for maximum number of pages of the Report.Note : [page],[pages] are MS-Access built-in Report Variables and must be used as shown.Code:Public Function PageNo(ByVal pg As Variant, _ByVal pgs As Variant) As String‘----------------------------------------------------------‘Output : Page: 1/25‘ : Call from a Report Text Box control‘Author : a.p.r. pillai‘Date : 01/09/2007‘Remarks : The Formatted Text takes up 15 character space‘----------------------------------------------------------Dim strPg As String, k As IntegerOn Error GoTo PageNo_Errpg = Nz(pg, 0): pg Read more:Useful
MS-Access and Mail Merge-2 2007-10-11 08:08:00 Form LettersA Form Letter can be anything like an Invitation to the Members of a Club or a Notice to the Share Holders of the Company or a Circular to the Head of the Departments of the Organization or a forwarding memo of Departmental Telephone Expenses Statement and so on. In all these cases the body text of the letter will be standard but the addressee information will change on every page of the document.When we plan for a Form Letter in Microsoft Access
we must carefully organize five different elements of this task.Design a Table and add a single record to hold the main letter contents like Memo Reference, Date, Subject, Body Text and Letter Footer Information like Department Head who will sign the document etc.Design a Form for the above Table for editing the Letter Body Text, for changing contents as and when it becomes necessary.Processing of data and designing a sub-report (like a statement on department-wise telephone expenses) if needed to insert into the body of the main
MS-Access and Mail Merge 2007-10-04 10:22:00 Address LabelsWhen we talk about Address Labels, Form Letters, Mail Merge and so on, the name that comes into our mind is MS-Word, loaded with plenty of functions for the above tasks. It needs a database to provide source data for address labels, Form Letters etc. We can prepare a Table in Word Document and use it for Addresses or attach one from Microsoft Access
or other database sources.Here, we are not going to use MS-Word for Address Labels, Form Letters and Mail Merge operations, we will do it in MS-Access. Yes, we will try Mail Merge too.We may not be able to do fancy paragraph formatting as we do in Word but preparing Form Letters in Access is important in situations like reporting of Agency Agreement Renewals, Bank Guarantee Renewal Reminders or Department-wise Monthly Stationery Expenses or Telephone Expenses for review etc. and needs only one or two standard paragraphs combined with the actual statement, which we can quickly put on the print within no time.We will start with
MS-Access and Mail Merge-3 2007-10-18 08:35:00 Continued from previous Post MS-Access
and Mail Merge-2.MAIL MERGE IN MS-ACCESSWe have already designed and tried the Form Letter preparation procedure through the earlier article. We need all those Objects for the Mail Merge in Access and here we have less work to do. I hope you understood the intricacies of the procedure explained there and how all those objects and methods works together to prepare the Form Letter. Since, we are through with the major designing tasks already; we can concentrate on the implementation of Mail Merge method with some minor changes in two or three places.I will give few examples below as how to insert Field Values from the Report source Query/Table and how to use Built-in Functions into the letter body text on the editing Form. The user needs only few simple rules to keep in mind to use the Mail Merge method.I will insert the Main Program Codes at the end of this Article. You may Copy and Paste them in appropriate locations in your Project as suggeste
Recommend Site To A Friend
