Code Problem

  • Thread starter Thread starter Mardi
  • Start date Start date
M

Mardi

Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
 
Hi Mardi

The best way to debug this is to set a breakpoint on your Set rst =... line,
and when the code stops execution at that point, examine strSQL in the
Immediate (Debug) window:
?strSQL

Chances are you will see immediately ahat the problem is, but if not then
you can copy the SQL string from the Immediate window and paste it into an
empty query in SQL view. Then try to run the query and it will prompt you
for what it thinks is the "missing parameter".

My guess is that either KeywordsID or DocID is actually a text field, so you
need to enclose the comparison values in quote marks.
 
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE [KeywordsID] =
""" & KeywordsChoose.ItemData(varList) & """ AND [DocID] = """ & Me.DocID &
""";"

if KeywordID and DocID are text and all on one line.
 
Terry,

Great that works. Although when I tried to add a second item from the
listbox it gave me another error msg. This time its saying Runtime
error 3464; Data type mismatch in criteria expression. I thought
maybe I should look in the listbox properties. But I'm thinking its
with the SQL statement instead. What do you think?

When I try debugging...its highlight and stops at this line of code:

Set rst = db.OpenRecordset(strSQL)

Thanks in advance for your help.

Terry said:
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE [KeywordsID] =
""" & KeywordsChoose.ItemData(varList) & """ AND [DocID] = """ & Me.DocID &
""";"

if KeywordID and DocID are text and all on one line.


Mardi said:
Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
Click to expand...
Click to expand...
 
Terry,

Actually KeywordsID is a number and DocID is a text. How would that
change the statement?

Thank you.


Terry said:
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE [KeywordsID] =
""" & KeywordsChoose.ItemData(varList) & """ AND [DocID] = """ & Me.DocID &
""";"

if KeywordID and DocID are text and all on one line.


Mardi said:
Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
Click to expand...
Click to expand...
 
Graham,

I've tried your debugging techniques. When I did it in the immediate
window the sql statement is doing what i want it to do. However, when
i copied the statement into a blank query in sql view it gave me an
error msg. Its saying invalid sql statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Can you see a problem?
At times I also get a data type mismatch error.

So you can see what I'm trying to do below is my code:

Private Sub cmdInsert_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varList As Variant
Dim strSQL As String
Dim booOverLimit As Boolean


Set db = CurrentDb()

For Each varList In lstKeywordsChoose.ItemsSelected

If lstKeywordsChosen.ListCount < 5 Then

'Check if Keyword already exist in the list
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE
[KeywordsID] = """ & lstKeywordsChoose.ItemData(varList) & """ AND
[DocID] = """ & Me.txtDocID & """;"

Set rst = db.OpenRecordset(strSQL)

'if no record exist then insert
If rst.RecordCount = 0 Then
With rst
.AddNew
![KeywordsID] = lstKeywordsChoose.ItemData(varList)
!DocID = Me.txtDocID
.Update
End With
End If

'MsgBox lstKeywordsChoose.ItemData(varList)
Set rst = Nothing

'Requery the list after update
lstKeywordsChosen.Requery

Else
'Set variable if over limit
booOverLimit = True

End If

Next varList


Set db = Nothing

'Pop up msg if over limit
If booOverLimit Then
MsgBox "Too many Keywords! Limit = 5"
End If

End Sub

Thanks in advance.


Graham Mandeno said:
Hi Mardi

The best way to debug this is to set a breakpoint on your Set rst =... line,
and when the code stops execution at that point, examine strSQL in the
Immediate (Debug) window:
?strSQL

Chances are you will see immediately ahat the problem is, but if not then
you can copy the SQL string from the Immediate window and paste it into an
empty query in SQL view. Then try to run the query and it will prompt you
for what it thinks is the "missing parameter".

My guess is that either KeywordsID or DocID is actually a text field, so you
need to enclose the comparison values in quote marks.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Mardi said:
Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
Click to expand...
Click to expand...
 
Mardi said:
Actually KeywordsID is a number and DocID is a text. How would that
change the statement?
Click to expand...

In that case drop the quotes around KeywordsID

... WHERE [KeywordsID] = " &
KeywordsChoose.ItemData(varList) & " AND [DocID] = """ &
Me.DocID & """;"
--
Marsh
MVP [MS Access]


Terry said:
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE [KeywordsID] =
""" & KeywordsChoose.ItemData(varList) & """ AND [DocID] = """ & Me.DocID &
""";"

if KeywordID and DocID are text and all on one line.


Mardi said:
Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Hi Mardi

Surely when you copied the string from the Immediate window and pasted it
into the SQL window, the first word must have been "SELECT"? I don't
understand how you could get the error you describe.

As Terry and Marsh have pointed out, the problem is usually the absence of
quotes. The rules are:
1) numbers should be plain (no delimiters)
2) text should be in quotes (either single or double)
3) dates should be delimited by # signs

Your example below should work if (and only if) both KeywordsID and DocID
are text fields (because you are enclosing both comperison values in
quotes).
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Mardi said:
Graham,

I've tried your debugging techniques. When I did it in the immediate
window the sql statement is doing what i want it to do. However, when
i copied the statement into a blank query in sql view it gave me an
error msg. Its saying invalid sql statement; expected 'DELETE',
'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. Can you see a problem?
At times I also get a data type mismatch error.

So you can see what I'm trying to do below is my code:

Private Sub cmdInsert_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varList As Variant
Dim strSQL As String
Dim booOverLimit As Boolean


Set db = CurrentDb()

For Each varList In lstKeywordsChoose.ItemsSelected

If lstKeywordsChosen.ListCount < 5 Then

'Check if Keyword already exist in the list
strSQL = "SELECT KeywordsID, DocID From Keywords_Master WHERE
[KeywordsID] = """ & lstKeywordsChoose.ItemData(varList) & """ AND
[DocID] = """ & Me.txtDocID & """;"

Set rst = db.OpenRecordset(strSQL)

'if no record exist then insert
If rst.RecordCount = 0 Then
With rst
.AddNew
![KeywordsID] = lstKeywordsChoose.ItemData(varList)
!DocID = Me.txtDocID
.Update
End With
End If

'MsgBox lstKeywordsChoose.ItemData(varList)
Set rst = Nothing

'Requery the list after update
lstKeywordsChosen.Requery

Else
'Set variable if over limit
booOverLimit = True

End If

Next varList


Set db = Nothing

'Pop up msg if over limit
If booOverLimit Then
MsgBox "Too many Keywords! Limit = 5"
End If

End Sub

Thanks in advance.


"Graham Mandeno" <[email protected]> wrote in message
Hi Mardi

The best way to debug this is to set a breakpoint on your Set rst =... line,
and when the code stops execution at that point, examine strSQL in the
Immediate (Debug) window:
?strSQL

Chances are you will see immediately ahat the problem is, but if not then
you can copy the SQL string from the Immediate window and paste it into an
empty query in SQL view. Then try to run the query and it will prompt you
for what it thinks is the "missing parameter".

My guess is that either KeywordsID or DocID is actually a text field, so you
need to enclose the comparison values in quote marks.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Mardi said:
Hi,

I also have a problem with my code. Its giving me a Runtime error
3061. Too few parameters. Expected 1. Any help is appreciated. What
could be the problem?

strSQL = "SELECT KeywordsID, DocID from Keywords_Master " _
& "WHERE KeywordsID = " &
lstKeywordsChoose.ItemData(varList) _
& " AND DocID = " & Me.DocID

Set rst = db.OpenRecordset(strSQL)


Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top