Problem with rs.Update I think?

[Joseph Markovich]

I'm having some trouble with VB in Access 2000. I have a form that the
user enters in just one number (in this case, it's a base salary) and
then the program is going to do a bunch of math (which is not all
shown) and populate a table with these new, calculated salary values.
I know I shouldn't be storing calculated values in the table, but I
have to have these numbers in there as a lookup too.

Anyway I think I might be getting too complex with this, but any
assistance anyone can give me (or pointers to make it simpler if
possible) would be greatly appreciated.

It errors out at the rs.Update line and I cannot figure out why. The
code is below.

Private Sub Populate_Click( )
' populate the salary schedule columns

Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
'Dim SQL_query As String
Dim Counter As Integer
Dim BaseSalary As Long
Dim Index As Currency
Dim Salary As Long
'Dim yearsexp As Integer
Dim Updated As Boolean

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
Counter = 0
Index = 0

'open the recordset to get all the salary values
rs.Open ("SELECT * FROM salaryschedule" ), CurrentProject. Connection
rs2.Open ("SELECT * FROM contractbase"), CurrentProject. Connection
rs3.Open ("SELECT * FROM indexes"), CurrentProject. Connection

rs.MoveFirst
rs2.MoveFirst
rs3.MoveFirst

'loop with counter based on year of experience and perform
calculations of each salary amount based on year and index pulled from
DB
Do Until Counter = 21
BaseSalary = rs2.Fields("Bas esalary")
Index = rs3.Fields("bac helorsindex")
Salary = (BaseSalary * Index)

'Updated = rs.Update("400" , Salary)
Updated = rs.Update("400" , Salary) 'it errors out here constantly with
"expected function or variable error"
Counter = Counter + 1
rs.MoveNext
rs3.MoveNext
Loop

Am I doing or overlooking something really stupid? Thanks! -Joe

[DFS]

"Joseph Markovich" <jo*@josephmark ovich.com> wrote in message
news:b5******** *************** **@posting.goog le.com...

I'm having some trouble with VB in Access 2000. I have a form that the
user enters in just one number (in this case, it's a base salary) and
then the program is going to do a bunch of math (which is not all
shown) and populate a table with these new, calculated salary values.
I know I shouldn't be storing calculated values in the table, but I
have to have these numbers in there as a lookup too.

Anyway I think I might be getting too complex with this, but any
assistance anyone can give me (or pointers to make it simpler if
possible) would be greatly appreciated.

It errors out at the rs.Update line and I cannot figure out why. The
code is below.

Private Sub Populate_Click( )
' populate the salary schedule columns

Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
'Dim SQL_query As String
Dim Counter As Integer
Dim BaseSalary As Long
Dim Index As Currency
Dim Salary As Long
'Dim yearsexp As Integer
Dim Updated As Boolean

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
Counter = 0
Index = 0

'open the recordset to get all the salary values
rs.Open ("SELECT * FROM salaryschedule" ), CurrentProject. Connection
rs2.Open ("SELECT * FROM contractbase"), CurrentProject. Connection
rs3.Open ("SELECT * FROM indexes"), CurrentProject. Connection

rs.MoveFirst
rs2.MoveFirst
rs3.MoveFirst

'loop with counter based on year of experience and perform
calculations of each salary amount based on year and index pulled from DB Do Until Counter = 21
BaseSalary = rs2.Fields("Bas esalary")
Index = rs3.Fields("bac helorsindex")
Salary = (BaseSalary * Index)

'Updated = rs.Update("400" , Salary)
Updated = rs.Update("400" , Salary) 'it errors out here constantly with
"expected function or variable error"
You're mixing together variable assignments, recordset updates, and what
appears to be a function. Try:
rs.Edit
rs("Salary") = 400
rs.Update

Or is 400 your column name? If so:
rs.Edit
rs("400") = Salary
rs.Update

Counter = Counter + 1
rs.MoveNext
rs3.MoveNext
Loop

Am I doing or overlooking something really stupid? Thanks! -Joe

[Fletcher Arnold]

"Joseph Markovich" <jo*@josephmark ovich.com> wrote in message
news:b5******** *************** **@posting.goog le.com...

I'm having some trouble with VB in Access 2000. I have a form that the
user enters in just one number (in this case, it's a base salary) and
then the program is going to do a bunch of math (which is not all
shown) and populate a table with these new, calculated salary values.
I know I shouldn't be storing calculated values in the table, but I
have to have these numbers in there as a lookup too.

Anyway I think I might be getting too complex with this, but any
assistance anyone can give me (or pointers to make it simpler if
possible) would be greatly appreciated.

It errors out at the rs.Update line and I cannot figure out why. The
code is below.

Private Sub Populate_Click( )
' populate the salary schedule columns

Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
Dim rs3 As ADODB.Recordset
'Dim SQL_query As String
Dim Counter As Integer
Dim BaseSalary As Long
Dim Index As Currency
Dim Salary As Long
'Dim yearsexp As Integer
Dim Updated As Boolean

Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
Set rs3 = New ADODB.Recordset
Counter = 0
Index = 0

'open the recordset to get all the salary values
rs.Open ("SELECT * FROM salaryschedule" ), CurrentProject. Connection
rs2.Open ("SELECT * FROM contractbase"), CurrentProject. Connection
rs3.Open ("SELECT * FROM indexes"), CurrentProject. Connection

rs.MoveFirst
rs2.MoveFirst
rs3.MoveFirst

'loop with counter based on year of experience and perform
calculations of each salary amount based on year and index pulled from
DB
Do Until Counter = 21
BaseSalary = rs2.Fields("Bas esalary")
Index = rs3.Fields("bac helorsindex")
Salary = (BaseSalary * Index)

'Updated = rs.Update("400" , Salary)
Updated = rs.Update("400" , Salary) 'it errors out here constantly with
"expected function or variable error"
Counter = Counter + 1
rs.MoveNext
rs3.MoveNext
Loop

Am I doing or overlooking something really stupid? Thanks! -Joe

As far as I can see, you need to return to the basics of opening, updating
and closing one recordset before you open 3. The first and most obvious
thing is this:

Updated = rs.Update("400" , Salary)

Where did this come from? The recordset has a *method* called 'Update' - it
is *not* a function. It looks like you are hoping for it to return a value
of True or False. In fact, you simply write:

rst.Update "MyFieldNam e", 50

To update the field called "MyFieldNam e" with a value of 50. (Do you really
have a field named "400"?)

Even using the update method is slightly unusual and in most of the coding I
see, it is not used at all. In DAO you had to use rst.update but using ADO
you could simply write:

rst("MyFieldNam e") = 50

And in my opinion it is clearer what is going on.
Secondly, and also very importantly is the fact that you have opened a new
recordset without specifying a LockType so you will be left with a default
value of adLockReadOnly. This makes it difficult to update the recordset!
If I had to loop through a recordset, my code might look like this:
Private Sub cmdUpdate_Click ()

On Error GoTo Err_Handler

Dim strSQL As String
Dim cnn As ADODB.Connectio n
Dim rst As ADODB.Recordset
Dim lngCount As Long

strSQL = "SELECT * FROM MyTable"

' Not always the fastest, but anyway:
Set cnn = CurrentProject. Connection

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn, adOpenDynamic, adLockOptimisti c

While Not rst.EOF
lngCount = lngCount + 1
rst("MyField") = lngCount
rst.MoveNext
Wend

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not cnn Is Nothing Then
Set cnn = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

Fletcher