Binaryworld - A dual use of a lookup query ... [ VB -> ADO ]

Usually in your application you use two different types of lookup query; the first one is for retrieving a page of records (or all records) to populate a pick list, the other one is to retrieve a single record, i.e. for decoding a code description while user is typing it in a textbox. So, if your queries are dynamically configured and are stored in a DB, you must duplicate the list of query statements. So you'll use in the first in the first situation a query like:

Click here to copy the following block

SELECT * FROM PUBLISHERS

In the other situation you'll use a query like:

Click here to copy the following block

SELECT * FROM PUBLISHERS WHERE COUNTRY = ?

But you can use just one query structured in dual mode:

Click here to copy the following block

SELECT * FROM PUBLISHERS WHERE (COUNTRY = ? OR 1 = ?)

When you want to populate a pick list you'll pass to the second parameter simply a 1 and all records will be retrieved. When you want to decode a single value, you must pass the code to the first parameter and a 0 to the second one. So, if all your queries use always one parameter to decode value, you can write a standard (dual) lookup routine like this:

Click here to copy the following block

Dim lConn As ADODB.Connection
Dim lCmd As ADODB.Command
Dim lRs As ADODB.Recordset
Dim lParAllRecords as Long
Dim lParSingleRecord as Long

Set lConn = New ADODB.Connection
lConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial " _
& "Catalog=PUBS;Data Source=(local)"
lConn.CursorLocation = adUseClient
Set lCmd = New ADODB.Command
Set lCmd.ActiveConnection = lConn
lCmd.CommandText = txtStatement.Text

'if you want all records:
lParAllRecords = 1
lParSingleRecord = 0 'not important in the first use

'if you want a single record:
lParAllRecords = 0
lParSingleRecord = "Germany" 'the correct value to retrieve one record

lCmd.Parameters(0).Value = lParSingleRecord
lCmd.Parameters(1).Value = lParAllRecords
Set lRs = lCmd.Execute