Dr.K
12-12-2007, 10:18 AM
The app I'm working on uses two different DBs: one contains lists of Security IDs, and the other DB contains the TickerSymbols, ShortNames and LongNames that corrospond to the IDs.
Right now, my code gets a list of IDs from DBA (RecordesetA), and then gets a recordset from DBB that contains all of the info for those securites, using a WHERE IN list to pull only the relevant data. The problem is that I have no way of sorting the records: what would be ideal is if the Recordset from DBB came back in the exact same order as the IN list.
My workaround is to just use FIND to pull the correct data from RecordsetB. Works perfectly, but the code would be a simpler if I could just order RecordsetB by the list used to create it.
strSQL = "SELECT [tblLineUpItems].[Position], [tblLineUpItems].[SecID]" _
& " FROM [tblLineUpItems]" _
& " WHERE ((([tblLineUpItems].[Position]) <> 0)" _
& " And (([tblLineUpItems].[LineUpID]) = " & LineUpID & "))" _
& " ORDER BY [tblLineUpItems].[Position];"
rstAcc.Open strSQL, conAcc, adOpenStatic, adLockReadOnly
strList = "("
rstAcc.movefirst
Do
strList = strList & rstAcc!SecID
rstAcc.movenext
If rstAcc.EOF = False Then strList = strList & ", "
Loop While rstAcc.EOF = False
strList = strList & ")"
strSQL = "SELECT MSE_SEC_ID, MSE_TCKR_SYMB, MSE_SEC_SHT_NM, MSE_SEC_LNG_NM" _
& " FROM MSECURTY" _
& " WHERE MSE_SEC_ID IN " & strList
rstDM.Open strSQL, conDM, adOpenStatic, adLockReadOnly
With ufoBuild1.listLineUp
rstAcc.movefirst
Let x = 0
Do
rstDM.movefirst
rstDM.Find ("MSE_SEC_ID = " & rstAcc!SecID)
.AddItem
.List(x, 0) = Null2Empty(rstDM!MSE_TCKR_SYMB)
.List(x, 1) = rstDM!MSE_SEC_SHT_NM
.List(x, 2) = rstDM!MSE_SEC_LNG_NM
.List(x, 3) = rstDM!MSE_SEC_ID
x = x + 1
rstAcc.movenext
Loop Until rstAcc.EOF = True
End With
rstAcc.Close
rstDM.Close
Right now, my code gets a list of IDs from DBA (RecordesetA), and then gets a recordset from DBB that contains all of the info for those securites, using a WHERE IN list to pull only the relevant data. The problem is that I have no way of sorting the records: what would be ideal is if the Recordset from DBB came back in the exact same order as the IN list.
My workaround is to just use FIND to pull the correct data from RecordsetB. Works perfectly, but the code would be a simpler if I could just order RecordsetB by the list used to create it.
strSQL = "SELECT [tblLineUpItems].[Position], [tblLineUpItems].[SecID]" _
& " FROM [tblLineUpItems]" _
& " WHERE ((([tblLineUpItems].[Position]) <> 0)" _
& " And (([tblLineUpItems].[LineUpID]) = " & LineUpID & "))" _
& " ORDER BY [tblLineUpItems].[Position];"
rstAcc.Open strSQL, conAcc, adOpenStatic, adLockReadOnly
strList = "("
rstAcc.movefirst
Do
strList = strList & rstAcc!SecID
rstAcc.movenext
If rstAcc.EOF = False Then strList = strList & ", "
Loop While rstAcc.EOF = False
strList = strList & ")"
strSQL = "SELECT MSE_SEC_ID, MSE_TCKR_SYMB, MSE_SEC_SHT_NM, MSE_SEC_LNG_NM" _
& " FROM MSECURTY" _
& " WHERE MSE_SEC_ID IN " & strList
rstDM.Open strSQL, conDM, adOpenStatic, adLockReadOnly
With ufoBuild1.listLineUp
rstAcc.movefirst
Let x = 0
Do
rstDM.movefirst
rstDM.Find ("MSE_SEC_ID = " & rstAcc!SecID)
.AddItem
.List(x, 0) = Null2Empty(rstDM!MSE_TCKR_SYMB)
.List(x, 1) = rstDM!MSE_SEC_SHT_NM
.List(x, 2) = rstDM!MSE_SEC_LNG_NM
.List(x, 3) = rstDM!MSE_SEC_ID
x = x + 1
rstAcc.movenext
Loop Until rstAcc.EOF = True
End With
rstAcc.Close
rstDM.Close