Welcome To The First Edition Of Tips-FX

See all newsletters

Tips-FX will be a Bi-Monthly email newsletter that will focus on providing tips, help and information for skilled Microsoft Access users.

In this edition    

JAZZ UP YOUR FORMS
YEAR 2000
ADVANCED GROUP BY
POPULAR ACCESS PAGES ON OUR SITE
ANNOUNCING VB123.COM
GOOD READING
DATA MINING AND FAST ACCESS GRAPHS

Jazz Up Your Forms

Add some Explorer like functionality to your command buttons using the following on MouseMove event.  CmdOpenForm is an example button on an form.  

Private Sub CmdOpenForm_MouseMove(Button As Integer, Shift
  As Integer, X As Single, Y As Single)

  On Error Resume Next
  CmdOpenForm.SetFocus
End Sub

Only ever use this on your main forms and never use it on any forms where you are managing data as it will cause the update record event to occur.  You can be more sophisticated by putting a toggle switch in the code so that focus is only set once and is turned off when the command button looses focus.

Year 2000

Are year 2000 issues going to affect your Access programs ?  Microsoft say No for Access 95/97 and Yes for Access 2. 

If you run the Total Access Inspector program written by FMS on the Northwind Database, the software identifies 77 High Risk issues that need to be addressed.
So what do you need to worry about and what do you need to fix. 

Access and Office uses a shared DLL program for converting dates and time before they are stored in the database. Not every PC can be guaranteed to be using the same DLL so it is possible to have discrepancies in the way dates are entered into the database.  Microsoft have an article in their online resource MSDN that outlines these issues
(and contradicts their previous Y2K statements).

http://msdn.microsoft.com/library/periodic/period99/html/msovba9903_y2k.htm

To fix these issues, the FMS program says that you need to
change the formats that all your date fields use to force
4 digit date entry.   eg  "mm/dd/yyyy" and not "Medium Date"

You also need to add to change the input field to force 4 digit date entries and while you are at it add some validation strings.

The other big issues are any code using 2 digit years or "medium date" or "short date" and importing and exporting. I have written up a summary of the FMS year 2000 reporting
software plus the things you can do manually at

http://www.gr-fx.com/Toolbox/99_reviews/ta2000.htm

Advanced Group By

Date related information in tables is one area where it would be unusual to analyze results by consolidating on a groups of data in the raw date form.  Usually you would
want to look at weekly, monthly or quarterly results whilst the actual information would be stored with one or more entries per day.

Using the access format function, the SQL shown below

SELECT DISTINCTROW Format([SalesDate],"yyyy-mm") AS
SalesMonth, Sum(sales) AS TotSales
FROM tblSalesResults
GROUP BY Format([SalesDate],"yyyy-mm");

producing the following output

SalesMonth  TotSales
1995-00      31560
1995-00      33340
1995-00      31584
1995-04      33358


So by consolidating the dates into months, we now can start analysing the data to look for possible patterns.   Note: 2 important things with this query.  Firstly the
Years are shown first followed by the months as numbers.   This guarantees that the output will be sorted sequentially. Also when you start building these functions, always use the full 4 digit year or you will be introducing a Year 2000 bug into your code as year 2000 will show as  "00" and sort first.  Read more about Consolidation Queries at

../98docs/consolidate.htm

Popular Access Pages On Our Site

../99/externalimages.htm

which discusses how to setup Access for Jpegs and the like

ANNOUNCING VB123 - THE SOFTWARE RESOURCE SITE

We have now established an easier web site address for you to find more of the great tips, help, lessons and links for Access, VB, ASP and Office that you receive in Access Unlimited

---->  http://www.vb123.com

Search the site at
http://www.vb123.com/search

And to celebrate this momentous occasion, check out this wonderful photo of Bill and the gang at Microsoft in all their 1970's glory (and wonder if you would have invested in Microsoft Mark 1).

http://www.vb123.com.au/toolbox/news/issue1_ms1970.jpg

Good Reading

Following are some links to good articles that you can  download and read for free.  The articles featured this month come from the MSDN site

Add a table of contents page to long reports
http://msdn.microsoft.com/library/periodic/period99/html/ima9951.htm

Time to starting think about changing to ADO for handling your recordsets rather than DAO. Well try these articles

http://msdn.microsoft.com/library/periodic/period99/html/SA99b1.HTM
    or try
http://msdn.microsoft.com/library/periodic/period99/html/sa99e1.htm

Note that there is an Microsoft download that will allow you to
start using ADO in your Access 97 databases.

And If you want lots of good code samples, Helen Feddema has plenty

http://www.helenfeddema.com/CodeSamples.htm

--->  "The Toolbox"  <---

The Toolbox is part of our web site that has been running for last two years and is focused on providing tips, help and lessons on VB, Access, Frontpage and Active Server Pages. Have a look around one day or even contribute and we will make sure to give you good exposure for your efforts.   The site has 300+ visitors a day.

http://www.vb123.com/Toolbox/

--->  Data Mining And Fast Access Graphs  <---

A new version of our data mining shareware Graf-FX is now available for download from

http://www.vb123.com/explore

The new version features wizards for starting your data mining more efficiently, setting up better grouping queries and generating filters for restricting your data. We have also jazzed up other parts of the program.


Thanks for reading our first newsletter and please send an email if you want to receive the next one. You can do this by clicking the the arrow button and signing up on the newsletter table of contents page.

Click this button to go to the next page in the article loop.



Garry Robinson - Software Consultant

Published  1999-06