List Box rowsource = ADO query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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...
 
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.
 
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.
 
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
********
 
Back
Top