Error on Modules

[Jack]

Can you please help me and check what is wrong with this
module?
I have indicated where the error occurs.
Thanks
============

Option Compare Database
Option Explicit

Public Function UserSecurity()
On Error GoTo EndIt

Dim strSQL, strCurUserID As String
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection

strCurUserID = ThisUser

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
strSQL = "SELECT tblSecurity.UserID,
tblSecurity.UserFirstName,
tblSecurity.UserLastName, tblSecurity.Password,
tblSecurity.Department, tblSecurity.cmdCustomer,
tblSecurity.cmdCustomerPlaceEditOrder1,
tblSecurity.cmdPrintCustomerOrderInvoiceStatement1,
tblSecurity.cmdCustomerInformation1,
tblSecurity.cmdInventory1 from
tblSecurity where UserID = '" & strCurUserID & "'"

rs.Open strSQL, cnn, adOpenStatic, adLockReadOnly
===========================================================
=====
The procedure gets stuck here and jumps to "Exit Function"
===========================================================
==========
On Error Resume Next


If rs.Fields(5) = True Then
Forms![MainMenu]![cmdCustomer].Enabled = True
End If
If rs.Fields(6) = True Then
Forms![CustomerMainMenu]!
[cmdCustomerPlaceEditOrder].Enabled =
True
End If
If rs.Fields(7) = True Then
Forms![CustomerMainMenu]!
[cmdPrintCustomerOrderInvoiceStatement].Enabled
= True
End If
If rs.Fields(8) = True Then
Forms![CustomerMainMenu]!
[cmdCustomerInformation].Enabled =
True
End If
If rs.Fields(9) = True Then
Forms![MainMenu]![cmdInventory].Enabled = True
End If
rs.Close


EndIt:
Exit Function

End Function

 

[Tim Ferguson]

Can you please help me and check what is wrong with this
module?
I have indicated where the error occurs.

Okay: so what is the error? Removing the On Error line might help...


Tim F

 

[Tracey Meyer]

Try this: Change the last portion of the sql statement to
this: From tblSecurity (((where UserID)=" & """" &
strCurUserID & """" & "));"

The strCurUserId is declared as text so it needs to be
enclosed in " when the SQL statement runs.

Also you may wan to Exit Sub at somepoint, otherwise all
of the code will run including the errorhandlers.

 

[Van T. Dinh]

I assume

strSQL = "SELECT tblSecurity.UserID,
tblSecurity.UserFirstName,
tblSecurity.UserLastName, tblSecurity.Password,
tblSecurity.Department, tblSecurity.cmdCustomer,
tblSecurity.cmdCustomerPlaceEditOrder1,
tblSecurity.cmdPrintCustomerOrderInvoiceStatement1,
tblSecurity.cmdCustomerInformation1,
tblSecurity.cmdInventory1 from
tblSecurity where UserID = '" & strCurUserID & "'"

is actually 1 line in your code.

Is the UserID Numeric or String?

If it is Numeric, you should use:

tblSecurity where UserID = " & strCurUserID