List Box rowsource = ADO query

[Corey]

I have a list box that I want to base its rowsource on an ADO recordset. The
user selects a Customer Number then the query goes to the server and looks up
the relevant information and displays it in the list box.

I have successfully done this using:
a. local tables
b. ODBC linked tables
c. populating a value list from the ADO recordset

However, I would like to base the rowsource directly on the ADO recordset.
Does anyone have any ideas? The list keeps coming back empty, no error is
reported. I use the same SQL to populate the value list...

Code:
stSQL = "SELECT PRKEY, PFCT1, PSDTE, PSEDT INTO TMP_ESP " & _
"FROM PPCS82F.ESPL01 " & _
"WHERE PRKEY LIKE '%" & Me!Customer & "' " & _
"AND PMETH='1' " & _
"ORDER BY PRKEY"

rsESP.Open stSQL, cn

Me!lbMeth4.RowSource = stSQL

 

[Arvin Meyer [MVP]]

It depends upon where you are putting the select statement. The form's open
event is the logical place to use code. Also, to be sure that the list box
loads your select statement, try adding:

Me.lbMeth4.Requery

after your other code.

 

[Corey]

I have the requery further down. it didnt seem to help.

I dont have anything in the forms.open statement as I need to wait for the
user to select a customer. Any other suggestions...

 

[Van T. Dinh]

Try:

Set Me.lbMeth4.Recordset = rsESP


after you open the Recordset and delete the statement

Me!lbMeth4.RowSource = stSQL

I assume you use A2002 or later. This certainly won't work in A97 and I am
not sure about A2000.

 

[Arvin Meyer [MVP]]

Are you sure that your connection string is right? Something like

strConnect = "Provider=SQLOLEDB;Data Source=ServerName;" & _
"Initial Catalog=DatabaseName;" & _
"User ID=Arvin;password=Icannottell4now"

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Corey]

My connection string is working for everything else, but here it is:

Dim strConn As String
strConn = "TWBPCS.TYPAC.COM.AU"

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=IBMDA400;Data source=TWBPCS.TYPAC.COM.AU;"

Regards, Corey.

 

[Van T. Dinh]

Did the Set statement work for you?

 

[Van T. Dinh]

Unfortunately, I know nothing about AS400 running BPCS.

It is possible that you need to set the Recordset type. Here is the actual
code I use in one of my working databases (SQL Server 2000 BE):

********
Private Sub PopulatecboProductID()

Dim rsa As ADODB.Recordset
Dim strSQL As String

On Error GoTo PopulatecboProductID_Err
Set rsa = New ADODB.Recordset
With rsa
.ActiveConnection = fnGetCnnSQL()
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.CacheSize = 16

' Populate Product Code combobox
strSQL = "SELECT P.ProductID, P.ProdCode, P.ProdDesc " & _
" FROM dbo.tblProduct P WITH (NOLOCK)" & _
" ORDER BY P.ProdCode"
.Open strSQL, , , , adCmdText
Set Me.cboProductID.Recordset = rsa
.Close
End With

PopulatecboProductID_Exit:
On Error Resume Next
Exit Sub

PopulatecboProductID_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description & vbCrLf &
vbCrLf & _
"(Programmer's note: Form_frmProduct_View.PopulatecboProductID)",
_
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume PopulatecboProductID_Exit
End Sub
********