dinamic query

[yaniv d]

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

 

[Guest]

What exactly do you mean by "cut query fields"? Can you give an example of
what you are trying to accomplish?

Also, the term is dynamic, not dinamic.


Tom Wickerath, Microsoft Access MVP

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

 

[yaniv d]

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

 

[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]

can you send me the same code but in ADO or ADODB?

 

[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]

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?

 

[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]

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