This book is really great and I haven't even got to the VBA stuff yet!
With the aid of the macro samples I completed my first macro, always used VBA before.
But I just wanted to comment on the Custom Sort Order solution (p105-110), when I was reading it in the book it just seemed to be too long winded so I decided to rewrite a more efficient solution. Maybe there were reasons why the solution was done as is, either way I'm thankful for it, as it helped me to understand macros and complete my first one.
The differences are:
No MoveCategory macro.
cmdMoveUp.Click embedded macro:
Macro Name tblCustomSortOrder.SwapSortOrderParameters
prmSelectedItem = CLng([lstCategories])
prmNewSortOrder = [lstCategories].[ListIndex]Requery
Control Name lstCategoriesRunMacro
Macro Name mcrEnableMoveButtons
So the main changes here are the parameters, use Clng to change to correct data type for use in SwapSortOrder macro. As we know this is the Up button send the new sort order which is ListIndex (zero-based), this value is essentially the SortOrder - 1.
The cmdMoveDown.Click embedded macro is the same except that the prmNewSortOrder is assigned ListIndex + 2 (which is the same as SortOrder +1).
The SwapSortOrder macro is:
prmSelectedItem ID of record to move
prmNewSortOrder New sort order positionLook Up A Record In tblCustomSortOrder
Where Condition = [ID]=[prmSelectedItem]
Expression = [tblCustomSortOrder].[SortOrder]
End EditRecordLook Up A Record In tblCustomSortOrder
Where Condition = [SortOrder]=[prmNewSortOrder] And [ID]<>[prmSelectedItem]
So the main changes here are we are looking up the selected item record and storing it's old SortOrder value as we need that for updating the other record. The record is then updated with the new sort order value. We then look up the other record which has the same sort order value as the record we have just moved. We then update its SortOrder value to the old sort order of the record we have just moved. And that's it, one less macro and only 2 lookups and updates.
There is no change to the macro mcrEnableMoveButtons.
Now to get reading the good bits (VBA).