By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Query not running.

PhilOfWalton
Expert 100+
P: 1,430
I have a combo box on a form whose row source is
Expand|Select|Wrap|Line Numbers
  1. SELECT TblLanguage.Language, TblLanguage.LanguageCode 
  2. FROM TblLanguage 
  3. WHERE (((TblLanguage.LanguageID)<>Tempvars!DefaultLanguageID)
  4. And ((TblLanguage.LanguageUsed)=True)) 
  5. ORDER BY TblLanguage.Language; 
  6.  
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
Expand|Select|Wrap|Line Numbers
  1.  StrSQL = Ctl.RowSource
  2.  Set QuerySet = MyDb.OpenRecordset(StrSQL)           ' Create a Query from the rowsource
  3.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT TblLanguage.Language, TblLanguage.LanguageCode 
  2. FROM TblLanguage WHERE (((TblLanguage!LanguageID)<>3) 
  3. And ((TblLanguage.LanguageUsed)=True)) 
  4. ORDER BY TblLanguage.Language;"
  5.  
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
Sep 17 '17 #1

✓ answered by ADezii

Phil, have you tried using a Temporary QueryDef (Line# 3)?
Expand|Select|Wrap|Line Numbers
  1. 'Replace the Name of the Tempvars with it's value
  2. StrSQL = Ctl.RowSource
  3. Set QDF = MyDb.CreateQueryDef("", StrSQL)
  4.  
  5. For Each Param In QDF.Parameters
  6.   Param.Value = Eval(Param.Name)
  7.   StrSQL = Replace(StrSQL, Param.Name, Param.Value)
  8. Next
  9.  
  10. Set QuerySet = MyDb.OpenRecordset(StrSQL)  

Share this Question
Share on Google+
23 Replies


ADezii
Expert 5K+
P: 8,623
You need to programmatically redefine the Control Source of the Combo Box - the following Code will work:
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Access.ComboBox
  2. Dim strSQL As String
  3. Dim QuerySet As DAO.Recordset
  4. Dim MyDB As DAO.Database
  5. Dim strControlSource As String
  6.  
  7. strControlSource = "SELECT TblLanguage.Language, TblLanguage.LanguageCode FROM TblLanguage " & _
  8.                    "WHERE (((TblLanguage.LanguageID)<>" & [TempVars]![DefaultLanguageID] & ") " & _
  9.                    "AND ((TblLanguage.LanguageUsed)=True)) ORDER BY TblLanguage.Language;"
  10.  
  11. Set ctl = Me![<Your Combo Box>]
  12. ctl.RowSource = strControlSource
  13.  
  14. Set MyDB = CurrentDb
  15. strSQL = ctl.RowSource
  16.  
  17. Set QuerySet = MyDB.OpenRecordset(strSQL)
  18.  
  19. With QuerySet
  20.   Do While Not .EOF
  21.     Debug.Print ![Language], ![LanguageCode]
  22.       .MoveNext
  23.   Loop
  24. End With
  25.  
  26. QuerySet.Close
  27. 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.
Sep 17 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
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
Sep 17 '17 #3

Rabbit
Expert Mod 10K+
P: 12,357
You'll need a wrapper function to use in the row sources

Expand|Select|Wrap|Line Numbers
  1. Function GetTempVar(strKey As String) As Variant
  2.     GetTempVar = TempVars(strKey)
  3. End Function
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table1
  3. 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.
Sep 17 '17 #4

ADezii
Expert 5K+
P: 8,623
  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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT TblLanguage.Language, TblLanguage.LanguageCode
    2. FROM TblLanguage
    3. WHERE (((TblLanguage.LanguageID)<>Tempvars!DefaultLanguageID)
    4. And ((TblLanguage.LanguageUsed)=True))
    5. ORDER BY TblLanguage.Language;
  3. To now change it into a Form that a Recordset can be built on would be:
    Expand|Select|Wrap|Line Numbers
    1. Dim ctl As Access.ComboBox
    2. Dim strSQL As String
    3. Dim QuerySet As DAO.Recordset
    4. Dim MyDB As DAO.Database
    5. Dim strControlSource As String
    6.  
    7. Set ctl = Me![Combo5]
    8.  
    9. Set MyDB = CurrentDb
    10.  
    11. strSQL = Replace(ctl.RowSource, CurrentDb.QueryDefs("qryLanguage").Parameters(0).Name, _
    12.                  Eval(CurrentDb.QueryDefs("qryLanguage").Parameters(0).Name))
    13.  
    14. Set QuerySet = MyDB.OpenRecordset(strSQL)
    15.  
    16. With QuerySet
    17.   Do While Not .EOF
    18.     Debug.Print ![Language], ![LanguageCode]
    19.       .MoveNext
    20.   Loop
    21. End With
    22.  
    23. QuerySet.Close
    24. Set QuerySet = Nothing
  4. Not really sure if this helps, but I'll throw it out there anyway.
Sep 17 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
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
Sep 17 '17 #6

NeoPa
Expert Mod 15k+
P: 31,419
How about using :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Language]
  2.        , [LanguageCode]
  3. FROM     [TblLanguage]
  4. WHERE    ([LanguageID]<>Val(Tempvars!DefaultLanguageID))
  5.   AND    ([LanguageUsed])
  6. ORDER BY [Language]
