|
|
This is hard to explain... please be patient!
I created a form, clickin a pushbutton runs some code which finaly
should generate a report.
The report is based upon a table linked to another one (let's name it
XYS) by an ID.
When I run the code from a form I get an errormessage sayin (and I have
to translate the syntax):
The database engine can not lock the table XYS because it is used by
another user or another process.
I'm the only use so it must be the process
When I click on the report iteself, everything is perfect, but when it
is run from within the code I get the message...
Maybe I should lock the table first, but I don't know how this is done.
When I delete the link and the teble XYS I don't get the message but of
course the report isn't correct)
Any help is welcome
Thanks
|
|
On Thu, 06 Nov 2008 14:45:47 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
[Quoted Text] >This is hard to explain... please be patient!
>
>I created a form, clickin a pushbutton runs some code which finaly
>should generate a report.
>The report is based upon a table linked to another one (let's name it
>XYS) by an ID.
>
>When I run the code from a form I get an errormessage sayin (and I have
>to translate the syntax):
>
>The database engine can not lock the table XYS because it is used by
>another user or another process.
>
>I'm the only use so it must be the process
>
>When I click on the report iteself, everything is perfect, but when it
>is run from within the code I get the message...
>
>Maybe I should lock the table first, but I don't know how this is done.
>When I delete the link and the teble XYS I don't get the message but of
>course the report isn't correct)
>
>Any help is welcome
>Thanks
Care to post your code? We can't see it from here.
Just opening a report should not lock the table. You're doing something else,
I'm guessing!
--
John W. Vinson [MVP]
|
|
Thanks for your reply
Didn't want to bother you with all the coding...
here it is... success :
Dim db As Database
Dim tb As Recordset, TB1 As Recordset, TB_naam As Recordset, TB_Klas As
Recordset, TB_braaf As Recordset, TB_Rapport As Recordset
Set db = CurrentDb()
Dim sql As String, SQL2 As String, SQL_Klas As String, SQL_Braaf As String
sql = "TRANSFORM Sum(Gedrag.Punten) AS SomVanPunten SELECT Gedrag.ID_ll,
Format([Datum],'yyyymm') AS Expr1, Sum(Gedrag.Punten) AS [Totaal Punten]
FROM Gedrag where gedrag.schooljaar='" & [Forms]![Gedrag]![schljr] & "'
GROUP BY Gedrag.ID_ll, Format([Datum],'yyyymm')PIVOT Gedrag.Basisregel
In ('TL','PL','MA','BE','TA','VM','AG','RL','GSM','RO', 'PI','KG','PG');"
Set TB1 = db.OpenRecordset(sql)
DoCmd.SetWarnings False
DoCmd.RunSQL "delete gedrag_rapport.* from gedrag_rapport;"
DoCmd.RunSQL "UPDATE Leerlingen SET Leerlingen.Gedrag = False;"
DoCmd.SetWarnings True
TB1.MoveFirst
Do Until TB1.EOF
SQL_Klas = "SELECT Leerlingen.* FROM Leerlingen WHERE
Leerlingen.ID =" & TB1!ID_ll & " ;"
Set TB_Klas = db.OpenRecordset(SQL_Klas)
SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
gedrag_rapport.ID_ll =" & TB1!ID_ll & " ;"
Set TB_naam = db.OpenRecordset(SQL2)
If TB_naam.RecordCount = 0 Then
TB_naam.AddNew
TB_naam!ID_ll = TB1!ID_ll
TB_naam!Afdeling = TB_Klas!Afdeling
TB_naam!Klas = TB_Klas!Klas
TB_naam!jaar = TB_Klas!jaar
TB_naam!sept_TL = TB1!TL
TB_naam!sept_PL = TB1!PL
TB_naam!sept_MA = TB1!MA
TB_naam!Sept_BE = TB1!BE
TB_naam!Sept_TA = TB1!TA
TB_naam!sept_VM = TB1!VM
TB_naam!Sept_AG = TB1!AG
TB_naam!Sept_RL = TB1!RL
TB_naam!Sept_GSM = TB1!GSM
TB_naam!Sept_RO = TB1!RO
TB_naam!Sept_KG = TB1!KG
TB_naam!Sept_PI = TB1!PI
TB_naam!Sept_PG = TB1!PG
TB_naam.Update
Else
TB_naam.Edit
TB_naam!Afdeling = TB_Klas!Afdeling
TB_naam!Klas = TB_Klas!Klas
TB_naam!jaar = TB_Klas!jaar
TB_naam.Edit
TB_naam!sept_TL = TB1!TL
TB_naam!sept_PL = TB1!PL
TB_naam!sept_MA = TB1!MA
TB_naam!Sept_BE = TB1!BE
TB_naam!Sept_TA = TB1!TA
TB_naam!sept_VM = TB1!VM
TB_naam!Sept_AG = TB1!AG
TB_naam!Sept_RL = TB1!RL
TB_naam!Sept_GSM = TB1!GSM
TB_naam!Sept_RO = TB1!RO
TB_naam!Sept_KG = TB1!KG
TB_naam!Sept_PI = TB1!PI
TB_naam!Sept_PG = TB1!PG
TB_naam.Update
End If
TB1.MoveNext
Loop
TB_naam.Close
DoEvents
Set TB_naam = Nothing
Set tb = db.OpenRecordset("gedrag_rapport")
tb.MoveFirst
Do Until tb.EOF
xrap1 = 0
xrap2 = 0
xrap3 = 0
xrap4 = 0
xrap5 = 0
xrap6 = 0
tb.Edit
xmnd9 = 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) + Nz(tb!Sept_PI) +
Nz(tb!Sept_PG) + Nz(tb!Sept_KG)
xmnd10 = 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) + Nz(tb!Okt_PI) +
Nz(tb!Okt_pg) + Nz(tb!Okt_KG)
xmnd11 = 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) + Nz(tb!Nov_PI) +
Nz(tb!Nov_PG) + Nz(tb!Nov_KG)
xmnd12 = 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) + Nz(tb!Dec_PI) +
Nz(tb!Dec_pg) + Nz(tb!Dec_KG)
xmnd1 = 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) + Nz(tb!Jan_PI) +
Nz(tb!Jan_PG) + Nz(tb!Jan_KG)
xmnd2 = 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) + Nz(tb!Feb_PI) +
Nz(tb!Feb_PG) + Nz(tb!Feb_KG)
xmnd3 = 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) + Nz(tb!Maa_PI) +
Nz(tb!Maa_PG) + Nz(tb!Maa_KG)
xmnd4 = 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(tb!apr_AG) +
Nz(tb!apr_RL) + Nz(tb!apr_GSM) + Nz(tb!apr_RO) + Nz(tb!Apr_PI) +
Nz(tb!Apr_PG) + Nz(tb!Apr_KG)
xmnd5 = Nz(tb!mei_TL) + Nz(tb!mei_PL) + Nz(tb!mei_MA) +
Nz(tb!mei_BE) + Nz(tb!mei_TA) + Nz(tb!mei_VM) + Nz(tb!mei_AG) +
Nz(tb!mei_RL) + Nz(tb!mei_GSM) + Nz(tb!mei_RO) + Nz(tb!Mei_PI) +
Nz(tb!Mei_PG) + Nz(tb!Mei_KG)
xmnd6 = Nz(tb!jun_TL) + Nz(tb!jun_PL) + Nz(tb!jun_MA) +
Nz(tb!jun_BE) + Nz(tb!jun_TA) + Nz(tb!jun_VM) + Nz(tb!jun_AG) +
Nz(tb!jun_RL) + Nz(tb!jun_GSM) + Nz(tb!jun_RO) + Nz(tb!Jun_PI) +
Nz(tb!Jun_PG) + Nz(tb!Jun_KG)
If xmnd9 <> 0 Then
tb!mnd9 = xmnd9
End If
If xmnd10 <> 0 Then
tb!mnd10 = xmnd10
End If
If xmnd11 <> 0 Then
tb!mnd11 = xmnd11
End If
If xmnd12 <> 0 Then
tb!mnd12 = xmnd12
End If
If xmnd1 <> 0 Then
tb!mnd1 = xmnd1
End If
If xmnd2 <> 0 Then
tb!mnd2 = xmnd2
End If
If xmnd3 <> 0 Then
tb!mnd3 = xmnd3
End If
If xmnd4 <> 0 Then
tb!mnd4 = xmnd4
End If
If xmnd5 <> 0 Then
tb!mnd5 = xmnd5
End If
If xmnd6 <> 0 Then
tb!mnd6 = xmnd6
End If
xrap1 = Nz(tb!mnd9) + Nz(tb!mnd10)
xrap2 = Nz(tb!mnd11) + Nz(tb!mnd12)
xrap3 = Nz(tb!mnd1) + Nz(tb!mnd2)
If Month(Date) = 3 Then
xrap4 = Nz(tb!mnd1) + Nz(tb!mnd2) + Nz(tb!mnd3)
Else
Xrap = 0
End If
xrap5 = Nz(tb!mnd3) + Nz(tb!mnd4)
If tb!Klas = 1 Or tb!Klas = 2 Or tb!Klas = 3 Then
xrap6 = Nz(tb!mnd5) + Nz(tb!mnd6)
Else
xrap6 = Nz(tb!mnd4) + Nz(tb!mnd5) + Nz(tb!mnd6)
End If
If xrap1 <> 0 Then
tb!rap1 = xrap1
End If
If xrap2 <> 0 Then
tb!rap2 = xrap2
End If
If xrap3 <> 0 Then
tb!rap3 = xrap3
End If
If xrap4 <> 0 Then
tb!rap4 = xrap4
End If
If xrap5 <> 0 Then
tb!rap5 = xrap5
End If
If xrap6 <> 0 Then
tb!rap6 = xrap6
End If
If xrap7 <> 0 Then
tb!rap7 = xrap7
End If
tb!Opmerking = "Er viel niets aan te merken op je gedrag. Prima
zo!"
XTL = Nz(tb!sept_TL) + Nz(tb!okt_TL) + Nz(tb!nov_TL) +
Nz(tb!dec_TL) + Nz(tb!jan_TL) + Nz(tb!feb_TL) + Nz(tb!maa_TL) +
Nz(tb!apr_TL) + Nz(tb!mei_TL) + Nz(tb!jun_TL)
XPL = Nz(tb!sept_PL) + Nz(tb!okt_PL) + Nz(tb!nov_PL) +
Nz(tb!dec_PL) + Nz(tb!jan_PL) + Nz(tb!feb_PL) + Nz(tb!maa_PL) +
Nz(tb!apr_PL) + Nz(tb!mei_PL) + Nz(tb!jun_PL)
XMA = Nz(tb!sept_MA) + Nz(tb!okt_MA) + Nz(tb!nov_MA) +
Nz(tb!dec_MA) + Nz(tb!jan_MA) + Nz(tb!feb_MA) + Nz(tb!maa_MA) +
Nz(tb!apr_MA) + Nz(tb!mei_MA) + Nz(tb!jun_MA)
XBE = Nz(tb!Sept_BE) + Nz(tb!okt_BE) + Nz(tb!nov_BE) +
Nz(tb!dec_BE) + Nz(tb!jan_BE) + Nz(tb!feb_BE) + Nz(tb!maa_BE) +
Nz(tb!apr_BE) + Nz(tb!mei_BE) + Nz(tb!jun_BE)
XTA = Nz(tb!Sept_TA) + Nz(tb!okt_TA) + Nz(tb!nov_TA) +
Nz(tb!dec_TA) + Nz(tb!jan_TA) + Nz(tb!feb_TA) + Nz(tb!maa_TA) +
Nz(tb!apr_TA) + Nz(tb!mei_TA) + Nz(tb!jun_TA)
XVM = 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)
XAG = 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)
XRL = 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)
XGSM = 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)
XRO = 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)
XPI = Nz(tb!Sept_PI) + Nz(tb!Okt_PI) + Nz(tb!Nov_PI) +
Nz(tb!Dec_PI) + Nz(tb!Jan_PI) + Nz(tb!Feb_PI) + Nz(tb!Maa_PI) +
Nz(tb!Apr_PI) + Nz(tb!Mei_PI) + Nz(tb!Jun_PI)
XPG = Nz(tb!Sept_PG) + Nz(tb!Okt_pg) + Nz(tb!Nov_PG) +
Nz(tb!Dec_pg) + Nz(tb!Jan_PG) + Nz(tb!Feb_PG) + Nz(tb!Maa_PG) +
Nz(tb!Apr_PG) + Nz(tb!Mei_PG) + Nz(tb!Jun_PG)
XKG = Nz(tb!Sept_KG) + Nz(tb!Okt_KG) + Nz(tb!Nov_KG) +
Nz(tb!Dec_KG) + Nz(tb!Jan_KG) + Nz(tb!Feb_KG) + Nz(tb!Maa_KG) +
Nz(tb!Apr_KG) + Nz(tb!Mei_KG) + Nz(tb!Jun_KG)
XTotaaL = 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)
If XTL <> 0 Then
tb!TL = XTL
End If
If XPL <> 0 Then
tb!PL = XPL
End If
If XMA <> 0 Then
tb!MA = XMA
End If
If XBE <> 0 Then
tb!BE = XBE
End If
If XTA <> 0 Then
tb!TA = XTA
End If
If XVM <> 0 Then
tb!VM = XVM
End If
If XAG <> 0 Then
tb!AG = XAG
End If
If XRL <> 0 Then
tb!RL = XRL
End If
If XGSM <> 0 Then
tb!GSM = XGSM
End If
If XRO <> 0 Then
tb!RO = XRO
End If
If XKG <> 0 Then
tb!KG = XKG
End If
If XPI <> 0 Then
tb!PI = XPI
End If
If XPG <> 0 Then
tb!PG = XPG
End If
If XTotaaL <> 0 Then
tb!TOTAAL = XTotaaL
End If
SQL2 = "SELECT leerlingen.* FROM leerlingen WHERE leerlingen.ID
= " & tb!ID_ll & " ;"
Set TB_naam = db.OpenRecordset(SQL2)
TB_naam.Edit
TB_naam!Gedrag = True
TB_naam.Update
tb.Update
tb.MoveNext
Loop
SQL_Braaf = "SELECT leerlingen.* FROM leerlingen WHERE leerlingen.[Nu
nog leerling]=True AND leerlingen.Gedrag=False;"
Set TB_braaf = db.OpenRecordset(SQL_Braaf)
Set TB_Rapport = db.OpenRecordset("Gedrag_rapport")
TB_braaf.MoveFirst
Do Until TB_braaf.EOF
If TB_braaf!Afdeling <> "ABO" Then
TB_Rapport.AddNew
TB_Rapport!ID_ll = TB_braaf!ID
TB_Rapport!Opmerking = "Er viel niets aan te merken op je gedrag.
Prima zo!"
TB_Rapport!Afdeling = TB_braaf!Afdeling
TB_Rapport!jaar = TB_braaf!jaar
TB_Rapport!Klas = TB_braaf!Klas
TB_Rapport.Update
End If
TB_braaf.MoveNext
Loop
tb.Close
TB_braaf.Close
TB_Rapport.Close
DoEvents
Set tb = Nothing
Set db = Nothing
'schrappen van niet uit te printen paginas
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE Gedrag_rapport.*, Gedrag_rapport.Klas FROM
Gedrag_rapport WHERE Gedrag_rapport.Klas='ABO';"
If Month(Date) = 2 Then
DoCmd.RunSQL "DELETE Gedrag_rapport.*, Gedrag_rapport.Klas FROM
Gedrag_rapport WHERE Gedrag_rapport.Klas='4';"
DoCmd.RunSQL "DELETE Gedrag_rapport.*, Gedrag_rapport.Klas FROM
Gedrag_rapport WHERE Gedrag_rapport.Klas='5';"
End If
DoCmd.SetWarnings True
DoCmd.OpenReport "Gedrag_rapport", acViewPreview
End Sub
John W. Vinson wrote:
[Quoted Text] > On Thu, 06 Nov 2008 14:45:47 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
>
>> This is hard to explain... please be patient!
>>
>> I created a form, clickin a pushbutton runs some code which finaly
>> should generate a report.
>> The report is based upon a table linked to another one (let's name it
>> XYS) by an ID.
>>
>> When I run the code from a form I get an errormessage sayin (and I have
>> to translate the syntax):
>>
>> The database engine can not lock the table XYS because it is used by
>> another user or another process.
>>
>> I'm the only use so it must be the process
>>
>> When I click on the report iteself, everything is perfect, but when it
>> is run from within the code I get the message...
>>
>> Maybe I should lock the table first, but I don't know how this is done.
>> When I delete the link and the teble XYS I don't get the message but of
>> course the report isn't correct)
>>
>> Any help is welcome
>> Thanks
>
> Care to post your code? We can't see it from here.
>
> Just opening a report should not lock the table. You're doing something else,
> I'm guessing!
|
|
On Fri, 07 Nov 2008 08:20:07 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
[Quoted Text] >Thanks for your reply
>Didn't want to bother you with all the coding...
>here it is... success :
By "success" are you saying your code now works, or that you're wishing us
success in wading through 350 lines of uncommented code??
I would have NO way to debug this without knowing more about the tables, the
queries you're running, or - at very least - what line triggers the error. I'd
really suggest putting a Breakpoint in the code (mouse click in the grey bar
to the left of the code window, next to an executable statement) and stepping
through the code; check the values of variables and the outcome of actions as
you go, and see what line triggers the error.
The error message seems to mean that you have a table or recordset already
open (perhaps as the recordsource of a form) and your code is attmepting to
open it a second time for editing, causing a conflict. But I'd have no way to
guess WHICH line of this might be causing the conflict.
--
John W. Vinson [MVP]
|
|
with succes I mean... good luck ;)
You are right... more details are needed...
I'll try to explain
My wife is the head of a school for disabled children... I am a physical
therapîst trying to write some code.. in other words, this all is hard
work for me.. please be patient
The errormessage shows up when I try to open the report, at this line:
DoCmd.OpenReport "Gedrag_rapport", acViewPreview
The code will finally create a report with "bad point" per pupil
This code already is a few years old. The reason why I changed it was
the following.
I have 2 tables
table1 (leerlingen) is the one with all data of every pupil (name,
class, orientation..)
table2 (gedrag_rapport) is a table containing all "bad points" per pupil.
In the beginning both tables were linked through name but this gave very
much problems, so I decided to change it and to make a link through ID
so I wrote:
SQL_Klas = "SELECT Leerlingen.* FROM Leerlingen WHERE
Leerlingen.ID =" & TB1!ID_ll & " ;"
Set TB_Klas = db.OpenRecordset(SQL_Klas)
SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
gedrag_rapport.ID_ll =" & TB1!ID_ll & " ;"
Set TB_naam = db.OpenRecordset(SQL2)
it was:
SQL_Klas = "SELECT Leerlingen.* FROM Leerlingen WHERE
Leerlingen.naam = '" & Replace(TB1!Naamll, "'", "''") & "' ;"
Set TB_Klas = db.OpenRecordset(SQL_Klas)
SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
gedrag_rapport.naamll = '" & Replace(TB1!Naamll, "'", "''") & "' ;"
Set TB_naam = db.OpenRecordset(SQL2)
In fact, the code runs just great... the final (generated) table is
correct but I can not get it in the report.
When I stop the code and click on the report name (from the list of all
reports)(this uses the just created table) the report is displayed
correctly. When I run it from the code I get the errormessage.
Hope I explained it better now...
Thank you so much for your kind help
JP, Belgium...
John W. Vinson wrote:
[Quoted Text] > On Fri, 07 Nov 2008 08:20:07 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
>
>> Thanks for your reply
>> Didn't want to bother you with all the coding...
>> here it is... success :
>
> By "success" are you saying your code now works, or that you're wishing us
> success in wading through 350 lines of uncommented code??
>
> I would have NO way to debug this without knowing more about the tables, the
> queries you're running, or - at very least - what line triggers the error. I'd
> really suggest putting a Breakpoint in the code (mouse click in the grey bar
> to the left of the code window, next to an executable statement) and stepping
> through the code; check the values of variables and the outcome of actions as
> you go, and see what line triggers the error.
>
> The error message seems to mean that you have a table or recordset already
> open (perhaps as the recordsource of a form) and your code is attmepting to
> open it a second time for editing, causing a conflict. But I'd have no way to
> guess WHICH line of this might be causing the conflict.
|
|
did some testing.....
The final report I want to be made is based upon 2 tables linked to
eachother via ID.
When I delete the "leerlingen" table, so there is only one table left,
the report is shown (but incorrect because some data are missing)
When I add "leerlingen", make the link between the 2 tables via ID... I
get the errormessage...
Strange
JP
Jean-Paul wrote:
[Quoted Text] > with succes I mean... good luck ;)
>
> You are right... more details are needed...
> I'll try to explain
>
> My wife is the head of a school for disabled children... I am a physical
> therapîst trying to write some code.. in other words, this all is hard
> work for me.. please be patient
>
> The errormessage shows up when I try to open the report, at this line:
>
> DoCmd.OpenReport "Gedrag_rapport", acViewPreview
>
> The code will finally create a report with "bad point" per pupil
>
> This code already is a few years old. The reason why I changed it was
> the following.
>
> I have 2 tables
> table1 (leerlingen) is the one with all data of every pupil (name,
> class, orientation..)
> table2 (gedrag_rapport) is a table containing all "bad points" per pupil.
>
> In the beginning both tables were linked through name but this gave very
> much problems, so I decided to change it and to make a link through ID
>
> so I wrote:
> SQL_Klas = "SELECT Leerlingen.* FROM Leerlingen WHERE
> Leerlingen.ID =" & TB1!ID_ll & " ;"
> Set TB_Klas = db.OpenRecordset(SQL_Klas)
> SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
> gedrag_rapport.ID_ll =" & TB1!ID_ll & " ;"
> Set TB_naam = db.OpenRecordset(SQL2)
>
> it was:
> SQL_Klas = "SELECT Leerlingen.* FROM Leerlingen WHERE
> Leerlingen.naam = '" & Replace(TB1!Naamll, "'", "''") & "' ;"
> Set TB_Klas = db.OpenRecordset(SQL_Klas)
> SQL2 = "SELECT gedrag_rapport.* FROM gedrag_rapport WHERE
> gedrag_rapport.naamll = '" & Replace(TB1!Naamll, "'", "''") & "' ;"
> Set TB_naam = db.OpenRecordset(SQL2)
>
> In fact, the code runs just great... the final (generated) table is
> correct but I can not get it in the report.
> When I stop the code and click on the report name (from the list of all
> reports)(this uses the just created table) the report is displayed
> correctly. When I run it from the code I get the errormessage.
>
> Hope I explained it better now...
> Thank you so much for your kind help
>
> JP, Belgium...
>
>
> John W. Vinson wrote:
>> On Fri, 07 Nov 2008 08:20:07 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be>
>> wrote:
>>
>>> Thanks for your reply
>>> Didn't want to bother you with all the coding...
>>> here it is... success :
>>
>> By "success" are you saying your code now works, or that you're
>> wishing us
>> success in wading through 350 lines of uncommented code??
>>
>> I would have NO way to debug this without knowing more about the
>> tables, the
>> queries you're running, or - at very least - what line triggers the
>> error. I'd
>> really suggest putting a Breakpoint in the code (mouse click in the
>> grey bar
>> to the left of the code window, next to an executable statement) and
>> stepping
>> through the code; check the values of variables and the outcome of
>> actions as
>> you go, and see what line triggers the error.
>> The error message seems to mean that you have a table or recordset
>> already
>> open (perhaps as the recordsource of a form) and your code is
>> attmepting to
>> open it a second time for editing, causing a conflict. But I'd have no
>> way to
>> guess WHICH line of this might be causing the conflict.
|
|
On Fri, 07 Nov 2008 23:49:22 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
[Quoted Text] >did some testing.....
>
>The final report I want to be made is based upon 2 tables linked to
>eachother via ID.
>
>When I delete the "leerlingen" table, so there is only one table left,
>the report is shown (but incorrect because some data are missing)
>
>When I add "leerlingen", make the link between the 2 tables via ID... I
>get the errormessage...
Again:
Please set a breakpoint in your code.
Step through the code using F8 (Debug... Step).
Tell us where in the code you get the error.
--
John W. Vinson [MVP]
|
|
Again.....(sorry)
The code runs perfect... I get a perfect generated table
Just when I want the report to be shown the errormessage displays (see
the message before my last post, I replied to myself)
when I delete the line with the open report command and ope the report
by clicking on the report file name in the report list, the report runs
perfectly. When I put it in the code (like the one I send you) I get the
errormessage...
The report is based upon 2 linked tables....
So, when I debug the code I can not find any problem....
Very strange, and anoying
JP
John W. Vinson wrote:
[Quoted Text] > On Fri, 07 Nov 2008 23:49:22 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
>
>> did some testing.....
>>
>> The final report I want to be made is based upon 2 tables linked to
>> eachother via ID.
>>
>> When I delete the "leerlingen" table, so there is only one table left,
>> the report is shown (but incorrect because some data are missing)
>>
>> When I add "leerlingen", make the link between the 2 tables via ID... I
>> get the errormessage...
>
> Again:
>
> Please set a breakpoint in your code.
>
> Step through the code using F8 (Debug... Step).
>
> Tell us where in the code you get the error.
>
|
|
On Sat, 08 Nov 2008 08:27:57 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
[Quoted Text] >Again.....(sorry)
>
>The code runs perfect... I get a perfect generated table
>Just when I want the report to be shown the errormessage displays (see
>the message before my last post, I replied to myself)
>
>when I delete the line with the open report command and ope the report
>by clicking on the report file name in the report list, the report runs
>perfectly. When I put it in the code (like the one I send you) I get the
>errormessage...
>
>The report is based upon 2 linked tables....
>
>So, when I debug the code I can not find any problem....
It may be a timing issue - when you run an action query, Access starts the
query in the background and then lets the code continue. Try putting a line
DoEvents
before opening the report.
--
John W. Vinson [MVP]
|
|
Problem solved...
That DoEvents thing was the solution...
Is it correct that this command forces processes to be finisched before
going to the next step (in my case, opening a report)?
Thanks
JP
John W. Vinson wrote:
[Quoted Text] > On Sat, 08 Nov 2008 08:27:57 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
>
>> Again.....(sorry)
>>
>> The code runs perfect... I get a perfect generated table
>> Just when I want the report to be shown the errormessage displays (see
>> the message before my last post, I replied to myself)
>>
>> when I delete the line with the open report command and ope the report
>> by clicking on the report file name in the report list, the report runs
>> perfectly. When I put it in the code (like the one I send you) I get the
>> errormessage...
>>
>> The report is based upon 2 linked tables....
>>
>> So, when I debug the code I can not find any problem....
>
> It may be a timing issue - when you run an action query, Access starts the
> query in the background and then lets the code continue. Try putting a line
>
> DoEvents
>
> before opening the report.
|
|
On Sun, 09 Nov 2008 11:45:47 +0100, Jean-Paul <jp.dewinter[ at ]telenet.be> wrote:
[Quoted Text] >Problem solved...
>That DoEvents thing was the solution...
Excellent!
>Is it correct that this command forces processes to be finisched before
>going to the next step (in my case, opening a report)?
Good analysis.
--
John W. Vinson [MVP]
|
|
John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> wrote in
news:f80fh45oe9rj6e5balleh58ttk2afigoid[ at ]4ax.com:
[Quoted Text] > On Sun, 09 Nov 2008 11:45:47 +0100, Jean-Paul
> <jp.dewinter[ at ]telenet.be> wrote:
>
>>Is it correct that this command forces processes to be finisched
>>before going to the next step (in my case, opening a report)?
>
> Good analysis.
Actually, it's not. It just releases the processor to do other
things, which doesn't mean those other things will complete before
the next line of code executes.
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
|
|
|