Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Input Data for a Listbox

Posted on 2004-08-24
6
Medium Priority
?
329 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

650 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