That was pretty easy. We changed the name servers, ran a couple of SQL INSERT statements and all the information seems to be here and properly linked up. I’ve got a couple of issues remaining, like the VBA syntax highliter, but they are minor. All-in-all, I’m extremely happy to be at longhead.com and this move really couldn’t have gone any better. So far, that is. There may be some links here and there that point to the old domain. I wasn’t really as careful as I should have been when linking images and other stuff.

iGSyntax Hiliter has a new version which I installed to try it out. I have a problem with it. I’ve made so many modifications to the code of the old version that it would take me too long to fix up the new code to be the same way. I could just accept the defaults, but I find the color choices and the use of scroll bars on the code boxes irritating, at best. So I’m trying to install the old version until I have time to mess with the new code and get it the way I like it. Until I get the old version installed on the new server, VBA code will be in plain text. Not pretty, but it will probably be easier to copy.

You know, it seems like just yesterday that I moved this blog to a new server. It wasn’t even a freakin’ year ago and now I’m moving again. My host is going out of business, or something, and I’m forced to find a new home.

The new home will be hosted by longhead.com. They host another site of mine and of some other people I know. I’m not particularly excited about moving, but Jake at longhead as made it pretty easy for me so far. I am excited about being on host that I count on.

You will experience problems with this site over the next couple of days, but I’ll be transferring all the posts and comments over, so as long as you get here, feel free to use it as you normally would. The plan is to be back in action on Monday like nothing ever happened.

When you add a parameter to an external data query, you can point that parameter to a range. Then when the range’s value changes, the query updates based on that parameter. See Parameters for more information.

Someone on the newsgroups asked what they should enter in the cell to show all the records. I tried everything. Emptying the cell showed all the records will no entry in that field. Asterisks (wildcard) showed nothing, but I’m sure it would have shown all the records with an asterisks in that field. The best I could do, eventually, was to use two parameters on the same field.

To demonstrate, this is a query from the Customers table of Northwind.mdb. The query shows the name and city of each customer and the parameter will be on the city field. When you enter a city in the range (to which the parameter points), the query will show all customers from that city. Here’s the query in MSQuery:

I’ve entered two criteria for ‘City’ and each has a parameter; one for greater than or equal to, and one for less than or equal to. The theory is that if both parameters are the same, it will be the same as having one parameterized criterion. In MSQuery, I entered London for each parameter when I refreshed.

I put the query back into Excel and clicked on the Parameter toolbar button on the External Data toolbar. I point the first parameter to cell D1 and check the box to have the query refresh.

I point the second parameter to E1 and don’t check the refresh box.

I enter a formula in E1 as shown below. If D1 is blank, I put a really “large” string in E1. Large meaning that no city name will be greater than this string. I accomplish that be repeating the largest character known to man, CHAR(255), four times. If D1 has something in it, like Madrid, then E1 has the same value and the effect is the same as one parameter.

When I blank out D1, however, E1 gets this really large string and the query shows all the records whose city is between NULL and ÿÿÿÿ, i.e. all the records.

Last week I asked readers to create a simple invoice application using the specifications I listed. See: Your Assignment: Create A Simple Invoice App.

Nine people (including me) responded:

• Kirk Anderson
• Per Arnader
• Fadi Chalohi
• Ken Clifton
• Donald R. Cossitt
• Doug Glancy
• Aurelio L’Ambrosa
• John Walkenbach
• Charlene Wright

You can download all nine workbooks (zip file). I changed the workbook names so they correspond to the author names. Some of the files contain macros, and they are all virus-free.

As you might suspect, there were lots of different approaches — ranging from very simple to very complex. I think we can all learn a few things by studying these apps.

I invite everyone who responded to leave a comment here with a brief description of how you approached the problem.

Here’s how I approached it:

I used Data Validation to allow the user to select the products from a list and specify the quantity. I used a macro to jump to the Quantity column after an item is entered. Another macro jumps back to the Item column when a Quantity is entered. This isn’t necessary, but I thought it was a nice touch.

Unit costs are obtained using a VLOOKUP formula. I also included a checkbox (labeled “Allow item & price overrides”), linked to a simple macro. When checked, the user can enter a product that’s not on the list, and also overwrite the VLOOKUP formula with a different price. A button at the top of the form clears the invoice, and also regenerates the formulas.

In order to determine whether sales tax should be added, I used separate cells for the city, state, and zip. I used a Data Validation Input Message to help identify these cells. Using separate cells for the address (at the top) required that I use merged cells down below.

This app is very simple, but it meets all of the original requirements. And I’m positive that it would take less than five minutes for a user to get up to speed.

 Thanks to all who participated. Hopefully, this will generate some good discussion.

If you use Firefox, there’s a search box to the right of the address bar which you can use to quickly search your favorite sites. And now, in addition to your favorite sites, you can search this one.

Go to the Mycroft Download Page for the Computer category and scroll down to the D’s.

Recently I got a new computer at work. I had the occasion to use your comments at Power User Setup and it was great to not have to wrack my brain remembering all that stuff. There is one annoyance that should be added to the list: The blasted clipboard toolbar that appears when you copy more than once. I have absolutely no use for this thing. I wonder who does.

Using the information at How to prevent the Office Clipboard toolbar from appearing when you use multiple Copy commands in any Office 2000 program, I add the AcbControl DWORD item to HKey_CURRENT_USERSoftwareMicrosoftOffice9.0CommonGeneral and set its base to Decimal and its value to 1.

I couldn’t resist trying it with some other regular polygons. Here’s how the pentagon worked out

I think, given more time, that those white blotches would be pentagons. The 50k iterations just didn’t get the job done. I had to up the ante to 5,000,000 iterations – 42 minutes on my machine (Triangle Fractal took 42 seconds). If I could (was willing to) get rid of the randomness, I’m sure it could be done much faster. But then it would lose its charm. Here’s the code

You’ll need to get GetNewPoint and PlacePointWsh from Triangle Fractal. I got the vertices from MathWorld.