Loops 2

[deakin69]

Hi can anyone help with the following problem please...
I have added a couple of fields (START_ODE & END_ODO BOTH NUMBER FIELDS FOR ENTERING MILEAGE) to a table (REQBOOK).

The table gets updated via a DO...LOOP statement ie.

-------------------------------------------------------
Do Until rstREQBOOK.EOF Or blnFound = True

If Me![BOOKING REF] = rstREQBOOK![BOOKING REF] Then
blnFound = True
rstREQBOOK.Edit
rstREQBOOK![end_ODO] = Me![ODO]
rstREQBOOK![START_ODO] = Me![START_ODO]
rstREQBOOK![Status] = "Returned"
rstREQBOOK![Total_Days_Hired] = Me![tdays]
rstREQBOOK![SYS_DATE_Returned] = Date
rstREQBOOK.Update
Else
End If

rstREQBOOK.MoveNext

Loop
-------------------------------------------------------

The BOOKING REF field on the table has been set up as the primary key.

My problem is that whereas before, each transaction on this table would take less than a second it now takes 20 seconds now that I have added the two new fields.
Does anyone know if this is usual behaviour for Access. If not then can anyone suggest a reason/cure for the problem ?

Thanks
Deakin69

 

[PHV]

Depending of the type of recordset (DAO or ADODB) have a look at the FindFirst or Find method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya deakin69 . . . . .

Try this:

[blue]   With rstREQBOOK
      .FindFirst "[BOOKING REF] = " & Me![BOOKING REF]
      
      If .NoMatch Then
         MsgBox "Record Not found!"
      Else
         blnFound = True
         .Edit
         ![end_ODO] = Me![ODO]
         ![START_ODO] = Me![START_ODO]
         ![Status] = "Returned"
         ![Total_Days_Hired] = Me![tdays]
         ![SYS_DATE_Returned] = Date
         .Update
      End If
   End With[/blue]

See Ya! . . . . . .

 

[deakin69]

Thanks Ace.
I tried your code but got the following message:

"Operation is not supported for this type of object"

Any other suggestions would be appreciated.

Deakin69

 

[deakin69]

Thanks to everyone who suggested a solution. Think I've cracked it. Needed a call to SQL to get the correct record in the first place.

        blnFound = False
        strSQL = "SELECT [BOOKING REF], end_odo, start_odo, status, total_days_hired, hire_cost, sys_date_returned " & _
                 "FROM REQBOOK " & _
                 "WHERE [booking ref] = " & Me![BOOKING REF] & ";"

        Set rstREQBOOK = CurrentDb.OpenRecordset(strSQL)

        rstREQBOOK.MoveFirst

        Do Until rstREQBOOK.EOF Or blnFound = True
            blnFound = True
            rstREQBOOK.Edit
            rstREQBOOK![end_ODO] = Me![ODO]
            rstREQBOOK![START_ODO] = Me![START_ODO]
            rstREQBOOK![Status] = "Returned"
            rstREQBOOK![Total_Days_Hired] = Me![tdays]
            rstREQBOOK![HIRE_COST] = Me!HIRE_COST
            rstREQBOOK![SYS_DATE_Returned] = Date
            rstREQBOOK.Update
        Loop

        rstREQBOOK.Close

 

[KenReay]

Why not use SQL properly, and use an Update Query?

DoCmd.RunSQL "UPDATE REQBOOK " & _
"SET [end_ODO] = " & Me!ODO & ", " & _
[Start_ODO] = " & Me!Satrt_ODO " &
"WHERE [booking ref] = " & Me![BOOKING REF] & ";"

You need to extend above to update the other columns,

This would be much faster than using Edit, Update



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK