List Box rowsource = ADO query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Corey - 29 Nov 2006 04:20 GMT

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

Reply to this Message

Arvin Meyer [MVP] - 29 Nov 2006 04:48 GMT

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.

Signature

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

>I have a list box that I want to base its rowsource on an ADO recordset.
>The
[quoted text clipped - 21 lines]
>
> Me!lbMeth4.RowSource = stSQL

Reply to this Message

Corey - 29 Nov 2006 05:31 GMT

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...

> 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
[quoted text clipped - 28 lines]
> >
> > Me!lbMeth4.RowSource = stSQL

Reply to this Message

Arvin Meyer [MVP] - 29 Nov 2006 14:30 GMT

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"

Signature

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

>I have the requery further down. it didnt seem to help.
>
[quoted text clipped - 38 lines]
>> >
>> > Me!lbMeth4.RowSource = stSQL

Reply to this Message

Corey - 29 Nov 2006 20:54 GMT

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.

> Are you sure that your connection string is right? Something like
>
[quoted text clipped - 46 lines]
>>> >
>>> > Me!lbMeth4.RowSource = stSQL

Reply to this Message

Van T. Dinh - 29 Nov 2006 21:14 GMT

Did the Set statement work for you?

Signature

Van T. Dinh
MVP (Access)

> My connection string is working for everything else, but here it is:
>
[quoted text clipped - 6 lines]
>
> Regards, Corey.

Reply to this Message

Van T. Dinh - 29 Nov 2006 05:51 GMT

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.

Signature

HTH
Van T. Dinh
MVP (Access)

>I have a list box that I want to base its rowsource on an ADO recordset.
>The
[quoted text clipped - 21 lines]
>
> Me!lbMeth4.RowSource = stSQL

Reply to this Message