Solved

Parameterized query as row source

Posted on 2006-10-19
32
2,544 Views
Last Modified: 2012-08-13
Hello and thank you for reading my question.

I would like to use a parameterized query as my rowsource for a combo box. The query requires two parameters, both text.

I have tried using the following, but it does not work: that is to say no errors occur, but no results are returned when I know that there should be 4 (that is what I get when I double click on the query and enter the two parameters.

Here is the relevant code:

strParameter1 = "Florida"
strParameter2 = "Miami"
Me.cboLocations.RowSource = "EXEC qryLocationsByStateAndCity '" & strParameter1 & "','" & strParameter2 & "'"
Me.cboLocations.Requery

Would someone tell me what I am doing wrong please?

Thank you,

FtB
0
Comment
Question by:fritz_the_blank
  • 13
  • 11
  • 5
  • +2
32 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17768578
can you show the procedure code, the error will be probably there.

usually, with stored procedures, if they do not only do a single SELECT statement, adding SET NOCOUNT ON as first statement in the procedure will solve the problem
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17768591
Where is this query? And what is the backend? is it SQL Server?

If not, then its just SELECT in Access and parameters are built up as part of where clause
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17768701
The back end is MS Access. I know that there is no error with the query because if I double click on it, enter the requested parameters, it returns the desired results. This leads me to believe that the problem lies with how I am passing the parameters to the query in the code above.

*****************************************
qryLocationsByStateAndCity

PARAMETERS strParameter1 Text ( 255 ), strParameter2 Text ( 255 );
SELECT intLocationID, strLocation FROM tblLocations
WHERE strState = strParameter1 AND strCity = strParameter2
ORDER BY strLocation;

******************************************

FtB
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17768710
Change your rowsource to be like this, forget the query

Me.cboLocations.RowSource = "SELECT intLocationID, strLocation FROM tblLocations " & _
                                             "WHERE strState = '" & strParameter1 & "' AND strCity = '" & strParameter2 & "' ORDER BY strLocation"

0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17768745
The problem with that is the following. Some queries will be much more complicated (number of joins, conditions and etc.) that will be used many places throughout the application. These queries will also serve for the web application as well. So, it is much better to have the query in one place to simplify code maintenance and ensure consistency accross the client-server and web applications.

FtB
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17768783
So why then cant u have a query that doesnt have parameters
then read it and change it at runtime?
e.g.

query saved as (still can run the query standalone)

SELECT intLocationID, strLocation FROM tblLocations
WHERE strState = [PAR1] AND strCity = [PAR2]
ORDER BY strLocation


Dim qdf as DAO.QueryDef
Dim sSql as String

set qdf = CurrentDB.QueryDefs("myquery")
sSql = REPLACE(qdf.SQL,"[PAR1]","'" & strParameter1 & "'")
sSql = REPLACE(sSQL,"[PAR2]","'" & strParameter2 & "'")

Me.cboLocations.RowSource = sSql
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17768895
The replace is an interesting idea, but since the query is looking for two parameters, the code won't fire.

Now, I can do something like:

        qdf.Parameters(strParameter1) = "FL"
       qdf.Parameters(strParameter2) = "Miami"


but then I don't know how to use the qdf as the recordsource.  using

Me.cboLocations.RowSource = qdf

doesn't work, nor does:

Me.cboLocations.RowSource = qdf.Execute


FtB
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17768909
No u cant use querydef like that

What I did was take the query and used REPLACE command to change the parameter prompts
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17769134
That means that I would have to remove parameters from all existing queries?

FtB
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17769960
No u dont, u can create a function to return the query portion
e.g.

add this to a module

Public Function ParseSQL(ByVal sQuery As String) As String

    Dim qdf As DAO.QueryDef
   
    Set qdf = CurrentDb.QueryDefs(sQuery)
   
    ParseSQL = Mid$(qdf.SQL, InStr(1, UCase$(qdf.SQL), "SELECT "))
End Function



Now u can set your query for the combo like this



Dim sSql as String

set qdf = ParseSQL("qryLocationsByStateAndCity")
sSql = REPLACE(sSQL,"strParameter1","'" & strParameter1 & "'")
sSql = REPLACE(sSQL,"strParameter2","'" & strParameter2 & "'")

Me.cboLocations.RowSource = sSql

0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17770659
I had really hoped that one of these would have worked:

strParameter1 = "Florida"
strParameter2 = "Miami"
strSQL = "EXEC qryLocationsByStateAndCity '" & strParameter1 & "','" & strParameter2 & "'"
Me.cboLocations.RowSource = strSQL
Me.cboLocations.Requery

Or:

strParameter1 = "Florida"
strParameter2 = "Miami"
strSQL = "qryLocationsByStateAndCity '" & strParameter1 & "','" & strParameter2 & "'"
Me.cboLocations.RowSource = strSQL
Me.cboLocations.Requery

No dice either way--the query acts as if it does not see either parameter; it returns no rows and no errors.

FtB
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17770686
No luck with this either:

Dim objQDF As DAO.QueryDef
Set objQDF = CurrentDb.QueryDefs("qryLocationsByStateAndCity")
objQDF.Parameters(0) = strParameter1
objQDF.Parameters(1) = strParameter2
Me.cboLocations.RowSource = "EXEC qryLocationsByStateAndCity"
Me.cboLocations.Requery


FtB
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17771033
Have you tried simply wrapping your actual parameter entry like so?

PARAMETERS [enter Something] Text ( 255 )

or

PARAMETERS [forms]![frmTest]![myField] Text ( 255 );
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 17771044
First of all a caveat, I have never used MS Access a front-end and have not used an MS Access database for well over 8 years.  Having said that can you tell us:
A. Can you confirm you using MS Access as a front-end? If I overlooked it, I am sorry.
B. Are you using (or attempting to use) ADO or DAO
C. Take the combobox and the parameters out of the picture.  Do you get results returned in the query?  In other words, if you do this:

Dim cn as ADODB.Connection      -- Assuming this was ADO
Set cn = New ADODB.Connection
cn.Open "Your Connection goes here"

Dim rs as ADODB.Recordset  
Set rs = cn.Execute("EXEC qryLocationsByStateAndCity 'Florida', 'Miami')
If NOT rs.EOF Then
   MsgBox rs.Fields("strLocation").Value
End If
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

D. If the answer is yes than add the parameters, as in:
strParameter1 = "Florida"
strParameter2 = "Miami"
Dim cn as ADODB.Connection      -- Assuming this was ADO
Set cn = New ADODB.Connection
cn.Open "Your Connection goes here"

Dim rs as ADODB.Recordset  
Set rs = cn.Execute("EXEC qryLocationsByStateAndCity '" & strParameter1 & "','" & strParameter2 & "'")
If NOT rs.EOF Then
   MsgBox rs.Fields("strLocation").Value
End If
rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

If the answer is no, as I suspect, than you will have to use the Command object to pass the parameters.  This is the way I have always done it.
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17771060
*****************************************
qryLocationsByStateAndCity

PARAMETERS [strParameter1] Text ( 255 ), [strParameter2] Text ( 255 );
SELECT intLocationID, strLocation FROM tblLocations
WHERE strState = [strParameter1] AND strCity = [strParameter2]
ORDER BY strLocation;

******************************************

J
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17771562
From this


strParameter1 = "Florida"
strParameter2 = "Miami"
strSQL = "EXEC qryLocationsByStateAndCity '" & strParameter1 & "','" & strParameter2 & "'"
Me.cboLocations.RowSource = strSQL
Me.cboLocations.Requery


to this, still minimal work no?

strParameter1 = "Florida"
strParameter2 = "Miami"

set qdf = ParseSQL("qryLocationsByStateAndCity")
strSql = REPLACE(strSQL,"strParameter1","'" & strParameter1 & "'")
strSql = REPLACE(strSQL,"strParameter2","'" & strParameter2 & "'")

Me.cboLocations.RowSource = strSQL


0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 65

Expert Comment

by:rockiroads
ID: 17771599
The syntax "exec" is surely used for stored procedures no?
Im assuming the frontends here are Access also
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17774285
Some clarifications to the questions above:

1) Unfortunately, I am using Access for the front and back end (no choice)
2) If I run the query in the query designer, it works perfectly fine (it prompts me for the two parameters and returns the results that I expect
3) If I create a command object in VB, append the parameters and then output the results to a recordset, it works fine (in this case 26 records, which is correct and the is what I get by running it in the query designer as well.
4) If I use a command object and a recordset, setting the row source to the recordset throws an error (data type mismatch or some thing) I have seen examples where folks build the list from the recordset (traverse through the recordset and populate the list one item at a time. I can do this but would prefer not to).
5) My problem with using the replace method is that while it works fine for text and date parameters, it would not work for Boolean and numeric values. Since there are a substantial number of queries in this project, I am looking for the cleanest way to do this that works both with Access as the front end as well as ASP for the front end.
6) I am using EXEC here because all of the examples that I have seen with QueryDef use it
7) I am open to using ADO or DAO as long as I can pass the parameters programmatically and set the result to the RowSource

