Too few parameters. Expected 1

M

Mike Collard

MS offer an explanation of the above error but it doesn't
work for me.

I get the error when trying to run the folowing:

Sub SaveChangedMarkerPrices(strGrade As String, strType As
String, dblCurrentMarkerPrice As Double, ctlMarkerPrice As
Control)

Dim dbsA As Database, rstA As Recordset
Dim frmA As Form
Dim strSql As String
Dim lngMkCat As Long

DoCmd.SetWarnings False

Set frmA = [Forms]![Pricing Screen]

If IsNull(DMax("[MK_CAT]", "Current Marker
Prices", "[GRADE] = " & "'" & strGrade & "' and [TYPE] = "
& "'" & strType & "' ")) Then Exit Sub

lngMkCat = DMax("[MK_CAT]", "Current Marker
Prices", "[GRADE] = " & "'" & strGrade & "' and [TYPE] = "
& "'" & strType & "' ")

strSql = "SELECT * from [Competitor Prices] WHERE [MK_CAT]
= " & lngMkCat & " AND [GRADE] = " & "'" & strGrade & "'
AND [TYPE] = " & "'" & strType & "' AND [INACTIVE_DATE] IS
NULL"

Set dbsA = CurrentDb
Set rstA = dbsA.OpenRecordset(strSql, dbOpenDynaset)

If dblCurrentMarkerPrice <> ctlMarkerPrice Then

datCurrentTimeStamp = DMax
("[TIME_STAMP]", "Current Marker Prices", "[GRADE] = "
& "'" & strGrade & "' AND [TYPE] = " & "'" & strType
& "' ")

If datCurrentTimeStamp > Date And
datCurrentTimeStamp <= Date + 1 Then

With rstA

.MoveFirst
.Edit
!MK_CAT = lngMkCat
!GRADE = strGrade
!Type = strType
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = frmA.EffectiveDate
!TIME_STAMP = Date + Time
.Update

End With

Else

With rstA

.MoveFirst
.Edit
!INACTIVE_DATE = frmA.EffectiveDate
.Update

.AddNew
!MK_CAT = lngMkCat
!GRADE = strGrade
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = frmA.EffectiveDate
!TIME_STAMP = Date + Time
.Update
End With

End If

End If

DoCmd.SetWarnings True
End Sub
***********************************************

Any ideas?
 
D

Dan Artuso

Hi,
The very first thing to do here is:
Debug.Print strSql
Exit Sub

right after you build your strSql.
Take a look at it and see what it looks like.
 
C

Chadlon

Mike,

Nothing concrete, but a few points regarding sources of nervousness ...

You make the task a bit harder by not saying at what point the error occurs,
but anyway -

It's a good idea to steer clear of field/control names that duplicate system
keywords - such as Type, Parent etc. You might seem to get away with it much
of the time, but then when you're not looking it bites. If you choose names
that (a) describe the property/attribute and (b) hint at the data type then,
firstly, you will more naturally avoid keyword clash and secondly, when
building SQL strings you'll have a better chance of knowing whether quotes
etc are required without constant reference to the schema. For example, how
does one know the data type of 'Grade' and 'Type' to be sure that they
do/don't need quotes.

You might want to avoid needless repetition of the same concatenation - only
invites keystroke errors when writing and involves wasted effort when
checking. You have the same string 3 times and a typo anywhere in there will
likely give the error you report.

You seem to wait a long time before deciding that you should not even be in
the routine. The 'has price changed' test (if that is what it is) is delayed
for no obvious reason. In this case that might be a blessing - it has
allowed the error to show up.

Trying to work out what the routine is doing requires some knowledge of the
data sets - and there are no clues in the code or comments. One strange
point is that the AddNew sequence does not assign a value to the Type field.
Just adds another question to the puzzle.

So, if I had to bet, I'd opt for either a snarl-up over the field name
"Type" or one or both of the fields Grade and Type does or does not require
quote-protection around the comparison value.

CD
Another version of the same routine:
Sub SaveChangedMarkerPrices(strGrade As String _
, strType As String _
, dblCurrentMarkerPrice As Double _
, ctlMarkerPrice As Control)

Dim dbsA As DAO.Database, rstA As DAO.Recordset
' Dim frmA As Form
Dim strSql As String
Dim lngMkCat As Long, crit$, var, keyDate

If dblCurrentMarkerPrice = ctlMarkerPrice Then Exit Sub

