in use by another

  • Thread starter Jean-Paul De Winter
  • Start date
J

Jean-Paul De Winter

I get an errormessage saying the table gedrag_rapport can not be locked
because it in in use by another user or process

The message appears when the report should be printed

What am I doing wrong?

Thanks
JP

I use following code


Dim db As Database
Dim TB As RecordsetSet db = CurrentDb()

Set TB = db.OpenRecordset("gedrag_rapport", dbOpenTable)

TB.MoveFirst
Dim a As Integer
Dim b As Integer
Dim c As Integer
Do Until TB.EOF
TB.Edit
TB!mnd9 = Nz(TB!sept_TL) + Nz(TB!sept_PL)
TB!mnd10 = Nz(TB!okt_TL) + Nz(TB!okt_PL)
TB!mnd11 = Nz(TB!nov_TL) + Nz(TB!nov_PL)
TB!mnd12 = Nz(TB!dec_TL) + Nz(TB!dec_PL)
TB!rap1 = Nz(TB!mnd9) + Nz(TB!mnd10)
TB!rap2 = Nz(TB!mnd11) + Nz(TB!mnd12)
TB!rap3 = Nz(TB!mnd1) + Nz(TB!mnd2)
TB!rap4 = Nz(TB!mnd1) + Nz(TB!mnd2) + Nz(TB!mnd3)
TB!rap5 = Nz(TB!mnd3) + Nz(TB!mnd4)
TB.Update

TB.MoveNext
Loop

DoCmd.OpenReport "Gedrag_rapport", acViewPreview

End Sub
 
K

Klatuu

You need to close the recordset when you are done with your loop:

Loop

TB.Close
Set TB = Nothing
Set db = Nothing

DoCmd.OpenReport "Gedrag_rapport", acViewPreview

May I suggest it would be much faster to execute and take less code if
instead of the recordset coding, you create an Update query that would do the
same thing, and execute the query. The code would change to:

CurrentDb.Execute("qupdGedrag"), dbFailOnError
DoCmd.OpenReport "Gedrag_rapport", acViewPreview

What are you doing with the variables A, B, and C?
Why are they not declared with the other Dims?
Why don't they have meaningful names, so you can guess at what they stand for?

On the other hand, if the fields you are calculating are just for this
report, an even better solution would be to do the calculations in the report
itself.
 
J

Jean-Paul De Winter

The errormessage still remains... even with your "close"-code
JP


Klatuu schreef:
 
J

Jean-Paul De Winter

I must indeed admit there are far better ways to get the job done but
this is the best I can.... I am a physical therapist in a school for
disabled children, not a programmer.
I was asked by the administration to write some code to automate some
tasks... that's all..
Anyway... the a, b or c variables don't belong in the code... I just
added them to check some things.
Here is the code I currently use... I still get the errormessage 3211 as
I told in my message.
I really don't know wht goes wrong here so any help would be welcome.

Private Sub Knop17_Click()

Dim db As Database
Dim TB As Recordset, TB1 As Recordset, TB_naam As Recordset, TB_Klas As
Recordset
Set db = CurrentDb()
Dim sql As String, SQL2 As String, SQL_Klas As String
sql = "TRANSFORM Sum(Gedrag.Punten) AS SomVanPunten SELECT
Gedrag.Naamll, Format([Datum],'yyyymm') AS Expr1, Sum(Gedrag.Punten) AS
Totaal Punten] FROM Gedrag GROUP BY Gedrag.Naamll,
Format([Datum],'yyyymm')PIVOT Gedrag.Basisregel In
('TL','PL','MA','BE','TA','VM','AG','RL','GSM','RO');"
Set TB1 = db.OpenRecordset(sql)
DoCmd.SetWarnings False
DoCmd.RunSQL "delete gedrag_rapport.* from gedrag_rapport;"
DoCmd.SetWarnings True
TB1.MoveFirst
Do Until TB1.EOF
SQL_Klas = "SELECT patienten.* FROM patienten WHERE
patienten.naam ='" & TB1!Naamll & "' ;"
Set TB_Klas = db.OpenRecordset(SQL_Klas)
SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
gedrag_rapport.naamll ='" & TB1!Naamll & "' ;"
Set TB_naam = db.OpenRecordset(SQL2)
If TB_naam.RecordCount = 0 Then
TB_naam.AddNew
TB_naam!Naamll = TB1!Naamll
TB_naam!afdeling = TB_Klas!afdeling
TB_naam!klas = TB_Klas!klas
TB_naam!jaar = TB_Klas!jaar
TB_naam.Update
End If
TB1.MoveNext
Loop

