Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44

    Unanswered: Get info via SQL string in VBA

    I am using Access 2007. I have a form that emails a report at the press of a button. What I would now like to do is get a value from a query. This value would then be inserted into the body of the email. Everything works perfect before I tried inserting the SQL statement. I set up 2 variables stID and stRTons. The query works fine out of VBA. I am thinking I have some syntax incorrect. The code looks like:
    Code:
    Dim stRTons As String
    Dim stID As String
    stID = Me.RCLoad_ID
    stRTons = "SELECT qry_EmptyCarInventoryEmail.TonsRemaining" & _
              "FROM qry_EmptyCarInventoryEmail" & _
              "WHERE qry_EmptyCarInventoryEmail.RCLoad_ID='" & stID & "';"
    Any help would be greatly appreciated.

    Thanks
    "Things are only impossible until they are not." ~Jean Luc Picard

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Well, you've created an SQL string but not done anything with it. You would need to open a recordset (DAO or ADO) on that SQL string and get the value from the recordset. Also, watch out for your spacing between lines. As is, you'll end up with:

    "...TonsRemainingFROM..."

    You might find a DLookup() to be a simpler method to get a single value from a table/query.
    Paul

  3. #3
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Quote Originally Posted by pbaldy View Post
    You might find a DLookup() to be a simpler method to get a single value from a table/query.
    Thanks pbaldy, I will give that a try.
    "Things are only impossible until they are not." ~Jean Luc Picard

  4. #4
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    I changed the SQL to a DLookUp
    Code:
    stRTons = DLookup("[TonsRemaining]", "qry_EmptyCarInventoryEmail", "[RCLoad_ID]=Form![RCLoad_ID]")
    This works perfect.

    Thanks again pbaldy!
    "Things are only impossible until they are not." ~Jean Luc Picard

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help! That syntax looks odd; if it gives you trouble, here's a good reference on the syntax:

    General: DLookup Usage Samples
    Paul

  6. #6
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    I used this example from Microsoft KB:

    Code:
    =DLookUp("[LastName]", "Employees", _
    "[EmployeeID] = Form![EmployeeID]")
    Thanks for all of your help!
    "Things are only impossible until they are not." ~Jean Luc Picard

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    It's usually syntax like this:

    =DLookUp("[LastName]", "Employees", "[EmployeeID] = " & Forms!MyFormName!EmployeeID & "")

    It might have worked by fluke for you but you should put in the actual form name in the where criteria part along with the " & .
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Actually I thought it was bad syntax myself, but according to the MS site:

    "Form" tells Microsoft Access that the field reference, "EmployeeID," comes from the current form.

    I wasn't familiar with that, but it appears to work like "Me!". I would agree with using the full form reference though, or "Me", which is more common and self-documenting.
    Paul

  9. #9
    Join Date
    Aug 2009
    Location
    Franklin, OH
    Posts
    44
    Thanks for the information pkstormy & pbaldy. I will make the changes and remember the proper syntax for the future.

    Thanks again.
    "Things are only impossible until they are not." ~Jean Luc Picard

Posting Permissions

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