Form based on an Append Query

[Guest]

I created an Append query containing a parameter on one of its fields. No
problem here, it runs and appends the records based on the typed parameter.

The problem is that I want to create a command button that will run this
query. The following partial code was created by the Command button wizard:

stDocName = "My Append Query"
coCmd.OpenQuery stDocName, acNormal, acEdit

When I clcik on the command button to run the query the message "Object
required" is displayed. I am stuck.

Any help? thank you.

 

[Guest]

wizards are really pretty dumb. The OpenQuery method is for Select Queries.

All you need to do is put this in the Click event of the command button.
(Using the code builder, not directly in the text box)

CurrentDb.Execute("MyAppendQueryNameGoesHere"), dbFailOnError

 

[Guest]

Klatuu,

I typed the code that you suggested but now I got the error:

"Invalid SQL statement: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'

I typed , UPDATE after the command line but it did not work.

Help again.

Thank you

 

[Guest]

It is not the code I posted that is the problem.
First, MyAppendQueryNameGoesHere should be replaced with the name of your
query.
Now, lets be sure this is an Action Query. You Said it is an Append query,
so it should start with INSERT INTO. If it does not, it is not an append
query.

 

[Guest]

Again, I created the append query in Query Design View, in one of its fields
i typed the criteria [Type your Code] so when the query runs certain records
are added to a table based on the code typed.

When I run the query from the design view or by opening it, the query runs
OK and adds the expected records. My next step is to create a button in a
form that will run the query so users can add their data when they entered
their code.

My mistake, I type the wrong query name, I corrected already and try to run
again via the command button on my form. Now, I got the following error
message:

Too few parameters. Expected 1

I apologize for being so (fill-in the blank).
I checked the The SQL code and it does start with INSERT INTO .

Once again, thank you.

 

[Guest]

The reason it is not working is because the Execute method does not go
through Access. It goes directly to Jet.
It would be better if you had a control on your form that has the value of
the code you want to filter the query on. This is the usual way to do it.
Then in the query criteria for the code you would reference the control on
the form using the folllowing syntax:

[Forms]![MyFormName]![MyControlName]

Then the query would find the value to filter on.

Again, I created the append query in Query Design View, in one of its fields
i typed the criteria [Type your Code] so when the query runs certain records
are added to a table based on the code typed.

When I run the query from the design view or by opening it, the query runs
OK and adds the expected records. My next step is to create a button in a
form that will run the query so users can add their data when they entered
their code.

My mistake, I type the wrong query name, I corrected already and try to run
again via the command button on my form. Now, I got the following error
message:

Too few parameters. Expected 1

I apologize for being so (fill-in the blank).
I checked the The SQL code and it does start with INSERT INTO .

Once again, thank you.

It is not the code I posted that is the problem.
First, MyAppendQueryNameGoesHere should be replaced with the name of your
query.
Now, lets be sure this is an Action Query. You Said it is an Append query,
so it should start with INSERT INTO. If it does not, it is not an append
query.

 

[Guest]

I tried that already, I created a text box where I type a code then click on
the command button that executes the query. In the query criteria I entered
[Forms]![Add Subject Codes]![txtCode] (which sre the names of my form and
control box.) I still get the same error as before... "Too few parameters.
Expected 1"

I added a "refresh" before the execute. nothing.

I opened the form, type my criteria in the control box, refreshed it and
then I minimized. While the form is minimized I tried to run the query and
nothing happens. If I close the form, then when I run the query it asks for
the data from the control box, I type a code and no problem records are
added.

I am flabbergasted...

The reason it is not working is because the Execute method does not go
through Access. It goes directly to Jet.
It would be better if you had a control on your form that has the value of
the code you want to filter the query on. This is the usual way to do it.
Then in the query criteria for the code you would reference the control on
the form using the folllowing syntax:

[Forms]![MyFormName]![MyControlName]

Then the query would find the value to filter on.

Again, I created the append query in Query Design View, in one of its fields
i typed the criteria [Type your Code] so when the query runs certain records
are added to a table based on the code typed.

When I run the query from the design view or by opening it, the query runs
OK and adds the expected records. My next step is to create a button in a
form that will run the query so users can add their data when they entered
their code.

