|
vb123.com |
|
|
Home Contact Us Order our Software RSS &
Newsletter
Get Good Help
The
Workbench
Find out who has your database open, start the
correct version of Access, easy compacting and backups, change startup
options, mde compile, shutdown database
Read and
Download Garry's Blog
About The Editor
Garry Robinson
writes for a number of popular computer magazines, is now a book author
and has worked on 100+ Access databases. He is based in Sydney,
Australia or try our new Expression Web |
Is Your Database Corrupt ?
Welcome To
Access Unlimited - Edition
11See 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
REPORTS AND FORMS - GET CONTROL OF THOSE CONTROLS
COLOUR THOSE DULL REPORTS by Terry Bell
WEB STYLE OVER CONTENT by Alex Cruickshank
CONVERTING EXCEL DATA - ITS THE WRONG WAY ROUND
GET CONTROL OF EXCEL USING VISUAL BASIC
ACCESS 2000 TIP - USEFUL PROPERTIES
UNIQUE NUMBER TIP
ACCESS 2000 - SQL SERVER BUG
-------------------------------------------------
COLOUR THOSE DULL REPORTS by Terry Bell
When you have a report that has blocks of similar, single line
details, it's hard on your eye picking out a single line. So,
you can put a ruler under the line you're interested in - or you
can alternate the backcolor of successive detail lines between
white and pale grey.
Here's a quick way to do it:
(1) Put the following function into a standard module
Function ShadowAlternateLines()
CodeContextObject.Section(0).BackColor = _
Abs(CodeContextObject.Section(0).BackColor - 31646433)
End Function
(2) Put "=ShadowAlternateLine()" in the OnFormat event of the Detail
section of your report.
If you already have some code being executed in the OnFormat event,
just add "ShadowAlternateLines" to the code.
(3) Make sure all the controls on your detail line have the BackStyle
set to transparent, and the initial BackColor of the detail line
should be white (the default).
That's all. From then on, you can apply alternate shadow lines to
any report by following steps 2 and 3.
Incidentally, the code has an interesting way of toggling between
two values. The color White has a value 16777215. The particular
shade of Grey I use has a value of 14869218. Add the two together
gives 31646433, and the Abs function does the rest.
If you want to pick a different color, add that color's value to
the white value and use the result in the Abs expression. Or
alternate between two different colors.
Of course, you can achieve the same result with an If statement,
and it would be a lot easier to understand what the hell is going
on. But you've got to have fun sometimes ...
Terry Bell, Melbourne, Australia
-----------------------------------------------
CONVERTING EXCEL DATA - ITS THE WRONG WAY ROUND
Have you ever tried to convert data from Excel to a database
such as Access and realised that the first column of data actually
holds the fieldnames that you want to import and the data goes from
left to right rather than down the page. Well the trick that you
need to use in the spreadsheet is the transpose option.
Select the cells that you want to switch.
Click the Copy button (Ctrl C).
Select the upper-left cell of the paste area.
The paste area must be outside the copy area.
On the Edit menu, click Paste Special.
Select the Transpose check box.
This will rotate the data and make it suitable for importing.
You may then want to give the data a range name to make it
easier to import.
-------------------------------------------
ACCESS 2000 TIP - USEFUL PROPERTIES
When you have a form open and wish to see the source code
under a button, click on the View menu and choose properties.
Now switch to the Events Tab control and select the On Click
event. Now you can click into the code without closing the
form. Same applies for all properties on a form.
If anyone knows how to turn off the properties sheet permanently for
end users, let us all know !!!!
-------------------------------------------
UNIQUE NUMBER TIP
Want to add the next unique number to a field but do not want to
or cannot use autonumbers, try this little bit of code for a field
called ClientNo. The code follows the command button code for
Add Record wizard
DoCmd.GoToRecord , , acNewRec
Me!Clientno = DMax("ClientNo", "Client") + 1
Not the most efficient bit of code ever, but it works.
-------------------------------------------
ACCESS 2000 - SQL SERVER BUG
An interesting insite into the wonderful world of finding and
posting of Microsoft bugs and then tracking down the issues.
15seconds.com posted this warning in their newsletter
"Bug Warning: Don't use Microsoft Access 2000 against your SQL
Server. There is a Microsoft confirmed bug in Microsoft Access
2000 (no patch available) that over rides inserts to tables with
the previous row. Causing you to lose the data you are
entering for data that exists in the next row up. This only
happens with big tables, or slow connections."
Read updates for this bug in a new posting at 15seconds.com
ftp://ftp.15seconds.com/AccessBugKB.txt
Alternatively try the Access Newsgroup for all the latest comments.
news://msnews.microsoft.com/microsoft.public.access.odbcclientsvr
Peter Vogel Editor of www.pinpub.com/access (Smart Access) says
"The bug, by the way, isn't quite what it seems. The record only
appears to be duplicated. If you requery the recordset you see
your new record and only one copy of the previous record. You can
get around this by issuing an SQL Insert query (which is what's
being done in the background, anyway)."
Here's a reply from Lisa Gurry, Office product manager at Microsoft
that was sent to Ken Spencer at www.32x.com
"When a new record is added to a linked SQL table and a cursor is
moved to a different record, the new record that has been added will
appear to disappear and be replaced by a duplicate of the previous
record. This issue will occur only if the SQL Server table has an
identity column and contains more than approximately 400 records.
When a record is added, a duplicate of the previous record
appears. The new record is not displayed until you refresh the
recordset. This behavior is related to the way Access requeries
the SQL Server database. This issue can affect Access 2000 users
on any operating system using SQL Server 6.5 and SQL Server 7.0
in this particular scenario.
"Customers should know their data is not lost and they should
not try to delete the apparent duplicate record because this will
actually delete the original record. Instead, they should refresh
the recordset to see the new record.
"Customers can avoid this issue in three ways: First, use an
Access project and open the SQL table directly. Second, if you
see this behavior in a form, use Visual Basic for Applications
(VBA) code in the BeforeInsert and AfterInsert events to requery
the data and move the form to the newly added record. Third, if
you see this behavior in a table, resort the table or close and
reopen the table."
Lisa said that Microsoft will soon post a Knowledge Base
article on its Web site about this problem.
"Glad we sorted all that out" - Ed
------------------------------------------
GOOD READING AND USEFUL SITES
Lots of good resources for programming in visual basic.
http://www.vbinformation.com/tutor.htm
Lots of Access links
http://www.somuch.com/listem.asp?TopicID=1&CategoryID=2
------------------------------------------
LAST EDITION
http://www.vb123.com/toolshed/news/issue10.htm
PREVIOUS GOOD READING LINKS
http://www.vb123.com/toolshed/news/read99.htm
-- OUR SOFTWARE AND RESOURCES -----------------------
Explore your data visually using our popular Access
data mining shareware
---> http://www.vb123.com/graf/
View our web site on your computer rather than the slow
old web and have access to all the software discussed in
the articles and information pages at www.vb123.com
---> http://www.vb123.com/toolshed/
So thanks for reading our popular newsletter.
Garry Robinson - Software Consultant
Click on this button
to read the previous edition of Access Unlimited Published 2000-01