Table Update ! **Resolved**
Hi there,
I am in a trouble, please help me. I am working in Access 2003 MDB and all the tables are linked tables.
I have a table which stores project records (one record for each project). In this table there is a field which I want to edit.
The value of this field is to be taken from another table having many records for each project. The value is the sum of a field in this table if Project ID matches in both the table.
I have to do this by VBA or if I create a update query then also it is possible.
*******************CODE*******************
Private Sub Report_Open(Cancel As Integer)
'Enter this project's total Actual Time
Set DB = CurrentDb()
sql1 = "select tbproject.* from tbProject WHERE tbProject.ProjectID = '" & Form_TmpInfoPrj.cmbPrj.Value & "'"
Set rst = DB.OpenRecordset(sql1, dbOpenDynaset)
rst![ActPrjHrs] = "SELECT (Int(CSng(Sum([Duration])))*24)+Format(Sum([Duration]),'hh') & ' : ' & Format(Sum([Duration]),'nn') AS Expr1" _
& " FROM tbTimeSheet INNER JOIN tbProject ON tbTimeSheet.PrjID = tbProject.PrjID" _
& " GROUP BY tbProject.PrjID HAVING (((tbProject.PrjID)='" & Form_TmpInfoPrj.cmbPrj.Value & "))"
rst.Update
rst.Close
DB.Close
Set rst = Nothing
Set DB = Nothing
Report_RPrjRep.RecordSource = RepStr
End Sub
****************************CODE*****************
The execution stuck at the red line why, I can't understand.
Regards,
Alankar
Re: Table Update ! *Resolved*
Hi Ecniv,
Now it is working, But As I used On Error Resume Next, I think it is not good.
You didn't use IsEmpty but that is also important to check whether ActPrj is Empty or not before placing its value to any table field.
When there is no data in Rst, an error occurs at
ActPrjTime = IIf(IsNull(Rst.Fields(0)), "00:00", Rst.Fields(0))
and ActPrjTime remains empty and On Error Resume Next forces it to next statment. So we should also check ActPrjTime is empty or not (It is not Null, it is Empty.
Regards,
Alankar
Re: Table Update ! **Resolved**
Glad you managed to get it to a working stage.
On error resume next just ignores any errors that occur.
You can check err.number and err.description - if err.number is 0 then there is no error.
Quote:
When there is no data in Rst, an error occurs at
ActPrjTime = IIf(IsNull(Rst.Fields(0)), "00:00", Rst.Fields(0))
and ActPrjTime remains empty and On Error Resume
As I posted in one of your threads, iif eveulates both true and false parts, and if the ActPrjTime is a date/time or time variable, it probably doesn't like the null/empty value.
you should probably use:
Code:
if isnull(rst(0)) then
actprjtime = cdate("00:00")
else
actprjtime = rst(0)
end if
CDate to make sure the variable being stored is a date (ignore that if you want a string) depending on what is in the table.
It is up to you, as you are the one who has to a) understand what you've got/done and b) be able to explain it and c) be able to fix/update it. If for work or a project, either way eventually you have to explain what it is doing to someone.
(unless you can run away and not leave anything behind in documentation purposes, which will probably make whoever has to support it curse a lot! ;) <- j/k)