Filling a list with an open recordset

[WantToKnowMore]

I've been using Access 2002 Desktop Developer's Handbook successfully, but on page 367, I'm a bit stuck.

I am trying to use a control on a form to limit a recordset I create to populate a combo box. I get the records just fine, and set the combo box's RowSourceType to "Table/Query" and set its Recordset to my new ADO recordset. I then can see the rows entered into my combo box, but when I select one, the data just disappears.

Here is my code

Sub FillStrategiesComboBox()
Dim strSQL As String
Dim rstAvailStrategies As ADODB.Recordset
Set rstAvailStrategies = New ADODB.Recordset

' get rows for my recordset; PE in where clause comes from this form

strSQL = "SELECT tblStrategies.StrategyID, tblStrategiesList.Code, tblStrategiesList.Description, " _
& "tblPEList.PECode FROM (tblStrategiesList INNER JOIN tblStrategies ON " _
& "tblStrategiesList.StrategyListID = tblStrategies.StrategyListID) INNER JOIN " _
& "(tblPEList INNER JOIN tblPE_StrategiesMatch ON tblPEList.PE_ID = " _
& "tblPE_StrategiesMatch.PE_ID) ON tblStrategies.StrategyID = tblPE_StrategiesMatch.StrategyID " _
& "WHERE (tblPEList.PECode = '" & [Forms]![frmPTAMain]![PE] & " ') " _
& "ORDER BY tblStrategiesList.Code"

' open the recordset
rstAvailStrategies.Open _
Source:=strSQL, _
ActiveConnection:=CurrentProject.Connection, _
CursorType:=adOpenStatic, _
Options:=adCmdText

'set the combo box to my recordset

cboStrategy.RowSourceType = "Table/Query"
Set cboStrategy.Recordset = rstAvailStrategies
' don't explicitly close the recordset here.
' It must be open for the list box to use
'
Set rstAvailStrategies = Nothing
End Sub

This is my first posting to this site, I hope that I can get some good responses and hopefully share my knowledge too. Thanks

 

[KenReay]

Hi

It may not be your SQL to blame, have you checked your bound column setting

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[PHV]

And what about this ?
Sub FillStrategiesComboBox()
Dim strSQL As String
' get rows for my recordset; PE in where clause comes from this form
strSQL = "SELECT tblStrategies.StrategyID, tblStrategiesList.Code, tblStrategiesList.Description, " _
& "tblPEList.PECode FROM (tblStrategiesList INNER JOIN tblStrategies ON " _
& "tblStrategiesList.StrategyListID = tblStrategies.StrategyListID) INNER JOIN " _
& "(tblPEList INNER JOIN tblPE_StrategiesMatch ON tblPEList.PE_ID = " _
& "tblPE_StrategiesMatch.PE_ID) ON tblStrategies.StrategyID = tblPE_StrategiesMatch.StrategyID " _
& "WHERE (tblPEList.PECode = '" & [Forms]![frmPTAMain]![PE] & " ') " _
& "ORDER BY tblStrategiesList.Code"
'set the combo box to my query
cboStrategy.RowSourceType = "Table/Query"
cboStrategy.RowSource = strSQL
cboStrategy.ReQuery
End Sub

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884