473,327 Members | 1,976 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

help with runtime error 3061 too few parameters. expected 1

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
3 4002
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
"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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: alessia | last post by:
hi, I have a query that visualizes me, according to some criterions from me inserted, name , Surname , Address , phone ok.. Now, through a button, positioned in a Form, I have to only export...
6
by: Jonathan LaRosa | last post by:
I am trying to open a recordset and I am getting an error and I can't figure out why. See code below. sqlString2 does not work. sqlString does. Clearly the problem is with the nested SELECT...
2
by: Steve Richfield | last post by:
There have been LOTS of postings about error 3061, but mine seems to be an even simpler case than the others. I have a simple **FUNCTIONING** query called qryEdits. Copying the SQL from the query,...
7
by: Hal | last post by:
Hi, Please see http://webmonky.myby.co.uk/problem.JPG I have 1 Text Box in a form (ms access), one for an Order # .I currently have the form working so when I enter (or scan) an order number,...
11
by: MLH | last post by:
If this is what MySQL is set to... SELECT DISTINCTROW qryVehiclesNowners5.SerialNum, qryVehiclesNowners5.VDescr, qryVehiclesNowners5.Owner, qryVehiclesNowners5.VehicleJobID ,...
4
by: Richard Hollenbeck | last post by:
I thought I was very specific in this SQL request. There is a form open with a selected record (and a corresponding "lngRecipeID" on that form. The table also has a field called "lngRecipeID". ...
1
by: Richard Hollenbeck | last post by:
I wonder what I'm missing? I really feel like a retard because I've been screwing with some code for a very long time. I just must be missing something very simple. In the following example,...
3
by: Kassimu | last post by:
Hi there, I have a table with thousands of record entries, usually the user searches this table through SearchForm resulting into some recordset. What I need to do on this recordset is to...
9
by: EVH316 | last post by:
When I call this function Access gives me error msg "Too few parameters. Expected 3. " on the Set rstDAO = qryDef.OpenRecordset. Then I changed to Set rstDAO = qryDef.OpenRecordset(dbOpenSnapshot,...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.