ACC97

  • Thread starter Thread starter toto
  • Start date Start date
T

toto

Hello,

I have a Query wich inserts record in a table.
When I run it from the Queries Tab, it works perfectly.

I run it from VBA (in a form), it runs wrong : it inserts all the records
but not the last one.
Each time it inserts only N-1 records.

the code is :
Dim q As QueryDef
Set q = CurrentDb.QueryDefs("MyQuery")
q.Execute
q.Close

Is there something I miss ?

Thanks,

Yves
 
It is hard to tell what is happening without knowing what is in the query.

Open the query in design view.
Then switch to SQL view (View menu.)
Copy the SQL statement, and paste it as a follow up to this thread.
 
the query is:
INSERT INTO Tblmanif ( maniftitre, manifsection, manifdescription,
manifsaison, manifprix, manifniveau, maniftype, maniftypedetail,
manifprixenfant, maniflieu, maniflieudetail, manifkm, manifdifficulte,
manifdenivele, manifdenivelebas, manifdeniveledetail, manifconseil,
manifrepas, manifHretour, manifHdepart, manifdepartdetail,
manifnbparticipant, manifdefJ1, manifdefJ2, manifdefJ3, manifdefJ4,
manifdefJ5, manifdefJ6, manifdefJ7 )
SELECT TmpCat.DernierDemaniftitre, Tblmanif.manifsection,
TmpCat.DernierDemanifdescription, saisoncourante() AS saison,
Tblmanif.manifprix, Tblmanif.manifniveau, Tblmanif.maniftype,
Tblmanif.maniftypedetail, Tblmanif.manifprixenfant, Tblmanif.maniflieu,
Tblmanif.maniflieudetail, Tblmanif.manifkm, Tblmanif.manifdifficulte,
Tblmanif.manifdenivele, Tblmanif.manifdenivelebas,
Tblmanif.manifdeniveledetail, Tblmanif.manifconseil, Tblmanif.manifrepas,
Tblmanif.manifHretour, Tblmanif.manifHdepart, Tblmanif.manifdepartdetail,
Tblmanif.manifnbparticipant, TmpCat.defj1, TmpCat.defj2, TmpCat.defj3,
TmpCat.defj4, TmpCat.defj5, TmpCat.defj6, TmpCat.defj7
FROM TmpCat LEFT JOIN Tblmanif ON TmpCat.DernierDemanifcptr =
Tblmanif.manifcptr
WHERE
(((Abs([defj1])+Abs([defj2])+Abs([defj3])+Abs([defj4])+Abs([defj5])+Abs([defj6])+Abs([defj7]))>0));

It inserts in TblManif the records from Tmpcat et some fields picked from
Tblmanif by a link from Tmpcat
When i run the query from the query tab, it runs perfectly.

Thanks,

Yves
 
Thanks for the query statement. I cannot see anything in the WHERE clause
that should be handled differently in code than in the interface, unless
there is a timing issue.

It would be good to add the dbFailOnError switch to the Execute. That way
Access will notify you if there is an error trying to insert a row. Try
something like this:

With CurrentDb()
.Execute "MyQuery", dbFailOnError
Debug.Print .RecordsAffected & " records added. TmpCat has " &
DCount("*", "TmpCat")
End With

If that leads no-where, examine that record that fails to append. Anything
that could be blocking it? Any of the defj* fields contain a Null? How do
the records get the the temp table? Is it possible that the processing of
appending them to the temp table is not yet complete when this code runs
(but would be complete by the time you ran it from the query window)?

If that leads no-where too, tell us more about the fields the outer join is
based on. Data type and size?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

toto said:
the query is:
Click to expand...

INSERT INTO Tblmanif ( maniftitre,
manifsection,
manifdescription,
manifsaison,
manifprix,
manifniveau,
maniftype,
maniftypedetail,
manifprixenfant,
maniflieu,
maniflieudetail,
manifkm,
manifdifficulte,
manifdenivele,
manifdenivelebas,
manifdeniveledetail,
manifconseil,
manifrepas,
manifHretour,
manifHdepart,
manifdepartdetail,
manifnbparticipant,
manifdefJ1,
manifdefJ2,
manifdefJ3,
manifdefJ4,
manifdefJ5,
manifdefJ6,
manifdefJ7 )