Set TB = db.OpenRecordset("gedrag_rapport")

TB.MoveFirst

Do Until TB.EOF
TB.Edit
TB!mnd9 = Nz(TB!sept_TL) + Nz(TB!sept_PL) + Nz(TB!sept_MA) +
Nz(TB!Sept_BE) + Nz(TB!Sept_TA) + Nz(TB!sept_VM) + Nz(TB!Sept_AG) +
Nz(TB!Sept_RL) + Nz(TB!Sept_GSM) + Nz(TB!Sept_RO)
TB!mnd10 = Nz(TB!okt_TL) + Nz(TB!okt_PL) + Nz(TB!okt_MA) +
Nz(TB!okt_BE) + Nz(TB!okt_TA) + Nz(TB!okt_VM) + Nz(TB!okt_AG) +
Nz(TB!okt_RL) + Nz(TB!okt_GSM) + Nz(TB!okt_RO)
TB!mnd11 = Nz(TB!nov_TL) + Nz(TB!nov_PL) + Nz(TB!nov_MA) +
Nz(TB!nov_BE) + Nz(TB!nov_TA) + Nz(TB!nov_VM) + Nz(TB!nov_AG) +
Nz(TB!nov_RL) + Nz(TB!nov_GSM) + Nz(TB!nov_RO)
TB!mnd12 = Nz(TB!dec_TL) + Nz(TB!dec_PL) + Nz(TB!dec_MA) +
Nz(TB!dec_BE) + Nz(TB!dec_TA) + Nz(TB!dec_VM) + Nz(TB!dec_AG) +
Nz(TB!dec_RL) + Nz(TB!dec_GSM) + Nz(TB!dec_RO)
TB!mnd1 = Nz(TB!jan_TL) + Nz(TB!jan_PL) + Nz(TB!jan_MA) +
Nz(TB!jan_BE) + Nz(TB!jan_TA) + Nz(TB!jan_VM) + Nz(TB!jan_AG) +
Nz(TB!jan_RL) + Nz(TB!jan_GSM) + Nz(TB!jan_RO)
TB!mnd2 = Nz(TB!feb_TL) + Nz(TB!feb_PL) + Nz(TB!feb_MA) +
Nz(TB!feb_BE) + Nz(TB!feb_TA) + Nz(TB!feb_VM) + Nz(TB!feb_AG) +
Nz(TB!feb_RL) + Nz(TB!feb_GSM) + Nz(TB!feb_RO)
TB!mnd3 = Nz(TB!maa_TL) + Nz(TB!maa_PL) + Nz(TB!maa_MA) +
Nz(TB!maa_BE) + Nz(TB!maa_TA) + Nz(TB!maa_VM) + Nz(TB!maa_AG) +
Nz(TB!maa_RL) + Nz(TB!maa_GSM) + Nz(TB!maa_RO)
TB!mnd4 = Nz(TB!apr_TL) + Nz(TB!apr_PL) + Nz(TB!apr_MA) +
Nz(TB!apr_BE) + Nz(TB!apr_TA) + Nz(TB!apr_VM) + Nz(ŠÂ¨‰V,uç‹EäÁ3Ò÷÷…Ò‰U…bÿÿÿ‹F,3É…À†ú\0\0\0‹EüƒÀf‹Pþf‹xf‰xþf‹xf‰Pf‹f‰8f‰P‹V,ƒÁƒÀ;ÊrÕMÀQÿ€<\0_^[‹å]Â\0‹Ó+ЉEð‰Uàë‹M‹Uà;Ê‹ÁsS¤$\0\0\0\0;Eôs‹øë‹û+øO‹Mð;Uôs‹Êë‹Ë+ÊI‹UèÙºØ4Š‹V,‹MüÙ‘‹~,‹Uø‹MàGÂ;Á‰~,r¹‹M‹ú;Ãs0;Eôs‹Èë‹Ë+ÈI‹UèÙEìØ Š‹N,‹UüÇÙŠ‹N,A;ÉN,rÓ‹M‹EäÁ3Ò÷÷…Ò‰U…YÿÿÿMÀQÿ€<\0_^[‹å]Â\0ÌÌÌÌÌÌÌÌÌ‹ÿU‹ì‹M‹ESV‹uW‹} ;ù‰F v(‰Áà‹×ÑêÂ3Ò÷÷…À‰Fu¸\0\0\0‰FÇF \0\0\0ë&¿Áà‹ÑÑêÂ3Ò÷ñÇF \0\0\0…À‰Fu¸\0\0\0‰F‹ÑÑê‹ÇÁà Â3Ò÷ñ‹^$ƒû‰F<‹ ðW\0w ÇFD\0\0\0ë‹~3Ò‹Á÷÷‹Ð¯×;щFDs@‰FD‹FD‹Èƒát+ÁƒÀ‰FD‹FD¯E‹M3ÿQV‰F ‰~‰~0‰~8èS‚þÿ‹V@‰F;V‰~4‰~‰>t ƒûv¸\0\0\0ë3À‰F(9=\0d\0t3Àƒûu ‰~(_^[]Â\03À_^[]Â\0ÌÌÌÌÌÌÌÌÌÌÌÌ‹ÿU‹ìV‹u ‹F4…Àt3À^] \0S‹^û \0\0\0u‹^Këß\0\0\0W‹}…ÿt‹F$€ÁàË„‡À\0\0\0…Àt‰FëS‹M‹QTiÒÀ' \0‹N ¸ÓMb÷âÁêÂ@\0\0¯V ÑhKMIXÁâRjÿô<\0…ÿ‰Ft…Àt‹N$ ‰Ááˉ„À\0\0\0‹F…ÀtE‹V ‰F‹F$ƒøv@‹…ðW\0hKMIXÁàPjÿô<\0…À‰u"…ÿu ‹NWQÿ=\0ÇF\0\0\0\0_[¸š\0\0À^] \0Vè]ûÿÿ…ÿ¹\0\0\0‰N4t(‹F$€ÁâÓ„—À\0\0ÿ\0‹F$€ÁàÃ9Œ‡À\0\0u‹N ‹~Áá‹ÑÁé3Àó«‹Êƒáóª_[3À^] \0ÌÌÌÌÌÌÌ‹ÿU‹ìV‹u‹F4…Àtp‹F…ÀW‹==\0t‹M …Éuj\0Pÿ×ÇF\0\0\0\0ƒ~$v‹…Àt j\0Pÿ×Ç\0\0\0\0‹F= \0\0\0ÇF4\0\0\0\0_u‹FHëŸ\0\0\0‹M …Ét‹v$¶ÁâЄ‘À\0\0ÿ3À^]Â\0ÌÌÌÌÌÌÌÌÌ‹ÿU‹ì‹E\0@\0\0™âÿ\0\0ÂÁø¿\0\0\0y
ÙðR\0]Â\0=¿\0\0\0~
ÙtN\0]Â\0Ù…øÊ\0]Â\0ÌÌÌÌÌÌÌÌÌÌ‹ÿU‹ìQÙEØðR\0ßàöÄz ¸\0\0\0€‹å]Â\0ÙEØS\0ßàöÄAu3À‹å]Â\0ÙEÇEü¿ÿÿØS\0Ù]‹E=\0\0\0O}ÙEÛ]ü‹Eü‹…øÍ\0‹å]Â\0ÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌÌ‹ÿU‹ìƒì,EÔPÿ\<\0¡ðÅ\0…À†ý\0\0SV¾dÓ\0WÇEü\0\0\0\0ºÔÄ\0‰uø‰Eô¤$\0\0\0\03ÛƒøŒ9\0\0xüÁïG¹ÔÄ\0½\0\0\0\0‹ÿÙBüØaüÙÀÞÉÙØ!ÙÀØÉÞÂÝØÙBØaÙÀØÉÞÂÝØÜS\0ßàöÄz
ÝØÙtN\0ëØ=tN\0Ù^üÙBüØaÙÀÞÉÙØa ÙÀØÉÞÂÝØÙBØaÙÀØÉÞÂÝØÜS\0ßàöÄz
ÝØÙtN\0ëØ=tN\0ÙÙBüØaÙÀÞÉÙØaÙÀØÉÞÂÝØÙBØaÙÀØÉÞÂÝØÜS\0ßàöÄz
ÝØÙtN\0ëØ=tN\0Ù^ÙBüØa ÙÀÞÉÙØa$ÙÀØÉÞÂÝØÙBØa(ÙÀØÉÞÂÝØÜS\0ßàöÄz
ÝØÙtN\0ëØ=tN\0Ù^ƒÆƒÁ0O…áþÿÿ¡ðÅ\0;Øsl‹uü< [‹ð ÔÄ\0<½`ÓB!apr_TA) + Nz(TB!mei_TA) + Nz(TB!jun_TA)
TB!VM = Nz(TB!sept_VM) + Nz(TB!okt_VM) + Nz(TB!nov_VM) +
Nz(TB!dec_VM) + Nz(TB!jan_VM) + Nz(TB!feb_VM) + Nz(TB!maa_VM) +
Nz(TB!apr_VM) + Nz(TB!mei_VM) + Nz(TB!jun_VM)
TB!AG = Nz(TB!Sept_AG) + Nz(TB!okt_AG) + Nz(TB!nov_AG) +
Nz(TB!dec_AG) + Nz(TB!jan_AG) + Nz(TB!feb_AG) + Nz(TB!maa_AG) +
Nz(TB!apr_AG) + Nz(TB!mei_AG) + Nz(TB!jun_AG)
TB!RL = Nz(TB!Sept_RL) + Nz(TB!okt_RL) + Nz(TB!nov_RL) +
Nz(TB!dec_RL) + Nz(TB!jan_RL) + Nz(TB!feb_RL) + Nz(TB!maa_RL) +
Nz(TB!apr_RL) + Nz(TB!mei_RL) + Nz(TB!jun_RL)
TB!GSM = Nz(TB!Sept_GSM) + Nz(TB!okt_GSM) + Nz(TB!nov_GSM) +
Nz(TB!dec_GSM) + Nz(TB!jan_GSM) + Nz(TB!feb_GSM) + Nz(TB!maa_GSM) +
Nz(TB!apr_GSM) + Nz(TB!mei_GSM) + Nz(TB!jun_GSM)
TB!RO = Nz(TB!Sept_RO) + Nz(TB!okt_RO) + Nz(TB!nov_RO) +
Nz(TB!dec_RO) + Nz(TB!jan_RO) + Nz(TB!feb_RO) + Nz(TB!maa_RO) +
Nz(TB!apr_RO) + Nz(TB!mei_RO) + Nz(TB!jun_RO)
TB!TOTAAL = Nz(TB!mnd1) + Nz(TB!mnd2) + Nz(TB!mnd3) +
Nz(TB!mnd4) + Nz(TB!mnd5) + Nz(TB!mnd6) + Nz(TB!mnd9) + Nz(TB!mnd10) +
Nz(TB!mnd11) + Nz(TB!mnd12)

TB.Update

TB.MoveNext
Loop

TB.Close
DoEvents
Set TB = Nothing
Set db = Nothing

DoCmd.OpenReport "Gedrag_rapport", acViewPreview

End Sub
 
Top