Append Query Using Form textbox functions issue

[DHarris]

I created a continuous form in Access2003 based on a query that users review customers and once reviewed clicks on a command button that executes an append query to update a table of the reviewed customer(s) data. The table includes the fields: customer_number, shipto_number [obtained from the associated forms textbox]. I also need to include the date reviewed and the user who performed the review in the append query. The command button is executing the query, however, the query appends 0 records. I have a textbox (USER) on the form that displays the user’s login id from a custom function and date added comes from the Now() function and is not on the form. The append query ask for the value for USER and then updates 0 records. I don't want the users to have to enter USER parameter & just execute the append once button pushed. I’m pretty new to this and this is the last thing I’m struggling with.

INSERT INTO CleanCusts ( Customer_Number, ShipTo_Number, Date_Added, Added_By )
SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now() AS Expr2, [USER] AS Expr1
FROM InvoicedAddressSelectqry
WHERE (((InvoicedAddressSelectqry.Cust)=[Forms]![VerifyCleanedCustomers]![Customer_Number]) AND ((InvoicedAddressSelectqry.ShipTo_Number)=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]) AND (([USER])=[Forms]![VerifyCleanedCustomers]![USER]));

[pks00]

First get your select working first, u do not need column aliases either
Create a new query and run it
the form has to be opened, does the query below work?

SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
FROM InvoicedAddressSelectqry
WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]
AND InvoicedAddressSelectqry.ShipTo_Number=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]
AND [USER]=[Forms]![VerifyCleanedCustomers]![USER]

If it doesnt, narrow it down to find your dodgy filter
eg
try this

SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
FROM InvoicedAddressSelectqry
WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]

does this work? if so then add the 2nd filter

SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
FROM InvoicedAddressSelectqry
WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]
AND InvoicedAddressSelectqry.ShipTo_Number=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]

etc, trial/error

[DHarris]

Yes, the original query does return data if I hardcode values for the form variables but does not if I use the form field variables (does not prompt me for values except for USER -
This query does work when I hardcode the substitution variables:
SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now() AS Expr1, [USER] AS Expr2
FROM InvoicedAddressSelectqry
WHERE (((InvoicedAddressSelectqry.Cust)="10002832") AND ((InvoicedAddressSelectqry.ShipTo_Number)="000000" ));

However, both queries does ask for the value for the alias [USER] when I run the query.

[pks00]

Yes, the original query does return data if I hardcode values for the form variables but does not if I use the form field variables (does not prompt me for values except for USER -
This query does work when I hardcode the substitution variables:
SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now() AS Expr1, [USER] AS Expr2
FROM InvoicedAddressSelectqry
WHERE (((InvoicedAddressSelectqry.Cust)="10002832") AND ((InvoicedAddressSelectqry.ShipTo_Number)="000000" ));

However, both queries does ask for the value for the alias [USER] when I run the query.

Is USER part of InvoicedAddressSelectqry ?

What Im getting at is u are using [USER] as a column to return, if u want it to return the form value then enter the full paqth

forms!.... as USER