Reserved word or mispelled argument name error

[Guest]

Hi,

I am using the following method to limit the users to delete or modify
records that they do not belong to their responsibility.
I have a table named tblAccessRights
Field1->RightID
Field2->Category
Field3-> UserLoginName
In the table I store the Login names of the users and the categories they
should have access

I have a form named Items that contains a comboBox named cboSelectCategory
that populates a subform that contains my records in continious view.
ComboBox 1st column is CategoryID (number) and 2nd column CategoryCode
(text). Combo is bound to the relevant field of a table named Categories

I use the following code in the On Curent event of the subform
(currLoginName is a public variable that gets its value from the
GetCurrentUserName() function)

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMsg As String, strTitle As String
Dim currLoginName As String

strSQL = "SELECT FROM [tblAccessRights] WHERE Category = '" & Forms!
Items!cboSelectCategory.Column(2) & "' AND UserLoginName = '" & currLoginName
& "'"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

If rs.EOF And rs.BOF Then
strMsg = "You may not allowed to modify these records"
strTitle = "Access rightsâ€
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
End If

The problem is that when the form is opened or requeried I get the error
message
“The Select statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrectâ€
Can you see what is wrong?

Thank you
GL

 

[Granny Spitz via AccessMonster.com]

Can you see what is wrong?

You're missing the column names, and column numbering in a combo box starts
at 0, so the second column is Column(1). Make sure you close the record set
and set it and the db variable to nothing before exiting the procedure.

strSQL = "SELECT Doric, Ionic, Corinthian " & _
"FROM [tblAccessRights] " & _
"WHERE Category = '" & _
Forms!Items!cboSelectCategory.Column(1) & _
"' AND UserLoginName = '" & currLoginName & "'"

 

[Van T. Dinh]

In addition to Granny's advice (and assuming JET Back-End), you need to
include the Selection list between the keywords SELECT and FROM.

Use * to indicate all Fields if you want to select all Fields.

Check Access Help (JET SQL Reference "book") on the SELECT statement.

 

[Guest]

Column number is Ok but it was stupid to forget the asterisk or the field
names in Select statement. After I put it I get the error message
"Too few parameters, expected 1"

In addition to Granny's advice (and assuming JET Back-End), you need to
include the Selection list between the keywords SELECT and FROM.

Use * to indicate all Fields if you want to select all Fields.

Check Access Help (JET SQL Reference "book") on the SELECT statement.

--
HTH
Van T. Dinh
MVP (Access)

Hi,

I am using the following method to limit the users to delete or modify
records that they do not belong to their responsibility.
I have a table named tblAccessRights
Field1->RightID
Field2->Category
Field3-> UserLoginName
In the table I store the Login names of the users and the categories they
should have access

I have a form named Items that contains a comboBox named cboSelectCategory
that populates a subform that contains my records in continious view.
ComboBox 1st column is CategoryID (number) and 2nd column CategoryCode
(text). Combo is bound to the relevant field of a table named Categories

I use the following code in the On Curent event of the subform
(currLoginName is a public variable that gets its value from the
GetCurrentUserName() function)

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strMsg As String, strTitle As String
Dim currLoginName As String

strSQL = "SELECT FROM [tblAccessRights] WHERE Category = '" & Forms!
Items!cboSelectCategory.Column(2) & "' AND UserLoginName = '" &
currLoginName
& "'"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

If rs.EOF And rs.BOF Then
strMsg = "You may not allowed to modify these records"
strTitle = "Access rights"
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletions = False
End If

The problem is that when the form is opened or requeried I get the error
message
"The Select statement includes a reserved word or an argument name that is
misspelled or missing, or the punctuation is incorrect"
Can you see what is wrong?

Thank you
GL