Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Access 2010 VBA Programmer's Reference
This is the forum to discuss the Wrox book Access 2010 Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Jerry Dennison; ISBN: 978-0-470-59166-6
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2010 VBA Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 15th, 2010, 06:29 PM
Authorized User
Points: 346, Level: 6
Points: 346, Level: 6 Points: 346, Level: 6 Points: 346, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 61
Thanks: 0
Thanked 13 Times in 13 Posts
Default Macros

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.

Last edited by malcolmdixon; October 15th, 2010 at 06:46 PM..
Reply With Quote
  #2 (permalink)  
Old June 10th, 2011, 04:47 PM
gjgriffith's Avatar
Wrox Author
Points: 517, Level: 8
Points: 517, Level: 8 Points: 517, Level: 8 Points: 517, Level: 8
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2009
Location: Boulder, CO
Posts: 110
Thanks: 5
Thanked 14 Times in 14 Posts
Default Nice Macro Malcolm!

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,
__________________
Geoffrey L. Griffith
http://www.ImagineThought.com

Wrox Author of:
Microsoft Access 2010 24-Hour Trainer
Access 2010 Programmer's Reference
Access 2007 VBA Programmer's Reference

*** Please click the THANKS button (to the right) if this post helped you! *** ---------------------------------------------------------------------------------------------------------->
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hotkeys in macros HaileyJ Access 2 September 10th, 2007 03:13 AM
Excel macros mustafayildirim@msn.com Excel VBA 1 August 29th, 2007 10:50 PM
Pause between macros paul20091968 Access VBA 2 April 6th, 2007 01:40 AM
Distributing Macros Larry Landis Word VBA 1 March 31st, 2006 02:29 PM
Macros vbprogwb Access VBA 21 November 12th, 2003 05:18 PM



All times are GMT -4. The time now is 02:09 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.