Query not running.

[PhilOfWalton]

I have a combo box on a form whose row source is

SELECT TblLanguage.Language, TblLanguage.LanguageCode 
FROM TblLanguage 
WHERE (((TblLanguage.LanguageID)<>Tempvars!DefaultLanguageID)
And ((TblLanguage.LanguageUsed)=True)) 
ORDER BY TblLanguage.Language; 
 

Tempvars. is set up as the database is opened and value is 3

This all works perfectly.

I am trying to re-build the query in code to extract the values of the query.

The relevant bits are

 StrSQL = Ctl.RowSource
 Set QuerySet = MyDb.OpenRecordset(StrSQL)           ' Create a Query from the rowsource
 

StrSQL is a dummy to allow me to play with the Row Source without corrupting it, and the Ctl references that Combo Box.

I get an error 3061 Too few parameters. Expected 1.

If I change the StrSQL to

SELECT TblLanguage.Language, TblLanguage.LanguageCode 
FROM TblLanguage WHERE (((TblLanguage!LanguageID)<>3) 
And ((TblLanguage.LanguageUsed)=True)) 
ORDER BY TblLanguage.Language;"
 

It runs OK.
If, in the Debug Window, I hover over the Tempvars!DefaultLanguageID, I get 3

If I change the rowsource by preceding it with
PARAMETERS Tempvars!DefaultLanguageID Long; SELECT....
I get the same error.

I'm completely stuck!!

Help please

Phil

[ADezii]

You need to programmatically redefine the Control Source of the Combo Box - the following Code will work:

Dim ctl As Access.ComboBox
Dim strSQL As String
Dim QuerySet As DAO.Recordset
Dim MyDB As DAO.Database
Dim strControlSource As String
 
strControlSource = "SELECT TblLanguage.Language, TblLanguage.LanguageCode FROM TblLanguage " & _
                   "WHERE (((TblLanguage.LanguageID)<>" & [TempVars]![DefaultLanguageID] & ") " & _
                   "AND ((TblLanguage.LanguageUsed)=True)) ORDER BY TblLanguage.Language;"
 
Set ctl = Me![<Your Combo Box>]
ctl.RowSource = strControlSource
 
Set MyDB = CurrentDb
strSQL = ctl.RowSource
 
Set QuerySet = MyDB.OpenRecordset(strSQL)
 
With QuerySet
  Do While Not .EOF
    Debug.Print ![Language], ![LanguageCode]
      .MoveNext
  Loop
End With
 
QuerySet.Close
Set QuerySet = Nothing

P.S. - Entering the Value of strControlSource directly into the Control Source Property of the Combo Box does not appear to work.

[PhilOfWalton]

Thanks ADezii, but I don't think that will work.

I am scanning all combo boxes in all forms (in possibly unknown databases) to use the Control Source to run the query.
I accept that if the query needs parameters like [Input a Date] or Forms!MyForm!MyData it will not work, but in this case we are presenting a defined paramater - Tempvars!DefaultLanguageID which is known to be 3.

Every Row Source and hence every query will be different.

It may be possible to parse the Rowsource for the WHERE clause and re-build the query supplying the correct values where possible, but I suspect that would be very tricky.

Phil

[Rabbit]

You'll need a wrapper function to use in the row sources

Function GetTempVar(strKey As String) As Variant
    GetTempVar = TempVars(strKey)
End Function

SELECT *
FROM Table1
WHERE Field1 = GetTempVar("keyName")

P.S. Do you need the SQL? Or just the available items in the combo box? Because the .ItemData() array of a combo box has what's available in the drop down.

[ADezii]

1. If the RowSource of a Combo Box is a stored QueryDef Object, then TempVars will show up in it's Parameters Collection which you can then Evaluate, modify the SQL, then create a Recordset on without changing the original Row Source.
2. A case in point would be qryLanguage which consists of your displayed SQL:
   

   SELECT TblLanguage.Language, TblLanguage.LanguageCode
   FROM TblLanguage
   WHERE (((TblLanguage.LanguageID)<>Tempvars!DefaultLanguageID)
   And ((TblLanguage.LanguageUsed)=True))
   ORDER BY TblLanguage.Language;

