Append Query Randomly Works?

[Guest]

I have a form (Enter Job) with yes/no controls. And in the on click event
for each control I have the following code (example):

Me.Plan = "Landscape Plan"

If LandscapePlanLA Then
DoCmd.OpenQuery "Append Agency1", acViewNormal
DoCmd.OpenQuery "Append Agency2", acViewNormal
Else
DoCmd.OpenQuery "Delete Plan Status", acViewNormal
DoCmd.OpenQuery "Delete Agency1", acViewNormal
DoCmd.OpenQuery "Delete Agency2", acViewNormal
End If

The two queries are similar in fashion and have the following SQL code:

INSERT INTO Agency1 ( Service, [Project Number] )
SELECT Jobs.Plan, Jobs.[Project Number]
FROM Jobs
WHERE (((Jobs.Plan)=Forms![Enter Job]!Plan) And ((Jobs.[Project
Number])=Forms![Enter Job]!ProjectNumber));

I've just recently gone into action queries, and don't know too much about
them. The delete queries work fine, but the append queries don't work
everytime. If I open the form and click on one of the controls, the append
query works. And if I scroll through the records and click on the same
control for a different record it works. But when I click on another
control, with the same coding, it doesn't run the append query, but I don't
get any errors. Any ideas?

Thanks in advance
Neil Cash

 

[Allen Browne]

Try the Execute method instead of OpenQuery. You can then exit if the append
failed before exiting the delete, and you can also get feedback on whether
any records were appended or deleted.

Example:
Dim db As DAO.Database
Set db = dbEngine(0)(0)
db.Execute "Append Agency1", dbFailOnError
Debug.Print db.RecordsAffected " record(s) appended."

If you need an all-or-nothing result where you can rollback the appended
records if the delete fails later, see:
Archive: Move records to another table
at:
http://members.iinet.net.au/~allenbrowne/ser-37.html

 

[Guest]

Thanks for getting back to me so soon. I put in your code and the debugger
stops it at:
db.Execute "Append Agency1", dbFailOnError
with an error of:
Too few parameters. Expected 2.

 

[Allen Browne]

Yes, it would. Didn't read your query statement to realize it refers to
controls on the form.

What we do is just build these query statements dynamically in code, i.e.:
Dim strSQL As String
strSQL = "INSERT INTO Agency1 ( Service, [Project Number] ) " & _
"SELECT Jobs.Plan, Jobs.[Project Number] FROM Jobs " & _
"WHERE ((Jobs.Plan = " & Forms![Enter Job]!Plan & _
") And (Jobs.[Project Number] = " & Forms![Enter Job]!ProjectNumber
& "));"
dbExecute strSQL, dbFailOnError

 

[Guest]

I put in your code exactly as you gave me, and got another run-time error.
It has a syntax error (missing operator) in query expression.....the WHERE
statement. I'm very unfamiliar with SQL coding and wouldn't know which
operator it'd be missing, otherwise I usually can figure it out by looking at
the format. Still many thanks.

 

[Allen Browne]

Immediately above the dbExecute line, add:
Debug.Print strSQL

When it fails, open the Immediate Window (press Ctrl+G) and look at the SQL
statement printed there. You should be able to see what is wrong. You can
even copy what you see there, and paste into SQL View of a new query to see
what it looks there if it helps.

If the text boxes on the Enter Job form are blank, the WHERE clause will be
malformed.

