Need code to update records in access database

[sklambert]

Hello,

I am trying to find some simple code to update all records in a recordset in an access database. In this case, the recordset will be a query. Since the search engine is down, I was wondering if anyone could help. I will pseudo code what I want to do:


'project management database, startdate will be on a Monday, first Enddate will be the following Friday. Total work hours in a week will be 30.

Ask for Startdate
Enddate = Startdate + 5
totalhours = 0

'If the hours for the task (record) + totalhours is less than or equal 30, make the due date the Friday of the current week, else reset totalhours and add 1 week to Enddate, and make the due date the Friday of the next week

Do until end of recordset
If totalhours + hours <= 30
add hours to totalhours
move Enddate to duedate
Else
totalhours = 0
Enddate = Enddate + 7
add hours to totalhours
move Enddate to duedate
End If
Read next record

 

[willir]

Here is one idea...

Create a function to calculate the EndDate per your requirements, and include the function in the update statement...

Function CalcEndDate(datStart As Date, intHours As Integer) As Date

Dim intHrsMax As Integer, intWeeks As Integer, EndDate As Date

intHrsMax = 30  'allows you to easily change value if requried

If IsDate(datStart) Then
    If Nz(intHours, 0) > 0 Then
        intWeeks = (intHours / intHrsMax) + 0.5001 - 1
        
        If intHours Mod intHrsMax > 0 Then
            intWeeks = intWeeks + 1
        End If
        
        EndDate = DateAdd("ww", intWeeks, datStart)
        CalcEndDate = DateAdd("d", -2, EndDate)
    Else
        CalcEndDate = datStart
    End If
Else
    CalcEndDate = Date
End If
End Function

SQL statement (you can use Query Builder)...

UPDATE YourTable SET YourTable.YourEndDateField = CalcEndDate([YourStartDateField],[YourHoursField]);

The code...
intWeeks = (intHours / intHrsMax) + 0.5001 - 1
accommodates the rounding up error you may experience in the way the Interger data type works.

The code calculates the next week and then substracts two days since I first calculate "weeks".

I tested the update for various circumstances -- seemed to work. I do not force / test for the StartDate to be a Monday.

Richard

 

[sklambert]

Thanks Richard,

This looks like it will probably work, but I also need the basic code to read and update records. Sorry, I know what I want to do, but just how to do it is the problem. I am a cobol programmer, not much VBA. I know it sounds simple, but how do I declare a recordset, read and update records? I need code like this:

Open recordset
Do until end of recordset
blah blah blah
read next record.

Thanks a bunch!

 

[willir]

The function will work with a recordset approach, but using a query is much faster.

Dim dbs as DAO.Database, rst as DAO.Recordset

Set dbs = CurrentDB()
Set rst = dbs.OpenRcordset("YourTable")

With rst
   .MoveFirst
   Do While Not .EOF
      .Edit
      !EndDate = CalcEndDate(!DateStart, !TotalHours)
      .Update
      .MoveNext
   Loop
End With

rst.Close
set rst = Nothing
dbs.Close
set dbs = Nothing

'versus
Dim strSQL as String

strSQL = "UPDATE YourTable SET YourTable.YourEndDateField = CalcEndDate([YourStartDateField],[YourHoursField]);"

DoCmd.RunSql strSQL

SQL statements are much faster with Access than the record set approach, especially with large data. But recordset allow more procedural stuff.
Richard

 

[sklambert]

Hey, thanks Richard. I am going to add this code to my database and see how it works. I'll let you know. Thanks again for the assistance!

 

[sklambert]

This helped me out and works perfectly. I have added tons of other stuff to the code to process the recordset. You got me on the right track. Thanks a million!!!