I should say also that I use debug.print and I have determined that the recordset state is 1 and that the record count is 26 when I use a recordset, but I can't seem to get the Rowsource to accept the recordset as the source.

Thank you once again to all who are posting. I really appreciate it.

FtB
0
 
LVL 34

Accepted Solution

by:
jefftwilley earned 300 total points
ID: 17774380
try

set me!combobox.recordset = rs
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17775463
I am going to have to plead ignorance, first.  However, if I was to speculate, I would have to say that the MS Access ComboBox (in much the same way as the VB MSGrid) does not appreciate you passing it a firehose cursor and a CursorLocation of adUseServer.  This is the result of using Execute from an ADO Connection or Command.  So if you want to go down this path I would suggest using the Recordset's Open method to pass the Command object as in:

Set Cmd = New ADODB.Command
With cmd
    Set .ActiveConnection = cn
    .CommandType = adCmdStoredProc
    .CommandText = "qryLocationsByStateAndCity"
    ' Add all parameters here but don't Execute it
End With

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.Open Cmd

' Than try setting it to the MS Access Combobox here - after verifying first you have the desired rows that is  :)
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17776186
@jefftwilley--

That seems to have helped greatly. I am now seeing records returned, but I am not getting the row that I want displayed in the combo box.

If I am using the recordset as the source, what shold I set for the properties in the form designer for the following attributes of the combo object:

