Results 1 to 12 of 12

Thread: OpenForm

  1. #1
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76

    Unanswered: OpenForm

    I am trying to open a form with a command button but I can not get the WHERE part of my code to work:

    Dim strDocName As String
    Dim strLinkCriteria As String

    strDocName = "frmCompany"
    strLinkCriteria = SELECT tblCompany.CompanyName, tblCompany.CompanyCustomer FROM tblCompany WHERE (((tblCompany.CompanyCustomer) = Yes))ORDER BY tblCompany.CompanyName

    DoCmd.OpenForm strDocName, , , strLinkCriteria

    Can anyone point out why I get error 3075 syntax error when I click the command button?

    Thanks
    Simon

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    WHERE (((tblCompany.CompanyCustomer) = True))
    or
    Code:
    WHERE (((tblCompany.CompanyCustomer) <> 0))
    Yes is not defined nor is it a recognized value.
    Have a nice day!

  3. #3
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Tried both of your ideas and they both give me the same error meesage

  4. #4
    Join Date
    Feb 2010
    Posts
    15
    Probably not the answer but why not design and save the query then design a form based on the query, you can add a button to open the form instead...

    If that makes sense?

  5. #5
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    I will have a lot of forms to open and I want to do it in the most efficient way and not have lots of querys. Basically, I just want to know how to do it in code.

    Thanks
    Simon

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The wherecondition is a WHERE clause without the word "where", not the entire SQL string. Try

    strLinkCriteria = "CompanyCustomer = True"

    Some syntax variations:

    http://www.baldyweb.com/wherecondition.htm
    Last edited by pbaldy; 02-25-10 at 18:45. Reason: Add link for syntax
    Paul

  7. #7
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Thats works but how do I sort into alphabetical order?

    ORDER BY tblCompany.CompanyName

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Either base the form on a query that sorts the way you want or set the Order By property of the form. I'd go with the former if you always want it sorted the same way.
    Paul

  9. #9
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    I have the orderby property on the form and want to sort by CompanyName. I type this into the property field but it does not sort it. What am I doing wrong here?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    To be honest I always do it with the underlying query if it's always going to be the same. This should also work:

    Code:
    Private Sub Form_Load()
      Me.OrderBy = "CompanyName"
      Me.OrderByOn = True
    End Sub
    Paul

  11. #11
    Join Date
    Feb 2010
    Location
    Yorkshire UK
    Posts
    76
    Thanks for your patience

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, hope it all works for you.
    Paul

Posting Permissions

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