Thursday, March 31, 2011

Adding A Data Macro To Automatically Fill in a Field

In the following table, I have used a Access 2010 data macro to fill in field. In this case I wanted the PassFail field to equal "Pass" only if all three questions were answered yes as in picture 1

If any of the question fields were No, then it would be a fail as below

I did this with the following data macro

So now how did I learn how to do this, I watched this video from Microsoft

Saturday, March 26, 2011

Video: Photo Renaming and Display using Microsoft Access

Here is a sample video of photo management software that we have created using Access for a number of mining companies. This video shows the software in action and doesn't include any vba code. In this particular sample, the photo is moved to a sub folder and name that corresponds with the primary keys of the records that the photo relates to. The photo is not stored in the database. The video goes for 5 minutes and is narrated by Garry Robinson.

Wednesday, March 23, 2011

The Second Ever Edition of Smart Access - September 1993

In September of 1993, the second Ever edition of Smart Access was written. This newsletter featured articles on customising report properties, windows class libraries, recursion management and documentation of Access databases using the FMS Total Access product we still sell today. Download the 2nd edition of Smart Access here.

  Please tell your friends about this newsletter.

Tuesday, March 22, 2011

Solid State Drives and Office

Woody's Office Watch discuss the speed implications of Solid State Drives and Microsoft Office, as I mentioned in my article on Apple MacBook Pro, I purchased a sony laptop machine with Solid State drives.  Also note that in the Apple article, 2 readers have commented that they have been very happy with the Mac Pro for development. Its seems to me that a hybrid machine with SSD drives and ordinary drives will be very popular in the future.

Sunday, March 20, 2011

Peter on quotes, specific record selection, hot key code and first monday

Peter Vogel looks at the following situations in an article he wrote for Access 95 that looks pretty handy for Access today:

How to handle quotes in code and when using the Dlookup function Read more

How to display the three or four records before the record a person wants and then set focus on the record the user asked for Read more

Create a shortcut key to run a function without putting the code under a button Read more

How to determine the last Friday and the first Monday of a month Read more

The Smart Access Related Topics Page

Smart Access Index Page
Over the last two years that I have been compiling the Smart Access articles into a comprehensive website, here is the primary page where I have been organising the articles.

The topics on this page include
Project Management & Data Modelling
List Boxes and Combo Boxes
Controls, check & text boxes, buttons etc
Forms and Menus
Sub Forms
MS Word & MS Excel
SQL Server etc
Access Data Projects
Protection and Security
Pivots and Charts
Managing Quality, Bugs and Errors
Imports, Exports and XML
User Interface and Documentation
Access Answers
Dates and Time

Real Time from Google

With so many unfortunate disasters happening around the world, you may think of searching for news that is current rather than the news that the TV stations want to give you. If you are into twitter and maybe facebook, you may stumble across the news in your inbox. Better though is go to the RealTime feed at Google and type in a search term.

Google Real Time in Action - "you don't need a Twitter account"

P.S My own twitter posts turn up in less than a minute into this service and they are not mass media topics

Friday, March 18, 2011

Microsoft - Millions of Reasons Why They Are Still The Force

Here are some interesting stats.  Kinect for the Xbox is the fastest growing electronic gadget to the 10 million mark.  Windows 7 has many more sales than Ipads/macs to the tune of 14 to one last time I looked. 2 million downloads of IE 9 in the first day.  Watch for this note on Windows 64 Bit IE9.  IE9 definitely loaded one of my complex web pages a lot faster.   BTW: The separate search box in IE9 is actually the windows address box. If you press Ctrl E keys, the search box opens instantly and you can start typing the search terms.  Also press the F11 key and IE9 looks sensational. Good ol' Microsoft, right clicks and hot keys, you dont get that in Ipad world.

Caveat: IE9 doesnt work so well on some websites (logmein is my bugbear) so I am downloading a different browser for those. I have used multiple browsers with multiple home pages for years anyway so no big deal

Here is a picture of IE9 with the old menu
Don't forget the hidden achievement of Microsoft in the last 2-3 years, switching from 32 Bit computing to 64 bit computing so that we can start to use more than 3.2 gigabytes of memory on our laptops and desktops. Something that probably was as hard in engineering terms as building and launching a satellite that takes photos of the earth.

Saturday, March 12, 2011

The Third Ever Edition Of Smart Access - October 1993

As I mentioned in dispatches, I would release scanned copies of the very early Smart Access magazines to those people who signed up for the new version of the newsletter.

In October, 1993, Paul Litwin was the editor and Ken Getz was already writing lots of articles.

Garry Robinson ~ Access MVP for 5 years