Control Source
Row Source Type
Row Source
Bound Column

At the moment, it appears to be grabbing the last field of the select by default.

FtB
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17776422
ok..high level

If your query/stored proc, manual value list is to represent 3 columns in your combo box, then you need to make your column Count 3.

the bound column is the one that you'll be using as the actual value. If you reference the value of this combo box from any other control, it will grab the bound column's value unless you specify otherwise using
me.combobox.Column(0)  0 being column 1

The recordsource for your combo box will provide the data as you already understand.
Rowsource type should already be set....table/query would be my guess.

See how that goes for you
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17776530
>>If your query/stored proc, manual value list is to represent 3 columns in your combo box, then you need to make your column Count 3.<<

Done.

>>Rowsource type should already be set....table/query would be my guess.<<

Done as well.


>>me.combobox.Column(0)  0 being column 1<<

Done as well.

As I recall, there may be some need to pass the recordset field names to the combo box. I am looking into that now.

FtB

0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17776803
Okay, it appears that I may be trying to do the impossible--Access does not wish to accept a recordset as the source. I had to do this to get it working:

rst.Open objCommand, , adOpenStatic, adLockReadOnly
strListItems = rst.GetString(adClipString,ColumnDelimeter:=""";""", RowDelimeter:=""";""")
rst.Close
set rst =Nothing
strListItems = """" & Left(strListItems, Len(strListItems) - 2)
Me.cboLocations.RowSource = strListItems

That is okay, but it seems awfully inefficient....

FtB
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 100 total points
ID: 17776843
Since your using Access, I still dont believe u can use parameter queries as a rowsource
It seems u have got something going though
My solution was just to parse the SQL in your parameter query, remove PARAMETERS and replacing parameters. Dont know how fast that is going to be, probably the same as yours, dont know
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17776877
Dont think my solution is that elegant either, its more of a workaround
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17777476
My solution is not great, but it does allow me to use parameterized queries with parameters of all sorts.

I can't believe that Access makes this so painful.....
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17777514
I guess its not made for anything that racks its brains
0
 
LVL 34

Expert Comment

by:jefftwilley
ID: 17777561
I'm just glad you got it going....don't EVEN throw any points over here....I'd give them back
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17777594
unless u got some lady luck as Eng would need that against Aus tomorrow :)
good nite all
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17791830
I don't want to delete the question since so many contributed to this thread. Does anyone object if I split the points based on what comments helped me the most?

FtB
0
 
LVL 46

Author Comment

by:fritz_the_blank
ID: 17867891
Thanks to all for helping. Ultimately I used a a command object with my parameterized query to create the recordset. Next, Iused the GetString() method to create a string for the source.

I hate it, but it will have to do for now.

FtB
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now