Hello,
For my database I have built a dialogbox where my users can select (with the purpose to built a query) from 3 combo boxes. The first (cborankby)serves as the "Order By" clause and gives about 10 options (turnover / production volumes/ employees etc). The second and third are a country and ownership selection for selection purposes.
Previously I have built a query which worked fine when the rankby was FIXED. However, the ranking in practise should be done for each of the 10 option thus my query failed in this. I don't think it is wise to create a query for each selection?!
So I tried with VBA following the fontstuff.com and reading FAQ705-2205 Access Tips. However, when I run the query NOTHING happens. Even though my code is placed on the on-click event of the "ok-button".
Has anybody got some tips for me please? Had the same problem before? Maybe better ways of going around this?
I am really getting stuck here, once again building 10 queries sounds stupid.
Two more things, I have got two forms: Company details and Company financial details, linked with each other. I am using access 2007, vb 6.5
Thanks a lot!
Haio
My code looks like this:
For my database I have built a dialogbox where my users can select (with the purpose to built a query) from 3 combo boxes. The first (cborankby)serves as the "Order By" clause and gives about 10 options (turnover / production volumes/ employees etc). The second and third are a country and ownership selection for selection purposes.
Previously I have built a query which worked fine when the rankby was FIXED. However, the ranking in practise should be done for each of the 10 option thus my query failed in this. I don't think it is wise to create a query for each selection?!
So I tried with VBA following the fontstuff.com and reading FAQ705-2205 Access Tips. However, when I run the query NOTHING happens. Even though my code is placed on the on-click event of the "ok-button".
Has anybody got some tips for me please? Had the same problem before? Maybe better ways of going around this?
I am really getting stuck here, once again building 10 queries sounds stupid.
Two more things, I have got two forms: Company details and Company financial details, linked with each other. I am using access 2007, vb 6.5
Thanks a lot!
Haio
My code looks like this:
Code:
Private Sub cmdOKtop10_Click()
On Error GoTo cmdOKtop10_Click_Err
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim qry As ADOX.View
Dim varItem As Variant
Dim strSortOrder
Dim strSQL As String
' Build sort clause
If Me.cboSortOrder1.Value <> "Turnover" Then
strSortOrder = " ORDER BY Company_details_1.[" & Me.cboSortOrder1.Value & "]"
Else
strSortOrder = ""
End If
' Build SQL statement
strSQL = "SELECT [Company financial detail].Turnover, [Company financial detail].[From which dairy], [Company details].Ownership, [Company details].Country, [Company financial detail].[Company name], [Company details].Location, [Company details].Employees, [Company financial detail].[Turnover Cheese], [Company financial detail].[Turnover Others], [Company financial detail].EBITDA, [Company details].Logo, [Company details].[Average Milk Price], [Company details].[Total milk input], [Company details].[Product volume Cheese], [Company details].[Product volume Milk and Liquid products], [Company details].[Product volume Others]"
FROM [Company details] And [Company financial detail]
WHERE strSortOrder & ";"
' Apply the SQL statement to the stored query
cat.ActiveConnection = CurrentProject.Connection
Set cmd = cat.Views("qrytopcompanies").Command
cmd.CommandText = strSQL
Set cat.Views("qrytopcompanies").Command = cmd
Set cat = Nothing
' Open the Query
DoCmd.OpenQuery "qrytopcompanies"
' If required the dialog can be closed at this point
DoCmd.Close acForm, "Dialog box for top 10"
' Restore screen updating
cmdOKtop10_Click_Exit:
DoCmd.Echo True
Exit Sub
cmdOKtop10_Click_Err:
MsgBox "An unexpected error hass occurred." _
& vbCrLf & "Procedure: cmdOKtop10_Click" _
& vbCrLf & "Error Number: " & Err.Number _
& vbCrLf & "Error Description:" & Err.Description _
, vbCritical, "Error"
Resume cmdOKtop10_Click_Exit
/[code]