By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,054 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

help with runtime error 3061 too few parameters. expected 1

P: n/a
i get the above runtime error on the following line of code
when i try to update a reord in my form

when it gets to the line

Set rs = DBEngine(0)(0).OpenRecordset(strSql)
the entire code is posted below

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strCode2Use As String
Dim strSql As String
Dim i As Integer

If Len(Forename) >= 2 Then
If Len(Surname) >= 2 Then
strCode2Use = Left$(Surname, 2) & Left$(Forename, 2)
strSql = "SELECT TOP 1 LionCode FROM lions WHERE LionCode
Like """ & _
strCode2Use & "*"" ORDER BY Code DESC;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then 'If we found a match
i = CInt(Right(rs!lionCode, 2)) + 1 'Increment the num in
last 2characters.
End If
rs.Close
lionCode = strCode2Use & Format(i, "00")
Else
Cancel = True
MsgBox "FirstName must be at least 2 characters."
End If
End If

Set rs = Nothing
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Try this:

strSql = "SELECT TOP 1 LionCode FROM lions WHERE " & _
"LionCode Like '" & strCode2Use & "*' ORDER BY Code DESC;"

HTH -Linda
"colm" <cobrien@don'tsendmespam.btinternet.com> wrote in message
news:kr********************************@4ax.com...
i get the above runtime error on the following line of code
when i try to update a reord in my form

when it gets to the line

Set rs = DBEngine(0)(0).OpenRecordset(strSql)
the entire code is posted below

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strCode2Use As String
Dim strSql As String
Dim i As Integer

If Len(Forename) >= 2 Then
If Len(Surname) >= 2 Then
strCode2Use = Left$(Surname, 2) & Left$(Forename, 2)
strSql = "SELECT TOP 1 LionCode FROM lions WHERE LionCode
Like """ & _
strCode2Use & "*"" ORDER BY Code DESC;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then 'If we found a match
i = CInt(Right(rs!lionCode, 2)) + 1 'Increment the num in
last 2characters.
End If
rs.Close
lionCode = strCode2Use & Format(i, "00")
Else
Cancel = True
MsgBox "FirstName must be at least 2 characters."
End If
End If

Set rs = Nothing
End Sub

Nov 13 '05 #2

P: n/a
"colm" <cobrien@don'tsendmespam.btinternet.com> wrote in message
news:kr********************************@4ax.com...
i get the above runtime error on the following line of code
when i try to update a reord in my form

when it gets to the line

Set rs = DBEngine(0)(0).OpenRecordset(strSql)
the entire code is posted below

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strCode2Use As String
Dim strSql As String
Dim i As Integer

If Len(Forename) >= 2 Then
If Len(Surname) >= 2 Then
strCode2Use = Left$(Surname, 2) & Left$(Forename, 2)
strSql = "SELECT TOP 1 LionCode FROM lions WHERE LionCode
Like """ & _
strCode2Use & "*"" ORDER BY Code DESC;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then 'If we found a match
i = CInt(Right(rs!lionCode, 2)) + 1 'Increment the num in
last 2characters.
End If
rs.Close
lionCode = strCode2Use & Format(i, "00")
Else
Cancel = True
MsgBox "FirstName must be at least 2 characters."
End If
End If

Set rs = Nothing
End Sub


It looks like one of the fields referred to in your query (LionCode or Code)
does not exist. I wonder if your ORDER BY clause should in fact read "ORDER
BY LionCode DESC"?
Nov 13 '05 #3

P: n/a

"colm" <cobrien@don'tsendmespam.btinternet.com> wrote in message
news:kr********************************@4ax.com...
i get the above runtime error on the following line of code
when i try to update a reord in my form

when it gets to the line

Set rs = DBEngine(0)(0).OpenRecordset(strSql)
the entire code is posted below

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strCode2Use As String
Dim strSql As String
Dim i As Integer

If Len(Forename) >= 2 Then
If Len(Surname) >= 2 Then
strCode2Use = Left$(Surname, 2) & Left$(Forename, 2)
strSql = "SELECT TOP 1 LionCode FROM lions WHERE LionCode
Like """ & _
strCode2Use & "*"" ORDER BY Code DESC;"
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
If rs.RecordCount > 0 Then 'If we found a match
i = CInt(Right(rs!lionCode, 2)) + 1 'Increment the num in
last 2characters.
End If
rs.Close
lionCode = strCode2Use & Format(i, "00")
Else
Cancel = True
MsgBox "FirstName must be at least 2 characters."
End If
End If

Set rs = Nothing
End Sub

Before the line Set rs... why not write:
Debug.Print strSql
This prints the sql statement to the immediate window - which you can access
by pressing Ctrl + G.

For BOB SMITH this should show:
SELECT TOP 1 LionCode FROM lions WHERE LionCode Like "SMBO*" ORDER BY Code
DESC;

Now you can paste this sql into the sql view of a new query which should
help show where the fault is. Should the order clause be "ORDER BY
LionCode DESC" and not "ORDER BY Code DESC"
You also could re-write this code to allow for error-handling so that you
don't get runtime errors like this. Let us know if you need an example of
this.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.