listbox recordsets

[Treva]

I have the code below to query an SQL Server Backend database

The first part uses a public function (populatelst) in a class module (clsCables) to query the database and return an ADODB Recordset to the rsPopLst Recordset in this module.

If I assign the rsPopLst recordset to the RecordSource property of the ListBox (lst). all is fine except that one field in the recorset is held in the database unformatted and I want it displayed formatted.

To do this I create another recordset (rsNewLst), add fields, and then populate this recordset from the rsPopLst recordset (tne nn funtion converts nulls to blanks and the cableref function formats the data). However when I use this new recordset to populate the listbox no data is displayed (the field names are however displayed as headers).

I know the rsNewLst recordset contains the appropriate data (I've checked in the immediate window). I've also found the listbox contains the appropriate number of of rows and that every row contains only null values.

I can't use a string constructed from the data because there is too much of it.

I would appreciate if someone could point me in the right direction.

Thanks

The Code****************************

Private Sub Form_Open(Cancel As Integer)

Dim c As New clsCables
Dim rsPopLst As New ADODB.Recordset
Dim rsNewLst As New ADODB.Recordset


c.CabLetter = "AR" ' Keep the returned data small for testing

Set rsPopLst = c.PopulateLst

rsNewLst.Fields.Append "UniqueID", adVarChar, 9, adFldUpdatable
rsNewLst.Fields.Append "WBox", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "Room", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "Description", adVariant, 50, adFldUpdatable
rsNewLst.Fields.Append "Comment", adVariant, 100, adFldUpdatable
rsNewLst.Fields.Append "CableNo", adVariant, 50, adFldUpdatable
rsNewLst.Fields.Append "DrawingNO", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "LinkRef", adVarChar, 15, adFldUpdatable


rsNewLst.Open

Do While Not rsPopLst.EOF
rsNewLst.AddNew
rsNewLst!UniqueID = nn(Trim(rsPopLst!UniqueID))
rsNewLst!wbox = nn(Trim(CableRef(rsPopLst!UniqueID)))
rsNewLst!Room = nn(Trim(rsPopLst!Room))
rsNewLst!Description = nn(Trim(rsPopLst!Description))
rsNewLst!Comment = nn(Trim(rsPopLst!Comment))
rsNewLst!CableNo = nn(Trim(rsPopLst!CableNo))
rsNewLst!DrawingNo = nn(Trim(rsPopLst!DrawingNo))
rsNewLst!LinkRef = nn(Trim(rsPopLst!LinkRef))
rsNewLst.Update
rsPopLst.MoveNext
Loop

lst.RowSourceType = "Table/Query"
Set lst.Recordset = Nothing

Set lst.Recordset = rsNewLst

End Sub

 

[doco]

You need to set the RowSource property of your list box to the query: you have told it you want to use a 'Table\Query' but have not told it which 'Table/Query'.

Also, Access comes with a function NZ() that will change null values to a given value. EG. =NZ(myField, 0)

 

[Treva]

Thank you for your reply doco, I hadn't heard of the nz() function before.

I don't understand about the Rowsource property. The query is an SQL query in a function in a class module which returns an ADODB rcordset into the rsPopLst recordset in the form module. If I replace the line 'set lstRecordset = rsNewLst' with 'Set lst.Recordsource = rsPopLst' then the list displays correctly but the Wbox field is displayed unformatted.

I think the problem somehow lies in the code where one recordset is copied to another with one field being changed although I cannot explain why this should be.

The database contains some fields which hold the minimum data required, but they need altering to make sense to the user. For example:

The 8th port in switch 116 in wiring cabinet A is held in the database as
A11608
It needs to be displayed on screen as WC-A Switch 116 Port 08.

When the data was in access the cableref function to format the field for display could be included in the SQL query. SQL Server will not support this.

Perhaps this method is not the way to go. I'm open to suggestions.

Thanks

 

[doco]

You need to tell the listbox which table or query you want it to list. Actually, you can set the RowSourceType property at design time and forget it.

You have created your 'recordset' which does not exist in your db as a physical table but a logical table. So, place your new 'table' in the rowsource property of the listbox. Then set the bound column property to the field you wish to display. If it is the first field in the recordset then '1' ... If you know it is going to be 1 all the time, then set that property at design time as well.

With lst
    .RowSourceType = "Table/Query"
    .RowSource = rsNewList
    .BoundColumn = 1
End With

HTH

 

[doco]

Are you wanting to parse the 'Wbox' field? I guess I am not understanding your question.

 

[Treva]

This is the first time i have posted on a forum and probably have not been as clear as to what I'm doing as I need to be.

I have an access based database that records various connections. Most fields in the database are stored as they are required on the screen but two fields, each of which is the primary key in the table, are 'stripped down' versions of what is displayed on the screen (as shown in the example last time). In the Access Database some functions (cableref and switchref) can be included in the SQL query to return a recordset with the appropriate fields in the correct format and then this recordset can be used to populate a ListBox using a statement such as 'listbox.recordset = recordset' and the field is displayed as it should along with a number of other fields.

With SQL Server, where I need to move the data, these functions do not work if placed in the SQL. I therefore run the SQL and return the recordset as before but with the field not in the correct format but as stored in the database. I then copy this recordset to a new recordset, changing one field with a function, but then the 'set listbox.recordset = recordset' no longer works, although the field names are displayed as headers and there appear to be the correct number of rows all the data has been replaced by nulls.

In brief, all works as long as I don't copy one recordset to another, using a function to change the way one field is stored in the recordset and then use this new recordset to populate the listbox.

I hope this is clearer (but it possibly isn't) and thank you for staying with me on this.

 

[boblarson]

Treva:

What you might not realize is that a list box has both a recordsource and rowsource. The definitions of them are:

recordsource - usually bound to a field to capture and save the value of the particular record that the form is on at the time. It can also be a calculated value using a formula.

Rowsource - The underlying query or table (still a query just not visibile to you) (or it can be a value list that is manually typed) that gives the list box (or combo box) the values from which to select. This "list" of values is what you select from to set the value which is then inserted to the recordsource.

Hopefully that helps clarify things and makes it more clear as to what you need to do to make things work the way you want.

 

[doco]

'listbox.recordset = recordset'

Is .recordset a valid listbox property?

Private Sub Form_Open(Cancel As Integer)

Dim c As New clsCables
Dim rsPopLst As New ADODB.Recordset
Dim rsNewLst As New ADODB.Recordset


c.CabLetter = "AR" ' Keep the returned data small for testing

Set rsPopLst = c.PopulateLst

rsNewLst.Fields.Append "UniqueID", adVarChar, 9, adFldUpdatable
rsNewLst.Fields.Append "WBox", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "Room", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "Description", adVariant, 50, adFldUpdatable
rsNewLst.Fields.Append "Comment", adVariant, 100, adFldUpdatable
rsNewLst.Fields.Append "CableNo", adVariant, 50, adFldUpdatable
rsNewLst.Fields.Append "DrawingNO", adVarChar, 50, adFldUpdatable
rsNewLst.Fields.Append "LinkRef", adVarChar, 15, adFldUpdatable


rsNewLst.Open

...

When and where does the database object 'rsNewList' get instanced?

However, as has been pointed out by both Bob and myself: if you have a recordset (or set of records) whether they are in a physical table in this database, another database, an Excel sheet, a delimited text file, or a logical file such as a query; you must notify the listbox object which table that is; and as suggested above.

Here is an example I created in the Northwinds sample database that returns unique list of EmployeeID data from the Orders database and displays them in a listbox.

Private Sub Form_Open(Cancel As Integer)
Dim sql As String

    sql = "SELECT DISTINCT EmployeeID " & _
          "FROM Orders " & _
          "Order By EmployeeID;"
    
    
    With Me.List0
        .RowSourceType = "Table/Query"
        .RowSource = sql
    End With
    
End Sub

Works like a charm...

 

[doco]

Additionally, if you open your copy of Northwinds and highlight the Orders table and select Design icon. Then select EmplyeeID from the list of fields in the table; and then select the 'Lookup' tab from the Field Properties dialog at the bottom of the window. You may see in the Row Source property

SELECT DISTINCTROW [Employees].[EmployeeID], [LastName] & ", " &
[FirstName] AS Name FROM Employees ORDER BY [Employees].[LastName], 
[Employees].[FirstName];

A combobox is being populated with a logical table based on a query. Which seems to be what you are wanting to do only with a listbox on a form - same thing.

 

[boblarson]

That is actually correct, .recordset is not a valid listbox property?

It is only available to forms and reports.

My original post was flawed -

Recordsource is where the recordset of the form/report is defined

CONTROLSOURCE is usually bound to a field to capture and save the value of the particular record that the form is on at the time. It can also be a calculated value using a formula.

and then

Rowsource - The underlying query or table (still a query just not visibile to you) (or it can be a value list that is manually typed) that gives the list box (or combo box) the values from which to select. This "list" of values is what you select from to set the value which is then inserted to the recordsource.


My apologies for the slip up.

 

[RoyVidar]

doco wrote
> Is .recordset a valid listbox property?

Yup, starting with the 2002 version.

Next question, is probably if you can assign a disconnected recordset created on the fly to it, the answer is yes. (Should you need to know whether such recordset can also be assigned to a continuous form, then the answer is no, as that needs more metainformation from underlying tables). In stead of working with yours, try this little thingie (assuming a form with a listbox named lstTest)

    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    With rs2
        .Fields.Append "id", adInteger
        .Fields.Append "carname", adVarWChar, 50
        .CursorLocation = adUseClient
        .Open , , adOpenStatic, adLockOptimistic
        .AddNew
        .Fields("id").Value = 1
        .Fields("carname").Value = "opel"
        .Update
        .AddNew
        .Fields("id").Value = 2
        .Fields("carname").Value = "volvo"
        .Update
        .AddNew
        .Fields("id").Value = 3
        .Fields("carname").Value = "suzuki"
        .Update
    End With
    With Me!lstTest
        .RowSourceType = "table/query"
        .BoundColumn = 1 ' needed?
        .ColumnCount = 2
        rs2.Sort = "id desc"
         Set .Recordset = rs2
    End With

I don't recall the details, but I think both cursor location and either locktype or openenum (or both) is of a bit of importance.

 

[doco]

doco wrote
> Is .recordset a valid listbox property?

Yup, starting with the 2002 version.

Hmmm.... using 2003 and don't see it in the properties list or the intellisense dropdowns.

 

[RoyVidar]

doco wrote
> Hmmm.... using 2003 and don't see it in the properties list or the intellisense dropdowns.

This property is available starting from the 2002 version, see for instance http://office.microsoft.com/en-us/access/HP030830011033.aspx (Binding of reports, list boxes, and combo boxes to ADO recordsets). I wouldn't expect to find it in the properties dialog (some properties are only found in code, this is one of them), but it should be found in VBE with Intellisence, at least when there are no other errors/typos in code. You do have Option Explicit at the top of each module, and it does compile?

The code should be copy/pasteable, just create a form with a listbox named lstTest, drop in a button with my code in it, and it should work (does on my setups, anyway - if you've removed the refrence to ADO, you'd need to add it again).

Treva wrote
> In brief, all works as long as I don't copy one recordset to another, using a function to change the way one field is stored in the recordset and then use this new recordset to populate the listbox.

It's the properties of the created recordset. Cursor location, Cursor type? and Locktype.

 

[Treva]

Hello again.

Thank you to everyone who became involved in this. I now have a solution.

Many thanks

 

[kleky]

listbox recordset values null

doco wrote
Next question, is probably if you can assign a disconnected recordset created on the fly to it, the answer is yes. (Should you need to know whether such recordset can also be assigned to a continuous form, then the answer is no, as that needs more metainformation from underlying tables). In stead of working with yours, try this little thingie (assuming a form with a listbox named lstTest)

    Dim rs2 As ADODB.Recordset
    Set rs2 = New ADODB.Recordset
    With rs2
        .Fields.Append "id", adInteger
        .Fields.Append "carname", adVarWChar, 50
        .CursorLocation = adUseClient
        .Open , , adOpenStatic, adLockOptimistic
        .AddNew
        .Fields("id").Value = 1
        .Fields("carname").Value = "opel"
        .Update
        .AddNew
        .Fields("id").Value = 2
        .Fields("carname").Value = "volvo"
        .Update
        .AddNew
        .Fields("id").Value = 3
        .Fields("carname").Value = "suzuki"
        .Update
    End With
    With Me!lstTest
        .RowSourceType = "table/query"
        .BoundColumn = 1 ' needed?
        .ColumnCount = 2
        rs2.Sort = "id desc"
         Set .Recordset = rs2
    End With

I don't recall the details, but I think both cursor location and either locktype or openenum (or both) is of a bit of importance.

Awesome Roy, thanks!

Treva didn't say what his solution was, so, 6 years later I won't keep you hanging on any longer...Roy's solution fixes the initial problem:

A custom ADODB recordset built on the fly will attach to a list box's Recordset property but each row only has NULL values.

After appending the fields you must add:

.CursorLocation = adUseClient
.Open , , adOpenStatic, adLockOptimistic

sorted