3. To now change it into a Form that a Recordset can be built on would be:
   

   Dim ctl As Access.ComboBox
   Dim strSQL As String
   Dim QuerySet As DAO.Recordset
   Dim MyDB As DAO.Database
   Dim strControlSource As String
    
   Set ctl = Me![Combo5]
    
   Set MyDB = CurrentDb
    
   strSQL = Replace(ctl.RowSource, CurrentDb.QueryDefs("qryLanguage").Parameters(0).Name, _
                    Eval(CurrentDb.QueryDefs("qryLanguage").Parameters(0).Name))
    
   Set QuerySet = MyDB.OpenRecordset(strSQL)
    
   With QuerySet
     Do While Not .EOF
       Debug.Print ![Language], ![LanguageCode]
         .MoveNext
     Loop
   End With
    
   QuerySet.Close
   Set QuerySet = Nothing

4. Not really sure if this helps, but I'll throw it out there anyway.

[PhilOfWalton]

Nice try guys, but still no luck.

Firstly the query isn't stored so ADezii's method won't work, and secondly, the forms & reports are opened in design view as I am retrieving all fixed information such as Label captions, Status Bar Text, Control Lip Text, Value Lists from Combo & List boxes.

With Rabbit's solution I can't see how I can get the KeyName from a general bit of SQL

Remembering that I want this to work for any database
I dare not open the forms in form view, because
a) The form may be set to go to a new record, and try to add data
b) Might update something
c) might not open because it needs parameters or arguments passed to it.

So the second solution using ItemData is not on.

