Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    Kentucky, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL criterion in code (Access 2000)

    Quite often in code I will use DoCmd.RunSQL or create a SQL string to use elsewhere. Anyway, quite frequently these use a criterion obtained from a form's field. Example: "Select * from tblCustomers where strCustomerID = '" & Forms!frmCustomer!txtCustomerID & "'"

    This works well, but I have to reconstruct it if I change records. I want something that I can write that will know to look to the field again without me having to reconstruct the string again, something like:
    "Select * from tblCustomers where strCustomerID = Forms!frmCustomer!txtCustomerID"

    Any suggestions??

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    Could you post a little more info please, its not quite clear to me what your getting at.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    I would imagine that there are two ways you could do this.

    the first is almost as you have typed it:

    "Select * from tblCustomers where strCustomerID = [Forms]![frmCustomer]![txtCustomerID]"

    The other possibility would be to set the after-update event of the txtCustomerID control to chang e the SQL, then requery the underlying control that uses this sql.

    Although in your example...using .runsql on a select query doesn't make much sense to me. Would this normally be an update or append query?

    Jayden

  4. #4
    New Lounger
    Join Date
    Jun 2002
    Location
    Kentucky, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    More info requested . . .

    If you have an SQL statement that you want to use when you are looping through records and you want it to pull one of its values from a field, you'll need to reconstruct the SQL string each time you change records in case the field has changed. But if you make it something "dynamic" then you can set it before you enter the loop and let it roll.

    Now I do it like this and have to change it on each pass of the loop.
    Ex: "SELECT* FROM tblCustomers WHERE strCustomerID = '" & Forms!frmName!txtID & "'"

    I'm thinking of something that would work like this looks like it could.
    Ex: "SELECT * FROM tblCustomers WHERE strCustomerID = Forms!frmName!txtID
    I want to set it before I enter the loop and just let it reset the strCustomerID equation.

    Something more "dynamic" is what I'm looking for, so I won't have to reconstruct the SQL string on each record.

    Sorry if this isn't clear enough. Like I said before I can accomplish what needs to be done, I just wanted to clean it up a bit.

  5. #5
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    Now you have got me confused ? What I suggested before perhaps isn't the answer. I now don't understand what you are trying to do?

    Sorry

  6. #6
    New Lounger
    Join Date
    Jun 2002
    Location
    Kentucky, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    You're right about the RunSQL. I do use it for append and update queries, I just wasn't thinking when I made the post. Sorry for any confusion.

    I tried what you said. I'm speechless right now. I've been doing what I showed you for over two years now. I know I had tried it the way you mentioned!! I think this is a case of making more than one change at a time, and crediting the wrong thing for the fix.

    I wonder how long it will take me to get into the habit of doing it this new way (that makes sense)!! Thanks again.

  7. #7
    New Lounger
    Join Date
    Jun 2002
    Location
    Kentucky, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL criterion in code (Access 2000)

    We must have been typing at the same time. My second post is to Rupert, my third post is to your second post.

    This works "SELECT strCustomerID FROM tblCustomer WHERE lngCustID = Forms!frmCustomer!txtID"!

Posting Permissions

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