Friday, March 11, 2011

Invisible Forms Part 2: Park Your Global Values Here

Tobi has discovered that adding an invisible form to your Access application can make a number of difficult tasks much easier.   Read more here

PDF: Citrix - tracking users and hidden forms

Migrating a project into the Citrix platform provided a workable environment for Tobi Hoffman’s company’s people nationwide to access a single database, yet presented some unexpected challenges. Since Citrix forces all users into a single application, keeping track of users’ individual security clearances was almost impossible—until the application got a form that no one saw.  Read the PDF here

vb123 Knowledge Base - How It Has Grown

A couple of years ago I purchased the rights to publish and sell what was one of the best Microsoft Access knowledge bases, Smart Access. The other good resources were Access Advisor which morphed into a VB/ASP/Access magazine and Inside Access, a lesser know but reasonable magazine.

So when I purchased that and put 100 or so articles online, I was left with a decision, switch to writing Access 2007/2010 articles or continue to find and make articles available from the collection. As it is time consuming to write more than 2 good new articles a month, I decided it was best to keep putting the Smart Access online as I could do this at a much faster pace.  There are now hundreds of articles online at and a large number of them I haven't mentioned in my newsletter yet.

These articles appear in two formats now.  html pages that are linked and properly indexed and available in the online search pane and PDF files that are only linked and really can only be found in Google or Bing.  In the last 6 months I put 150 PDF's online as these were quicker to publish than the HTML format. In this newsletter, you will see articles from Tobi Hoffman about Citrix and Invisible Forms, one in PDF and one in HTML format. That should demonstrate the formats.
The catch: The only way to get the Smart Access articles in their entirety is to purchase them.  Many of the readers have already done this, thanks, that simply encourages me to do more work online. The other catch is that the download databases that accompany most of the articles must be purchased.

Tuesday, March 08, 2011

Saturday, March 05, 2011

Getting an Apple - Is it a good idea?

A reader asks

May I ask a few minutes of your time to offer an opinion re Apple computers?
I’m a long term Access developer, should really be retired; had PCs since the early 80’s but fed up with their vulnerability to outside threats and the deterioration in their performance once they have been in use for a year. I need a laptop and I usually buy the best going with the largest screen on offer.
The primary use I put my computer to is the development of Access applications; still with 2007.

Do you think an Apple Mac would provide a better long term platform for my work?

Every year or two I generally waste a lot of time in January and February (not so busy then) puzzling about whether I should upgrade my laptop or desktop. This year it was my laptop. After some thought I realised that I probably wasted a bit of time in the last 4 years with laptops that were cheaper and the hardware didn't last all that long.  So this year I vowed to spend a little more money.

My goal every time I buy a new machine is more and more about ergonomics. This time round I did spend quite a bit of time in the Apple store because an Apple Mac Book Pro wins big on that score. In case you didn't know, Macs come with bootcamp and also an internal virtual system that can Windows 7 and XP if you want.  So I started to dream of software like garage band for my sons and other neat programs that run on a mac that I have never used.  In the end I didnt go for a Mac Book Pro for the following reasons.

I liked the 13 inch MacB Pro but it was as slow as my previous computer in terms of hardware. The 15inch MacB Pro felt really neat but it too was under specified. One thing that really irritated me was that the place where your wrists sat was quite hot. This also seemed a problem with a number of Windows machines. Disk space at 300-500gbytes also seemed a little light on as there would have to be two operating systems on the machine. I did find out along the way that some specialist firms would add extra memory and disk but by then the price was going up and up.   One thing that was apparent was that Mac's had good batteries.  Unfortunately a lot of the speed of a mac is due to the fact that the hardware is optimised for the Mac software as there is only one version of hardware to cope with.  Windows has to cope with all sorts of configurations and hence hardware/software cannot be thus optimised. But if you are running Windows on a Mac, a lot of this advantage disapears.  I didn't look at a 17 inch MBPro as I don't like walking around with a piano on my back.

In the end I went for a Sony total ultra light beast of a laptop with really fast solid state memory disk drives. This had the compromise of not much disk space but as a Windows laptop allows for more peripherals than a mac, I will be find other places to store all the files that I don't use very much. 

In the end I kept thinking of my business, have I every made any money from someone using a Mac and the answer is no. Maybe if they bring out Access for Mac, then that may change. That said, I see programs like LogmeIn running on an IPAD as being a very good way to run Access and Office software on a remote machine.  This is an interesting area, contact me if you agree

Other favourable mentions were HP Envy and Dell machines, and watch for Samsung, their next machine could really rock as they own the sold state drive market.

Garry Robinson - Access programmer from way back then till now.