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 mustduplicate the list of query statements. So you’ll use in the first in the first situation a query like:
SELECT * FROM PUBLISHERSIn the other situation you’ll use a query like:
SELECT * FROM PUBLISHERS WHERE COUNTRY = ?But you can use just one query structured in dual mode:
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:
Dim lConn As ADODB.ConnectionDim lCmd As ADODB.CommandDim lRs As ADODB.RecordsetDim lParAllRecords as LongDim lParSingleRecord as LongSet lConn = New ADODB.ConnectionlConn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial " _ & "Catalog=PUBS;Data Source=(local)"lConn.CursorLocation = adUseClientSet lCmd = New ADODB.CommandSet lCmd.ActiveConnection = lConnlCmd.CommandText = txtStatement.Text'if you want all records:lParAllRecords = 1lParSingleRecord = 0 'not important in the first use'if you want a single record:lParAllRecords = 0lParSingleRecord = "Germany" 'the correct value to retrieve one recordlCmd.Parameters(0).Value = lParSingleRecordlCmd.Parameters(1).Value = lParAllRecordsSet lRs = lCmd.Execute
