Solved

Input Data for a Listbox

Posted on 2004-08-24
6
309 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
[X]
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
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

732 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