VBA DAO OpenRecordset Error

[hannoudw]

Hi,

Can anyone help me with this error?
"Two few parameters, expected 1."

I have this code:

Private Sub Article1_AfterUpdate()
Dim db As DAO.Database
Dim dataset As DAO.Recordset
Dim FirstPrice, SecondPrice As Long
Dim sql As String
Set db = CurrentDb

Set dataset = db.OpenRecordset("select price, price_after_discount " _
& "as firstprice, secondprice" _
& " from item" _
& " where article= " & CStr(Me.Article1.Value) & " ", dbOpenDynaset)

'sql = "select price, price_after_discount " _
'& "as firstprice, secondprice" _
'& " from item" _
'& " where article= " & CStr(Me.Article1.Value) & " "
'MsgBox sql

FirstPrice = CLng(dataset![FirstPrice])
SecondPrice = CLng(dataset![SecondPrice])
Me.Price1.Value = FirstPrice
Me.Price_after_discount1.Value = SecondPrice

If (Me.Price_after_discount1.Value = 0) Then
Me.prix_vente_unitaire1.Value = FirstPrice
Me.Difference1.Value = 0
Else
Me.prix_vente_unitaire1.Value = SecondPrice
Me.Difference1.Value = FirstPrice - SecondPrice
End If
db.Close
End Sub

Thanks for advance

[NeoPa]

That error occurs when trying to execute invalid SQL. It would be better when asking a SQL question to post the SQL itself if you can. Not a problem if that's too difficult for you, but generally that would be considered your responsibility before asking such a question.

It appears the SQL is equivalent to :

"select price, price_after_discount as firstprice, secondprice from item where article= %A "

where %A is replaced by the value found in Me.Article1.

It's hard to know for sure without this value available, but I would guess this is where the issue is. Either that, or one of the fields selected ([price], [price_after_dis count], [secondprice]) either doesn't exist at all or is misspelled.

We don't have the information to be much more help at this point I'm afraid.

[hannoudw]

@NeoPa
that's the code of the sql:

Private Sub Article1_AfterUpdate()
If (CheckIfExist(CLng(Me.Article1.Value)) = False) Then
MsgBox "The article number that you entered is false"
Me.Type1.SetFocus
Else

Dim db As DAO.Database
Dim dataset1 As DAO.Recordset
Dim FirstPrice, SecondPrice As Integer
Set db = CurrentDb

[B]Set dataset1 = db.OpenRecordset("select nz(Price,0), nz(Price_after_discount,0) " _
& "as firstprice, secondprice  " _
& "from item " _
& "where article= " & CStr(Me.Article1.Value) & "", dbOpenDynaset)[/B]

Me.achat_num1.Value = 1
FirstPrice = CLng(dataset1![FirstPrice])
SecondPrice = CLng(dataset2![SecondPrice])
Me.Price1.Value = FirstPrice
Me.Price_after_discount1.Value = SecondPrice

If (Me.Price_after_discount1.Value = 0) Then
Me.prix_vente_unitaire1.Value = FirstPrice
Me.Difference1.Value = 0
Else
Me.prix_vente_unitaire1.Value = SecondPrice
Me.Difference1.Value = FirstPrice - SecondPrice
End If
db.Close

End If

I didn't get you well when you was talking about the %A

[hannoudw]

I just try it with 2 SQL and it worked !!

Dim db As DAO.Database
Dim dataset1, dataset2 As DAO.Recordset
Dim FirstPrice, SecondPrice As Integer
Set db = CurrentDb

Set dataset1 = db.OpenRecordset("select nz(Price,0) " _
& "as firstprice " _
& "from item " _
& "where article= " & CStr(Me.Article1.Value) & "", dbOpenDynaset)

Set dataset2 = db.OpenRecordset("select nz(Price_after_discount,0) " _
& "as secondprice " _
& "from item " _
& "where article= " & CStr(Me.Article1.Value) & "", dbOpenDynaset)

I wonder why it keep giving error in one SQL

[NeoPa]

hannoudw:
@NeoPa
that's the code of the sql:

No. That's VBA.

hannoudw:
I didn't get you well when you was talking about the %A

That was SQL code, except your VBA would put in the string, at the point marked by %A (and replacing %A), the value from the control specified.

Assuming the control had a value of 32, then the SQL string would be :

"select price, price_after_discount as firstprice, secondprice from item where article= 32 "

[NeoPa]

The first one didn't work because there is no native field called [SecondPrice]. Your first SQL tried to access it, thus the error.

If you'd looked at the SQL originally this would all have been more obvious.

There's no reason why the two fields shouldn't be selected from the same SQL query, if they're both done correctly.