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]));
3 4555 pks00 280
Recognized Expert Contributor
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
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 InvoicedAddress Selectqry.Cust, InvoicedAddress Selectqry.ShipT o_Number, Now() AS Expr1, [USER] AS Expr2
FROM InvoicedAddress Selectqry
WHERE (((InvoicedAddr essSelectqry.Cu st)="10002832") AND ((InvoicedAddre ssSelectqry.Shi pTo_Number)="00 0000"));
However, both queries does ask for the value for the alias [USER] when I run the query.
pks00 280
Recognized Expert Contributor
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 InvoicedAddress Selectqry.Cust, InvoicedAddress Selectqry.ShipT o_Number, Now() AS Expr1, [USER] AS Expr2
FROM InvoicedAddress Selectqry
WHERE (((InvoicedAddr essSelectqry.Cu st)="10002832") AND ((InvoicedAddre ssSelectqry.Shi pTo_Number)="00 0000"));
However, both queries does ask for the value for the alias [USER] when I run the query.
Is USER part of InvoicedAddress Selectqry ?
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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: John |
last post by:
Hi - I am trying to perform a simple append query, with no luck. I
have a table (MktPrices) that has the following fields: BondID,
PriceDate, Price. The objective is to allow the user to input a date
and have the append query create a new record for each BondID, using
the input date and the most recent price - which the user can then
update on a tabular form. I have created a totals query
(qryMostRecentPrice) which selects the most...
|
by: Ray Holtz |
last post by:
I have a form that shows a single record based on a query criteria.
When I click a button it is set to use an append query to copy that
record to a separate table, then deletes the record from the first
table. Both tables have a 'DateTime' field that shows when it was last
updated using the Now function.
What I am trying to do is get the 'DateTime' field updated after the
append query copies it to the new table. I have tried updating...
|
by: Geoff |
last post by:
I am running an append query using query by example (but included equivalent
SQL code). The query counts the number of bookings and appends this number
to bookings to Tbl_Weekly
INSERT INTO Tbl_Weekly ( NoofBooks, StartDate, EndDate )
SELECT Count(Bookings.DateofEvent) AS Noofbook, Forms!Printouts!WSdate AS
WeekStart, Forms!Printouts!WEdate AS WeekEnd
FROM Bookings
WHERE (((Bookings.DateofEvent)>!! And
(Bookings.DateofEvent)<(!!)))
|
by: kabradley |
last post by:
Hello Everyone,
So, thanks to nico's help I was finally able to 'finish' our companies access database. For the past week or so though,I have been designing forms that contain a subform and an option group so that whenever a certain button on the option group is pressed the correct subform source object property is changed to display the correct form. For instance, if they click "add new investment" the subform's source object is now changed to...
|
by: SolomonShavitzMetsFan |
last post by:
I am writing for lack of a better word a "mini program" within a form
that does a lot of other things and is a bound form for other
purposes. The form (frmCustDetail) is bound to another table
("tblCalls")than that to which I want to write. I want to have a
button that is called "AddCalls" like below that calls an append query
that opens a new record for a "Call" being made. This table with the
calls just has a Call_ID field (the key...
| |
by: jcethiopia |
last post by:
Hi I'm a new user with Access and I need help with some VBA syntax. I am trying to run an append query using a string SQL statement. Basically, I need to append to "tbl_All" various fields from two different tables.
I need this to be dynamic because depending on what the user selects, it needs to take fields from a specific table. I am therefore using a variable to refer to this table: EquipmentTbl is the variable that i have already...
|
by: bilalkhan |
last post by:
Hello
I m using MS Acces 2007, I need to create a form through which a user can enter all the parameter values in the form and these values will automatically picked by a query:
Here is the query which i want to link with a form
INSERT INTO ( , , , , , , , Cases, Premium, , )
SELECT ., ., ., ., ., ., ., .Cases, .Premium, ., .
FROM
|
by: iheartvba |
last post by:
Hi,
I have a simple append query which takes data from a form and appends it into a table.
INSERT INTO tblSmsSent ( MobileNumber, ClientName, TimeSent, AppointmentTime, AppointmentDate )
SELECT !! AS Expr1, !! AS Expr2, Now() AS Expr3, !! AS Expr4, !! AS Expr5
FROM tblSmsSent;
The Table has the following fields
Field Type
|
by: Kevin Sims |
last post by:
Hopefully the title makes enough sense to get my point across.
Using Access 97 here.
I am trying build a query which uses a form to select the criteria. It works fine if the source contains just one item.
Here is the criteria example:
=(!!)
Problem is the table I am referencing from cboUNIT is setup into groups and each group contains multiple items. For example GROUP1 is units 4,7 and 8.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |