Question Strange VBA-behavior (1 Viewer)

I ran into a strange behaviour in a rutine that deletes a line in a table and then generates two values based on DSums. The second DSum allways returns '0'. Putting the Dsums before the delete, they work fine. The code:

A) This gives the problem:
CurrentDb.Execute "DELETE * FROM Table3 WHERE Table3.XY=0;"
MLngInicio = CLng(Forms!Informes_Inicio!FInicio)
MFilter = "(Fecha < " & MLngInicio & ")"
MSumDeber = Nz(DSum("[Importe]", "Table1", MFilter), 0)
MSumHaber = Nz(DSum("[Importe]", "Table2", MFilter), 0)

B) This works fine:
MLngInicio = CLng(Forms!Informes_Inicio!FInicio)
MFilter = "(Fecha < " & MLngInicio & ")"
MSumDeber = Nz(DSum("[Importe]", "Table1", MFilter), 0)
MSumHaber = Nz(DSum("[Importe]", "Table2", MFilter), 0)
CurrentDb.Execute "DELETE * FROM Table3 WHERE Table3.XY=0;"

More strangely even, the above mentioned behaviour ocurrs if there is following code to insert the values MSumDeber and MSumHaber into a new line in Table3 (with a recordset-method). If that code is commented out, the problems ocurrs no matter if I use pattern A or B :banghead:

I would suggest to try a decompile first

ben

Thank you, Ben, for the quick reply. In fact, the size of the db shrunk to 80%, so there had to be a lot of junk in it. Unfortunately, it solved the problem only partially: The first time the sub is called, the error ocurs in any case (A or B), then, by changing the filter criteria and calling again (with AfterUpdate) all is fine. I must admitt that there is one more filter criteria from a control on the active form, which I omitted in the presented code to shorten it here.

EL_Gringo said:

Thank you, Ben, for the quick reply. In fact, the size of the db shrunk to 80%, so there had to be a lot of junk in it. Unfortunately, it solved the problem only partially: The first time the sub is called, the error ocurs in any case (A or B), then, by changing the filter criteria and calling again (with AfterUpdate) all is fine. I must admitt that there is one more filter criteria from a control on the active form, which I omitted in the presented code to shorten it here.

Click to expand...

It sounds like you compact the database, which is good reduce it some Mbits, but did you also decompile it?

You do this by starting ACCESS with
MSACCESS.exe /decompile

And then selecting your db

why not ReCalc the form first before anything else.

CurrentDb.Execute "DELETE * FROM Table3 WHERE Table3.XY=0;"
Me.Recalc
MLngInicio = CLng(Forms!Informes_Inicio!FInicio)
MFilter = "(Fecha < " & MLngInicio & ")"
MSumDeber = Nz(DSum("[Importe]", "Table1", MFilter), 0)
MSumHaber = Nz(DSum("[Importe]", "Table2", MFilter), 0)

boerbende said:

It sounds like you compact the database, which is good reduce it some Mbits, but did you also decompile it?

You do this by starting ACCESS with
MSACCESS.exe /decompile

And then selecting your db

Click to expand...

Yes, I did that, including opening the db with SHIFT held down, compact&repair etc. To no avail.

I finally found the error, and as usual, the problem is to be found in front of the Display. I'm quite ashamed to admit... table 2 has no field 'Fecha'. Which still leaves the mystery why in some instances the second DSum returned a result diferent from 0.
Thanks anyway for the good ideas, guess I'm getting a bit rusty and forgot about the decompile altogether.

CurrentDb.Execute "DELETE * FROM Table3 WHERE Table3.XY=0;"
MLngInicio = CLng(Forms!Informes_Inicio!FInicio)
MFilter = "(Fecha < " & MLngInicio & ")"
MSumDeber = Nz(DSum("[Importe]", "Table1", MFilter), 0)
MSumHaber = Nz(DSum("[Importe]", "Table2", MFilter), 0)

Click to expand...

if your delete statement deletes the currently viewed record, then !finicio will probably be Null, which will also probably explain why the queries return 0.

Might that be the problem?

gemma-the-husky said:

if your delete statement deletes the currently viewed record, then !finicio will probably be Null, which will also probably explain why the queries return 0.

Might that be the problem?

Click to expand...

Dear gth, thanks for your reply, but as I mentioned before I found the error, and now the code is returning the right result. !FInicio is retrieved from an unbound field on another form and can never be Null nor zero, because in that case I could not open the current form. But it still puzzles me that in spite of the missing field 'Fecha' in Table2 the erroneous code sometimes returned a valid result...