Append queries

A

Amateur

What can be my mistake?
- I have two append queries 1 with data the other one without
- I would like to append both queries to one table
The warnings tell me always that there are no rows to be added - neither in
query 1 nor in query 2 - but - query 2 has one row to be appended.
Why does my sql command get not correctly executed?
****************************
Private Sub Command29_Click()
Dim stDocName As String


DoCmd.OpenQuery “billcemeteryyearâ€, acViewNormal, acEdit
DoCmd.OpenQuery “billcemeteryhalfyearâ€, acViewNormal, acEdit


End Sub
****************************
 
O

Ofer Cohen

The message usually includes the reason why no record was added to the query.

It's very hard to know the reson for that without knowing the table
structure and the data appended to it.

The first thing I would do is check the message properly
Second, check the structure of the table against to the data you are trying
to append, it usually happen because of key vaulation
 
A

Amateur

If I run the append queries manually one by one everything is correct and
working fine.
If I run the append queries from my form with the command button SQL I get
the message for both queries (even if one query has a row to append): You are
about to append 0 rows.
How can that be?
Thanks
Klaus
 
M

Maurice

Could be a long shot but try it without de optional arguments...
I assume these are append queries..

DoCmd.OpenQuery “billcemeteryyearâ€
DoCmd.OpenQuery “billcemeteryhalfyearâ€

Maurice
 
A

Amateur

Thanks for the advice Maurice, but it doesn't change anything. Still the same
Klaus
 
O

Ofer Cohen

Mybe the queries need to run in a certain order, so the half year should run
before the year
(if the depend on each other)

DoCmd.OpenQuery “billcemeteryhalfyearâ€
DoCmd.OpenQuery “billcemeteryyearâ€
 
A

Amateur

Thanks, I tried that before without any positive result - really slowly I am
running out of ideas.
What I don't understand is, if I run them one-by-one - everything is fine.
Only if I would like to run both with a command button to execute my SQL
command from my form, it's not working.
I get only question marks out of my head because I cannot find a logic
explanation for that.
Klaus
 
J

John W. Vinson

What can be my mistake?
- I have two append queries 1 with data the other one without
- I would like to append both queries to one table
The warnings tell me always that there are no rows to be added - neither in
query 1 nor in query 2 - but - query 2 has one row to be appended.
Why does my sql command get not correctly executed?
****************************
Private Sub Command29_Click()
Dim stDocName As String


DoCmd.OpenQuery “billcemeteryyear”, acViewNormal, acEdit
DoCmd.OpenQuery “billcemeteryhalfyear”, acViewNormal, acEdit


End Sub
****************************

Don't use OpenQuery. Instead, execute the Querydef object:

Dim db As DAO.Database
Dim qd1 As DAO.Querydef
Dim qd2 As DAO.Querydef
Set db = CurrentDb
Set qd1 = db.Querydefs("billcemeteryyear")
qd1.Execute, dbFailOnError
<you can look at qd1.RecordsAffected to see how many were>
qd2.Execute, dbFailOnError
Set qd1 = Nothing
Set qd2 = Nothing

John W. Vinson [MVP]
 
A

Amateur

Hi John
thanks for your response and to show me how little knowledge I have, for
that what I am planning to do - I do not understand anything from your code.
Does your code mean that I have to do my sql as follows?

Private Sub Command29_Click()
Dim stDocName As String
Dim db As DAO.Database
Dim qd1 As DAO.Querydef
Dim qd2 As DAO.Querydef
Set db = CurrentDb
Set qd1 = db.Querydefs("billcemeteryyear")
qd1.Execute, dbFailOnError
<you can look at qd1.RecordsAffected to see how many were>
qd2.Execute, dbFailOnError
Set qd1 = Nothing
Set qd2 = Nothing

Dim db As DAO.Database
Dim qd1 As DAO.Querydef
Dim qd2 As DAO.Querydef
Set db = CurrentDb
Set qd1 = db.Querydefs("billcemeteryhalfyear")
qd1.Execute, dbFailOnError
<you can look at qd1.RecordsAffected to see how many were>
qd2.Execute, dbFailOnError
Set qd1 = Nothing
Set qd2 = Nothing

