Results 1 to 6 of 6
  • Thread Tools
  1. 2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Open Form With a Where Clause (A2K SP1)

    I am creating an app where the user will open several forms in succession. One of the forms contains 2 subforms.

    As I understand it, I cannot link the subforms to a key passed from the form unless I open the form with a keyed record.

    This is a 2 part question:

    1 Is there a way to view a specific record in a subform without linking from the master form or alternatively is there a way to open a linked subform from a non-linked master form?

    2 What is the appropriate syntax for opening a form from another form when the record key consists of 2 fields?

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,320
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Re: Open Form With a Where Clause (A2K SP1)

    >>As I understand it, I cannot link the subforms to a key passed from the form unless I open the form with a keyed record.<<

    I'm not sure I understand this. But let me explain what you can do with subforms. When you place a subform on a form, it can be linked to the mainform via the Child/master linking properties. When you do this, the information on the subform will change whenever you change to a different record on the mainform. If you don't use the master/child linking, then you will manually have to tell the subform to requery. In this circumstance, the subform's recordsource is usually a query that includes a selection criteria based on a value on the mainform (or anywhere else for that matter).

    When opening a form from a form, you use the DoCmd.OpenForm method. If you look in help, you will see the allowed parameters. One of these is the WHERE clause, which is like an SQL WHERE clause except you don't use the word "where". So, build your selection criteria the same way, using AND between the fields if more than one.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. 2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Open Form With a Where Clause (A2K SP1)

    I am still not clear on the syntax for the where clause. All of the examples that I have seen are based on the search key being a field on the form that contains the Open Form command. Assuming that I want to Open a form with a query as the record source containing the field Key - what would the Open Form where clause look like if I wanted the search key to be a local variable rather than a control on the currently open form, e.g. instead of "Forms![Form Name]![Control Name] = [Key]" what should the where clause look like if the local variable in named Local?

  5. 3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    240
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Open Form With a Where Clause (A2K SP1)

    Hi
    Is this the sort of thing you are after.

    docmd.OpenForm "frmName",acNormal,[QueryNameHere optional if blank leave a comma],[SQLClauseHere without 'WHERE' optional],acFormEdit

    Cheers
    Geof

  6. 2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Re: Open Form With a Where Clause (A2K SP1)

    Well actually, I understand the syntax at that level and use this form of command often. My question had to do with th specific syntax for the SQL clause where the search key is not a bound control on a form.

  7. Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Form With a Where Clause (A2K SP1)

    If you're trying to do that from a saved query, you can't reference a form variable because queries don't understand variables. You may be able to reference a module level variable declared public, since that makes it a property of the form. Then you could try Forms!Form.Variable as the syntax.

    If you're trying to reference an unbound control on a form, the syntax is identical to using a bound control.
    Charlotte

Posting Permissions

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