>To recap what I am trying to do and what you suggested
>
>I want a drop down list of orders for a site. Data is in a table. You suggest using Rowsource = 3 (SQL select to a cursor) instead of 5 (SQL select to an array). To be sure I understand what I am doing.
>
>1. Can I do the SQL select and in the init method of the form since it invoves 2 tables, 2 wheres and several fields and it is easier to see what I am coding? If so is the Rowsource the name of the cursor?
>
>2. Where do I put the requery command? In the method that does the save, i.e do I use something like
>
>Thisform.comboname.requery()
>
>3. Are there an performance penalties I should be aware off?
>
>4. First time this is used there is no data. What is a good way to handle this situation,
>i.e. check if _tally is zero and insert a blank line into the cursor or is there a better way? In this case what is a good method to not seek for a value and then get last record in table?
>Thansk for your help so far.
1.Yes you can do SQL select in init or even when you want. RowSource is not the name of the select cursor, it's the SQL commadn itself.
2.Yes requery commadn is :
Thisform.comboname.requery()
You put it wherever makes sense (and makes sense in a lot of places - ie: selecting orders for customer and customerID is typed in a textbox, then it might in textbox.lostfocus. Or it should change based on another listbox/combo's selection then might be in its InterActtiveChange etc)
3.Penalties. Good question. It's always possible to create performance penalties. However think that as any SQL and the SQL you use to select into array. If that one is slow then should be this one, if that's fast then this one is too.
4.SQL cursors might return no data. Then you simply check for eof('cursorname'). It's just a matter of setting a property (listindex) or locating a record in the cursor to make one selected or not. You might set ListIndex to 0 or to recno() of cursor if you want one selected.
You always know if you have 0 records or n Records, if selected 1st or the 20000th. And despite an array you might get the 1st column value or the last column value from directly cursor (doesn't matter listbox has 1 column or 5 or 10).
Anyway getting harder to tell in English :)
Assume this as a very basic sample. We have one textbox (Text1), 1 listbox (List1), and 2 CommandButtons on form :
With this.list1
.RowSourceType = 3
.RowSource = "select cust_id as customerID, "+;
"order_date as oDate,* "+;
" from orders"+;
" where cust_id == thisform.text1.Value"+;
" into cursor myCursor"
.ColumnCount = 2
.ColumnWidths = "100,100"
endwith
With thisform.list1
.Requery()
select myCursor
.ListIndex = Iif(Eof(),0,Recno())
endwith
select myCursor
scatter name oRec
MessageBox(myCursor.Order_id)
You don't have other pains like converting values, using things like List(.ListIndex,nColumn) etc etc.
And finally for a listbox of course a grid is the best alternative IMHO :)
Cetin