Solved

Input Data for a Listbox

Posted on 2004-08-24
6
261 Views
Last Modified: 2008-02-01
Greetings

How do I capture the output from the following code segment so that it is available to a listbox on a form?

Any assistance would be appreciated.
______________________________________________________________________________  
 Do Until rcdRecordSet.EOF
   
      If rcdRecordSet.Fields("CONNECTED") = True Then
         strComputerName = StripNonAlphanumeric(rcdRecordSet.Fields("COMPUTER_NAME"))
         strLoginName = StripNonAlphanumeric(rcdRecordSet.Fields("LOGIN_NAME"))
         strConnected = StripNonAlphanumeric(rcdRecordSet.Fields("CONNECTED"))
         If IsNothing(rcdRecordSet.Fields("SUSPECT_STATE")) Then
            strSuspectState = "Null"
         Else
            strSuspectState = rcdRecordSet.Fields("SUSPECT_STATE")
         End If
      End If
      strUserList = strComputerName & Space(5) & strLoginName & Space(5) & strConnected & Space(5) & strSuspectState
      MsgBox strUserList

      rcdRecordSet.MoveNext
   Loop
0
Comment
Question by:PeterActon
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
You could build what you are doing with code directly into a query and then use the query as the listbox's rowsource, set the Column Count property to 4.

In Access 2003 (and I think 2002) you can use your recordset object as the recordset of the listbox.
Set Me.lstUser.Recordset = rcdRecordSet

You could seperate with ; instead of Space(5) and then ause the list box's .AddItem method to add the reecords to the list?

      End If
      Me.lstUser.AddItem strComputerName & ";" & strLoginName & ";" & strConnected & ";" & strSuspectState
     rcdRecordSet.MoveNext
   Loop

What version of Access are you using and which of the above would you like to persue?

Steve
0
 

Author Comment

by:PeterActon
Comment Utility
Hi Steve

Thanks for your help. I am using Access 2000.

The following statement produces the error "Method or data member not found." on the ".AddItem" method:

Me.lstUser.AddItem strComputerName & ";" & strLoginName & ";" & strConnected & ";" & strSuspectState

Regards


0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
How about building a query and using that as the rowsource or even just build the SQL on the fly as the rowsource?
what is the SQL for rcdRecordSet?

Steve
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:PeterActon
Comment Utility
Steve

I would love to use SQL to build the recordset but don't know how to do it in this case. I am trying to amend the Microsoft code which determines who is logged into the database. It uses the User Roster to keep track of connections.

Code as follows:
______________________________________________________________________________

Sub CheckDatabaseUsers()
' **********************************************************************************************
' Show the Computer Name, Login Name, Connected Status and Suspect State for the current users.
' **********************************************************************************************
On Error GoTo ErrHandler

   Const dbDataSchema = "{947bb102-5d43-11d1-bdbf-00c04fb92675}"
   Dim conConnection As New ADODB.Connection
   Dim rcdRecordSet     As New ADODB.Recordset
   Dim strDataProvider  As String
   Dim strDataSchema  As String
   Dim strDataSource    As String
   Dim strComputerName    As String
   Dim strLoginName    As String
   Dim strConnected    As String
   Dim strSuspectState    As String
   Dim strUserList         As String

   strDataProvider = "Provider=Microsoft.Jet.OLEDB.4.0;"
   strDataSource = "Data Source=\\Nts-per\Units\Statsrv\SPITF\Workload Planner\Workload Planner Data.mdb"
   
   conConnection.Open strDataProvider & strDataSource & ";"
   Set rcdRecordSet = conConnection.OpenSchema(adSchemaProviderSpecific, , dbDataSchema)
   
   Do Until rcdRecordSet.EOF
   
      If rcdRecordSet.Fields("CONNECTED") = True Then
         strComputerName = StripNonAlphanumeric(rcdRecordSet.Fields("COMPUTER_NAME"))
         strLoginName = StripNonAlphanumeric(rcdRecordSet.Fields("LOGIN_NAME"))
         strConnected = StripNonAlphanumeric(rcdRecordSet.Fields("CONNECTED"))
         If IsNothing(rcdRecordSet.Fields("SUSPECT_STATE")) Then
            strSuspectState = "Null"
         Else
            strSuspectState = rcdRecordSet.Fields("SUSPECT_STATE")
         End If
      End If
      strUserList = strComputerName & Space(5) & strLoginName & Space(5) & strConnected & Space(5) & strSuspectState
      MsgBox strUserList
      rcdRecordSet.MoveNext
   Loop

ExitHere:
   On Error Resume Next
   rcdRecordSet.Close
   Set rcdRecordSet = Nothing
   conConnection.Close
   Set conConnection = Nothing
   Exit Sub
ErrHandler:
   With Err
      MsgBox "Error: " & .Number & vbCrLf & .DESCRIPTION, vbCritical Or vbOKOnly, .Source
   End With
   Resume ExitHere
End Sub
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
Comment Utility
ok ... set the rowsourcetype to Value List ... and use this to build a string of all the users ...

    strUserList = strUserList & ";" & strComputerName & ";" & strLoginName & ";" & strConnected & ";" & strSuspectState
    rcdRecordSet.MoveNext
Loop

If Len(strUserList) > 0 Then
    strUserList = Right$(strUserList, Len(strUserList)-1)
End If

Me.lstUser.Rowsource = strUserList


Steve
0
 

Author Comment

by:PeterActon
Comment Utility
Steve - Thanks a lot. Your help was really appreciated.

All the best
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now