[AccessD] Populate Unbound Listbox from SQL Server RecordsetHelp.

Asger,

This is exactly what I ended up doing, I had a bit of bother with it a first, but that was on account of user stupidity (I am the user by the way! <g>). I hadn't set up the listbox properties correctly WRT bound column and number of columns visible etc, Once i did that all is good.

Thanks for getting back to me and clarifying that.

cheers
Darryl.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Asger Blond
Sent: Tuesday, 19 August 2008 8:26 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Populate Unbound Listbox from SQL Server
RecordsetHelp.


Hi Darryl,

Why use a recordset here? Why not just set the RowSource for your listbox to
strSQL, like this:

strSQL = ""
strSQL = strSQL & "SELECT BaselineSource, BaselineSourceID FROM
TFCT_tblBaselineSource"
strSQL = strSQL & " ORDER BY SortSequence;"
[Forms]![ITFCT_frmStartHere].lstbxAPLIDxUser.RowSource = strSQL

Asger


-----Oprindelig meddelelse-----
Fra: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] På vegne af Darryl Collins
Sendt: 19. august 2008 02:35
Til: 'Access Developers discussion and problem solving'
Emne: [AccessD] Populate Unbound Listbox from SQL Server Recordset Help.

Hi all,

I think I am getting my brain bogged down in all this and fouling something
simple up.  In short I want to use an ADO connection to return a RecordSet
to populate an unbound listbox on an Access Form.  And I cannot seem to get
it to work.  I do this all the time in Excel - that is copy a recordset to a
worksheet.  Can you populate an unbound listbox using the same concept?
Everything seems to work except the 'populate RowSource in listbox with
recordset data' bit. hmmmm.

'''========== IN EXCEL I USE THIS =======================

Option Explicit
Option Private Module

Sub DL_MECTable()

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

<non relevant code snipped>

Set cnn = New ADODB.Connection
Set rst = New ADODB.Recordset
Set gobjXL = Application.ThisWorkbook

strSQL = ""
strSQL = strSQL & "SELECT BaselineSource, BaselineSourceID FROM
ITFCT_tblBaselineSource"
strSQL = strSQL & " ORDER BY SortSequence;"

With rst
    .Source = strSQL
    .ActiveConnection = DbADOConStr
    .CursorType = adOpenForwardOnly
    .LockType = adLockReadOnly
    .CursorLocation = adUseClient
    .Open Options:=adCmdText
    '.Open Options:=adCmdStoredProc
End With

Sheet2.Range("D13").CopyFromRecordset rst

<non relevant code snipped>

End Sub

'=====================================================


in Access I was using the same ADO connection code (which works fine) and
then:

[Forms]![ITFCT_frmStartHere].lstbxAPLIDxUser.RowSource = rst

but this goes into error.  I think I am fouling something up there.

Anyone got any pointers here?

Regards

Darryl.


This email and any attachments may contain privileged and confidential
information
and are intended for the named addressee only. If you have received this
e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this
e-mail
has been sent to you in error. It is your responsibility to check this
e-mail and
any attachments for viruses.  No warranty is made that this material is free
from
computer virus or any other defect or error.  Any loss/damage incurred by
using this
material is not the sender's responsibility.  The sender's entire liability
will be
limited to resupplying the material.


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.