No value given for one or more required parameters

[Guest]

I am creating a security log in case a user tries to open a form they do not
have access to. (The users should not be able to see the form, but you never
know. I suppose this is just a 'Just in case' messure...)

The code below is supposed to check the user table to see if the current
user has permission to have the form open, but I cannot seem to open this
recordset. I use this exact code in other places in the database with no
problem. Now I get the 'No value given for one or more required parameters'
error. The SQL works and the connection is correct so it must be in the
rst.open, but why?


Private Sub Form_Load()
Set cn = CurrentProject.Connection
Set rst = New ADODB.Recordset

Dim strSQL As String

strSQL = "SELECT * FROM tUsers WHERE txtUserID ='" & fOSUserName & "'"
rst.Open strSQL, cn

Dim sqlSecurity As String
userID = fOSUserName()
If rst!lngUserLevel <> 10 Then
MsgBox "You do not have appropriate permission to view this form", vbOKOnly,
"Invaild User"


sqlSecurity = "INSERT INTO
tSecurityLog(txtNTName,lngSecurityNum,dtmDate,dtmTime) " & _
"VALUES ('" & userID & "',1,'" & Date & "','" & Time & "')"
DoCmd.RunSQL "sqlSecurity"
Exit Sub
DoCmd.Close acForm, "fUsers"
End If

End Sub

Thanks in advance!
PJ

 

[Douglas J. Steele]

First, you should store date and time in a single field, not in two separate
fields. If there are times when you only want the date, or only the time,
you can use the DateValue or TimeValue functions respectively.

The problem you're encountering is because date and time values must be
delimited with # characters, not '. Additionally, since Access tends to be
picky about accepting specific formats for dates, and you don't have any
control over the Short Date format which your users choose, you're best off
explicitly formatting the date:

sqlSecurity = "INSERT INTO tSecurityLog( " & _
"txtNTName,lngSecurityNum,dtmDateTime) " & _
"VALUES ('" & userID & "',1," & _
Format(Now(), "\#yyyy\-mm\-dd hh\-nn\-ss\#") & ")"

If you insist on keeping the date and time separate, use

sqlSecurity = "INSERT INTO tSecurityLog( " & _
"txtNTName,lngSecurityNum,dtmDate,dtmTime) " & _
"VALUES ('" & userID & "',1," & _
Format(Date, "\#yyyy\-mm\-dd\#") & ", " & _
Format(Time, "\#hh\:nn\:ss\#") & ")"

 

[Guest]

The problem I am running into occurs when the recordset is trying to load:
strSQL = "SELECT * FROM tUsers WHERE txtUserID ='" & fOSUserName & "'"

=> rst.Open strSQL, cn <=

There is something about either the connection or the way the recordset is
set up that is throwing the error.

Point taken on the date/time set up. I don't seperate them for any good
reason and more often than not, I am looking at them together.

 

[Douglas J. Steele]

Have you checked what's in strSQL? Perhaps your fOSUserName is wrong.

Why not move

userID = fOSUserName()

up, and then use

strSQL = "SELECT * FROM tUsers WHERE txtUserID ='" & userID & "'"

Also, are you sure the field is named txtUserID?