Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

dinamic query

Discussion in 'Information Technology' started by yaniv d, Jul 28, 2006.

Tags:
  1. yaniv d

    yaniv d
    Expand Collapse
    Guest

    hello all,
    does anyone know how to cut query fields dinamicly through VBA?
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

  4. yaniv d

    yaniv d
    Expand Collapse
    Guest

    hi
    i mean that i want to remove or add fields in the query with vba or in
    another way
     
  5. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Try this for an existing query named "qryTest", which is based on a table
    named "My Table", with fields named "Field1", "Field2" and "Field3". You
    might, for example, have an option group on a form with buttons that allowed
    you to specify custom sort orders.


    Option Compare Database
    Option Explicit

    Sub BuildQuery(intSortOrder As Integer)
    On Error GoTo ProcError

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qryTest")

    Select Case intSortOrder
    Case 1
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field1"
    Case 2
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field2"
    Case Else
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field3"
    End Select

    qdf.SQL = strSQL


    ExitProc:
    On Error Resume Next
    Set qdf = Nothing
    db.Close: Set db = Nothing
    Exit Sub
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in procedure BuildQuery..."
    Resume ExitProc
    End Sub


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "yaniv d" wrote:

    > hi
    > i mean that i want to remove or add fields in the query with vba or in
    > another way
     
  6. yaniv d

    yaniv d
    Expand Collapse
    Guest

    can you send me the same code but in ADO or ADODB?
     
  7. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Although I'll be the first to admit that ADO is not one of my stronger areas,
    try this version:


    Sub EditQueryUsingADO(intSortOrder As Integer)
    On Error GoTo ProcError

    ' Early binding
    ' Note: Requires references set to
    ' "Microsoft ActiveX Data Objects 2.x Library" and
    ' "Microsoft ADO Ext. 2.x for DDL and Security"

    Dim conn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim cat As New ADOX.Catalog
    Dim view As ADOX.view
    Dim strSQL As String

    Set conn = Application.CurrentProject.Connection
    Set cat = New ADOX.Catalog


    Select Case intSortOrder
    Case 1
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field1"
    Case 2
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field2"
    Case Else
    strSQL = "SELECT Field1, Field2, Field3 FROM [My Table] " _
    & "ORDER BY Field3"
    End Select


    ' Open the Catalog
    Set cat.ActiveConnection = conn

    Set view = cat.Views("qryTest")
    Set cmd = view.Command

    cmd.CommandText = strSQL
    Set view.Command = cmd


    ExitProc:
    'Clean up
    On Error Resume Next
    Set cmd = Nothing: Set view = Nothing: Set cat = Nothing
    conn.Close: Set conn = Nothing
    Exit Sub
    ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, "Error in procedure EditQueryUsingADO..."
    Resume ExitProc
    End Sub



    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "yaniv d" wrote:

    > can you send me the same code but in ADO or ADODB?
     
  8. yaniv d

    yaniv d
    Expand Collapse
    Guest

    thanks,
    i decided to do it use combo box and by them to filter,
    my question is do you think there is an option to compare between to
    table and update them in the current FORM?
     
  9. Tom Wickerath

    Tom Wickerath
    Expand Collapse
    Guest

    Repost as a new question.

    Please try to explain exactly what you want to accomplish, because I'm back
    to not understanding what it is that you are wanting to do. In any case, I
    believe the scope has changed enough that this warrants a new thread.


    Tom Wickerath, Microsoft Access MVP

    http://www.access.qbuilt.com/html/expert_contributors.html
    http://www.access.qbuilt.com/html/search.html
    __________________________________________

    "yaniv d" wrote:

    > thanks,
    > i decided to do it use combo box and by them to filter,
    > my question is do you think there is an option to compare between to
    > table and update them in the current FORM?
     
  10. yaniv d

    yaniv d
    Expand Collapse
    Guest

    i need to build a form that will implement groups by grouping
    level,every group wil be implementing the sum and avarage of some
    paramenters coming from a query.
    i need 2 things,:
    1.to implement a dinamic grouping level
    for example:
    country->location->job category
    or
    country->grade->job category
    or
    country->location->grade

    those are just for example i need to perform more complex grouping then
    this

    2.i need to save the values that will be added to the form in a table
    that when the form will be loaded again it will bring the new values
    that entered the last time

    thats it in brief
     

Share This Page