Macros

malcolmdixon · October 15th, 2010, 06:29 PM

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:
RunDataMacro

Macro Name tblCustomSortOrder.SwapSortOrder

Parameters

prmSelectedItem = CLng([lstCategories])

prmNewSortOrder = [lstCategories].[ListIndex]

Requery

Control Name lstCategories

RunMacro

Macro Name mcrEnableMoveButtons

Repeat Count

Repeat Expression

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:
Parameters
Name Description

prmSelectedItem ID of record to move

prmNewSortOrder New sort order position

Look Up A Record In tblCustomSortOrder

Where Condition = [ID]=[prmSelectedItem]

Alias tblCustomSortOrder

SetLocalVar

Name lngOldSortOrder

Expression = [tblCustomSortOrder].[SortOrder]

EditRecord

Alias

SetField

Name [SortOrder]

Value =[prmNewSortOrder]

End EditRecord

Look Up A Record In tblCustomSortOrder

Where Condition = [SortOrder]=[prmNewSortOrder] And [ID]<>[prmSelectedItem]

Alias tblCustomSortOrder

EditRecord

Alias

SetField

Name [SortOrder]

Value =[lngOldSortOrder]

End EditRecord

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).

Malc.

gjgriffith · June 10th, 2011, 04:47 PM

Hi Malcolm,

I saw your posting here a while back and I meant to respond earlier...I'm so sorry! Thank you so much for the kind words about the Access 2010 Programmer's Reference, you are too kind!

Also, I wanted to follow up about your comment in regard to the Custom sort Order Macro. Specifically you said:

Quote:

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.

First off, I wanted to say nice work on the Macro that you've provided. This macro will work well in a pure Access Database Application and it is very concise...again Great Work!

But I did want to say that there is a good reason for the solution that was provided in the Access 2010 Programmer's Reference as well. Specifically, the Custom Sort Order macro should also work well with Access Web Apps on SharePoint (albeit a minor issue). Unfortunately, the CLng() function would not be available if you were running the Macro from SharePoint directly in the application, so that would be the only issue I can see in that case. Otherwise, if you weren't using SharePoint (which is probably the case in 99.999% of all the Access Applications out there), the Macro you've provided will work great and is definitely very concise!

Anyway, I hope that answers your question, but if you have anything else, please let me know! And thanks for posting your questions Malcolm, the posts I have seen from you are always very insightful!

And thanks again for the kind comments about the Access 2010 Programmer's Reference, I am extremely pleased to hear that it has been helpful to you! And if there is anything else we can do to help out, please let me know and we'll do our best to help where we can!

Thanks again,