SELECT TmpCat.DernierDemaniftitre,
Tblmanif.manifsection,
TmpCat.DernierDemanifdescription,
saisoncourante() AS saison,
Tblmanif.manifprix,
Tblmanif.manifniveau,
Tblmanif.maniftype,
Tblmanif.maniftypedetail,
Tblmanif.manifprixenfant,
Tblmanif.maniflieu,
Tblmanif.maniflieudetail,
Tblmanif.manifkm,
Tblmanif.manifdifficulte,
Tblmanif.manifdenivele,
Tblmanif.manifdenivelebas,
Tblmanif.manifdeniveledetail,
Tblmanif.manifconseil,
Tblmanif.manifrepas,
Tblmanif.manifHretour,
Tblmanif.manifHdepart,
Tblmanif.manifdepartdetail,
Tblmanif.manifnbparticipant,
TmpCat.defj1,
TmpCat.defj2,
TmpCat.defj3,
TmpCat.defj4,
TmpCat.defj5,
TmpCat.defj6,
TmpCat.defj7

FROM TmpCat LEFT JOIN Tblmanif
ON TmpCat.DernierDemanifcptr = Tblmanif.manifcptr

WHERE (((Abs([defj1]) + Abs([defj2]) + Abs([defj3])
+ Abs([defj4]) + Abs([defj5]) + Abs([defj6]) + Abs([defj7]))>0));
 
I have tested your code, it gives no error, it shows me the exact (bad)
number of records inserted.
It's not a record wich fails, it's always the last one who "fails" whatever
the number to insert, the last one whaever it is, is missing.
But the code don't give error on running..

When I run it from query tab it works fine.
When I run it from code (VBA in a form, or command line) it inserts all the
record but one.

Yves




Allen Browne said:
Thanks for the query statement. I cannot see anything in the WHERE clause
that should be handled differently in code than in the interface, unless
there is a timing issue.

It would be good to add the dbFailOnError switch to the Execute. That way
Access will notify you if there is an error trying to insert a row. Try
something like this:

With CurrentDb()
.Execute "MyQuery", dbFailOnError
Debug.Print .RecordsAffected & " records added. TmpCat has " &
DCount("*", "TmpCat")
End With

If that leads no-where, examine that record that fails to append. Anything
that could be blocking it? Any of the defj* fields contain a Null? How do
the records get the the temp table? Is it possible that the processing of
appending them to the temp table is not yet complete when this code runs
(but would be complete by the time you ran it from the query window)?

If that leads no-where too, tell us more about the fields the outer join
is based on. Data type and size?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

toto said:
the query is:
Click to expand...

INSERT INTO Tblmanif ( maniftitre,
manifsection,
manifdescription,
manifsaison,
manifprix,
manifniveau,
maniftype,
maniftypedetail,
manifprixenfant,
maniflieu,
maniflieudetail,
manifkm,
manifdifficulte,
manifdenivele,
manifdenivelebas,
manifdeniveledetail,
manifconseil,
manifrepas,
manifHretour,
manifHdepart,
manifdepartdetail,
manifnbparticipant,
manifdefJ1,
manifdefJ2,
manifdefJ3,
manifdefJ4,
manifdefJ5,
manifdefJ6,
manifdefJ7 )

SELECT TmpCat.DernierDemaniftitre,
Tblmanif.manifsection,
TmpCat.DernierDemanifdescription,
saisoncourante() AS saison,
Tblmanif.manifprix,
Tblmanif.manifniveau,
Tblmanif.maniftype,
Tblmanif.maniftypedetail,
Tblmanif.manifprixenfant,
Tblmanif.maniflieu,
Tblmanif.maniflieudetail,
Tblmanif.manifkm,
Tblmanif.manifdifficulte,
Tblmanif.manifdenivele,
Tblmanif.manifdenivelebas,
Tblmanif.manifdeniveledetail,
Tblmanif.manifconseil,
Tblmanif.manifrepas,
Tblmanif.manifHretour,
Tblmanif.manifHdepart,
Tblmanif.manifdepartdetail,
Tblmanif.manifnbparticipant,
TmpCat.defj1,
TmpCat.defj2,
TmpCat.defj3,
TmpCat.defj4,
TmpCat.defj5,
TmpCat.defj6,
TmpCat.defj7

FROM TmpCat LEFT JOIN Tblmanif
ON TmpCat.DernierDemanifcptr = Tblmanif.manifcptr

WHERE (((Abs([defj1]) + Abs([defj2]) + Abs([defj3])
+ Abs([defj4]) + Abs([defj5]) + Abs([defj6]) + Abs([defj7]))>0));
It inserts in TblManif the records from Tmpcat et some fields picked from
Tblmanif by a link from Tmpcat
When i run the query from the query tab, it runs perfectly.

Thanks,

Yves
Click to expand...
Click to expand...
 
