473,234 Members | 1,628 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,234 software developers and data experts.

Problem with rs.Update I think?

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("Basesalary")
Index = rs3.Fields("bachelorsindex")
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
Nov 12 '05 #1
2 3563
DFS

"Joseph Markovich" <jo*@josephmarkovich.com> wrote in message
news:b5*************************@posting.google.co m...
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("Basesalary")
Index = rs3.Fields("bachelorsindex")
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



Nov 12 '05 #2
"Joseph Markovich" <jo*@josephmarkovich.com> wrote in message
news:b5*************************@posting.google.co m...
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("Basesalary")
Index = rs3.Fields("bachelorsindex")
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 "MyFieldName", 50

To update the field called "MyFieldName" 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("MyFieldName") = 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.Connection
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, adLockOptimistic

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
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Frnak McKenney | last post by:
I'm using an in-core DataSet as an image of my application's 'database' (a multi-table Access97 mdb file). Updates are made to the DataTables within the DataSet via forms with bound TextBoxes,...
3
by: Steven Blair | last post by:
Hi, I have a problem understanding the SqlDataAdaptor and the DataSet. I have a dll which has various methods for running stored procedures etc. It also has a method which allows the client to...
7
by: Peter D.C. | last post by:
Hi I want to update data hold in several textbox controls on an asp.net form. But it seems like it is the old textbox values that is "re-updates" through a stored procedure who updates a SQL...
2
by: Mike Collins | last post by:
I cannot get the correct drop down list value from a drop down I have on my web form. I get the initial value that was loaded in the list. It was asked by someone else what the autopostback was...
7
by: Dabbler | last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the following error when trying to update (ExecuteNonQuery): System.Data.SqlClient.SqlException: Procedure or Function...
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
6
by: Jeff North | last post by:
I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00 and expriencing problems with setting referential integrity on a link table. The tables' schema is as follows:...
7
by: Vlado Jasovic | last post by:
Hello, I'm using typed dataset for databinding to windows controls and I'm having some problems. I'm trying to move all business logic to datatable column_changing events and the problem that...
11
by: SAL | last post by:
Hello, I have a Gridview control (.net 2.0) that I'm having trouble getting the Update button to fire any kind of event or preforming the update. The datatable is based on a join so I don't know...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.