Invalid SQL in VBA

[Guest]

I am getting an invalid SQL error when I run this. I would like capture the
Computer name and compare it to the result of a query to determine if the
user is in a group that needs buttons visible or hidden.

Private Sub Form_Load()

Dim dbconn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmdCommand As ADODB.Command
'Dim qdf As QueryDef
Dim userCom As String
Dim uservar As String

userCom = Environ$("ComputerName")
Set dbconn = New ADODB.Connection
With dbconn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "C:\Documents and Settings\Users\My Documents\ Inventory
Reporting.mdb"

End With

Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = dbconn
'Set qdf = New QueryDef

With cmdCommand
.CommandText = "SELECT qryUsersGroups.UserGroupId,
qryUsersGroups.ComputerName WHERE qryUsersGroups.ComputerName" = userCom

.CommandType = adCmdText
.Execute
End With

Set rst = New ADODB.Recordset
Set rst.ActiveConnection = dbconn
rst.Open cmdCommand

Debug.Print userCom
uservar = qryUsersGroups.UserGroupID

If uservar <> 3 Then
Command25.Visible= False
End If
End Sub

 

[Matthias Klaey]

I am getting an invalid SQL error when I run this. I would like capture the
Computer name and compare it to the result of a query to determine if the
user is in a group that needs buttons visible or hidden.
[...]


With cmdCommand
.CommandText = "SELECT qryUsersGroups.UserGroupId,
qryUsersGroups.ComputerName WHERE qryUsersGroups.ComputerName" = userCom

[...]

Try

"SELECT qryUsersGroups.UserGroupId, qryUsersGroups.ComputerName
WHERE qryUsersGroups.ComputerName = '" & userCom & "'"

(all on one line). Notice the single quotes!

HTH
Matthias Kläy

 

[Guest]

JoeA,

First of all you have to change the code like below
qryUsersGroups.ComputerName WHERE qryUsersGroups.ComputerName " & = userCom

Secondly if the Computername is a string field in the DB you should change
the code in:
WHERE qryUsersGroups.ComputerName '" & = userCom & "'"
(see the addtional ' in the code)

Is the Computername fieldtype string?