Brain not working this morning, help with SQL please.

Hello,

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.

Here is my query:

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.

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 MVP
http://I.Am/DougSteele
(no e-mails, please!)


"martinmike2" wrote in message
...
Hello,

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.

Here is my query:

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.

On Nov 4, 8:03*am, "Douglas J. Steele"
wrote:
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!)

"martinmike2" wrote in message

...



Hello,

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.

Here is my query:

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 -

Doug,

it is telling me that there is a "syntax error in string in query
expression 'ComputerName = 'data'.

I double checked and the field is text so qoutes would make sense,
just not working.

On Nov 4, 8:03*am, "Douglas J. Steele"
wrote:
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!)

"martinmike2" wrote in message

...



Hello,

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.

Here is my query:

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 -

hmmm, I just ran the same trim(rs.fields(0)) code ni debug and found a
carriage return at the end. I'm willing to bet that that is my
problem.

On Nov 4, 8:03*am, "Douglas J. Steele"
wrote:
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!)

"martinmike2" wrote in message

...



Hello,

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.

Here is my query:

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:
*********************
'**************
*********************
'************** 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

What happens if you remove the code you added to go against tblLoggedIn? Do
you get the correct data?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"martinmike2" wrote in message
...
On Nov 4, 8:03 am, "Douglas J. Steele"
wrote:
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!)

"martinmike2" wrote in message

...



Hello,

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.

Here is my query:

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:
*********************
'**************
*********************
'************** 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

Well, i get all of the computer names just fine, but since we don't
use usergroups, the user always shows as "Admin". tblLoggedIn is
there to get the actual UserName of the person using the system.

I figured that was your intent. We're trying to debug why your code isn't
working properly...

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"martinmike2" wrote in message
...
Well, i get all of the computer names just fine, but since we don't
use usergroups, the user always shows as "Admin". tblLoggedIn is
there to get the actual UserName of the person using the system.

ok, I have made some progress. I stepped through the code with a
watch on strUserToCheck and noticed that the problem was null fields
in tblLoggedIn. I added a nested IF statement to check if rst!UserID
= NULL and if so to set strUserToCheck = "Unknown". Before this the
code would hit the first null field and then kick out to the form.
Adding this seems to have corrected the name issues.

Doug, this seems to have solved the problem. Thank you for your help
and patience.

Doug,

Looks like I sorted out the issue.

I found that there were empty records in tblLoggedIn and when the code
would hit one of these it would dump out to the form. I added an IF
to check for null values and if so to set strUserToCheck to
"Unknown". This has solved the issue and the code works as needed.

I appreciate your help and patience in this matter.