recover in a query

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

How do I do to recover in a query all the querys except for the querys
update and querys increments and querys exclusion?
 
I want to catch the names of all the querys of my bank except for the action
querys.
 
Action queries will start DELETE, UPDATE or INSERT INTO, while you want all
the queries that start SELECT. Assuming you've got a reference set to DAO,
the following untested air code should do what you're asking for:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Left$(qdfCurr.SQL, 6) = "SELECT" Then
Debug.Print qdfCurr.Name
End If
Next qdfCurr
 
Douglas J. Steele said:
Action queries will start DELETE, UPDATE or INSERT INTO, while you want all
the queries that start SELECT. Assuming you've got a reference set to DAO,
the following untested air code should do what you're asking for:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Left$(qdfCurr.SQL, 6) = "SELECT" Then
Debug.Print qdfCurr.Name
End If
Next qdfCurr
Click to expand...

Remember, and the .SQL property may begin with PARAMETERS, and it if
does, the above will not work.


Sincerely,

Chris O.
 
Douglas J. Steele said:
Action queries will start DELETE, UPDATE or INSERT INTO, while you want all
the queries that start SELECT. Assuming you've got a reference set to DAO,
the following untested air code should do what you're asking for:

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If Left$(qdfCurr.SQL, 6) = "SELECT" Then
Debug.Print qdfCurr.Name
End If
Next qdfCurr
Click to expand...

After looking at the DAO 3.6 Object models list of properties for the
QueryDef object, I think the property that needs to be tested is
..Type.

Public Sub ListSelectQueryDefs()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If qdfCurr.Type = dbQSelect And _
Left(qdfCurr.Name, 1) <> "~" Then
Debug.Print qdfCurr.Name
End If
Next qdfCurr

End Sub


Sincerely,

Chris O.
 
Frank Dulk said:
But as I place that inside of a combo, for him to catch the names of the
querys?
Click to expand...

Frank Dulk,

I am not 100% sure I understand what you are asking.

If I'm right, you want to display the list of all the queries produces
by the VBA code example in a ComboBox?

Well:

1: Create a blank form, and drop a combo box on it (it should be named
Combo0 by default.
2: Press the Code button. Select Form and Load. Enter the name of
the VBA Sub into the event procedure. It should look like:

Private Sub Form_Load()

ListSelectQueryDefs

End Sub

I saved my form as: "Exam_ComboBox_LoadFromFunction".


3: Set Combo0's RowSourceType to Value List.
4: Add the following VBA code to a new Module.

Public Sub ListSelectQueryDefs()

Dim dbCurr As DAO.Database
Dim qdfCurr As DAO.QueryDef

With Forms!Exam_ComboBox_LoadFromFunction!Combo0
Set dbCurr = CurrentDb()
For Each qdfCurr In dbCurr.QueryDefs
If qdfCurr.Type = dbQSelect And _
Left(qdfCurr.Name, 1) <> "~" Then
'Debug.Print qdfCurr.Name
.RowSource = .RowSource & "," & qdfCurr.Name
End If
Next qdfCurr
.RowSource = Replace(.RowSource, ",", "", 1, 1)
Debug.Print .RowSource
End With


dbCurr.Close
Set qdfCurr = Nothing
Set dbCurr = Nothing

End Sub

5: Run the run the Form.

6: Warning: My exmpales database had so many Queries that the maximum
length of the RowSource property was exceeded. I used the debugger to
jump past the loop and finish the Sub, and the Combo Box did display
all the queries that managed to fit into the property.

Obviously, that doesn't work properly.

Start a new thread, in microsoft.public.access.forms, entitled:

Load Combo Box with Query List from Sub

And fully describe the issue.

The people who hang out there specialize in answer questions about
forms and controls.


Sincerely,

Chris O.
 
Back
Top