My mistake, I type the wrong query name, I corrected already and try to run
again via the command button on my form. Now, I got the following error
message:

Too few parameters. Expected 1

I apologize for being so (fill-in the blank).
I checked the The SQL code and it does start with INSERT INTO .

Once again, thank you.

It is not the code I posted that is the problem.
First, MyAppendQueryNameGoesHere should be replaced with the name of your
query.
Now, lets be sure this is an Action Query. You Said it is an Append query,
so it should start with INSERT INTO. If it does not, it is not an append
query.

:

Klatuu,

I typed the code that you suggested but now I got the error:

"Invalid SQL statement: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'

I typed , UPDATE after the command line but it did not work.

Help again.

Thank you

:

wizards are really pretty dumb. The OpenQuery method is for Select Queries.

All you need to do is put this in the Click event of the command button.
(Using the code builder, not directly in the text box)

CurrentDb.Execute("MyAppendQueryNameGoesHere"), dbFailOnError

:

I created an Append query containing a parameter on one of its fields. No
problem here, it runs and appends the records based on the typed parameter.

The problem is that I want to create a command button that will run this
query. The following partial code was created by the Command button wizard:

stDocName = "My Append Query"
coCmd.OpenQuery stDocName, acNormal, acEdit

When I clcik on the command button to run the query the message "Object
required" is displayed. I am stuck.

Any help? thank you.

 

[Guest]

IT WORKED! I used the open query statement instead of the execute and it
worked.
Thanx.

I tried that already, I created a text box where I type a code then click on
the command button that executes the query. In the query criteria I entered
[Forms]![Add Subject Codes]![txtCode] (which sre the names of my form and
control box.) I still get the same error as before... "Too few parameters.
Expected 1"

I added a "refresh" before the execute. nothing.

I opened the form, type my criteria in the control box, refreshed it and
then I minimized. While the form is minimized I tried to run the query and
nothing happens. If I close the form, then when I run the query it asks for
the data from the control box, I type a code and no problem records are
added.

I am flabbergasted...

The reason it is not working is because the Execute method does not go
through Access. It goes directly to Jet.
It would be better if you had a control on your form that has the value of
the code you want to filter the query on. This is the usual way to do it.
Then in the query criteria for the code you would reference the control on
the form using the folllowing syntax:

[Forms]![MyFormName]![MyControlName]

Then the query would find the value to filter on.

Again, I created the append query in Query Design View, in one of its fields
i typed the criteria [Type your Code] so when the query runs certain records
are added to a table based on the code typed.

When I run the query from the design view or by opening it, the query runs
OK and adds the expected records. My next step is to create a button in a
form that will run the query so users can add their data when they entered
their code.

My mistake, I type the wrong query name, I corrected already and try to run
again via the command button on my form. Now, I got the following error
message:

Too few parameters. Expected 1

I apologize for being so (fill-in the blank).
I checked the The SQL code and it does start with INSERT INTO .

Once again, thank you.

:

It is not the code I posted that is the problem.
First, MyAppendQueryNameGoesHere should be replaced with the name of your
query.
Now, lets be sure this is an Action Query. You Said it is an Append query,
so it should start with INSERT INTO. If it does not, it is not an append
query.

:

Klatuu,

I typed the code that you suggested but now I got the error:

"Invalid SQL statement: expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'

I typed , UPDATE after the command line but it did not work.

Help again.

Thank you

:

wizards are really pretty dumb. The OpenQuery method is for Select Queries.

All you need to do is put this in the Click event of the command button.
(Using the code builder, not directly in the text box)

CurrentDb.Execute("MyAppendQueryNameGoesHere"), dbFailOnError

:

I created an Append query containing a parameter on one of its fields. No
problem here, it runs and appends the records based on the typed parameter.

The problem is that I want to create a command button that will run this
query. The following partial code was created by the Command button wizard:

stDocName = "My Append Query"
coCmd.OpenQuery stDocName, acNormal, acEdit

When I clcik on the command button to run the query the message "Object
required" is displayed. I am stuck.

Any help? thank you.