In the Jobs table, if Plan is a field of type Text (not Number), you need
extra quotes, i.e.:
strSQL = "INSERT INTO Agency1 ( Service, [Project Number] ) " & _
"SELECT Jobs.Plan, Jobs.[Project Number] FROM Jobs " & _
"WHERE ((Jobs.Plan = """ & Forms![Enter Job]!Plan & _
""") And ...
Same with Project Number field.

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

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

I put in your code exactly as you gave me, and got another run-time error.
It has a syntax error (missing operator) in query expression.....the WHERE
statement. I'm very unfamiliar with SQL coding and wouldn't know which
operator it'd be missing, otherwise I usually can figure it out by looking
at
the format. Still many thanks.

Yes, it would. Didn't read your query statement to realize it refers to
controls on the form.

What we do is just build these query statements dynamically in code,
i.e.:
Dim strSQL As String
strSQL = "INSERT INTO Agency1 ( Service, [Project Number] ) " & _
"SELECT Jobs.Plan, Jobs.[Project Number] FROM Jobs " & _
"WHERE ((Jobs.Plan = " & Forms![Enter Job]!Plan & _
") And (Jobs.[Project Number] = " & Forms![Enter
Job]!ProjectNumber
& "));"
dbExecute strSQL, dbFailOnError

 

[Guest]

I went ahead and made those changes, and applied it (as a test) to three
controls (say a, b, and c for now). The append query doesn't fail, but now
it really randomly works. I went ahead and tested the control on ten
records, and it never consistently worked. Here's what it appended to the
table:
1. a,a,b
2. b,a,b
3. a,a,b
4. b,a,b
5. a,a,b
6. b,a,b
7. a,a,b
8. b,a,b
9. a,a,b
10. b,c,b
I'm not too familiar with declaring public variables, so for each control I
just copied the set of declarations and the commands. That shouldn't matter
should it? It's appending records, just not the right ones.

 

[Allen Browne]

Okay, so you have a working SQL statement, and your Debug.Print statements
verify that the record is being added.

Now you want to understand what is going on. First, the action query will
pick up the value from the current record in your form, not for all records
in your form. Secondly, open your table in design view, and check out the
Data Type of the fields in the SQL statement. Make sure you use the extra
quotes if the field is Text. Use # as the delimiter if the field is
Date/Time. Use no delimiter the field is Number, Currency, or Yes/No.

Beyond that, it is going to be up to you to take these one at a time, and
figure out why you got the result you did from the statement you created.
Remember that you can Debug.Print your strSQL string, paste it into SQL View
of a query, and then switch it to Design View, to get Access to show you
graphically how it understands what you have asked for.

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

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

I went ahead and made those changes, and applied it (as a test) to three
controls (say a, b, and c for now). The append query doesn't fail, but
now
it really randomly works. I went ahead and tested the control on ten
records, and it never consistently worked. Here's what it appended to the
table:
1. a,a,b
2. b,a,b
3. a,a,b
4. b,a,b
5. a,a,b
6. b,a,b
7. a,a,b
8. b,a,b
9. a,a,b
10. b,c,b
I'm not too familiar with declaring public variables, so for each control
I
just copied the set of declarations and the commands. That shouldn't
matter
should it? It's appending records, just not the right ones.

Immediately above the dbExecute line, add:
Debug.Print strSQL

When it fails, open the Immediate Window (press Ctrl+G) and look at the
SQL
statement printed there. You should be able to see what is wrong. You can
even copy what you see there, and paste into SQL View of a new query to
see
what it looks there if it helps.

If the text boxes on the Enter Job form are blank, the WHERE clause will
be
malformed.

In the Jobs table, if Plan is a field of type Text (not Number), you need
extra quotes, i.e.:
strSQL = "INSERT INTO Agency1 ( Service, [Project Number] ) " & _
"SELECT Jobs.Plan, Jobs.[Project Number] FROM Jobs " & _
"WHERE ((Jobs.Plan = """ & Forms![Enter Job]!Plan & _
""") And ...
Same with Project Number field.

 

[Guest]

Thanks for all your help, I really appreciate it. I guess I'll get started

Okay, so you have a working SQL statement, and your Debug.Print statements
verify that the record is being added.

Now you want to understand what is going on. First, the action query will
pick up the value from the current record in your form, not for all records
in your form. Secondly, open your table in design view, and check out the
Data Type of the fields in the SQL statement. Make sure you use the extra
quotes if the field is Text. Use # as the delimiter if the field is
Date/Time. Use no delimiter the field is Number, Currency, or Yes/No.

Beyond that, it is going to be up to you to take these one at a time, and
figure out why you got the result you did from the statement you created.
Remember that you can Debug.Print your strSQL string, paste it into SQL View
of a query, and then switch it to Design View, to get Access to show you
graphically how it understands what you have asked for.

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

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

I went ahead and made those changes, and applied it (as a test) to three
controls (say a, b, and c for now). The append query doesn't fail, but
now
it really randomly works. I went ahead and tested the control on ten
records, and it never consistently worked. Here's what it appended to the
table:
1. a,a,b
2. b,a,b
3. a,a,b
4. b,a,b
5. a,a,b
6. b,a,b
7. a,a,b
8. b,a,b
9. a,a,b
10. b,c,b
I'm not too familiar with declaring public variables, so for each control
I
just copied the set of declarations and the commands. That shouldn't
matter
should it? It's appending records, just not the right ones.

Immediately above the dbExecute line, add:
Debug.Print strSQL

When it fails, open the Immediate Window (press Ctrl+G) and look at the
SQL
statement printed there. You should be able to see what is wrong. You can
even copy what you see there, and paste into SQL View of a new query to
see
what it looks there if it helps.

If the text boxes on the Enter Job form are blank, the WHERE clause will
be
malformed.

In the Jobs table, if Plan is a field of type Text (not Number), you need
extra quotes, i.e.:
strSQL = "INSERT INTO Agency1 ( Service, [Project Number] ) " & _
"SELECT Jobs.Plan, Jobs.[Project Number] FROM Jobs " & _
"WHERE ((Jobs.Plan = """ & Forms![Enter Job]!Plan & _
""") And ...
Same with Project Number field.