473,320 Members | 2,054 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,320 software developers and data experts.

Updating recordset programmatically : <Item not found in this collection.>

I had some help on this one earlier, but I'm still having a bit of
trouble. I'm sure it's something simple that I just don't know.

I'm using Access2000. I have one table with employee salary
information. I need to calculate the bi-weekly gross pay (BWG) for
each employee. The BWG is slightly different for leap years. As the
BWG will have to be adjusted at the beginning and end of every leap
year, I want to be able to update all employees' BWG with one mouse
click. I've written a function in a module to do this, but I'm
getting an error "Data type conversion error." and the line
rs(fldBWG) = rs(fldSal) * 0.038251
is highlighted.

I'm not sure if this is the correct way to set the field vars, but I
did it like this:
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")
where Salary and BWG are field names in the table tblEmployees.
Both Salary and BWG are of data type Currency, format: Currency,
Decimal Places: 2.

When the code stops executing at the highlighted line:
fldSal has correct value of the field Salary of the first record.
Mousing over the 'rs' in rs(fldSal) gives the message <Data type
conversion error.>
fldBWG has current value of the field BWG of the first record before
the assignment (as the execution stops before the assignment has been
made.)
Mousing over the 'rs' in rs(fldBWG) gives the message <Item not found
in this collection.>

This last item seems to be the problem, but I don't know why it's "not
found in this collection."

Below is my entire code.
Any help (or suggestions of a better way to do this) is appreciated.

Public Function BWGUpdate()
' calcualte and display bi-weekly gross

Dim intYear As Integer ' current year
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fldSal As DAO.Field ' salary
Dim fldBWG As DAO.Field ' bi-weekly gross

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEmployees")
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")

' get current year
intYear = Val(Right(Date, 4))

rs.MoveFirst
Do While Not rs.EOF
'check for leap year
If blnLeapYear(intYear) Then
rs(fldBWG) = rs(fldSal) * 0.038251 ' error occurs here
Else
rs(fldBWG) = rs(fldSal) * 0.038356
End If

rs.Update
rs.MoveNext
Loop

rs.Close
End Function

Public Function blnLeapYear(intYear As Integer) As Boolean
' check if current year is a leap year
' if 2/29/intYear is a valid date, we are in a leap year
blnLeapYear = IsDate("2/29/" & intYear)
End Function
Nov 13 '05 #1
2 3946
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're doing too much work (and your trying to multiply Field objects
instead of numbers). Get rid of the Field assignment statements and try
this as the calculation:

If blnLeapYear(intYear) Then
rs!BWG = rs!Salary * 0.038251
Else
rs!BWG = rs!Salary * 0.038356
End If

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQQkiXYechKqOuFEgEQLMNwCgmkiCG9PPHuVG3KYVPDtUa5 q62egAnAg/
olPpuOAqncpoDpRfXxsCToA7
=Y8LP
-----END PGP SIGNATURE-----
RBohannon wrote:
I had some help on this one earlier, but I'm still having a bit of
trouble. I'm sure it's something simple that I just don't know.

I'm using Access2000. I have one table with employee salary
information. I need to calculate the bi-weekly gross pay (BWG) for
each employee. The BWG is slightly different for leap years. As the
BWG will have to be adjusted at the beginning and end of every leap
year, I want to be able to update all employees' BWG with one mouse
click. I've written a function in a module to do this, but I'm
getting an error "Data type conversion error." and the line
rs(fldBWG) = rs(fldSal) * 0.038251
is highlighted.

I'm not sure if this is the correct way to set the field vars, but I
did it like this:
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")
where Salary and BWG are field names in the table tblEmployees.
Both Salary and BWG are of data type Currency, format: Currency,
Decimal Places: 2.

When the code stops executing at the highlighted line:
fldSal has correct value of the field Salary of the first record.
Mousing over the 'rs' in rs(fldSal) gives the message <Data type
conversion error.>
fldBWG has current value of the field BWG of the first record before
the assignment (as the execution stops before the assignment has been
made.)
Mousing over the 'rs' in rs(fldBWG) gives the message <Item not found
in this collection.>

This last item seems to be the problem, but I don't know why it's "not
found in this collection."

Below is my entire code.
Any help (or suggestions of a better way to do this) is appreciated.

Public Function BWGUpdate()
' calcualte and display bi-weekly gross

Dim intYear As Integer ' current year
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim fldSal As DAO.Field ' salary
Dim fldBWG As DAO.Field ' bi-weekly gross

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblEmployees")
Set fldSal = rs.Fields("Salary")
Set fldBWG = rs.Fields("BWG")

' get current year
intYear = Val(Right(Date, 4))

rs.MoveFirst
Do While Not rs.EOF
'check for leap year
If blnLeapYear(intYear) Then
rs(fldBWG) = rs(fldSal) * 0.038251 ' error occurs here
Else
rs(fldBWG) = rs(fldSal) * 0.038356
End If

rs.Update
rs.MoveNext
Loop

rs.Close
End Function

Public Function blnLeapYear(intYear As Integer) As Boolean
' check if current year is a leap year
' if 2/29/intYear is a valid date, we are in a leap year
blnLeapYear = IsDate("2/29/" & intYear)
End Function


Nov 13 '05 #2
Thank you for your help. I have worked with Access enough to be
mostly comfortable with it, and I'm comfortable writing in VB, but I
have little experience using VBA with Access. I have recently been
given several assignments where I have found the need to use VBA. I
have asked several questions of this group in the last few weeks, and
I have continually received helpful responses. I expect I will have
more questions in the future.

Thank you to all who have been so helpful.

Randy
Nov 13 '05 #3

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

Similar topics

7
by: Rob Meade | last post by:
Lo all, I'm having a little bit of trouble (betty...). I have removed some of the obvious stuff from this example (like connections being opened/closed etc) I create a recordset in ASP (not...
3
by: Nachi | last post by:
I am getting 2 resultsets depending on conditon, In the second conditon i am getting the above error could anyone help me.......... CREATE proc sp_count_AllNewsPapers @CustomerId int as...
2
by: Donald Firesmith | last post by:
I am having trouble having Google Adsense code stored in XSL converted properly into HTML. The <> unfortunately become &lt; and &gt; and then no longer work. XSL code is: <script...
8
by: Tom | last post by:
Hi I have an aspx page which has javascript to configure xmldata. I added breakpoint to the button method. When I click submit button, it did not go to those breakpoint and show the following...
5
by: cover | last post by:
After a while of deleting records in a MySQL db, there gets to be the gaps in the id numbering system. i.e. 1, 2, 3, 6, 7, 12, and so on. Is there a way to renumber the id system in a table for 1,...
10
by: Szabolcs Horvát | last post by:
Consider the attached example program: an object of type 'A' is inserted into a 'map<int, Am;'. Why does 'm;' call the copy constructor of 'A' twice in addition to a constructor call? The...
3
by: ajay2552 | last post by:
Hi, I have a query. All html tags start with < and end with >. Suppose i want to display either '<' or '>' or say some text like '<Company>' in html how do i do it? One method is to use &lt,...
14
by: Michael | last post by:
Since the include function is called from within a PHP script, why does the included file have to identify itself as a PHP again by enclosing its code in <?php... <?> One would assume that the...
0
by: leon70 | last post by:
Hi Group, I built a gSoap 2.7.11 Web services client in C++ on AIX and have got it talking to the remote service. The service is sending back an array of objects to my client but the client is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.