Results 1 to 2 of 2
  1. #1
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682

    Query within a Form Example + others (Recommended Example!!)

    Actual steps to embedding a query into a form.
    1. Create a subform (leave the sourceobject blank).
    2. To then populate that subform with a query:
    - use code like this: Forms!MyFormName!TheSubformName.Sourceobject = "Query.MyQueryName"



    ********************
    Note: You do NOT need to know how to create SQL statements to use this example! There's no SQL statement creating or complex vba coding involved in this example (it's a simple trick of knowing how to populate the sourceobject of a subform with a query name versus a form name and that is all!)

    I guarantee you this will cut out 75% of your development time in creating reports/exporting routines! This routine has allowed me to get a fully functional complete reporting AND exporting type system up and running within a few minutes. You'll no longer need to spend hour upon hour designing complex reports or exporting routines. Just simply import the very simple forms in this example into your application! (there's NO complex coding to this - it's VERY simple ---- again, to show a query within a form you just have to populate the sourceobject of a subform with the query name (ie. ="Query.MyQueryName")
    ********************

    For exporting - The only complex part of this coding is the code (in the module) which utilizes the common dialog popup box (which again, is typical coding to do this found in any MSAccess developers book.) This is the code to graphically let the user select the folder they want to export to.

    I personally just import the forms in this example into every application I create. Then it's a simple matter of designing the query the user requests and that's it. The query automatically shows in the listbox where the user can click on it and view/print/export the results in the form.

    (note that the other coding is stuff I like to put in all my applications aside from the general purpose of this example which is to simply show how to embed a query into a form.) * As you can see, to embed a query into a form does NOT require coding knowledge such as how to create a SQL statement, etc...

    The attached example demonstrates the following:

    1. Grabbing the user loginID automatically (although not required - use as needed).

    -------------------------------------- (the intent of this example)
    2. How to easily embed a query itself within a form (without the need to design a subform for the query or any SQL creation coding). This is a lot easier than you'd suspect and is only 1 line of code! (once you know the very simple trick to doing it in the form design.) Note again - there's NO coding such as that to create any SQL statements to do this! It's a simple matter of form design and populating a subform with the query name (instead of a subform name). Again, there's also no need to design a form/subform for each query! You just design your requested query from the user and that's it! The whole intent of this example is to show you how to easily setup the form to do this.
    --------------------------------------

    3. How to then export that query to a file (with a browse button to select the folder and export as *.dbf, xls, or csv) - ExportForm. Select folder, select whether to add the date to the filename or not (defaults to Yes), select export type, select filename (pre-populated with query name) and then click "Export Data". You can then click the "Open Folder" button to open the folder and view the results. This is a nice little form I designed which is based upon the form showing the query to simply export that data. You can also use this Export form to see any type of coding needed to export a query/table to an excel, dbf, or txt format with the options indicated in this paragraph.

    4. Printing the query itself (the simple print command).

    5. Track the history of query use by the users! (stores this in a table) (use as needed.)

    6. How to Minimize an MSAccess application (use as needed)

    7. Sample Import type form with a Browse button (just complete the import code - example shown.)

    8. Disabling the MSAccess Menu system by having a frmMainFormBackground that maximizes and then a popup/modal Main Menu form (click Admin Close to get behind the scenes.) Simple trick but keeps the users from accessing the upper MSAccess Menu system (but allows it for print preview). Keeps users from getting behind the scenes (use as needed - not required).

    9. Using the Mousehand over buttons to display a hand when hovering (use as needed - not required).

    Note the rowsource query of the listbox on the ReportForm which simply shows "any" queries which are named with the starting wording of "Export" (which you can change if you like by editing the criteria for the rowsource query of the listbox.) Just create a new query and name it "ExportSomething" and that query then automatically displays on the ReportForm.

    I no longer design ANY reports EVER anymore since using this method! It takes me 5 minutes to create a new query, name it ExportSomething and I'm done!! I don't need to do anything else since the ReportForm and frmExcel and rptExcel report does the rest (the user can then view/export/print the query results and I can see a log of how often/when each user has used that query.) If the user decides to export the query, they can then easily do it. I will have a query showing results and another for totals versus putting totals on the report. My users really like the ease of exporting the data quickly.

    This is my favorite example and I use the above methods in all my applications.

    *** Make sure to add this website to your intranet security in IE to be able to open any MSAccess files within a zip download.
    Last edited by pkstormy; 10-30-2010 at 08:09 PM.

  2. #2
    ped's Avatar
    ped is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Aug 2011
    Posts
    51
    Thank you very very much for sharing this....

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

Similar Threads

  1. Query to only show a value in a form from a query
    By cwwaicw311 in forum Queries
    Replies: 28
    Last Post: 03-27-2010, 02:31 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