Notice the use of Val().
Sep 17 '17 #7

ADezii
Expert 5K+
P: 8,623
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim ctl As Access.ComboBox
    2. Dim QuerySet As DAO.Recordset
    3. Dim MyDB As DAO.Database
    4. Dim qdfTemp As DAO.QueryDef
    5. Dim strQueryName As String
    6.  
    7. Set ctl = Me![Combo5]
    8. Set MyDB = CurrentDb
    9.  
    10. Set qdfTemp = CurrentDb.CreateQueryDef("qryTemp", ctl.RowSource)
    11.     qdfTemp.SQL = Replace(ctl.RowSource, CurrentDb.QueryDefs("qryTemp").Parameters(0).Name, _
    12.                   Eval(CurrentDb.QueryDefs("qryTemp").Parameters(0).Name))
    13.  
    14. Set QuerySet = MyDB.OpenRecordset(qdfTemp.SQL)
    15.  
    16. With QuerySet
    17.   Do While Not .EOF
    18.     Debug.Print ![Language], ![LanguageCode]
    19.       .MoveNext
    20.   Loop
    21. End With
    22.  
    23. CurrentDb.QueryDefs.Delete "qryTemp"
    24.  
    25. QuerySet.Close
    26. Set QuerySet = Nothing
    27.  
Sep 17 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
'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
Sep 17 '17 #9

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 17 '17 #10

PhilOfWalton
Expert 100+
P: 1,430
Thanks

I'll do both
Sep 17 '17 #11

Rabbit
Expert Mod 10K+
P: 12,357
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:
Expand|Select|Wrap|Line Numbers
  1. For i = 0 To UBound(TempVars)
  2.     strSQL = Replace(strSQL, "[TempVars]![" & TempVars(i).name & "]", TempVars(i).value)
  3. 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.
Sep 17 '17 #12

NeoPa
Expert Mod 15k+
P: 31,419
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).
Sep 18 '17 #13

PhilOfWalton
Expert 100+
P: 1,430
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.

Expand|Select|Wrap|Line Numbers
  1.     ' We need to add a QueryDef so let's make sure it's not there first
  2.     For Each QDF In MyDb.QueryDefs
  3.         If QDF.Name = "PP_Test" Then
  4.             MyDb.QueryDefs.Delete QDF.Name
  5.             Exit For
  6.         End If
  7.     Next QDF
  8.  
  9.     ' Replace the Name of the Tempvars with it's value
  10.     StrSQL = Ctl.RowSource
  11.     Set QDF = MyDb.CreateQueryDef("PP_Test", StrSQL)
  12.     For Each Param In QDF.Parameters
  13.         Param.Value = Eval(Param.Name)
  14.         StrSQL = Replace(StrSQL, Param.Name, Param.Value)
  15.     Next
  16.  
  17.     Set QuerySet = MyDb.OpenRecordset(StrSQL)           ' Create a Query from the modified rowsource
  18.  
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
Sep 18 '17 #14

ADezii
Expert 5K+
P: 8,623
Phil, have you tried using a Temporary QueryDef (Line# 3)?
Expand|Select|Wrap|Line Numbers
  1. 'Replace the Name of the Tempvars with it's value
  2. StrSQL = Ctl.RowSource
  3. Set QDF = MyDb.CreateQueryDef("", StrSQL)
  4.  
  5. For Each Param In QDF.Parameters
  6.   Param.Value = Eval(Param.Name)
  7.   StrSQL = Replace(StrSQL, Param.Name, Param.Value)
  8. Next
  9.  
  10. Set QuerySet = MyDb.OpenRecordset(StrSQL)  
Sep 18 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
Brilliant

Works a treat

Thanks,

Phil
Sep 18 '17 #16

ADezii
Expert 5K+
P: 8,623
You're welcome, Phil. Good Luck with your Project.
Sep 18 '17 #17

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 18 '17 #18

PhilOfWalton
Expert 100+
P: 1,430
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
Sep 18 '17 #19

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 18 '17 #20

PhilOfWalton
Expert 100+
P: 1,430
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
Sep 18 '17 #21

NeoPa
Expert Mod 15k+
P: 31,419
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.
Sep 18 '17 #22

ADezii
Expert 5K+
P: 8,623
@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.
Sep 19 '17 #23

NeoPa
Expert Mod 15k+
P: 31,419
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).
Sep 19 '17 #24

Post your reply

Sign in to post your reply or Sign up for a free account.