Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    4

    Unanswered: Sql code wont pick up form

    Hi guys

    Can anyone tell me what is wrong with the following code:

    What I am trying to do is run a pass through query base don the criteria within the form. As im running the code the query is runnning the criteria as zero and updating the form.Rather than vice versa. i cannot see where I am going wrong

    Public Sub RUN_PASS_THROUGH_QUERIES()




    Dim POS As Integer
    Dim dbscurrent As Database
    Dim qdfPassThrough As QueryDef

    [Forms]![frm_Dept_Comm_Data]![POS_DEPT_NO_DEPT_COMM].Text = POS



    Set dbscurrent = CurrentDb
    DoCmd.DeleteObject acQuery, "Pqry_TEST001"
    Set qdfPassThrough = dbscurrent.CreateQueryDef("Pqry_Test001")

    qdfPassThrough.Connect = "ODBC;DSN=SlmsADHOCJPTP98S12F;"
    qdfPassThrough.SQL = "SELECT JPGPBH.GPSTB043.FWEEK,JPGPBH.GPSTB043.POS_DEPT_NO, JPGPBH.GPSTB043.DEPT_CODE,JPGPBH.GPSTB043.COMM_COD E,JPGPBH.GPSTB043.Sales_Val,JPGPBH.GPSTB043.REDCT_ VAL,JPGPBH.GPSTB043.DSPSL_VAL,JPGPBH.GPSTB043.FINC L_STRES_VAL From JPGPBH.GPSTB043 WHERE JPGPBH.GPSTB043.POS_DEPT_NO='" & POS & "';"


    qdfPassThrough.ReturnsRecords = True
    DoCmd.SetWarnings False
    DoCmd.OpenQuery ("Qry_Test001")

    End Sub

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    1) Your query is totaly unreadable. Try building it like:
    Code:
    qry = "Select "& _
            "field1, " & _
            "Field2, " & _ 
        "From "& _
            "Table "
    2)[Forms]![frm_Dept_Comm_Data]![POS_DEPT_NO_DEPT_COMM].Text = POS
    is the wrong way around should be:
    POS = [Forms]![frm_Dept_Comm_Data]![POS_DEPT_NO_DEPT_COMM].Text
    (.text may have to be .value as well -where .value is not even necessary as its the default- [also se 4])

    3) Your using createquerydef, if the query allready exists this may/will cause a problem....

    4) if your running this of the form. There is no need to use forms...., but use: me.Fieldname

    5) Disambiguate, use DAO.Database instead of just Database
    Regards
    Last edited by namliam; 01-29-04 at 08:50.

  3. #3
    Join Date
    Jan 2004
    Posts
    4
    Thank you for your reply it has worked a treat. Apologies for the layout of the code. I am very new to this and am still learning.

    Thanks Again

    SAYED

Posting Permissions

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