Table Update ! **Resolved**

[Alankar]

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

[Ecniv]

Not sure, VBA can be finicky... Try without the dbopendynaset.

Also you may (depending on how many records) want to opt for an Update Sql statement and execute it (either in code or hold it as a query and execute in code).

Also - you had better look for the chance that no records are returned, in which case your code will error. Something to think about.

[Alankar]

Hi Ecniv,

I tried it without dbOpenDynaset but no success. It is giving error message "RunTime error '3061' Too few parameters. Expected 1." I can't understand it.

I'll deal with Null, later, first I have to it optimistic.

Regards,

Any clue ?

Alankar

[Ecniv]

Ah ok.. welll that says that it needs a parameter or perhaps you are using something it doesn't like (data type mismatch) in the query.

Is the Project ID a number or a string..?
If it is a number, you do not need the single quotes.

[FunkyDexter]

Not sure if this'll help because it looks like you've gone down the 'doing it in vba' route but the sql query to do this would be:-

update projectTable
set sumField =
(select sum(summedField) from relatedTable r
inner join projectTable p
on (p.projectID = r.projectID))

I don't have access installed to check this but it's valid sql and works in SQL server so access should be happy with it (once you've substituted the apropriate field and table names of course).

If you can handle it as a sql query rather than in vba it should be much more efficient.

[Alankar]

Hi Ecniv,

But ProjectID is a string in both the tables.
What is the problem ???

Regards,

Alankar

Ah ok.. welll that says that it needs a parameter or perhaps you are using something it doesn't like (data type mismatch) in the query.

Is the Project ID a number or a string..?
If it is a number, you do not need the single quotes.

[Alankar]

Hi,

I tried following update query in Access but it didn't work.

UPDATE tbTimeSheet INNER JOIN tbProject ON tbTimeSheet.PrjID = tbProject.PrjID SET tbProject.ActPrjHrs = (Sum([duration]));

Please help me out.

Regards,
Alankar

Not sure if this'll help because it looks like you've gone down the 'doing it in vba' route but the sql query to do this would be:-

update projectTable
set sumField =
(select sum(summedField) from relatedTable r
inner join projectTable p
on (p.projectID = r.projectID))

I don't have access installed to check this but it's valid sql and works in SQL server so access should be happy with it (once you've substituted the apropriate field and table names of course).

If you can handle it as a sql query rather than in vba it should be much more efficient.

[Alankar]

Hi,

Now I am using following code which is working fine.

********************CODE***********************
Private Sub Report_Open(Cancel As Integer)
'Enter this project's total Actual Time
Dim DB As Database
Dim Rst As Recordset
Dim Rst1 As Recordset

Set DB = CurrentDb()

'Get Total Actual Project time from TimeSheet
Actprj = "SELECT (Int(CSng(Sum([Duration])))*24)+Format(Sum([Duration]),'hh') & ' : ' & Format(Sum([Duration]),'nn') AS Expr1" _
& " FROM tbTimeSheet HAVING ((tbTimeSheet.PrjID)='" & Form_TmpInfoPrj.cmbPrj.Value & "')"
Set Rst = DB.OpenRecordset(Actprj)
If Not IsEmpty(Rst) Then
ActPrjTime = IIf(IsNull(Rst.Fields(0)), "00:00", Rst.Fields(0))

'Update Total Project Time to Project Table
Actprj1 = "SELECT ActPrjHrs" _
& " FROM tbProject WHERE ((tbProject.PrjID)='" & Form_TmpInfoPrj.cmbPrj.Value & "')"

Set Rst1 = DB.OpenRecordset(Actprj1)
Rst1.Edit
Rst1.Fields(0) = ActPrjTime
Rst1.Update
Rst1.Close
End If
Rst.Close
Set Rst = Nothing
DB.Close
Set DB = Nothing
Report_RPrjRep.RecordSource = RepStr
End Sub

*******************CODE**********************

Now problem of NULL is there as it may be the case that no record found in the tbTimeSheet table for the selected projectID. What should I do to prevent this ?

Regards,
Alankar

[Ecniv]

Ok... That uh I suppose works.

Better thing would be to debug.print sql1 and see what it actually is. Maybe there combo box is not sending the data you think it is.
If you use a breakpoint and put it on the rst.open... line the program will stop running there. Press Ctrl+G to get to the immediates window and type:

?sql1

This should print the sql statement below. Copy the whole text, and go to ms access. Make a new query, but close all the pop up windows asking for tables. The Top left there should be a drop down on the toolbar to change to sql view, or go through the view>sql view on the menus.
Paste in the sql statement. And Press the Exclamation mark to run. If it errors, it should give you a more meaningful (hopefully) location of the error.



As to the no records returned.
If you do an rst.open and it works, you have a recordset.
If there are no records in that recordset, then the EOF (end of file) flag is set immediately. You can do things depending on that flag...
So your code would be something like:

on error resume next 'ignores errors
rst.open ......
if err.number=0 then
  if rst.eof then
    msgbox "No records"
  else
    msgbox "Has records"
    'do stuff with records
  end if  
endif
rst.close

[Alankar]

Hi Ecniv,

I used EOF function on Recordset but, but whether the record is there or not, the recordcount returns 1 and EOF returns false. What is this mess, I can't get it.

I know the recordset may have 1 record or no record then why it is showing recordcount=1 and EOF=False in both the cases ?

I used Immidiate Window and debug tricks you suggested.

Regards,
Alankar

[FunkyDexter]

UPDATE tbTimeSheet INNER JOIN tbProject ON tbTimeSheet.PrjID = tbProject.PrjID SET tbProject.ActPrjHrs = (Sum([duration]));

Yeah - it barfs on the inner join though I'm not sure why. At a glance I'd expect your query to work. Hang on, I'll have a fiddle with my one and substitute in your table names etc.

[Ecniv]

You are using DAO, so the recordcount will only be right if you move to the last record and move back to the first record. If EOF is set then you have no records to move to.
Trust the EOF

UPDATE 
tbTimeSheet INNER JOIN tbProject ON tbTimeSheet.PrjID = tbProject.PrjID 
SET tbProject.ActPrjHrs = (Sum([duration]));

If I read this right you are updating the timesheet table, but are setting the a field in the project..???

[FunkyDexter]

Try This:-

update tbProject set ActPrjHrs =
(
select sum([duration])
from tbTimeSheet
inner join tbProject
on (tbTimeSheet.PrjID = tbProject.PrjID)
)

If your project table includes a duration field as well as the timesheet table you'll need to qualify the sum(duration) bit with the table name. So select sum(tbTimeSheet.[duration]).

I think (but don't quote me on it) that your query barfed because you can't update a joined dataset (though I have a vague memory Oracle's PL/SQL lets you - someone may correct me there though). My query above is slightly different because it's only updating a single table but it's getting the value it wants from a join.

[Alankar]

Hi,

Now I changed some lines and inserted Rst.Movelast and Rst.MoveFirst and then cheking Rst.Recordcount and Rst.EOF bot are still same. I am using Linked tables in my MDB. Could it be a problem ?

Set Rst = DB.OpenRecordset(Actprj)
Rst.MoveLast
Rst.MoveFirst
If Not Rst.EOF Then
ActPrjTime = Rst.Fields(0)

Also I made a query in Access for the same select statement

SELECT (Int(CSng(Sum([Duration])))*24)+Format(Sum([Duration]),'hh') & ' : ' & Format(Sum([Duration]),'nn') AS Expr1
FROM tbTimeSheet
WHERE (((tbTimeSheet.PrjID)=[Form_TmpInfoPrj].[cmbPrj].[Value]));

It also returns a record in both the cases, but when there is no record then the record it shows is #ERROR. Could you get get now ?

Regards,
Alankar

You are using DAO, so the recordcount will only be right if you move to the last record and move back to the first record. If EOF is set then you have no records to move to.
Trust the EOF


If I read this right you are updating the timesheet table, but are setting the a field in the project..???

[Ecniv]

See my pm about the .eof order.
Also see your other thread about nulls.

[Alankar]

Hi Ecniv,

But this also not worked.

Still it is giving error. But to prevent this I used On error Resume Next and IsEmpty functions.

***********************CODE*********************

Set Rst = DB.OpenRecordset(Actprj)
Rst.MoveLast
Rst.MoveFirst
On Error Resume Next
If Not Rst.EOF And Not Rst.BOF Then
ActPrjTime = IIf(IsNull(Rst.Fields(0)), "00:00", Rst.Fields(0))

'Update Total Project Time to Project Table
Actprj1 = "SELECT ActPrjHrs" _
& " FROM tbProject WHERE ((tbProject.PrjID)='" & Form_TmpInfoPrj.cmbPrj.Value & "')"

Set Rst1 = DB.OpenRecordset(Actprj1)
Rst1.Edit
Rst1.Fields(0) = IIf(IsEmpty(ActPrjTime), "00:00", ActPrjTime)
Rst1.Update
Rst1.Close
End If

**********************CODE*********************

It is not good but working fine.

Regards,
Alankar

See my pm about the .eof order.
Also see your other thread about nulls.

[Ecniv]

Hi Ecniv,

On Error Resume Next
Set Rst = DB.OpenRecordset(Actprj)
If Not Rst.EOF And Not Rst.BOF Then
'---- BEFORE you move it - cause if you have no records, moving causes an error
    Rst.MoveLast
    Rst.MoveFirst


'---- in access the nz function does the same as you iif
'----    note that iif ALWAYS evaluates both arguements
'----    cdate converts to date/time I assume that as you have defaulted to 00:00 it is a date/time field
'    ActPrjTime = nz(Rst.Fields(0)), cdate("00:00"))
    ActPrjTime = IIf(IsNull(Rst.Fields(0)), "00:00", Rst.Fields(0))


    'Update Total Project Time to Project Table
    Actprj1 = "SELECT ActPrjHrs" _
            & " FROM tbProject WHERE ((tbProject.PrjID)='" & Form_TmpInfoPrj.cmbPrj.Value & "')"
    
    Set Rst1 = DB.OpenRecordset(Actprj1)
    if not rst1.eof then
        Rst1.Edit
        Rst1.Fields(0) = IIf(IsEmpty(ActPrjTime), "00:00", ActPrjTime)
       Rst1.Update
    end if
    Rst1.Close
End If

Use the code tags (the CODE above the posting box, put the actual code between the tags to get a special box...)

Try the above... Let me know what happens

[Alankar]

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

[Ecniv]

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.

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:

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)