Append Queries on Form (1 Viewer)

[darth sidious]

Hi

I have a form(frmNewTest) that generates a fitness test id when you enter the month and year (you do not need to enter anything in the Fitness test id field which is greyed out).

This data on the form should then append to the tblFitnessTest. However, when I select the button to add test it returns a message 0 rows to append.

I have attached the database and the form in question (frmNewTest). There are two append queries attached to the button on the form and neither work correctly. I have tried almost everything but can't get it to work. Any assistance would be greatly appreciated.

Many Thanks

Darth Sidious

 

[JHB]

Remove the table from the query:
Your query:

INSERT INTO tblFitnessTest ( FitnessTestID, TestMonth, TestYear )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![myTestMonth] AS Expr2, [Forms]![frmNewTest]![myTestYear] AS Expr3
[COLOR=red][B]FROM tblFitnessTest[/B][/COLOR];

Correct is:

INSERT INTO tblFitnessTest ( FitnessTestID, TestMonth, TestYear )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![myTestMonth] AS Expr2, [Forms]![frmNewTest]![myTestYear] AS Expr3;

 

[darth sidious]

Remove the table from the query:
Your query:

INSERT INTO tblFitnessTest ( FitnessTestID, TestMonth, TestYear )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![myTestMonth] AS Expr2, [Forms]![frmNewTest]![myTestYear] AS Expr3
[COLOR=red][B]FROM tblFitnessTest[/B][/COLOR];

Correct is:

INSERT INTO tblFitnessTest ( FitnessTestID, TestMonth, TestYear )
SELECT [Forms]![frmNewTest]![myFitnessTestID] AS Expr1, [Forms]![frmNewTest]![myTestMonth] AS Expr2, [Forms]![frmNewTest]![myTestYear] AS Expr3;

Hi

Thanks for the response it worked!. When I used expression builder to create the append query I'm pretty sure I didn't add FROM tblFitnessTest. How did this get there?

Thanks

Darth Sidious

 

[JHB]

I think you had choosen the table in the thought it should be the table you you do the "insert to", but it is the table you select from.

(Try do a new append query, then you'll see)

 

[darth sidious]

Thanks will do .