DoCmd.SetWarnings False
crit$ = "[GRADE] = '" & strGrade & "' and [TYPE] = '" & strType & "' "
var = DMax("[MK_CAT]", "Current Marker Prices", crit$)
If IsNull(var) Then Exit Sub

lngMkCat = CLng(var)
strSql = " SELECT * " & _
" FROM [Competitor Prices]" & _
" WHERE [MK_CAT] = " & CStr(lngMkCat) & _
" AND [INACTIVE_DATE] IS NULL And " & _
crit$

keyDate = [Forms]![Pricing Screen]!EffectiveDate

Set dbsA = CurrentDb
Set rstA = dbsA.OpenRecordset(strSql, dbOpenDynaset)

datCurrentTimeStamp = DMax("[TIME_STAMP]", "Current Marker Prices",
crit$)
If datCurrentTimeStamp > Date And datCurrentTimeStamp <= Date + 1 Then
' alternatively If Int(xxx) = Date ??
With rstA
.MoveFirst
.Edit
!MK_CAT = lngMkCat
!GRADE = strGrade
!Type = strType
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = keyDate
!TIME_STAMP = Now
.Update
End With
Else
With rstA
.MoveFirst
.Edit
!INACTIVE_DATE = keyDate
.Update

.AddNew
!MK_CAT = lngMkCat
!GRADE = strGrade
!Type = strType ' ??? required ???
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = keyDate
!TIME_STAMP = Now
.Update
End With
End If
Set rstA = Nothing
Set dbsA = Nothing
DoCmd.SetWarnings True
End Sub




Mike Collard said:
MS offer an explanation of the above error but it doesn't
work for me.

I get the error when trying to run the folowing:

Sub SaveChangedMarkerPrices(strGrade As String, strType As
String, dblCurrentMarkerPrice As Double, ctlMarkerPrice As
Control)

Dim dbsA As Database, rstA As Recordset
Dim frmA As Form
Dim strSql As String
Dim lngMkCat As Long

DoCmd.SetWarnings False

Set frmA = [Forms]![Pricing Screen]

If IsNull(DMax("[MK_CAT]", "Current Marker
Prices", "[GRADE] = " & "'" & strGrade & "' and [TYPE] = "
& "'" & strType & "' ")) Then Exit Sub

lngMkCat = DMax("[MK_CAT]", "Current Marker
Prices", "[GRADE] = " & "'" & strGrade & "' and [TYPE] = "
& "'" & strType & "' ")

strSql = "SELECT * from [Competitor Prices] WHERE [MK_CAT]
= " & lngMkCat & " AND [GRADE] = " & "'" & strGrade & "'
AND [TYPE] = " & "'" & strType & "' AND [INACTIVE_DATE] IS
NULL"

Set dbsA = CurrentDb
Set rstA = dbsA.OpenRecordset(strSql, dbOpenDynaset)

If dblCurrentMarkerPrice <> ctlMarkerPrice Then

datCurrentTimeStamp = DMax
("[TIME_STAMP]", "Current Marker Prices", "[GRADE] = "
& "'" & strGrade & "' AND [TYPE] = " & "'" & strType
& "' ")

If datCurrentTimeStamp > Date And
datCurrentTimeStamp <= Date + 1 Then

With rstA

.MoveFirst
.Edit
!MK_CAT = lngMkCat
!GRADE = strGrade
!Type = strType
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = frmA.EffectiveDate
!TIME_STAMP = Date + Time
.Update

End With

Else

With rstA

.MoveFirst
.Edit
!INACTIVE_DATE = frmA.EffectiveDate
.Update

.AddNew
!MK_CAT = lngMkCat
!GRADE = strGrade
!PRICE_PPL = ctlMarkerPrice
!EFFECTIVE_DATE = frmA.EffectiveDate
!TIME_STAMP = Date + Time
.Update
End With

End If

End If

DoCmd.SetWarnings True
End Sub
***********************************************

Any ideas?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Too few parameters, expected n when executing SQL from VBA 2
Error 3061: Too few parameters. Expected 1 1
Too Few Parameters, 1 expected 1
too few parameters in SQL statement (expected 1) 2
ListBox Coding Problem: Too few parameters. Expected 1. 5
Error 3061 Too few parameters expected 1 - good tested query 4
Runtime error 'Too few parameters. Expected 1' 2
Run-time error '3061': Too few parameters. Expected 1. 6

Top