Append query error (1 Viewer)

[garywood84]

I am trying to make an append query to select records from one table based on certain criteria, and then insert the results into another table. I first created a select query to get the required data and then conveted this into an append query. However, when it runs, I get the following error:

"The INSERT INTO statement contains the following unknown field name: ‘ProgrammeID’. Make sure you have typed the name correctly, and try the operation again."

The SQL of the query is:

INSERT INTO tblMaterialsSalesOrders ( EventID, MaterialQuantity, MaterialID )
SELECT [Forms]![frmOrderMaterials]![EventID] AS EventID, IIf([tblMaterials].[MaterialStandardQuantityPer]="Class",[tblMaterials].[MaterialStandardQuantity]*[Forms]![frmOrderMaterials]![NumberOfClasses],IIf([tblMaterials].[MaterialStandardQuantityPer]="Group",[tblMaterials].[MaterialStandardQuantity]*[Forms]![frmOrderMaterials]![NumberOfGroups],IIf([tblMaterials].[MaterialStandardQuantityPer]="Student",[tblMaterials].[MaterialStandardQuantity]*[Forms]![frmOrderMaterials]![NumberOfStudents],IIf([tblMaterials].[MaterialStandardQuantityPer]="Teacher",[tblMaterials].[MaterialStandardQuantity]*[Forms]![frmOrderMaterials]![NumberOfTeachers],IIf([tblMaterials].[MaterialStandardQuantityPer]="Volunteer",[tblMaterials].[MaterialStandardQuantity]*[Forms]![frmOrderMaterials]![NumberOfVolunteers],0))))) AS MaterialQuantity, tblMaterials.MaterialID, *
FROM tblMaterials
WHERE (((tblMaterials.ProgrammeID)=[Forms]![frmOrderMaterials]![ProgrammeID]));

Please help - it's driving me insane!

Gary

 

[DCrake]

Change this line from

WHERE (((tblMaterials.ProgrammeID)=[Forms]![frmOrderMaterials]![ProgrammeID]));

To

WHERE tblMaterials.ProgrammeID=" & [Forms]![frmOrderMaterials]![ProgrammeID] & ";"

 

[garywood84]

Thanks, David.

I managed to solve this before I saw your post, by simply recreating the whole query.

But, for future reference, can you tell me why changing the code as you suggest would have fixed the original query, and how you know to write it this way?

Thanks,

Gary

 

[SOS]

Thanks, David.

I managed to solve this before I saw your post, by simply recreating the whole query.

But, for future reference, can you tell me why changing the code as you suggest would have fixed the original query, and how you know to write it this way?

Thanks,

Gary

Gary:

The reason for doing so is so that you can pass the actual value to the query instead of trying to have the engine determine it by itself. While it may be that sometimes you can pass a form reference that way to it, it really works better and is more stable if you don't include the form reference within the string, but concatenate it in so that it is just getting the VALUE of the control(s) instead.