Results 1 to 3 of 3
  1. #1
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46

    Not My Bosses ID... Ever... Exclude certian value in a Query using VBA

    Hello All,



    I have a simple question that I cannot find any guidance on either on the net or in books. I cannot find any VBA anything about "omitting", "excluding", or "isolating a value", anything...

    My Boss, Mr. Jones wants me to use our employee database at work to select names at random using a query in Access. No big deal just make a query and run it, output it to a report, print it and done. But here is the catch he wants to make sure that his name never gets selected. I know what you are thinking just change the query criteria so that he doesn't get selected, ever. But that solution won't work in this instance... I want to use VBA to make sure that his ID never gets selected... This way if someone opens the query in design view they won't see him being excluded.

    I attached a simple databse with a simple query. Any help on this would be great. Thanks.
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If users have access to tables and queries and full design mode, then they can see whatever they want, including the VBA code, and this effort is meaningless.

    Don't use parameterized queries. Options:

    1. Instead of table or query object, use an SQL statement created directly in the form or report RecordSource:
    SELECT * FROM table/query WHERE ID<>1;"

    2. Use code to set filter for form or report. One way to open form/report with filtered recordset is with the WHERE CONDITION of DoCmd.OpenForm (or OpenReport):
    DoCmd.OpenForm "form name", , , "ID<>1"
    Last edited by June7; 07-31-2013 at 12:37 PM.
    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.

  3. #3
    BusDriver3 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jul 2011
    Posts
    46
    June 7,

    Thanks for the speedy and honest response. You are correct on all accounts and I understand that anyone that can open the query can open other items in the database. Luckily for me the team that I work with thinks that Access is the "Missing Link" or the "Devil"! They are not scared to use it but they are scared to explore it. Your hints were great and they worked for my situation.

    Solution that worked for me = I created a Report (I am using Access 2010) > Design View > Properties > Event > On Load > Clicked the Build Button > Used June7's guidance > Saved the Report > Closed Report > Open Report > No Mr. Jones!

    Thanks so MUCH!

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

Similar Threads

  1. Print Label when certian condition is met.
    By talktime in forum Reports
    Replies: 3
    Last Post: 12-15-2012, 03:26 AM
  2. Replies: 1
    Last Post: 09-06-2012, 01:40 PM
  3. Query to exclude non matching records
    By L1882 in forum Queries
    Replies: 1
    Last Post: 04-03-2012, 08:44 AM
  4. exclude field in query
    By chrisy in forum Queries
    Replies: 2
    Last Post: 10-28-2011, 09:53 AM
  5. Replies: 4
    Last Post: 10-25-2011, 10:07 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
  •  
Other Forums: Microsoft Office Forums