I am hoping to get the required information from any database so that it can be translated into different languages, so I will not be writing the original database and the Combo Row Source my be an SQL, a stored query or a Value List (I haven't looked at Field Lists yet)

Phil

[NeoPa]

How about using :

SELECT   [Language]
       , [LanguageCode]
FROM     [TblLanguage]
WHERE    ([LanguageID]<>Val(Tempvars!DefaultLanguageID))
  AND    ([LanguageUsed])
ORDER BY [Language]

Notice the use of Val().

[ADezii]

1. I have arrived at a workable solution, but it is a tad bit awkward, and I'm not sure that you will like it.
2. Since my prior solution will work on Stored QueryDefs, for Row Sources that are SQL Statements containing a TempVars References (only one), why not
   1. Create a Query based on the Row Source of the Combox.
   2. Change the SQL of this Query, evaluating any TempVars that are within the SQL (only one for now).
   3. Create a Recordset based on this Query and retrieve any desired results.
   4. DELETE the Query.
3. I know that this approach is a little unorthodox, but it has been tested and does work, so I figured that I would throw it out there.
   

   Dim ctl As Access.ComboBox
   Dim QuerySet As DAO.Recordset
   Dim MyDB As DAO.Database
   Dim qdfTemp As DAO.QueryDef
   Dim strQueryName As String
    
   Set ctl = Me![Combo5]
   Set MyDB = CurrentDb
    
   Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", ctl.RowSource)
       qdfTemp.SQL = Replace(ctl.RowSource, CurrentDb.QueryDefs("qryTemp").Parameters(0).Name, _
                     Eval(CurrentDb.QueryDefs("qryTemp").Parameters(0).Name))
    
   Set QuerySet = MyDB.OpenRecordset(qdfTemp.SQL)
    
   With QuerySet
     Do While Not .EOF
       Debug.Print ![Language], ![LanguageCode]
         .MoveNext
     Loop
   End With
    
   CurrentDb.QueryDefs.Delete "qryTemp"
    
   QuerySet.Close
   Set QuerySet = Nothing
    

[PhilOfWalton]

'Fraid not and anyway, even if it did work, how would I know in general terms where to put the Val() statement.

Don't forget this is a particular example from any Combo Box's Row Source

Trying you suggestion still is asking for 1 paramater

Thanks

Phil

[NeoPa]

Any chance of emailing the DB over? What you say doesn't make sense to me. I can't imagine that response from the situation as you've described it.

Alternatively, happy to connect and have a look at it with you if you're still up and around.

[PhilOfWalton]

Thanks

I'll do both

[Rabbit]

Sounds like you want to do this without modifying any of the database objects aside from module code and contain it within a single function.

What about a loop through the TempVars collection and using replace to replace all occurrences of the TempVar name with the value? Something like:

For i = 0 To UBound(TempVars)
    strSQL = Replace(strSQL, "[TempVars]![" & TempVars(i).name & "]", TempVars(i).value)
Next i

Note: code is untested. And you may have to use multiple replaces to account for all the ways TempVars might be referenced. For example, with brackets, without brackets, with brackets on only one of them, etc.

[NeoPa]

It seems that the fundamental problem was down to the Expression Service being unable to recognise TempVars references - even though Jet/ACE DOES.
IE. SELECT TempVars!DefaultLanguageID failed even though SELECT 3 worked fine.

The solution was much as Rabbit outlined, but not quite as straightforward. Each occurrence of a TempVars reference in any of the SQL being worked with needed to be recognised and replaced with its value. Bearing in mind that value had to be represented as a literal within a SQL string (so needing to be handled differently for text, numeric & date values).

[PhilOfWalton]

Thanks a lot for all your help and bits and pieces I gleaned from all of you.

In the end, the solution was quite simple, but I would still appreciate any comments.

    ' We need to add a QueryDef so let's make sure it's not there first
    For Each QDF In MyDb.QueryDefs
        If QDF.Name = "PP_Test" Then
            MyDb.QueryDefs.Delete QDF.Name
            Exit For
        End If
    Next QDF
 
    ' Replace the Name of the Tempvars with it's value
    StrSQL = Ctl.RowSource
    Set QDF = MyDb.CreateQueryDef("PP_Test", StrSQL)
    For Each Param In QDF.Parameters
        Param.Value = Eval(Param.Name)
        StrSQL = Replace(StrSQL, Param.Name, Param.Value)
    Next
 
    Set QuerySet = MyDb.OpenRecordset(StrSQL)           ' Create a Query from the modified rowsource
 

The only drawback is the creation and removal of a new QueryDef and I don't know if there are any implications of doing this repeatedly.

Phil

[ADezii]

Phil, have you tried using a Temporary QueryDef (Line# 3)?

'Replace the Name of the Tempvars with it's value
StrSQL = Ctl.RowSource
Set QDF = MyDb.CreateQueryDef("", StrSQL)
 
For Each Param In QDF.Parameters
  Param.Value = Eval(Param.Name)
  StrSQL = Replace(StrSQL, Param.Name, Param.Value)
Next
 
Set QuerySet = MyDb.OpenRecordset(StrSQL)  

[PhilOfWalton]

Brilliant

Works a treat

Thanks,

Phil

[ADezii]

You're welcome, Phil. Good Luck with your Project.

[NeoPa]

Is there any possibility of coming across undefined parameters in any of your RowSources? EG. SELECT [ID],[Enter Value Here] FROM ....

That will cause a problem if there is. If you can guarantee that every parameter will, necessarily, be a TempVars parameter then that's a neat and clever solution.

[PhilOfWalton]

I think that's more than likely, but I don't think it will possible to deal with it.....unless you have other thoughts

Thanks

Phil

[NeoPa]

My earlier post suggested targeting the string TempVars specifically. That would still be my recommendation, in spite of the fact that ADezii's approach is very clever.

[PhilOfWalton]

Hi Neopa, I accept what you say, but if the Tempvar is expecting a value from say an input box, although we can determine whether it is expecting a Number, Text or Date, it isn't going to get that value (Nobody there to input it), so I don't see hoe the Query can run

Phil

[NeoPa]

Hi Phil.

We're talking about the RowSource having a parameter reference other than a TempVars reference. It isn't the TempVars that will have a parameter. That's just one of the possible ways a parameter can be used in a RowSource.

[ADezii]

@PhilOfWalton:
Personally, I would maintain a Log consisting of all Parameters that could NOT be resolved listing the Date, Query Name, SQL, Parameter Name, Input Type (Date, Boolean, Text, etc.), Parameter Type (TempVars, Form Field, User Input, etc.). I know that this is more work, but I do feel that it would warrant the effort involved down the line.

[NeoPa]

Bearing in mind (I believe) that this database is supposed to handle any other database it isn't practical to work from what you know to be there. It can never know what is to be there - being essentially infinite.

That's my understanding of the situation. Phil may well contradict that understanding as he has a much better appreciation of the overall project (obviously).