vb123.com.au                                                          

  Welcome To Tips-FX - Edition 5
See all newsletters

Tips-FX is a email newsletter that provides free
tips, help and information for skilled Microsoft
Access users and related software disciplines.

In this edition

Finding Things In Reports
Building Documentation
Microsoft Y2K Guide
Dates Are Hard Enough Even Without Y2K
Controlling Microsoft Graph And Chart - Free Download
Good Reading And Useful Sites - Online IT Books


FINDING THINGS IN REPORTS

Have you ever wondered which page a certain customer or
product exisits in a large Access report.  Unfortunately
there is no way to search the actual report so you have to
go back to the query behind the report.

Well if you use the following menu sequence once you preview
the report, you will find that you can search for a phrase

Tools  ... Office Links  ... Publish it with MS Word

Now Access grinds away and produces a fairly ugly Rich Text
Format document that does one good thing.  It maintains the
pages as they are in the report.   Now uses the Find command
in Word and search for the phrase that you are interested in.

Once you find the customer on page 45, you can return to the
preview of the report in Access and type the page number in the
page movement bar at the bottom of the report.

-------------------------------------------------------------
BUILDING DOCUMENTATION

During the last couple of weeks I had a couple of inquiries
about how we put together our help file for Graf-FX.  We
actually use a budget priced product called Helllp!   This
product works from within Word and generates and opens the
help file from within Word.  If you only ever have to do a
small help file and wish to avoid some of the cryptic help
requirements of the Help Builder, its a pretty good little
program.

http://www.guysoftware.com/

If you like building web pages and are not sure about
building help files, there is an alternative.  On your
form, you can put a hyperlink to your web based help and
there will be your help system.  Not quite as integrated
but pretty professional none the less.  To add a hyperlink
to an access form, in design mode, choose
menu  Insert  ...  Hyperlink.

Now if you wish to make your web based help better, try
graphics hotspots.  These are areas on your graphics (gif
and jpeg files) which you define as a hyperlink. Try

http://www.gr-fx.com/graf/grafview.htm

which has a series of hot spots that point to bookmarks
within the html page.  In Frontpage, the first thing you
have to do is insert a screen shot that you have saved
in gif or jpeg format.  Click on the image and a toolbar
will appear that will allow you to do things to the image.
One of these options is a rectangular box.  Select it and
then define a rectangular portion on the image.  When you
have completed the hotspot, you will be asked to nominate
a hyperlink and/or a bookmark.

These hotspot solve one of the hardest things in software
documentation and that is describing which button on the
form that you are actually talking about.  ie click the red
button just to right of the second combo box and just
below the toolbar.

------------------------------------------------------------
MICROSOFT Y2K GUIDE (For home computer users)

Microsoft have a site designed for all those running their
business behind brick walls rather than in ivory towers.

Microsoft Y2K Guide -
Click Here

In particular the Access pages
Click Here

Note: That there is a fix for the serious Access 2 issues
but it is currently unavailable for downloads

One recommendation is to set the short date in your regional
settings of the control panel to 4 digits. This may actually
effect your applications but now a good time to find out.
The real reason for 4 digits is that at least the 1900 dates
that are supposed to 2000 dates will be visible on the screen.



-------------------------------------------------------------
DATES ARE HARD ENOUGH EVEN WITHOUT Y2K

In the following vb in an Access 97 form, I was trying to
generate a record set that I wanted to filter on two date
fields on a form.   What I wanted to achieve was to find
out if a booking existed for an accommodation before running
an update query.

One of the date fields was referenced as follows.


fromDate = [Forms]![FX_StudentMatch]![FromDate]


and the recordset checked against the date as follows


if  fromDateSave >= rstBookings!FromDateFld  then

This code failed because I had a Australian dd/mm/yyyy format
on the field.  To fix this and come up with a very reliable
method, I convert all the dates into their long integer
equivalent number using an Access function called  CLng

The code that worked is as follows

fromDateSave = CLng([Forms]![FX_StudentMatch]![FromDate])

