Chapter 14 - Macros

Here's the first step of my simple Access macro. I was trying to open my Inception to Date (ITD) table and then sort by first org, then fund, and finally by account group. These actions are relatively simple to perform in datasheet view by selecting the first column heading, holding down the shift key and then moving the cursor to the second and third columns. While continuing to hold down the shift key you just click the down arrow on one of the selected columns and choose sort smallest to largest.

Here's the second step of the macro. The table opens just fine.

In the third and final step I was not able to figure out which Access macro command selects columns to sort. I was only able to sort by the first column using the macro.

There doesn't appear to be a macro recorder in Access like the macro recorder in Excel. Here's what the macro looks like in Visual Basic using the macro recorder in Excel.

Sub Macro8()
'
' Macro8 Macro
' Macro recorded 11/25/2007 by University of Alaska
'
' Keyboard Shortcut: Ctrl+h
'
Range("A1:H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End Sub

The macro I'd really like to create would take the VistaPlus inception to date report, add two columns on the left, copy down each of the grant numbers in column A, column down each of the fund numbers in column B, sort by org number in column C, delete any row without an org number, then sort by grant, fund, org and account code. The macro would then insert a column to the right of account codes, look up and return the account group, then delete the account codes. The inception to date data would then be ready to be used for the Vista Plus ITD Report in my Chapter 14 database.