CurrentDB.Execute issue - changes are not applied

[liuc74]

Hi,


I'm not very skilled with Access and a problem with Currentdb.Execute gave me an headache.

This is the scenario:
A user select some employees checking them on a continuos form binded to a temp table (tbl_check) that I fill before the form is opened.
When he click the save button, I insert the selected employees (only the ID Field) into another table (tbl_Afferenza) which maps the N:M relationship between employee and office tables.
After that I need to update the tbl_Ufficio (office table)table setting it's NumImpiegati (nr of employee)field with the number of employee associated to that office (count on tbl_Afferenza table).

The problem is that the first insert works , but the subsequent update does not.

This is the code i wrote:

Function SaveRecord() As Boolean


Dim strSql As String
Dim blnTrans As Boolean


    On Error GoTo errSaveRecord
        
    Form.Refresh
    
    BeginTrans
    blnTrans = True
    
    With CurrentDb


        'Insert the selected employee (flag_check=true) into tbl_Affrenza (N:M relationship)


        strSql = "INSERT INTO tbl_Afferenza (MATRICOLA, IDUFFICIO) " & _
        "SELECT STRID, " & txtHiddenIdUfficio & _
        " FROM tbl_Check " & _
        " WHERE flag_check=true"
    
        .Execute strSql, dbFailOnError
        
             
        ' Update the tbl_Ufficio's NumImpiegati field with the number of employee associated to the office
        strSql = "UPDATE TBL_UFFICIO " & _
        "SET NUMIMPIEGATI = DCOUNT(""MATRICOLA"",""TBL_AFFERENZA"",""IDUFFICIO=" & txtHiddenIdUfficio & """) " & _
        "WHERE ID=" & txtHiddenIdUfficio
       
        .Execute strSql, dbFailOnError
        
        
    End With
    SaveRecord = True
    CommitTrans
    blnTrans = False


okSaveRecord:
  


    Exit Function
errSaveRecord:


    SaveRecord = 0
    If blnTrans = True Then
        blnTrans = False
        Rollback
    End If


End Function

The DCOUNT function doesn't find the record just added.

I tried with and without transaction.
I also tried to use CurrentDB.Execute each time and, as you can see, the With CurrentDB block.
I don't know how to solve it.

[Minty]

Because you are using a transaction , the records you insert aren't committed before you try your record count update.

You need to set a db reference for each update, at the moment you are doing the whole thing within one "db session", which is why the updated record doesn't appear.

Personally, I wouldn't store this value , simply use a totals query to return the current staff per office, and display it. It will always be up to date and accurate.
Secondly why all the transactional stuff anyway ? Unless you have a super flakey network I don't think it's adding anything, as you aren't asking the user to confirm to commit the changes?

You could probably also achieve this with a multiselect list box of employees, and avoid the temp table all together?

[liuc74]

Thank you for answer.
As I said, I'm not familiar with Access. I will take a look at multiselect list box.

I'm not sure that the problem is the transaction because I tried also without transaction and also with db reference for each update.
Moreover, I have updated the code adding this lines between the insert and the update

 Set rs = .OpenRecordset("Select Count(Matricola) as NumImpiegati From tbl_Afferenza Where IdUfficio=" & txtHiddenIdUfficio)
 lngNR = rs!NumImpiegati

The update is slightly modified

strSql = "UPDATE TBL_UFFICIO " & _" SET NUMIMPIEGATI = " & lngNR & _
" WHERE ID=" & txtHiddenIdUfficio
        
.Execute strSql, dbFailOnError

And now it works even within transaction.

[Minty]

Okay, that's a bit strange. But if it's working then all is good.
I still wouldn't use the transaction functions if you don't really need them.

I would also set a Db variable and use that, just a personal preference, some timing tests have shown it's quicker on certain recordset operations on large datasets.
It also allows you to get the number of records affected, which you can't do as easily with CurrentDb.