Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    118

    Question Unanswered: Whats the synthax for a QueryDef function?

    I'm trying to write a little function to take in user input and insert it into a query.

    This is the query:

    PARAMETERS [TB] Text ( 255 );
    SELECT [dbo_Drawings].[DwgTag], [dbo_Drawings].[DwgType]
    FROM dbo_Drawings
    WHERE ((([dbo_Drawings].[DwgType])=[TB]));


    I'm trying to pass what the user puts into text0 into "TB"

    I got this bit of code but i cant get it to work:

    Code:
    Private Sub Command2_Click()
    
    Dim qDF AS QueryDefs
    
    qDF.Parameters("TB") = Me.Text0
    
    MsgBox TB
    
    DoCmd.OpenQuery "TBQuery"
    It says Sub or Function not defined.

    End Sub

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    You've got several problems where.

    What you are interested in is manipulating a querydef.

    But the variable you have created, qDF, is defined as the collection of querydefs, not an individual query definition.

    So, first of all, you need to DIM qdf As QueryDef . . .

    . . . notice that there is no 's' on the end of QueryDef--you are dimensioning a variable for a SINGLE query definition.

    Then, you have to point that query definition variable to the specific query definition you want to work with. You will notice that your current code does not reference the query you want to work with (until you tell it to run--which is too late).

    You need a line like this:

    Set qDF=CurrentDb().QueryDefs("TBQuery")

    Now, you qDF is pointing to the specific query definition that you want to work with.

    Now, your assignment statement will work.

    BTW--it is good practice, after you are finished manipulating the query definition, to "clear" the qDF variable by including a line like this:

    Set qDF = Nothing
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Mar 2004
    Posts
    118
    I must be doing something else wrong, because i get thesame error

    Note: I'm using MSA2000


    Code:
    Private Sub Command2_Click()
    
    Dim qDF As QueryDef
    
    Set qDF = CurrentDb().QueryDefs("TBQuery")
    
    qDF.Parameters("TB") = Me.Text0
    
    MsgBox TB
    
    DoCmd.OpenQuery "TBQuery"
    
    End Sub

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    What is the error you get, and where?

    That MsgBox line does not make any sense.

    TB would be interpreted as a varaiable. Have you defined TB somewhere else?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Private Sub Command2_Click()

    Dim qDF As QueryDef
    Dim TB As String

    Set qDF = CurrentDb().QueryDefs("TBQuery")

    qDF.Parameters("TB") = Me.Text0

    ' TB=Me.Text0

    MsgBox TB

    DoCmd.OpenQuery "TBQuery"

    End Sub

    You haven't defined TB anywhere, and you haven't set TB to be anything. Now it should work

  6. #6
    Join Date
    Mar 2004
    Posts
    118
    Thats not it either...

    It just doesent seem to like QueryDef

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    The DoCmd.OpenQuery "TBQuery" is not right here, because it will ask you for the value of TB

  8. #8
    Join Date
    Mar 2004
    Posts
    118
    Ok, but i give it the value of TB in the textbox...

  9. #9
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Yes, but it is not stored in the SQL. If you try to print hte SQL you will see it not.
    MsgBox (qDF.sql)

  10. #10
    Join Date
    Mar 2004
    Posts
    118
    So I need to make an sql string for it to work?


    I want to plug that value in an already existing query... How does that work?

Posting Permissions

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