By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,656 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

Append Query Using Form textbox functions issue

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO CleanCusts ( Customer_Number, ShipTo_Number, Date_Added, Added_By )
  2. SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now() AS Expr2, [USER] AS Expr1
  3. FROM InvoicedAddressSelectqry
  4. WHERE (((InvoicedAddressSelectqry.Cust)=[Forms]![VerifyCleanedCustomers]![Customer_Number]) AND ((InvoicedAddressSelectqry.ShipTo_Number)=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]) AND (([USER])=[Forms]![VerifyCleanedCustomers]![USER]));
Jan 6 '07 #1
Share this Question
Share on Google+
3 Replies


pks00
Expert 100+
P: 280
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?

Expand|Select|Wrap|Line Numbers
  1. SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
  2. FROM InvoicedAddressSelectqry
  3. WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]
  4. AND InvoicedAddressSelectqry.ShipTo_Number=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]
  5. AND [USER]=[Forms]![VerifyCleanedCustomers]![USER]

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

Expand|Select|Wrap|Line Numbers
  1. SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
  2. FROM InvoicedAddressSelectqry
  3. WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]
does this work? if so then add the 2nd filter

Expand|Select|Wrap|Line Numbers
  1. SELECT InvoicedAddressSelectqry.Cust, InvoicedAddressSelectqry.ShipTo_Number, Now(), [USER]
  2. FROM InvoicedAddressSelectqry
  3. WHERE InvoicedAddressSelectqry.Cust=[Forms]![VerifyCleanedCustomers]![Customer_Number]
  4. AND InvoicedAddressSelectqry.ShipTo_Number=[Forms]![VerifyCleanedCustomers]![ShipTo_Number]

etc, trial/error
Jan 7 '07 #2

P: 2
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.
Jan 8 '07 #3

pks00
Expert 100+
P: 280
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
Jan 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.