Results 1 to 2 of 2
  1. #1
    ser01 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Posts
    64

    Form Record Source - Best practices.

    I learned MS Access on my own. I started from knowing nothing about it, to being quite proficient. However since there are many different ways to do the same thing, many times I am wandering which is better. The database that I create for our company is getting quite big, as more and more data and features are added tot it. At the moment the front end is about 5MB after a compact and repair, and that does not include any data. Also the number of objects is getting quite large. In this context efficiency and best practices is more and more on my mind. The question of this post is about a form's record source. Currently I have quite a few forms that are based on select queries. The SQL for the queries are being dynamically modified with VBA to display the necessary data. This is the method I learned and that's how I am doing it. Recently however, I realized that you can have the whole SQL as the form's record source. So I could use VBA to modify that, and not use a query at all. This would eliminate quite a few select queries from the list of my objects, and perhaps simplify my front end a bit. So what are your guys thoughts on this?

  2. #2
    Gray is offline Novice
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    England
    Posts
    16
    Hi

    I learned the same way as you.. and painful it was!! I use the method you are considering... SQL Selects in the recordsource.. Why?

    Partly because I'd met our friend SQL before... but also to get around some of the native form sorts and filters (when you right_click on a field) which can be a nuisance; especially those where a combobox is populated by a SELECT statement or Dlookup.

    You can dynamically build and re-apply recordsources in the form's VB. There are tutorials out there showing how to build pop-up forms where you can offer custom sorts and filters to the user... and (by switching the form's AllowFilters=false) gain greater control over what's being done. Requirements are then written as SQL clauses and applied to the form.

    Actually, I went that route but my pop-up form got very complex (adding the correct delimiters to dates/strings and maintaining the correct order of existing filters/sorts with new ones)... so I scrapped it. Here's where I can save you a year of your life and 10,000 lines of code...

    I use a kind of hybrid ...leave AllowFilters=True then when a user elects to filter/sort using the right-click method their request can be trapped in the ApplyFilter Event(s)... I then capture it and, where appropriate, edit the recordsource' SQL and re-apply it. AFAIK it can't do complex things like OR... OR..ORs but it'll do a awful lot!

    To do this, you'll need some SQL and VB skills because it needs either in-line editing of the existing SQL or a re-build of it each time a new request is made (i.e. with all the old and new params... I find this easier). But I think that is a plus in a way, you'll get a feel for SQL and that's what the whole world, it seems, runs on .

    Just watch out for the ApplyFilter events in Form/Subforms.. there are some interdependencies.. and sometimes they don't get fired (when deleting the very last filter field in a subform when filteron=false for example). By doing table JOINs in your SQL you can do quite nifty sorts and filters.

    Suggest, recording the sorts/filters people have requested so you can re-build the SQL when the form is next loaded and impress your users by taking them back to where they left off. I keep a User preferences table for this purpose.

    So... mine goes like this...

    Open form - rebuild SQL with previous sorts/filters... later that day, user right clicks and requests a new sort/filter... grab request in ApplyFilter event... store new reqs in User Preferences table... edit SQL with new params... apply new SQL...user closes form and goes home happy knowing next morning he can start where he left off...

    Might sound I bit daunting but.... the big advantage is that if you keep it as generic as possible you can use it on your other forms... think how many standing queries that saves!

    HTHs a bit?

    Rgds

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update form record source & accde file
    By snoopy2003 in forum Programming
    Replies: 10
    Last Post: 05-14-2011, 01:10 PM
  2. Resetting Record Source on Form
    By Cheshire101 in forum Programming
    Replies: 5
    Last Post: 05-05-2011, 08:52 AM
  3. Replies: 2
    Last Post: 11-29-2010, 11:16 AM
  4. Replies: 1
    Last Post: 07-04-2010, 03:31 PM
  5. Changing the record source in a form
    By lmichaud in forum Forms
    Replies: 1
    Last Post: 07-09-2006, 09:20 AM

Posting Permissions

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