Is the record that is missing when you run this from code the record you are
currently entering on a form?

If so, it could be that the record has not yet been saved and therefore does
not yet exist in the table (new record) or does not meet the criteria to be
included (old record that is being modified).

toto said:
I have tested your code, it gives no error, it shows me the exact (bad)
number of records inserted.
It's not a record wich fails, it's always the last one who "fails"
whatever the number to insert, the last one whaever it is, is missing.
But the code don't give error on running..

When I run it from query tab it works fine.
When I run it from code (VBA in a form, or command line) it inserts all
the record but one.

Yves




Allen Browne said:
Thanks for the query statement. I cannot see anything in the WHERE clause
that should be handled differently in code than in the interface, unless
there is a timing issue.

It would be good to add the dbFailOnError switch to the Execute. That way
Access will notify you if there is an error trying to insert a row. Try
something like this:

With CurrentDb()
.Execute "MyQuery", dbFailOnError
Debug.Print .RecordsAffected & " records added. TmpCat has " &
DCount("*", "TmpCat")
End With

If that leads no-where, examine that record that fails to append.
Anything that could be blocking it? Any of the defj* fields contain a
Null? How do the records get the the temp table? Is it possible that the
processing of appending them to the temp table is not yet complete when
this code runs (but would be complete by the time you ran it from the
query window)?

If that leads no-where too, tell us more about the fields the outer join
is based on. Data type and size?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

toto said:
the query is:
Click to expand...

INSERT INTO Tblmanif ( maniftitre,
manifsection,
manifdescription,
manifsaison,
manifprix,
manifniveau,
maniftype,
maniftypedetail,
manifprixenfant,
maniflieu,
maniflieudetail,
manifkm,
manifdifficulte,
manifdenivele,
manifdenivelebas,
manifdeniveledetail,
manifconseil,
manifrepas,
manifHretour,
manifHdepart,
manifdepartdetail,
manifnbparticipant,
manifdefJ1,
manifdefJ2,
manifdefJ3,
manifdefJ4,
manifdefJ5,
manifdefJ6,
manifdefJ7 )

SELECT TmpCat.DernierDemaniftitre,
Tblmanif.manifsection,
TmpCat.DernierDemanifdescription,
saisoncourante() AS saison,
Tblmanif.manifprix,
Tblmanif.manifniveau,
Tblmanif.maniftype,
Tblmanif.maniftypedetail,
Tblmanif.manifprixenfant,
Tblmanif.maniflieu,
Tblmanif.maniflieudetail,
Tblmanif.manifkm,
Tblmanif.manifdifficulte,
Tblmanif.manifdenivele,
Tblmanif.manifdenivelebas,
Tblmanif.manifdeniveledetail,
Tblmanif.manifconseil,
Tblmanif.manifrepas,
Tblmanif.manifHretour,
Tblmanif.manifHdepart,
Tblmanif.manifdepartdetail,
Tblmanif.manifnbparticipant,
TmpCat.defj1,
TmpCat.defj2,
TmpCat.defj3,
TmpCat.defj4,
TmpCat.defj5,
TmpCat.defj6,
TmpCat.defj7

FROM TmpCat LEFT JOIN Tblmanif
ON TmpCat.DernierDemanifcptr = Tblmanif.manifcptr

WHERE (((Abs([defj1]) + Abs([defj2]) + Abs([defj3])
+ Abs([defj4]) + Abs([defj5]) + Abs([defj6]) + Abs([defj7]))>0));
It inserts in TblManif the records from Tmpcat et some fields picked
from Tblmanif by a link from Tmpcat
When i run the query from the query tab, it runs perfectly.

Thanks,

Yves
"Allen Browne" <[email protected]> a icrit dans le message de
OF2PmS%[email protected]...
It is hard to tell what is happening without knowing what is in the
query.

Open the query in design view.
Then switch to SQL view (View menu.)
Copy the SQL statement, and paste it as a follow up to this thread.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a Query wich inserts record in a table.
When I run it from the Queries Tab, it works perfectly.

I run it from VBA (in a form), it runs wrong : it inserts all the
records but not the last one.
Each time it inserts only N-1 records.

the code is :
Dim q As QueryDef
Set q = CurrentDb.QueryDefs("MyQuery")
q.Execute
q.Close
Click to expand...
Click to expand...
Click to expand...
 
Thanks that's the solution.
As always "the answer is in the question"
a simple
Me.Refresh
in the code form, before the query solves the problem.

The problem was not the query, it was just the selection form, the "last
one" always missing was the one not saved when the query run.
After the query, the form was closed (and so saved), the table was fine, all
records saved, so the query run perfectily, launched from the query tab.