Dim rstBookings As RecordSet
Set rstBookings = CurrentDb.OpenRecordset("select FromDate, clng(fromDate) as
FromDateFld from Bookings where accomid = " & AccomIdSave, dbOpenSnapshot)

With rstBookings
  Do Until .EOF
    If (fromDateSave >= !FromDateFld) Then
      MsgBox "A booking exists ", vbCritical
      rstBookings.close

      Exit Sub

    End If
    .MoveNext
  Loop
End With
rstBookings.close


So the lesson here is if you are getting in a tangle
with dates, think about converting the dates into numbers.  

If you don't like this method, try Adam Cogans suggestion 

fromDateSave = format([Forms]![FX_StudentMatch]![FromDate],"mm/dd/yyyy")




-- Advertisement -----------------------------------------

***** Graf-FX *****

Try out our popular Access shareware tool on your databases

Download a trial version from
http://www.vb123.com/explore


-----------------------------------------------------------


GOOD READING AND USEFUL SITES

Macmillan press have a facility where you can read quite
a few different types of computer books online.  I checked
out the Access books and quickly browsed through a book
called Microsoft® Access 97 Quick Reference

The book came up with a good description on the use of
criteria in text boxes that I have pasted as follows

"Most of your data will be text data type. There are some
handy expressions for use with text criteria. To enter
criteria you need to be in the criteria cell of the Design
View of a query or a filter.

Steps
1. Enter in the complete text match you want. Access puts
   quotes around the expression.

2. Include the asterisk (*) wild-card character for any text
   you are not sure of. A single asterisk can take the
   place of any number of characters. You can also use
   multiple asterisks in one expression.
   Jo*ns*n will find Johnson, Johanson, Jonsen, and Johnsen.
   Access enters Like "Jo*ns*n" in the criteria cell.

3. Enter Not and then the text you don't want to match.
   Not NY will find all states except NY.

4. Use the question mark (?) as a wildcard character for
   one letter. For example, C? would find CO, CT, and CA.

5. Choose the Datasheet View button to see the results of
   the query or choose the Run button to update records if
   the query is an action query. "

The Macmillan site looks very interesting if you want to do
some reading of material on computer topics prior to
purchase of a book. The time taken to enroll and select a
book to read is about 5 minutes.  No search facilities is a
bit of a downer as you have to go through each page one
at a time.  But its free !!!

http://pbs.mcp.com/

---

Want a free read of an article on all sorts of computer
topics.  Try

http://www.zdjournals.com/free_issue.htm



-----------------------------------------------------------
 

Welcome To Access Unlimited - Edition 4
See all newsletters

Access Unlimited is a email newsletter that provides free tips, help and information for skilled Microsoft Access users and related software disciplines.

In this edition

MICROSOFT SOLUTIONS DATABASE
ANNOUNCING VB123 - THE SOFTWARE RESOURCE SITE
MAKING ARROW KEYS WORK IN CONTINUOUS FORMS VIEW
INDEXING FIELDS IN MICROSOFT ACCESS
MAKING EXCEL A REPORTING AND GRAPHING TOOL
CONTROLLING MICROSOFT GRAPH
PLACES TO SUBMIT YOUR SHAREWARE
GOOD READING AND USEFUL SITES
IN THE PRESS

MICROSOFT SOLUTIONS DATABASE

Tucked away in the Microsoft Office directory is a file called Solutions.mdb.   This list gives a brief outline of all the different things that you are likely to find in that database.

General Application Help
Sample forms
Query by form
Using queries with forms and reports
Control what you print on reports
Calculate totals on reports
Create advanced reports
Sample reports
Work with combo boxes, list boxes, subforms, and subreports
Work with forms and controls
The Web And Other Things

For more on what you can find in this valuable learning tool
http://www.vb123.com/Toolbox/99_free/solutions.htm

MAKING ARROW KEYS WORK IN CONTINUOUS FORMS VIEW

The other day I was asked to make the down arrow key work in a continuous record form in Access.  If you have ever been frustrated why you cannot get down arrow support in
these forms, you are not the only one.   After searching around for a while, I think I have come up with a general solution.  To implement it, copy this code into the visual
basic section of any form that you need this functionality.


Private Sub Form_Load()
  Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  On Error Resume Next
  Select Case KeyCode
    Case vbKeyDown
      ' Go to the next record
       DoCmd.GoToRecord , , acNext
    Case vbKeyUp
      ' Go to the previous record
       DoCmd.GoToRecord , , acPrevious
  End Select
End Sub

Search Access for help on the "SendKeys" and "KeyPreview"
to find out more about these topics.

INDEXING FIELDS IN MICROSOFT ACCESS

Don't apply indexes to fields that contain much the same data. For example, indexing a Yes/No field is almost always a performance degrading operation. Similarly, if you have a
number field that only contains two or three values, and index wouldn't be a good idea. To check the number of unique entries in an index, use the Access DistinctCount property. Compare this value to the number of records in the table and you can quickly see if the index is doing you any good.

This tip comes to you courtesy of Adam Cogan at SSW who can be found at http://www.ssw.com.au.

SSW runs the popular Access Users Group in Sydney.


You can find a few other related indexing tips at
http://www.vb123.com/Toolbox/99/indexfields.htm

MAKING EXCEL A REPORTING AND GRAPHING TOOL

I would like to hear from anyone who would be interested in purchasing VB code to automate Excel from Access or VB.

Send email to Garry --->  access@gr-fx.com


Running Excel 2000 and Excel 97 together.  Some Notes

.. Excel 2000 becomes the default spreadsheet no matter what.
.. Use the "send to" menu to open a file to Excel 97
.. Having no troubles at the moment running both versions

CONTROLLING MICROSOFT GRAPH

Yep.  Garry got another article into the Microsoft hall of fame (otherwise known as MSDN).  This one gives away all the tricks on controlling graphs in Microsoft Access.

http://msdn.microsoft.com/library/periodic/period99/html/06sa/SA/SA99F1.HTM

Naturally this article wouldn't be in existence without the usual support of Peter Vogel and the Smart Access magazine

http://www.pinpub.com

PLACES TO SUBMIT YOUR SHAREWARE

If you have written shareware and wish to share it with the world, here are a collection of sites that you can place your shareware on.  We have systems in place to send Graf-FX to over 75 shareware sites and can use these to publish your software to the web if you are interested

http://www.filefarm.com/info/
http://www.jumbo.com/pages/upload/submit.htp
http://new.freeware32.com/submit.php3
http://cws.internet.com/submit.html
http://www.topfile.com/cgi-bin/db/add.cgi
http://www.davecentral.com/submit.html
http://www.softwarevault.com/addprog.asp

-- Advertisement -----------------------------------------

***** Graf-FX *****

Try out our popular Access shareware tool on your databases

Download a trial version from
http://www.vb123.com/explore

GOOD READING AND USEFUL SITES

Interested in discussions on the XML interchange standard
  http://www.informationweek.com/747/xml.htm


Excel 2000 standout new feature (in Garry's view) is OLAP. This article explains what OLAP is and how to spell it.
  http://www.informationweek.com/700/00olmsf.htm

Can you ever find what you want at the Microsoft Site. This article gives you some of the low downs.
  http://www.winmag.com/library/1999/0801/ana0010.htm

Great site for links through to other Access, VB resources

http://baobabcomputing.com/databasecentral/Database_Technologies_and_Languages
/index.shtml

A run through some of features of Office 2000 (mainly Word)
  http://www.currents.net/magazine/national/1716/covr1716.html

Lots of commercial and semi commercial Access products
  http://www.mvps.org/access/resources/products.htm

Your looking for Access tips.  Here are 10 more ...
  http://malektips.envprogramming.com/microsoft_accesstoc.html

 

Welcome To Tips-FX - Edition 3
See all newsletters

Tips-FX is a email newsletter that provides free tips, help and information for skilled Microsoft Access users and related software disciplines.

In this edition

Download a useful TimeClock control
Learn how to round significant figures in queries
A find and replace add-in for Access
A review of a find and replace tool for Windows files
Good reading and useful sites
Useful Key Strokes
How to setup and test ODBC
Make your web site stand out in the favorites list

The Time Clock


Its give away time. If you need a totally general form that you can use for entering times in your Access forms head to

../99docs/timeclock.htm

This form works like the Date Control that comes with Access only this one is a totally unsecured form that won't cause you too much heartaches when you upgrade.

Rounding Of Numbers

Have you ever added a formula to to a query and found that the answer is something like this

1342.3434553413

You reset the format property in the grid of the query to 2 decimal places and this fixes the display. You then export the file to text or Excel and all the decimals come flooding back. To fix it

First open a new module and add the following code

Function Round2(x)
'
' Rounds a number to 2 decimal places
' Uses arithmetic rounding
'
Round2 = Int(x * 100 + 0.5) / 100
End Function


Close the module and open a query

Enclose the equation inside the Round2 function as follows

round2([Field1]/[field2])

and your output will now be rounded to two decimal places. The following shows it working in a SQL query

SELECT [cost]/[quantity] AS LotsOfDecimals,
round2([cost]/[quantity]) AS TwoDecimals
FROM itemsSold;

You can pick up this routine and plenty of others by downloading the neatcode database from the Microsoft site. Follow the directions from

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

Now open the database and you will find lots of functions that you can copy into your database free of charge.


Review: Search and Replace tool for Windows Files (Ver 2.94)

http://www.funduc.com/
Company: Funduc software

Funduc has several tools on offer, all of them available as shareware (for 60 days) or registered versions.  The registered version of Search & Replace is $25 US dollars.
At first you think it is just the same as the windows find  tool, but it is different.

Not only can you locate the files but if you specify specific text to find within the file it will also give you the location of the text within the document. With the registered
version of the tool you can also replace the specified text, although that is only recommended for text files such as *.ini, *.txt, *.csv and *.HTML etc. (In the unregistered you can only replace five times in a session)

It operates almost like grep for those who grew up with Unix.

It has many options

Case sensitive search
Search sub directories
Whole word search
Fully user customizable tool bar
Ability to search inside of zip files
Ability to set/change file time, date and attributes of
located files
Date filter
Scriptable

I enjoyed using it and found that the user can modify the tool bar also. The many options make this tool very powerful. I would recommend it to anyone working with text files, or
needing to make bulk changes to file attributes in more than one directory at once.

4 out of 5 stars

Review by Scott McManus
scott@skandus.com

Good Reading and Useful Sites


Find out about the Web Components Of Office 2000
 

Useful Key Strokes

If you are programming in visual basic (in Office or VB), here are three invaluable key strokes that you can call upon.

Definition

If you want to see the code in a subroutine / object class that you are using, right click on the subroutine and choose "Definition". This will take you directly to the code of that subroutine.

Last Position

Now that you have used "Definition" and want to return to where you were before, right click again and choose "Last Position"

Control SpaceBar

Say you have a variable with a name dimensioned as follows

Dim ThisIsAVeryLongVariableName as Integer

When you are typing the variable name, you might type the first
6 characters of the variable name

... ThisIs

Now hit Control Spacebar and VB will fill in the rest of the
variable name for you. I first picked this tip up from the
free tips section of Smart Access

http://www.pinpub.com

Add An Icon File To Your Web Site

If you want to make your web site standout in other peoples favorites list, here is a really useful thing to do.

In the top directory of your web site, add an icon file called favicon.ico like we have

http://www.gr-fx.com/favicon.ico

This will now make your icon appear in the IE 5 browser when people visit your site. It will also save the icon if you are luck enough to have people add your site to their
favorites.

How popular is IE5. We now have 600 people a day visit our
site and they use

IE4 45%
IE5 25%
Netscape 4 19%
IE3 8%
Others 3%

By the way, an icon file is a 40*40 bitmap with a filetype
of ico. Search for *.ico using windows find file.

 

Welcome To Tips-FX - Edition 2

See all newsletters

Tips-FX is a email newsletter that focus on providing tips, help and information for skilled Microsoft Access users and related software disciplines.

Features

Office/Access 2000 installations issues
Simple Year 2K checklist
Making the data in graphs your own
Data Mining - Reading Material
Links, Articles online etc.


Verify Changes To Records

The following vb will allow you to confirm changes to records on your forms

Private Sub Form_BeforeUpdate(Cancel As Integer)
' Confirm with user that this record is to be modified

Dim updRecord As Byte

updRecord = MsgBox("Confirm Amounts record change", _
vbOKCancel, "Record Modification")

If updRecord = vbCancel Then

Cancel = True

End If

End Sub

http://www.gr-fx.com/Toolbox/99_accvb/confirmrecordchanges.htm



Access Graphs - Change The Underlying Data Of The Charts

When you run the access chart wizard to create a graph in a form, you will find that the base data that makes up the form is not the data that you originally specified for the chart. Instead it is some trial data with totals for fields such as East, West, North.

If you want to add your own data to the design view of the graph, try the following steps.

First make a demonstration graph in a form using
Forms .. New .. Chart Wizard

Open the query / table that you wish to see in the graph.

Click in the top left hand corner of the data grid to select all the rows. Copy the rows to clipboard (Control C)

Now open the graph in in edit mode and select the datasheet behind the graph.

Click in the top left hand corner of the datasheet grid to select all the data in the chart

Paste the rows you selected before into the graph (Control V)

Now your graph should have your own data embedded in it.

Note: This is the only way to get the stock market graphs to work.

 

Click on this button to read the first edition of Tips-FX

Published  1999-09