What happens if you remove the code you added to go against tblLoggedIn? Do
you get the correct data?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Assuming that ComputerName is a text field, you need quotes.
Eliminating the redundant parentheses Access is so fond of inserting, try:
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblLoggedIn " & _
"WHERE ComputerName= '" & Trim(rs.Fields(0)) & "'")
Exagerated for clarity, that second line is
"WHERE ComputerName= ' " & Trim(rs.Fields(0)) & " ' ")
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)
I am trying to query a table of logged in users
( tblLoggedIn.ComputerName, tbl.LoggedIn.[UserName]) with the criteria
ComputerName = Trim(rs.fields(0)).
rs.Fields is an array pulled from the ldb schema. Basically, I want
to find the record in tblLoggedIn that matches the computername
returned from the ldb schema.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblLoggedIn WHERE
(((tblLoggedIn.ComputerName)= " & Trim(rs.Fields(0)) & "));")
My problem is, I am getting a "Missing ( or [ error. If I surround
Trim(rs.fields(0)) with single qoutes, then I get a syntax error.- Hide
quoted text -
- Show quoted text -
ok, I solved it.
Dim compid as string
compid = trim(rs.fields(0))
compid = left(compid, 12)
then I inserted compid into the WHERE clause instead of the the trim
command and the query worked. Now I have a new problem, lol. (I love
how solving one problem births more)
Now, when I run my form to show the computers connected to the
database I get the right number of records, but the records are being
duplicated. For example:
I have 4 people using the system but the code is returning only two of
the computer/username combinations and then duplicating them both
twice, so I have 4 records, but only 2 computers.
CODE:
(Using IntuiDev code, derived from microsoft knowledgebase)
'*******************************************************************************************
'**************
*********************
'************** code courtesy of
*********************
'**************
*********************
'************** IntuiDev IT-Solutions / O. Rabbachin (C) 2001/2002
*********************
'**************
www.intuidev.com /
www.intuidev.de
*********************
'************** Email: (e-mail address removed)
*********************
'**************
*********************
'************** you may use the code provided here provided this
*********************
'************** copyright-notice remains unchanged.
*********************
'**************
*********************
'*******************************************************************************************
'**************************************************************
'set this constant to the table you'd like to use for checking!
Private Const mconCheckupTableName As String =
"tbl_NetworkMonitor_DummyTable"
'**************************************************************
'**************************************************************
'if you set this constant to True, if you're checking against a
'secured DB; If so, provide the SEC-constants below as well!
Private Const mconfSecuredDB As Boolean = False
'-> name/path of the MDW to be used
Private Const mcon_SEC_MDW_Name As String = ""
'-> Admin-account / User-name
Private Const mcon_SEC_AdminsAcountName As String = ""
'-> PWD of the above Admin-user
Private Const mcon_SEC_AdminsAcountPWD As String = ""
'**************************************************************
'will offer space to store the path to a backend
Private mstrConnectedDB As String
Private Sub cmdRefreshListbox_Click()
'refresh the display
Transfer_UserRosterMultipleUsers mstrConnectedDB
txtRefreshCountdown = txtRefreshPeriod
End Sub
Private Sub Form_Activate()
'maximize the form
DoCmd.Maximize
End Sub
Private Sub Form_Load()
'for regular usage you'll want to use this form within your
frontend.
'For the sample, just check the one and only table specified within
'the mconCheckupTableName-constant (see this form's header-section)
check_and_restore_TableLink mconCheckupTableName
mstrConnectedDB = getConnectedDB_PathName(mconCheckupTableName)
'set defaults
txtDBc = "Database: " & mstrConnectedDB
txtRefreshPeriod = 30
txtRefreshPeriod_AfterUpdate
cmdRefreshListbox_Click
End Sub
Private Sub Form_Resize()
Const conMargin As Long = 300
Dim intOrgWidth As Integer
' On Error Resume Next
Painting = False
If InsideHeight < 4515 Then InsideHeight = 4515: Exit Sub
If InsideWidth < 6975 Then InsideWidth = 6975: Exit Sub
intOrgWidth = lstConnections.Width
'horizontal
lblHeader1.Width = InsideWidth
lblHeader2.Width = lblHeader1.Width
txtDBc.Width = InsideWidth
LineHeader.Width = InsideWidth
lstConnections.Left = conMargin
lstConnections.Width = InsideWidth - conMargin * 2
txtRefreshCountdown.Left = InsideWidth - conMargin -
txtRefreshCountdown.Width
adjust_listbox_columns lstConnections, intOrgWidth
align_listbox_labels lstConnections, 1, lbl1, lbl2, lbl3, lbl4
'vertical
lstConnections.Height = InsideHeight - SECTION(acHeader).Height - _
SECTION(acFooter).Height - lstConnections.Top - conMargin
Painting = True
End Sub
Private Sub Form_Timer()
txtRefreshCountdown = txtRefreshCountdown - 1
If txtRefreshCountdown = 0 Then txtRefreshCountdown =
txtRefreshPeriod: cmdRefreshListbox_Click
End Sub
Private Sub txtRefreshPeriod_AfterUpdate()
txtRefreshCountdown = txtRefreshPeriod
End Sub
'will show all user's currently being connected to a database
'
'* based on information provided within the Microsoft KnowledgeBase *
Private Sub Transfer_UserRosterMultipleUsers(ByVal
strPath_Filename_ToBackend As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strRowSource As String
Dim strUserToCheck As String
Dim rst As DAO.Recordset, compid As String
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
lstConnections.RowSource = ""
DoCmd.Hourglass True
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Data Source") = mstrConnectedDB
If mconfSecuredDB Then
.Properties("User Id") = mcon_SEC_AdminsAcountName
.Properties("Password") = mcon_SEC_AdminsAcountPWD
.Properties("Jet OLEDB:System database") =
getPath(mstrConnectedDB) & mcon_SEC_MDW_Name
End If
.Open
End With
'The user roster is exposed as a provider-specific schema rowset
'in the Jet 4 OLE DB provider. You have to use a GUID to
'reference the schema, as provider-specific schemas are not
'listed in ADO's type library for schema rowsets
Set rs = cn.OpenSchema(adSchemaProviderSpecific, ,
"{947bb102-5d43-11d1-bdbf-00c04fb92675}")
'Output the list of all users in the current database.
strRowSource = ""
'Debug.Print rs.Fields(0).NAME, "", rs.Fields(1).NAME, "",
rs.Fields(2).NAME, rs.Fields(3).NAME
While Not rs.EOF
'Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2),
rs.Fields(3)
If getCleanedString(rs.Fields(1)) = "Admin" Then
On Error Resume Next
compid = Trim(rs.Fields(0))
compid = Left(compid, 12)
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblLoggedIn "
& _
"WHERE ComputerName= '" & compid & "'")
rst.MoveFirst
strUserToCheck = rst!UserName
End If
strRowSource = strRowSource & _
"""" & getCleanedString(rs.Fields(0)) & """;""" &
strUserToCheck & """;""" & _
Choose(CBool(rs.Fields(2)) + 2, "Yes", "No") & """;"""
& Nz(rs.Fields(3), "N/A") & """;"
' getCleanedString(rs.Fields(1))
rs.MoveNext
Wend
'cut off trailing ';' and transfer to listbox
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
lstConnections.RowSource = strRowSource
'clean up
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing
DoCmd.Hourglass False
End Sub
Function getCleanedString(ByVal strIn As String) As String
Dim strOut As String, intCounter As Integer, strChar As String * 1
strOut = ""
For intCounter = 1 To Len(strIn)
strChar = Mid(strIn, intCounter, 1)
If Asc(strChar) >= 32 Then strOut = strOut & strChar
Next intCounter
getCleanedString = Trim(strOut)
End Function
