Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Input Data for a Listbox

Posted on 2004-08-24
6
293 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
ID: 11878655
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
ID: 11878777
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
ID: 11878789
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:PeterActon
ID: 11878825
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
ID: 11878963
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
ID: 11879061
Steve - Thanks a lot. Your help was really appreciated.

All the best
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

789 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