SQL Runtime query in VBA doesn't work

[Heejoe]

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:

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]

 

[HarleyQuinn]

You seem to be putting your ORDER BY statement in your WHERE clause in the above code.

It's worth using Debug.Print strSQL once you've populated the variable to see what's actually in there.

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[HarleyQuinn]

Looking a bit more 'in-depth' the query is fundamentely wrong (IMO). There is no join between the two tables, the FROM, WHERE and ORDER BY will be missing from strSQL (as they aren't added to the string). I'm also not to up on 2007 (I don't use it) so I can't say whether you can use AND specify the tables used (seems I don't use it I've changed it).

Maybe something more like (I've highlighted my changes in [red]red[/red]:

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] [red]" _[/red]
    [red]& "[/red]From [Company details] [red],[/red] [Company financial detail] [red]" _[/red]
     [red]& "[/red]Where " [red]& stryourwhereclause & [/red]strSortOrder & ";"

Hope this helps

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Heejoe]

Thanks for your efforts HarleyQuinn,

Things have changed.

I inserted your recommendations and defined the "stryourwhereclause" from my original SQL code, but now I am getting an error (at least button works again ). The error is [Error number 424; Object required]. No further details.

Does this mean the query behind it isn't working properly? Or is the clause still wrong?

Hope you (or anyone) can help me again. It is one of the final stages of my database!

Haio

 

[HarleyQuinn]

What code do you have now? Also, what line generates the error?

HarleyQuinn
---------------------------------
Black coat, white shoes, black hat, cadillac. The boy's a timebomb!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Remou]

I think you should use DAO rather than ADO, DAO is native for Access, and as you are running in Access, there is no need for the added complication of ADO.

I note that you refer to Company_details_1 in strSortOrder but this is not a table in your query.

Consider:

'You should add a reference to 
'Microsoft DAO 3.x Object Library

Dim db As Database
Dim strSQL As String
Dim strSortOrder

    Set db = CurrentDb
    
    If Me.cboSortOrder1.Value <> "Turnover" Then
        strSortOrder = " ORDER BY cd.[" & Me.cboSortOrder1.Value & "]"
    Else
        strSortOrder = ""
    End If
    
    ' Build SQL statement using aliases
    ' cfd = Company financial detail
    ' cd = Company details
    strSQL = "SELECT cfd.Turnover, cfd.[From which dairy], cd.Ownership, " _
    & "cd.Country, cfd.[Company name], cd.Location, cd.Employees, cfd.[Turnover Cheese], " _
    & "cfd.[Turnover Others], cfd.EBITDA, cd.Logo, cd.[Average Milk Price], " _
    & "cd.[Total milk input], cd.[Product volume Cheese], " _
    & "cd.[Product volume Milk and Liquid products], cd.[Product volume Others] " _
    & "FROM [Company details] cd , [Company financial detail] cfd " _
    & strSortOrder

    If Not IsNull(DLookup("Name", "MSysObjects", "Name='tmpViewQuery' AND Type <> 5")) Then
        MsgBox "Problem with this name"
        Exit Sub
    End If
    
    If Not IsNull(DLookup("Name", "MSysObjects", "Name='tmpViewQuery' AND Type = 5")) Then
        db.QueryDefs("tmpViewQuery").SQL = strSQL
    Else
        CurrentDb.CreateQueryDef "tmpViewQuery", strSQL
    End If
    
    DoCmd.OpenQuery "tmpViewQuery", acViewDesign

I would also look at your field names. Field names with spaces will just cause you more and more grief as time goes by.

http://lessthandot.com

 

[Remou]

... also, this:

"FROM [Company details] cd , [Company financial detail] cfd "

gives a cartesian query (includes all possible combination of rows from both tables), and I very much doubt that you want that. You need a join of some description, probably an INNER JOIN, probably on the lines of:

"FROM [Company details] cd " _
& "INNER JOIN [Company financial detail] cfd " _
& "ON cd.CompanyID = cfd.CompanyID"

It can be useful to build your query in the query design window and then switch to SQL view to get the SQL, however, the query design window can be buggy, so look on the SQL as a guide, rather than an answer.

http://lessthandot.com

 

[Heejoe]

Had a few days off for studying, back at it now so will look at both your answers.
Thanks a lot for your efforts. I will get back asap.

 

[Heejoe]

I tried to understand and implement your changes. My original (working) query in sql =

 SELECT cfd.Turnover, cfd.[From which dairy], cd.Ownership, cd.Country, cfd.[Company name], cd.Location, cd.Employees, cfd.[Turnover Cheese], cfd.[Turnover Others], cfd.EBITDA, cd.Logo, cd.[Average Milk Price], cd.[Total milk input], cd.[Product volume Cheese], cd.[Product volume Milk and Liquid products], cd.[Product volume Others]
FROM [Company details] AS cd INNER JOIN [Company financial detail] AS cfd ON cd.[Company name] = cfd.[Company name]
ORDER BY cfd.Turnover DESC;

Thus giving me only the option to sort by "cfd.Turnover". This now must be done by selecting one of the options from "cborankby". The choices have to be filled in by the sql command to the query (qrytopcompanies).

@HarleyQuinn: The error comes up straight after pushing the ok button, not getting to the 'vbacode'. I referenced to dao and checked if it works (through a module).

@ HarleyQuinn and Remou: New code is shown below. My hunge that the insertion into the query doesn't work, or my referencing to te sqlcommand is not valid.

When I delete the "apply sql to stored query" the Error is not given. Nothing happens to be exact....

Thanks a lot for your help! Hope, in the future, that I can help you/others as well.

 Private Sub cmdOKtop10_Click()
On Error GoTo cmdOKtop10_Click_Err
 Dim db As DAO.Database
    Dim blnQueryExists As Boolean
    Dim varItem As Variant
Dim strSQL As String
Dim strSortOrder
Dim stryourwhereclause

' Check for the existence of the stored query
    blnQueryExists = False
    Set cat.ActiveConnection = CurrentProject.Connection
    For Each qry In cat.Views
        If qry.Name = "qrytopcompanies" Then
            blnQueryExists = True
            Exit For
       End If
    Next qry
' Create the query if it does not already exist
   If blnQueryExists = False Then
    cmd.CommandText = "SELECT * FROM Company_Details_1"
        cat.Views.Append "qrytopcompanies", cmd
    End If
    Application.RefreshDatabaseWindow
' Turn off screen updating
    DoCmd.Echo False
' Close the query if it is already open
    If SysCmd(acSysCmdGetObjectState, acQuery, "qrytopcompanies") = acObjStateOpen Then
        DoCmd.Close acQuery, "qrytopcompanies"
    End If

    
' Build sort clause
    If Me.cborankby.Value <> "Turnover" Then
        strSortOrder = " ORDER BY cd.[" & Me.cborankby.Value & "]"
        Else
        strSortOrder = ""
    End If

'Build the where clause
stryourwhereclause = "((([Company details].Ownership)=[Forms]![Dialog box for top 10]![cbotype]) AND (([Company details].Country)=[Forms]![Dialog box for top 10]![cboCountryselection])) OR ((([Company details].Country)=[Forms]![Dialog box for top 10]![cboCountryselection]) AND ((([Company details].[Ownership]) Like [Forms]![Dialog box for top 10]![cbotype]) Is Null)) OR ((([Company details].Ownership)=[Forms]![Dialog box for top 10]![cbotype]) AND ((([Company details].[Country]) Like [Forms]![Dialog box for top 10]![cboCountryselection]) Is Null) AND ((([Company details].[Ownership]) Like [Forms]![Dialog box for top 10]![cboCountryselection]) Is Null)) OR (((([Company details].[Ownership]) Like [Forms]![Dialog box for top 10]![cboCountryselection]) Is Null) AND (([Forms]![Dialog box for top 10]![cbotype]) Is Null))"

' Build SQL statement
    strSQL = "SELECT SELECT cfd.Turnover, cfd.[From which dairy], cd.Ownership, cd.Country, cfd.[Company name], cd.Location, cd.Employees, cfd.[Turnover Cheese], cfd.[Turnover Others], cfd.EBITDA, cd.Logo, cd.[Average Milk Price], cd.[Total milk input], cd.[Product volume Cheese], cd.[Product volume Milk and Liquid products], cd.[Product volume Others] " _
    & "[Company details] AS cd INNER JOIN [Company financial detail] AS cfd ON cd.[Company name] = cfd.[Company name] " _
    & "strSortOrder;"
     Debug.Print strSQL
      
  ' 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"

Exit_cmdOKtop10_Click:
     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 Exit_cmdOKtop10_Click



End Sub

 

[Heejoe]

I realise that the code is a bit long...
Though if any of you could solve my problem or has experiences with "queries on the fly" I would really appreciate it!

Haio

 

[Heejoe]

Mmm,

Seems like I am talking to myself here.

I found the object:

 Set qdf = db.QueryDefs(qrytopcompanies)

The query "qrytopcompanies" exists. When I but the name between brackets it does not give me an error. Nothing happens, not even the "print sql" window.

When I leave the brackets error 3265 pops up. I have tried putting the db.querydef.create in but then it DOES recognise the existence of the query.

Bit lost here unfortunately.

Hope you can help.

Haio