Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2006
    Posts
    1

    Red face Unanswered: method or data member not found

    I am creating a query by form and I'm having a heck of a time doing it. I finally found some code that would fit what I'm trying to do. Referenced DAO and part of the problems I was having went away, but now it gets to the bottom line (in red) and gives me the error message "method or data member not found". The database is called Student_data. What am I doing wrong?

    wells8244
    ************************************************** **

    Private Sub Command6_Click()

    Dim db As Database
    Dim qd As QueryDef

    Dim MyWhere As Variant

    Set db = DBEngine.Workspaces(0).Databases(0)

    On Error Resume Next
    db.querydefs.Delete ("e-mail_query")
    On Error GoTo 0
    MyWhere = Null

    If Left(Me![ID], 1) = "*" Or Right(Me![ID], 1) = "*" Then
    MyWhere = MyWhere & (" AND [ID] like '" + Me![ID] + "'")
    Else
    MyWhere = MyWhere & (" AND [ID] = '" + Me![ID] + "'")
    End If

    If Left(Me![FirstName], 1) = "*" Or Right(Me![FirstName], 1) = "*" Then
    MyWhere = MyWhere & (" AND [FirstName] like '" + Me![FirstName] + "'")
    Else
    MyWhere = MyWhere & (" AND [FirstName] = '" + Me![FirstName] + "'")
    End If

    If Left(Me![LastName], 1) = "*" Or Right(Me![LastName], 1) = "*" Then
    MyWhere = MyWhere & (" AND [LastName] like '" + Me![LastName] + "'")
    Else
    MyWhere = MyWhere & (" AND [LastName] = '" + Me![LastName] + "'")
    End If

    If Left(Me![Degree Plan], 1) = "*" Or Right(Me![Degree Plan], 1) = "*" Then
    MyWhere = MyWhere & (" AND [Degree Plan] like '" + Me![Degree Plan] + "'")
    Else
    MyWhere = MyWhere & (" AND [Degree Plan] = '" + Me![Degree Plan] + "'")
    End If

    If Not IsNull(Me![Hours First]) Then
    MyWhere = MyWhere & (" AND [Hours]>= " & Me![Hours First])
    ElseIf IsNull(Me![Hours First]) Then
    MyWhere = MyWhere & (" AND [Hours] like *")
    End If

    Set qd = Database.student_data("E-mail_Query", "Select * from Student_Record_Table " & (" where " + Mid(MyWhere, 6) + ";"))

    DoCmd.OpenQuery "E-mail_Query"
    End Sub

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the syntax is
    set qd = database.createquerydef("", "SELECT blah blah")
    for a disposable qdef, or
    set qd = database.createquerydef("keepMe", "SELECT blah blah")
    for a saved qdef

    also, if all you want to do is to edit the sql. maybe try something like
    set qd = currentdb.querydefs("e-mail_query")
    qd.sql = "SELECT blah blah"
    qd.close
    set qd = nothing


    izy
    currently using SS 2008R2

Posting Permissions

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