Results 1 to 8 of 8

Thread: Sql using vb

  1. #1
    Join Date
    Jan 2008
    Posts
    37

    Unanswered: Sql using vb

    Im sorry i know this question is similar to some others but im still confused. My current form displays a persons department number, im trying to find that departments name by searching a department table using an sql statement. But i get an error saying data method or member not found. What am i doing wrong? My code is the following:

    Dim strSELECT As String
    Dim strFROM As String
    Dim strWHERE As String
    Dim sql As String


    strSELECT = "tblDepartment.[DP_NAME]"
    strFROM = "tblDepartment"
    strWHERE = "tblDepartment.[DP_NO] = '" & Me.TU_DP_NO & "';"
    sql = strSELECT & strFROM & strWHERE

    With WordObj.ActiveDocument.Bookmarks
    ActiveDocument.Bookmarks("bmk" & Enqbmk).Range.Text = DoCmd.RunSQL(sql)
    End With

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    check the spacing and wording
    strSELECT = "tblDepartment.[DP_NAME]"
    strFROM = " from tblDepartment"
    strWHERE = " where tblDepartment.[DP_NO] = '" & Me.TU_DP_NO & "';"
    sql = strSELECT & strFROM & strWHERE

    mind you there doesn't seem to be lot of point creating 3 variables

    strSELECT = "tblDepartment.[DP_NAME]"
    strSELECT = strSELECT & " from tblDepartment"
    strSELECT = strSELECT & " where tblDepartment.[DP_NO] = '" & Me.TU_DP_NO & "';"

  3. #3
    Join Date
    Jan 2008
    Posts
    37
    I changed it to as above but there still seems to be a problem with the DoCmd its highlighting it and saying expected function or variable?

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Even though the SQL returns one column and one row it is still considered a recordset. Also, the RunSQL is typically used with action queries (UPDATE, APPEND, DELETE) you are using it with a SELECT query.

    I believe you should change you approach. You can either use the DLookup function to return the value you want, or you need to use a ADO or DAO recordset object to do it. In your case I would recommend the DLookup. ADO/DAO will require more research and learning.

  5. #5
    Join Date
    Jan 2008
    Posts
    37
    The DLookup function is certainly easier and works perfectly. Thank you!

  6. #6
    Join Date
    Jan 2008
    Posts
    37
    Sorry another quick question relating to dlookup functions, what is the syntax for making the criteria equal to a variable? I have a variable that gets a code for the original tutor and im trying to find the name relating to that code, but it is not printing anything out, is it something to do with quotation marks?

    Dim oldTutName As Variant
    oldTutName = DLookup("[TU_FORENAME]", "tblTutor", "[TU_CODE] =" & oldTutCode)

    (oldTutCode retuns a string code and works fine)

  7. #7
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    If oldTutCode is a string that you need to surround it with quotes like:

    oldTutName = DLookup("[TU_FORENAME]", "tblTutor", "[TU_CODE] = '" & oldTutCode & "'")

    I used single quotes. Hope that helps.

    C

  8. #8
    Join Date
    Jan 2008
    Posts
    37
    thats great, thanks

Posting Permissions

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