Copy/Append record into same table

[ill_comms]

Hi All,
I have the following append statement that is throwing back this error "Parameter [@SCHEDULE_ID] has no default value". It is a stored query and is used via a web page.

I have tested the variable before it goes into the query and it has got a valid values. It is something to do with this query.

INSERT INTO SCHEDULED_MAINTENANCE (SERVICE_TYPE_ID, MODULE_CODE, ASSET_GEOMETRY, ASSET_TYPE, FREQUENCY, FREQUENCY_UNIT, FREQUENCY_SELECTION, FREQUENCY_DAY, FREQUENCY_WEEK, FREQUENCY_MONTH, PRIORITY_LEVEL1, PRIORITY_LEVEL1_UNIT, PRIORITY_LEVEL2, PRIORITY_LEVEL2_UNIT, PRIORITY_LEVEL3, PRIORITY_LEVEL3_UNIT, OWNED_BY, INSPECTION_MAINTENANCE, ACTIVITY_CODE )
SELECT A.SERVICE_TYPE_ID, A.MODULE_CODE, A.ASSET_GEOMETRY, A.ASSET_TYPE, A.FREQUENCY, A.FREQUENCY_UNIT, A.FREQUENCY_SELECTION, A.FREQUENCY_DAY, A.FREQUENCY_WEEK, A.FREQUENCY_MONTH, A.PRIORITY_LEVEL1, A.PRIORITY_LEVEL1_UNIT, A.PRIORITY_LEVEL2, A.PRIORITY_LEVEL2_UNIT, A.PRIORITY_LEVEL3, A.PRIORITY_LEVEL3_UNIT, A.OWNED_BY, A.INSPECTION_MAINTENANCE, A.ACTIVITY_CODE
FROM SCHEDULED_MAINTENANCE AS A
WHERE A.SCHEDULE_ID=[@SCHEDULE_ID];

Any help would greatly be appreciated
Hayden

 

[Galaxiom]

[@SCHEDULE_ID] is not a variable. Access is expecting it as a Parameter.

If the query was run directly Acess would prompt for it to be entered. However on the web page it is run as a querydef where the parameter must be defined before the query is run.

Set qdf = Currentdb.QueryDefs("YourQueryName")
qdf.Parameters("@SCHEDULE_ID") = @SCHEDULE_ID
qdf.Execute

However I would recommend changing the parameter name inside the query (and the parameter setting) to something different form the variable to make it clearer.

 

[ill_comms]

Hi,

yes, bad terminology on my part, it is a parameter to the query and it is being passed through correctly already, I've even just made sure as you suggested by changing the parameter names.

The error still remains the same.