Solved

Input Data for a Listbox

Posted on 2004-08-24
6
302 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

713 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