SQL syntax and ADO

[Guest]

Hello,
I am trying to write some code to be able to modify a recordset based on 2
criteria but I'm always getting errors like "no value given for one or more
required parameters".
I have tried different way to find the required recordset but no success.
If I use the .find method thje error I get is "Rowset does not support
scrolling backward"
This is the firat time I am using the ADO and SQL in hard code
Can enyone help me trying to understand my problem
Here is my code:

Dim Rst As ADODB.Recordset
Set Rst = New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
Dim num As Integer
Dim val As Double
Dim strSQL As String, var As String
num = Forms!Budget!IdBranch
val = Me.Avril
var = "Other Exp"

Rst.ActiveConnection = CurrentProject.Connection
Rst.Open "SELECT * FROM Budget"
'WHERE Budget.IdBranch = Forms!Budget!IdBranch"
'AND budget.Variante = var"

strSQL = "[IdBranch] = " & num
'strSQL = strSQL & " AND "
'strSQL = strSQL & "[Variante] = " & var

Rst.MoveFirst
Rst.Find strSQL
With Rst
.Open
Avril = val
Total = Janvier + Fevrier + Mars + Avril + Mai + Juin + Juillet _
+ Aout + Septembre + Octobre + Novembre + Decembre
.Save
End With

Rst.Close
Set Rst = Nothing
conn.Close
Set conn = Nothing

Thanks

 

[Tim Ferguson]

I am trying to write some code to be able to modify a recordset based
on 2 criteria but I'm always getting errors like "no value given for
one or more required parameters".

Actually this is a DAO group -- you'll find more help on ADO methods on
groups that have ADO in the name.

If I use the .find method thje error I get is "Rowset does not support
scrolling backward"

You need to check the parameters for the ADODB.Recordset.Open method --
the default is set to be a short, fast, forward-only snapshot. You will
need a proper dynamic cursor to navigate to random records. Sorry I can't
remember the details but they are well documented in the help files.

A few other thoughts:-

As far as I can see, this variable is never used?

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

This is the line to check for recordset types

Rst.Open "SELECT * FROM Budget"

Yes: I would really embed all the WHERE criteria into the original SQL
and get rid of the .Find method altogether. Kinder to the network by only
transferring one record, and kinder to other users by not locking records
unnecessarily.

Rst.Find strSQL

Oooh I say: this looks like a Major Design Problem. Whatever happened to

SELECT SUM Amount AS YearlyTotal
FROM MonthlyBudgets
WHERE IDBranch = "Somebranch"
AND CurrentYear = 2004
AND Variante = "Other Exp"

Total = Janvier + Fevrier + Mars + Avril + Mai + Juin + Juillet _
+ Aout + Septembre + Octobre + Novembre + Decembre

Since conn is never set, this is not necessary