Results 1 to 8 of 8
  1. #1
    jarredcody is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    4

    Pass Thru Query - Start Up Form - Selection

    I have a pass-through query pulling in the data I want....from Oracle OBI ODBC


    It works fine -

    The Issue I am having - I need a way to have a user narrow the query thru a form...

    Drop Down or Text Field Entry in a Start Up Form that the user can then limit the query to.

    I would like to include a prompt in the query - but pass-through doesn't accept it


    WHERE ("- Standard Calendar"."Day Date" =CURRENT_DATE) AND ("- Organization"."District Num" = '01')


    WHERE ("- Standard Calendar"."Day Date" =[PROMPT DATE]) AND ("- Organization"."District Num" = '[PROMPT DISTRICT]')

    WHERE ("- Standard Calendar"."Day Date" =[FORM]![FIELD1]) AND ("- Organization"."District Num" = '[FORM]![FIELD2]')

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in a continous form, but a text box to filter the data,and other controls to filter.
    once set, click filter button

    Code:
    sub btnFilter_Click()
    dim sWhere as string
    
      sWhere = "1=1"
      if not Isnull(txtDate) then   sWHERE =sWhere " and [DateField]=#" & me.txtDate & "#"
      if not Isnull(cboDistrict) then sWhere = sWhere  & " AND "[District Num]= '" & me.cboDistrict & "'"
    
    if sWhere = "1=1" then
        me.filterOn = false
    else
      me.filter = sWhere
      me.filterOn = true
    endif
    end sub

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Get the syntax from the query by adding a criteria. Then copy and paste this SQL to the appropriate event of the form and take the criteria from the fields on the form.

    Add this code:
    Code:
        Dim qdf As QueryDef
        Set qdf = CurrentDb.QueryDefs("queryname")
            
        qdf.SQL = "SELECT .... FROM ... WHERE " (replace the criteria with the fields that are on the form)
        Set qdf = Nothing

  4. #4
    jarredcody is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    4
    ???

    Thanks for the replies...

    I am not trying to use the form as a data display for the user... I am only trying to make a start up "pop up" here are the filters available.... click one.... and click run.


    Now that I think of it ...

    Is there a way to simply...


    Have 13 buttons

    and have 13 different queries saved.... that would overwrite the prior passthru...
    or VBA to rename the 'active' passthru query name?


    I could create all 13 queries... all the same but modified pass thrus...

    have a form with the 13 different queries.... and the user clicks on one.... the program would rename query1 to activequery and all the other mktbls etc I have linked to the activequery would run off that.


    Now is something like renaming a query macro possible?

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    13 queries all the same? I'm not sure I get what you are saying.

    My reply was assuming that the user would enter the criteria on a form - date, district, etc - and then click a button to run a query. The code I provided would add the values that the user entered to the SQL of the query before running it. To run it add the command DoCmd.OpenQuery "queryname".

  6. #6
    jarredcody is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    4
    You said it right...


    I want to have a form


    FROM SVR
    WHERE ("- Standard Calendar"."Day Date" =CURRENT_DATE )AND ("- Organization Current Detail"."District Num" = '01') AND ("- Organization Current Detail"."Division Num" = '01') AND ("- Organization Current Detail"."Building Num" = '01') AND ("- Organization Current Detail"."Unit Num" = '01')

    Orange is Optional - I want to give the user the choice.... hopefully with either a text box or a drop down

  7. #7
    jarredcody is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    4
    Quote Originally Posted by aytee111 View Post
    Get the syntax from the query by adding a criteria. Then copy and paste this SQL to the appropriate event of the form and take the criteria from the fields on the form.

    Add this code:
    Code:
        Dim qdf As QueryDef
        Set qdf = CurrentDb.QueryDefs("queryname")
            
        qdf.SQL = "SELECT .... FROM ... WHERE " (replace the criteria with the fields that are on the form)
        Set qdf = Nothing

    Not sure where to put any of that?

    Thank you

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    How is the query being run, will the user click on a button? If so, the code will go into the OnClick event of the command button. By using what is called "string concatenation" you will recreate the SQL for your query depending on what values the user has entered, as Ranman described.

    I could not see your form design, it is too small.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Pass thru Query for Form
    By Nyrubi in forum Forms
    Replies: 15
    Last Post: 11-04-2016, 02:51 PM
  2. Pass combo box selection to docmd.runsql
    By xopherira in forum Modules
    Replies: 8
    Last Post: 08-10-2015, 05:03 PM
  3. Replies: 5
    Last Post: 04-27-2015, 02:40 PM
  4. Replies: 7
    Last Post: 03-11-2015, 12:48 PM
  5. Pass a value from a query to a form
    By cwwaicw311 in forum Forms
    Replies: 22
    Last Post: 03-22-2010, 10:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums