Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    Join Date
    Nov 2008
    Posts
    9

    Unanswered: New to macros - a little help?

    Hey there everyone!

    So here's the gist of the situation - I've been using Access for a few years now, comfortable with my SQL, forms, reports, etc but I've only just started on macros. So basically I was just wondering if someone could help me with two questions:

    Firstly, I was wondering about just what exactly you can use macros to do. For example, would it be possible to make a notification appear automatically the moment you open your .mdb file if, say, a deadline in some record of an 'Assignments' table or something is a week away? What kind of kewl automated stuff can macros do for you?

    Also, does anyone know any good resources for learning about using macros?

    Any help on this front would be greatly appreciated ^-^

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The advice of 99% of people on here would be "don't use macros!". Use VBA instead. You can do everything in VBA you can do in Macros, plus 100 times as much more. There's no way we can list stuff - to do so would take years and we'd miss loads of it. Instead, decide what you want to do (what is you business need, what does the specification require of you) and then try to figure out the code. i.e. start with the problem and derive a solution rather than read through a list of millions of solutions trying to identify your problem.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2008
    Posts
    9
    VBA hey? Hmm, my knowledge of VBA is fairly limited. Better work on that. Out of curiosity though, is it possible to do automated notifications and stuff with VBA? That's something in particular that I want to learn to do.

    Thanks heaps for the prompt reply and the advice =D

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by sphynx
    is it possible to do automated notifications and stuff with VBA?
    Piece of piddle. You could do it without VBA or macros too really, depending on your exact requirements.

    Come back with a specific requirement and we'll show you how.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if it can be done in macros.. its can be done in VBA
    many macros decompose into VBA when run.

    the macros are often a group of VBA commands packaged together into a logical unit.

    Macros can be very usefull when you first start out (a bit like the stabilisers on a bike), but as you get more experienced the packaging can become limiting. quite oiften you can save a macro as code.....
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2008
    Posts
    9
    Fair comment. Yeah I have noticed that macros have underlying VBA code. It might be worth picking it up. I've got some books on it, I've been meaning to do so.

    I don't really have a specific problem, I'm just trying to learn the skills that I could use as necessary. So let's say we're a little more specific with the initial example I gave:

    We have an Assignments table which includes the subject, name of assignment, a brief description of the task and a due date. We also have a query which finds all the assignments which are due within a week of the current date. So I was wondering: in such a scenario how could we make the database throw a notification the second we open it warning us that a certain assignment is due within the upcoming week?

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can do almost anything with VBA.

    Agreed with Poots. Macros are actually harder than VBA imo and I avoid them like the plague.

    Example:
    You could set an event procedure for the On Open property of your startup form. That event procedure could, for example, find all records from an Assignments table that have a date that is less than a week from today and if there's more than zero, make a button appear on your startup form that says "Assignments Due Report".

    Trying to do that with macros would probably be possible too, but I have no idea really as I gave up on macros completely as soon as I got a grip on VBA.

    One of the biggest operation problems with macros is that, especially in larger databases, you cannot tell where a particular macro has been used. With VBA you can do a search.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    sphynx - any reason not to just have a listbox on your first form linked to the query that shows all of today's tasks? This is more dashboard like rather than throwing up a message box, and IMHO more refined.

    no VBA too
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2008
    Posts
    9
    Hmm, that sounds fairly straight-forward Startrekker. Thanks for the advice, I'll give that a go.

    Pootle, I don't really follow what you mean by "list box" -- like a drop-down box? I don't really know how to do what you mean but I'll look into it and give that a go tomorrow.

    It's getting late though so I'm off to bed. Thanks for all the help! ^-^

  10. #10
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I use macros a real lot but as others have said what you can do in macro can be done in VBA. The only macro action, apart form Autoexec, that I have not been able to do in VBA is the counterpart of RunMacro and where the running of the macro is stopped when a condition is met. However, I have not tried too hard to do it

    VBA allows you to make functions that are not a standard part of Access. If you want to use Access to open Word and insert data to Bookmarks etc and etc you must use VBA. Ditto for Excel. You can use a macro to run a batch file to open Word or Excel but you can't do anything when they open.

    The reason I use macros (when they can to the job) is as follows:

    1) Less typing and typos when using object names such as forms etc because they are drop down lists.

    2) If you are working with people whereby changes are made it is easy over the phone to show then how to change a macro..."go the 8th action line etc". Code is hard and especially in the later versions of Access where all the code on the form is displayed as opposed to just the code behind the label or button.

    3) Making a macro and sending it to someone is easy. For code you need to send the form and your form might be different to theirs. You could do the same thing with code in a module but where modules are not as good is the module name is not what is used, the function name inside the module is used. With a macro what you see as the macro name is what is referenced.

    The negatives as I see them with macros (especially very large ones) are

    1) They don't offer word processing type features. For example, if you wanted to change a field name reference in code then Find and Replace will do it on all the code in one go. The "writing" in code acts a little bit like as if it were written in Word

    2) If you had made a mistake such as referring to a field name but due to typo it does not exist then when you run the code (click on alabel or button) it will bring up DeBug and highlight the problem area. A macro will just bring up a Halt box and the macro name and action. Can be quite messy do deal with on a very large macro.

    3) I think this has changed with Access 2007......but macros are not part of the form. In other words if you export a form the code comes with it. In the case of macro only the reference to the macro name travels with the export.

    4) This might be the most important for a beginner. As was mentioned 99% of the posters on Access forums are anti macro and all their answers will be posted as code or a snippet of code. If you can't do VBA then you will not understand the answer or where to place the code. A vast number of posts that give help show code for what is the counterpart of simple macro actions such as setting the value of a field, running a query, opening a related form etc. but if don't know what to do with it then all is lost

    Another negative with macros from the perspective of getting forum help is you can't simply copy and paste a macro to your post. You have to try and type out the action lines or attach a small DB with the macro.
    Last edited by Mike375; 11-21-08 at 02:06.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Mike375
    The only macro action, apart form Autoexec, that I have not been able to do in VBA is the counterpart of RunMacro and where the running of the macro is stopped when a condition is met. However, I have not tried too hard to do it
    For AutoExec, have code run on the OnLoad of your start form. I tend to have that call a module that has all my initialisation code. I'm not sure what the other bit does but I bet we can sort it for you

    OP - the macros available in Access map to the Access Application DoCmd object's methods.

    e.g. instead of the OpenForm macro
    Code:
    Application.DoCmd.OpenForm "formName" 'Various conditions
    As such, you can do everything in really basic code that you can do in Macros, plus you can throw in some error handling and better control of flow.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    How do you do Runmacro and the macro stops on a condition.

    The macro being run would be typically a "go to next record" and probably a SetValue or two or perhaps a GoToControl, Copy, Paste etc and would stop on [ID] Is Not Null or similar. i use a nice one to do progressibe total and i have yet to see a query that can equal "going down the records" to equal it for flexibility.

    I mainly use Autoexec to turn CopyObject and I email the little DB and the person opens it and Presto.

    Don't get me wrong I use heaps of code and for such things as Access to Word, creating batch files, installing Letters from OLE embededs Word docs, saving opened Word docs with date/time stamp, copying folders with date/time etc and etc.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Mike375
    How do you do Runmacro and the macro stops on a condition.

    The macro being run would be typically a "go to next record" and probably a SetValue or two or perhaps a GoToControl, Copy, Paste etc and would stop on [ID] Is Not Null or similar. i use a nice one to do progressibe total and i have yet to see a query that can equal "going down the records" to equal it for flexibility.
    For this (cumulative totals, if I understand correctly) you have two options. A query that works it out on the fly (better if the values change regularly however these can perform poorly as they rely on Theta Joins: the particular type of Theta Join means the processed rows are exponentially related to the number of source rows, so small increases in the number of rows in the source table can result in a large increase in processing). The other alternative, and a closer match, is to loop through a recordset and increment a variable, writing it to the column on each pass.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by pootle flump
    The other alternative, and a closer match, is to loop through a recordset and increment a variable, writing it to the column on each pass.
    I guess that is what I do with the RunMacro. If it is a progressive total and someone sorts then it run down again to give the progressive on the different sort.

    But how do you replicate the RunMaco with code. I know that you could put a Call inside the code but how do you do the repeat until the condition is met.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Since I'm not familiar with RunMacro, it is difficult for me to say. If by "until the condition is met", you mean until there are no records left then you just test the EOF (End Of File) property of the recordset.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •