Newbie with SQL & ADO

[Alain]

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

 

[Dirk Goldgar]

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

Bonjour, Alain -

There are many problems with your code, and even a question as to
whether you need to be opening a recordset to perform your update at
all.

First, to open a recordset on Budget that will allow the use of the Find
method, this would suffice:

Dim Rst As ADODB.Recordset
' note: the "conn" variable is not needed.
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"

Set Rst = New ADODB.Recordset

Set Rst.ActiveConnection = CurrentProject.Connection

Rst.Open "SELECT * FROM Budget", , adOpenKeySet
' The above statement must specify a cursor type that
' supports bidirectional movement.

Second, if you want to be able to restrict your recordset by
uncommenting these statements ...

'WHERE Budget.IdBranch = Forms!Budget!IdBranch"
'AND budget.Variante = var"

.... you must modify them to embed the values of the variables, not the
names:

& " WHERE Budget.IdBranch = " & num _
& " AND budget.Variante = '" & var & "'"

Third, be aware that the Find method only permits a single column to be
searched, so this ...

strSQL = "[IdBranch] = " & num

.... will work, but you won't be able to uncomment these statements:

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

However, if you opened the recordset already filtered for those values,
you don't need to do that, or to call the Find method at all.

Fourth, this line ...

.Open

.... is inappropriate where you have it, and should be removed.

Fifth, in these lines:

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

.... are the month names (Janvier through Decembre) and Total all
supposed to be the names of fields in the recordset? If so, you must
identify them as such. Try this:

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

Sixth, this line:

.Save

should be

.Update

The Save method is for saving the recordset to a file or stream object.

I *think*, if I've understood properly what you are trying to do, those
changes should make it work. However, I recommend that you not have a
Total field in your Budget table, if its value should always be the sum
of the values of the individual months. It is far better to calculate
the total whenever you need it -- that could be done by a calculated
field in a query.

And once you've eliminated the need to update the Total field after
you've updated Avril, there's really no need to use a recordset at all.
It would be much more efficient just to execute an update query:

CurrentProject.Connection.Execute _
"UPDATE Budget SET Avril = " & val & _
" WHERE "[IdBranch] = " & Forms!Budget!IdBranch & _
" AND Variante = '" & var & "'"

.... or something like that.