Thanks to John & Allen
Yves



John Spencer said:
Is the record that is missing when you run this from code the record you
are currently entering on a form?

If so, it could be that the record has not yet been saved and therefore
does not yet exist in the table (new record) or does not meet the criteria
to be included (old record that is being modified).

toto said:
I have tested your code, it gives no error, it shows me the exact (bad)
number of records inserted.
It's not a record wich fails, it's always the last one who "fails"
whatever the number to insert, the last one whaever it is, is missing.
But the code don't give error on running..

When I run it from query tab it works fine.
When I run it from code (VBA in a form, or command line) it inserts all
the record but one.

Yves




Allen Browne said:
Thanks for the query statement. I cannot see anything in the WHERE
clause that should be handled differently in code than in the interface,
unless there is a timing issue.

It would be good to add the dbFailOnError switch to the Execute. That
way Access will notify you if there is an error trying to insert a row.
Try something like this:

With CurrentDb()
.Execute "MyQuery", dbFailOnError
Debug.Print .RecordsAffected & " records added. TmpCat has " &
DCount("*", "TmpCat")
End With

If that leads no-where, examine that record that fails to append.
Anything that could be blocking it? Any of the defj* fields contain a
Null? How do the records get the the temp table? Is it possible that the
processing of appending them to the temp table is not yet complete when
this code runs (but would be complete by the time you ran it from the
query window)?

If that leads no-where too, tell us more about the fields the outer join
is based on. Data type and size?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

the query is:

INSERT INTO Tblmanif ( maniftitre,
manifsection,
manifdescription,
manifsaison,
manifprix,
manifniveau,
maniftype,
maniftypedetail,
manifprixenfant,
maniflieu,
maniflieudetail,
manifkm,
manifdifficulte,
manifdenivele,
manifdenivelebas,
manifdeniveledetail,
manifconseil,
manifrepas,
manifHretour,
manifHdepart,
manifdepartdetail,
manifnbparticipant,
manifdefJ1,
manifdefJ2,
manifdefJ3,
manifdefJ4,
manifdefJ5,
manifdefJ6,
manifdefJ7 )

SELECT TmpCat.DernierDemaniftitre,
Tblmanif.manifsection,
TmpCat.DernierDemanifdescription,
saisoncourante() AS saison,
Tblmanif.manifprix,
Tblmanif.manifniveau,
Tblmanif.maniftype,
Tblmanif.maniftypedetail,
Tblmanif.manifprixenfant,
Tblmanif.maniflieu,
Tblmanif.maniflieudetail,
Tblmanif.manifkm,
Tblmanif.manifdifficulte,
Tblmanif.manifdenivele,
Tblmanif.manifdenivelebas,
Tblmanif.manifdeniveledetail,
Tblmanif.manifconseil,
Tblmanif.manifrepas,
Tblmanif.manifHretour,
Tblmanif.manifHdepart,
Tblmanif.manifdepartdetail,
Tblmanif.manifnbparticipant,
TmpCat.defj1,
TmpCat.defj2,
TmpCat.defj3,
TmpCat.defj4,
TmpCat.defj5,
TmpCat.defj6,
TmpCat.defj7

FROM TmpCat LEFT JOIN Tblmanif
ON TmpCat.DernierDemanifcptr = Tblmanif.manifcptr

WHERE (((Abs([defj1]) + Abs([defj2]) + Abs([defj3])
+ Abs([defj4]) + Abs([defj5]) + Abs([defj6]) + Abs([defj7]))>0));

It inserts in TblManif the records from Tmpcat et some fields picked
from Tblmanif by a link from Tmpcat
When i run the query from the query tab, it runs perfectly.

Thanks,

Yves
"Allen Browne" <[email protected]> a icrit dans le message de
OF2PmS%[email protected]...
It is hard to tell what is happening without knowing what is in the
query.

Open the query in design view.
Then switch to SQL view (View menu.)
Copy the SQL statement, and paste it as a follow up to this thread.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I have a Query wich inserts record in a table.
When I run it from the Queries Tab, it works perfectly.

I run it from VBA (in a form), it runs wrong : it inserts all the
records but not the last one.
Each time it inserts only N-1 records.

the code is :
Dim q As QueryDef
Set q = CurrentDb.QueryDefs("MyQuery")
q.Execute
q.Close
Click to expand...
Click to expand...
Click to expand...
 
Blush!
It's simple to spot the problem, when you've done it yourself.

But thanks for the kind words.
 
Back
Top