End Sub

Please, I am totally lost - can you explain to me in easy words what your
code is doing? or, if it is not asked to much to alter my code?
Thanks
Klaus
 
J

John W. Vinson

Hi John
thanks for your response and to show me how little knowledge I have, for
that what I am planning to do - I do not understand anything from your code.
Does your code mean that I have to do my sql as follows?

Private Sub Command29_Click()
Dim stDocName As String
Dim db As DAO.Database
Dim qd1 As DAO.Querydef
Dim qd2 As DAO.Querydef
Set db = CurrentDb
Set qd1 = db.Querydefs("billcemeteryyear")
qd1.Execute, dbFailOnError
<you can look at qd1.RecordsAffected to see how many were>
qd2.Execute, dbFailOnError
Set qd1 = Nothing
Set qd2 = Nothing

Dim db As DAO.Database
Dim qd1 As DAO.Querydef
Dim qd2 As DAO.Querydef
Set db = CurrentDb
Set qd1 = db.Querydefs("billcemeteryhalfyear")
qd1.Execute, dbFailOnError
<you can look at qd1.RecordsAffected to see how many were>
qd2.Execute, dbFailOnError
Set qd1 = Nothing
Set qd2 = Nothing

End Sub

Please, I am totally lost - can you explain to me in easy words what your
code is doing? or, if it is not asked to much to alter my code?
Thanks

Well, that code doesn't much resemble what I posted... <g>

It's not SQL (the language of queries); it's VBA, Visual Basic for
Applications, the language of modules. You had posted some VBA code which
executes two queries; I posted some alternative VBA code to execute those same
two queries.

Here's what I'd suggest that you use. Change the button name from Command29 to
something meaningful - cmdRunBills let's say - just so you can understand the
code yourself later.

Private Sub cmdRunBills_Click()
Dim db As DAO.Database ' define a Database object
Dim qd1 As DAO.Querydef ' define two Querydef objects
Dim qd2 As DAO.Querydef
On Error GoTo Proc_Error ' set up an error trap
Set db = CurrentDb ' set the Database object to the current database
Set qd1 = db.Querydefs("billcemeteryyear") ' select your first action query
qd1.Execute, dbFailOnError ' run it; if there's an error it will be trapped
MsgBox "Bill Cemetery Year query affected " & qd1.RecordsAffected & " records"
Set qd2 = db.Querydefs("billcemeteryhalfyear") ' select the next query
qd2.Execute, dbFailOnError ' run it
MsgBox "Bill Cemetery Half-year affected " & qd2.RecordsAffected & " records"
Proc_Exit:
On Error Resume Next ' ignore any errors in the cleanup
Set qd1 = Nothing ' clean up after yourself
Set qd2 = Nothing
Exit Sub
Proc_Error: ' any errors come here
' Display the error on the screen
MsgBox "Error " & Err.Number & " in cmdRunBills_Click:" & vbCrLf _
& Err.Description
Resume Proc_Exit ' after displaying the error, just quit
End Sub


COpy this entire program into the Click event code of your command button;
select Debug... COmpile; correct any errors or post back for help doing so.


John W. Vinson [MVP]
 
A

Amateur

Dear John
thanks for the code, I copied it into my VB and get the following error
message on the following line:
qd1.Execute, dbFailOnError ' run it; if there's an error it will be trapped
*********************************************************
Compile error: Wrong number of arguments or invalid property assignment
*********************************************************
Can you tell me what that means?
Thanks
Klaus
 
J

John W. Vinson

Dear John
thanks for the code, I copied it into my VB and get the following error
message on the following line:
qd1.Execute, dbFailOnError ' run it; if there's an error it will be trapped
*********************************************************
Compile error: Wrong number of arguments or invalid property assignment
*********************************************************
Can you tell me what that means?
Thanks

It means I wasn't thinking clearly and mistyped the code. Leave out the comma.

qd1.Execute dbFailOnError ' run it; if there's an error it will be trapped

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top