Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Advice for a Newbie, trying to learn?

    Hi, all!

    I'm looking for some guidance. I'm a career insurance lady, who's suddenly found herself building two Access databases from scratch...and I'd never heard of Access until this June. I've read "Access 2007 for Dummies" cover-to-cover, have "Access 2007 All-In-One Desk Reference for Dummies", and am currently reading "Access 2007 VBA Programming for Dummies". I've been collecting sample code and resources from online, as well. But I have 0 real training for this.

    My question is what websites, books, or other learning methods/materials would experts recommend?

    My projects are turning out to be VERY VBA heavy, and I've even had to write a little SQL, here lately. I'm at a point where I've been able to take code from online, and tweak it to fit my database, but I'm having some difficulties writing my own, and I'm going to HAVE to learn, because both of these databases are going to require massive custom programming to achieve their primary purposes. (To give an idea of the depth, for one of them, I have a 30 page document regarding insurance requirements that I need to translate into VBA, so that users can type information from the insurance policy they're looking at into a review Form, and have the system tell them if it is good or not, and that includes scenarios in which multiple answers to other questions will affect a single requirement elsewhere. Then I have another, slightly shorter guide I have to do the same thing with. And these change every couple of years, but some accounts will still be subject to prior rules, so it has to be able to compare several bits of data to the guides, and determine which edition applies, along with which of the two major ones.)

    And that's probably going to be the easy one. I could write you a novel about what my primary project is supposed to do.

    What is the BEST way for a newbie to learn to program at this level?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,866
    Its possible you are maybe doing things a way you dont need to. The beauty of Access is it can do a lot WITHOUT VBA.
    You can build full apps by using forms, queries and macros. Using macros to run the queries, and open reports should do everything you need.

    What do you need in VBA? I'll bet it can be accomplished a simpler way.

  3. #3
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Thanks, ranman256!

    What I have coded so far are some fairly simple, but neccesary (or just nifty) items, like logins, pop-up calendars for date fields, having being able to open the account setup form with client data already in it, and things like that. Some other functions I'm looking at are:

    • Audit trails - my boss REALLY wants to have a history of all changes/deletions/adds, made by whom, and when. (found code for this)
    • "Mousetrap" - to prevent users from scrolling through records. (found code)
    • Searches - I want a user to be able to type in a client or account number, and pull up that record in the Form. (finding a lot of different ways to do this, but not one that really fits my needs just yet)
    • Highlight empty fields - some of the fields on the Forms don't need to be set to required, but we want a reminder in place that data is missing, so I'm trying to write a code that highlights empty fields on the Form any time the user clicks "Save".


    There's a lot of different functions I need to build into this, but these were a few of the more generic ones that stood out in my mind. The real challenge though is coming grom the main functions of the projects. I'll use a bit from the one that's easier to explain, as an example:

    Database first needs to determine which Client this account belongs to. From there, it determines which of the two major guides applies, based on the branch code. Then it needs to compare the dates of the guides to the effective date of the policy, and whether certain conditions exist on the account to determine which edition of the guide applies. It then needs to take the physical characteristics of the collateral (such as year of construction, flood zone, location, value, etc) and compare that to the relevant guide, to determine what types of insurance are required, and at what limits/deductibles/etc. Users will then input information from the insurance policy into the form, and the form needs to tell them if it's compliant are not. Sometimes, answers to one or more questions regarding what's on the policy will affect requirements further down the form, so I need it to be able to read what's already in the Form, and update accordingly as it goes (figure I can do a lot of this with combo-boxes and enabling/disabling controls based on answer). I also want to be able to provide "Help" items that users can click to give them more information (basically, exerpts from the guides). Then, say, if the answer to "Wind Exclusion" is "Yes", I need it to prompt the user to make sure there's windstorm insurance in place, and fill out the separate wind review Form, because the account has to have a separate wind policy. Once complete, users click a button to run the information into a Report that will be saved on file, and presented to Client and auditors, as needed; I want that Report to leave off items that don't apply for that particular account based on answers provided.

    The requirements are very...tiered, for lack of a better term. If you answer one way, all this applies, and if you answer another, all THAT applies, and even within those, if you answer one way or another, it can branch off again and again. That's the main reason for this particular database; minimizing human error in these reviews, and training needed to get new hires up to speed (or the entire department, every time the rules change).

    I can see some of these things being accomplished with macros, but what I'm afraid of is having a big, tangled web of macros that I have to change piece by piece every time the rules change (or am I thinking of macros wrong?). I'm looking to make the bulk of this a standard module that runs basically the body of the requirements, and I was thinking class modules or macros to pull the data into the relevant fields, if that makes sense? I'm kind of afraid to rely too heavily on macros too, because the books warned that Microsoft was trying to phase them out. Of course, the books are for 2007, and we're in 2014 and still using macros, so I'm guessing they haven't had a lot of success with that.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,866
    It sound like you are over complicating Access. Forms, queries and macros will do 99% of what you need. No programming.
    Now some business rules could get complicated and need code, but macros are the way to go.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,866
    Audit trails - query , no code
    "Mousetrap" -no code, simple property setting
    Searches - query
    Highlight empty fields -now that needs code

    Code:
    usage:
    if IsValidForm() then
       SaveData 
    end if
    
    public Function IsValidForm() as boolean
    dim vMsg
      select case true
           case txtName = "" 
              vMsg = "Client Name is missing"
              txtName.setfocus
    
           case isnull(cboState )
              vMsg = "State is missing"
             cboState.setfocus
      end select
      if vMsg <>"" then msgbox vmsg,vbCritical,"Required"
      IsValidForm =vMsg =""
    end sub

  6. #6
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Thanks! I'll have to take another look at queries. I was afraid I'd missed or missunderstood some things; it's all a bit daunting, starting from 0.

    And Microsoft isn't getting rid of macros anytime soon? That's been my major reason for not using them. The book said Microsoft was phasing them out, so I figured I needed to just skip it and learn VBA, because I didn't want to have to do a lot of conversions when the company upgrades from Office 2007. If they're not going away anytime soon though, I'd rather do macros than VBA. VBA makes me want to punch kittens, sometimes. (Right now, I'm trying to program the company's color palettes into a standard module, so I can use them on forms and reports, and it is being a MASSIVE pain.)

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Using VBA would be considered a Maintance Driven approach and macros would be considered a tools driven approach. The issue with tools driven approaches to software development is longevity where maintenance driven approaches are employed to ensure longevity (low maintenance).

  8. #8
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Yeah, that's leading me back to VBA. The database project from my example is a voluntary project for another department, that I pretty much want to hand over to their supervisor when I'm done, and not have to touch often. It needs to be low maintenance too, because I believe I'm the only one in this office who works with Access and VBA (they could go through corporate, but I'd rather make this as easy to maintain as possible without outisde help).

    What is it that causes the longevity problem with macros? Can they still be used for some simple functions? I'm also looking to add email buttons to the contact info, to auto-fill a new email with that contact's email address, and I was hoping macros would handle that, and maybe some of the other little things. Would macros just be a longevity issue in the major cogs and gears?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,658
    Highlight empty fields - look at Conditional Formatting.

    I don't use macros, with one exception - had to use an AutoKeys macro. Macro structure changed quite a bit between 2003 and 2007 versions and I think more changes in 2010. But then a lot about Access changed. VBA has not.

    I don't have any books dedicated to programming in Access but this one sounds interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc. I expect there is a later edition.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    The difficulty with the highlighting of blank fields is that my boss doesn't want them to constantly be lit up. Basically, she wants a gentle reminder, so what I'm trying to do is attach a code to the "Save" button that will check certain fields, and highlight them if they're empty, but where the user can eliminate the highlighting (at that time) by hitting "Refresh", or navigating away. They don't need to be highlighted all the time, just a reminder when someone saves incomplete data.

    Thanks! I'll check that book out. You're throwing some new terms at me though...XML and ASP? I hadn't seen mention of those anywhere else yet. Whole other programming languages?

  11. #11
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,592
    I also do not use macros.... for anything. With very rare exceptions, anything you do in a macro can be done better/faster in VBA. (IMO)

    Did any of the books you read talk about how to start designing a dB?

    Start by writing a 2- 3 sentence "mission statement".
    Then use pencil and paper/ whiteboard/etc to design the tables, set relationships, sketch forms and reports.

    Use only letters, numbers or the underscore in object names.
    Do not use multi-value-fields (MVF)
    Do not use look up fields (different from look up tables).
    See http://access.mvps.org/access/tencommandments.htm
    and http://access.mvps.org/access/lookupfields.htm

    And speaking of primary keys:
    (I use an Autonumber PK field in every table.)

    Autonumbers--What they are NOT and What They Are
    http://www.utteraccess.com/wiki/index.php/Autonumbers

    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp

    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm


    Be aware of reserved words.... see http://www.allenbrowne.com/AppIssueBadWord.html



    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,557
    IncidentalProgrmmer,

    It sounds like you are quite organized and have done some research re Access, macros, vba. As the others have said, I also do not use macros. I find I can debug and test vba quite quickly. Seemed to me macros were trying to do something behind the scenes for me and it was always sort of arm's length away. Anyway, as for learning, there are some good free videos from 599CD on youtube.(There are others too.) Learning Access and vba is fine. However, I strongly recommend learning and experimenting with some database concepts and design.

    Here is a link to information I have suggested to others with similar question.

    My preference is to work from a good business description, documented business rules and a data model.
    I have described a technique ("stump the model") to vet/verify the model the data model with the business rules and users and managers in a number of responses.
    Here's one post -- it really helps clarify processes, data , rules...

    another

    Good luck.

    PS:
    Database design is a little bit like learning to sail ---you can read a lot of books and watch tutorials, but -sooner or later you have to getdown to the nitty gritty and get wet.

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    If you plan on accessing your database via a web browser (online) you may benefit from macros. Macros, and other intrinsic tools, do not always work from one generation/version of Access to another. So, if a user were to upgrade their software, you may get unpredictable results when running the app.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,658
    XML and ASP code are of most interest in website design and building data driven web pages.

    However, XML coding can be used to build customized ribbons in Access 2007 and up.

    I think deploying Access database to the web using SharePoint does mandate macros.

    I have never built web database, never used SharePoint.

    Most of the time VBA will work between versions, but operating systems can also impact. I had to change two lines of code in my database when we upgraded to Windows 7 Access 2010 but I got 64-bit OS and everyone else still runs 32-bit.

    Certainly cannot count on a lower version to open and run db created with higher version.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    Thank you all SO MUCH for the tips and links! My favorites bar just exploded.

    Sorry, I should have been more specific. These databases will not be online. The only people who will need into them are located here, and those of us who need it have remote access, so we're keeping it on our local server (that terminology is probably wrong, but I don't know servers). Currently, my plan is to try to complete certain core parts of both, release them, then work on the rest behind the scenes, and patch in new functions as I complete them. For the one database, there's one client using the product, and only one guide that we need at the moment, so I figured that's what I'll release first, and then build the rest and patch it in. For my main project, release the Client, Account, and Contacts portions (basically, a glorified roledex) so we can start entering data (because a lot of it will be manual), and then patch in the the rest. For the first one, the data that we need on the client and accounts is in another management system, so I can just import it, but the data we need for my main project is mostly in files on the server, so that's going to be all kinds of fun.

    We DO use Citrix, which I've concluded will be the bane of my existance. It's already picked a fight with Access that I had to call IT for, and I'm nowhere near launching these things yet. I've read up on the apparent war between the two, a little. Does anyone else have experience with using Access on a Citrix desktop, and does having users enter the database through a cloned front end solve the problems? That's the impression I got.


    Ssanfu: The Dummies Access 2007 book did go into design a little bit, but it was mostly the technical stuff, so I've been kind of basing it off similar systems that I've used. I'm just really starting to learn to love autonumber, but I've mostly been using Client, Account, Policy, etc. numbers as my primary keys. I'm going to read the information you provided on autonumber though, and see if that's a better way to do it.

    Both project folders have a "Purpose & Notes" Word document with a summary of the intended purpose of the database, a section for things my coworkers have asked for and what I need or am waiting on to complete them, and a list of all the Tables and Forms and the names of every field and control in them (for easy access when writing code). There's also growing piles of paper notes and diagrams on my desk, and I got a large sheet of cardboard where I'm charting the tables, Primary Keys, and relationships for both on either side. My desk is looking more like I work in technology than in insurance, these days.


    Orange: I like that idea. I've already told my coworkers that as we go along, I want them to try to break it, so I can fix it now before we're dependent on it. We have bi-weekly meetings for status and brainstorming, but I haven't really been able to turn it loose for them to try and break yet. Mostly, I break it; most recently, by trying to program the company color palette into it. I'm reading those posts you linked, and will try to implement that into my meetings.

    Your post does bring up one of my bigger challenges though. The project for my department is based on procedures I'm still learning, being new here. That's why I took on the second project for the other department though, because it's for what I was doing for the last 2 years at my old job. I'm hoping that working on that one will help me increase my expertise with Access and VBA because I already know the subject, and in the meantime, I'm learning the subject for my main project, and can really move forward with it once I have because I'll have learned Access well from the other. It's a bit of a tangled method.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Advice to learn database design
    By cib in forum Database Design
    Replies: 2
    Last Post: 01-27-2014, 09:10 PM
  2. Replies: 27
    Last Post: 10-08-2013, 03:20 PM
  3. New To SQL, Need To Learn About Triggers
    By David92595 in forum SQL Server
    Replies: 4
    Last Post: 03-20-2013, 11:27 AM
  4. Best Approach to learn VBA
    By Richie27 in forum Access
    Replies: 3
    Last Post: 06-16-2012, 01:33 PM
  5. trying to learn visual basic
    By darklite in forum Programming
    Replies: 7
    Last Post: 08-24-2011, 02:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums