Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2009
    Posts
    20

    Question Unanswered: Access 2007 Opening a form based on a query

    Hi dbforums

    First post here and found that you chaps and chapettes seem to know what you're talking about compared to the rest of the places I have been.

    I am using Access 2007 which is a challnege compared to my Access 2003 skills but I want to be able to open a form that is based on a query. I can do simple queries but the one I want is slightly trickier and I am not sure how best to do it.

    I would like the form to show records that DO NOT have a value in the Closed Date field (which is one of the fields in my table). If it does have any date value of any kind then I don't want that record to be viewable by the user browsing through the records.

    Anyone any ideas how I can do that? Im sure someone will tell me its a doddle!

    Thanks for your input in advance

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just pop the following into the where clause of the query your form runs off:
    Code:
    ...
    WHERE [Closed Date] IS NULL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2009
    Posts
    20
    Thanks for the response

    I think I might need something slightly different...

    I have a form that loads with a single button - you click it and it loads the form to enter customer details. What I want to happen is when that form loads for it to NOT show any records that have a Date Closed entry? I want to stop users from making changes to records that are dead...

    Any ideas on that ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's step through it.

    You have your form ready yes?
    And you have a query written for that form yes?
    And you are displaying the rows returned by that query in your form right now yes?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2009
    Posts
    20
    I have a form ready

    I don't have the query yet as I am not sure how to construct the query which the form should load from as thats the bit Im kinda stuck on

    The form is currently looking at a table - what I want the form to do when a user clicks the button to open it is to show only records that have NO Closed Date entries...

    Does that make more sense ????

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT col1, col2, colN
    FROM myTable
    WHERE [Closed Date] IS NULL
    Use that SQL instead of your table. Just put in your column and table names.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2009
    Posts
    20
    Ok sorry for a thicky I am new to all this.

    Where would I put that SQL code??? I am an absolute n00b at this stuff so apologies for making it harder than it probably is...

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can either:
    a) Create a new query, click on SQL view and paste it in there. Then bind your form to that.
    b) Paste the SQL in to the form property you currently have the table name entered in to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Aug 2009
    Posts
    20
    Hi

    Thanks for that - made the query I have changed the table name but for the columns do I need to put ALL the columns in or just the Closed Date coloum name ???

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - how about a bit of research?
    Check out this nice simple SQL tutorial. In particular pay attention to the SELECT clause for your answer.
    SQL Tutorial
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Aug 2009
    Posts
    20
    Top notch - thanks for your help!!!!

Posting Permissions

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