473,327 Members | 2,074 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Update query not sticking...

Hi folks;
Here's a mystery for you. I've written an update query that runs every time my database opens and does a little housekeeping amongst the records. It updates a yes/no value in one table to True if a text value in another table ="Client". Anyway, I run the query and it gives the usual "You are about to update 83 records blah blah.... Yes or No?" But if I click Yes, nothing happens! The 83 records stay un-updated! Anyone know what's happening here? Also, I'd like this query to run in the background on startup, is there any way of suppressing the warning messages?

Thanks guys!
Aug 3 '07 #1
8 1592
puppydogbuddy
1,923 Expert 1GB
Hi folks;
Here's a mystery for you. I've written an update query that runs every time my database opens and does a little housekeeping amongst the records. It updates a yes/no value in one table to True if a text value in another table ="Client". Anyway, I run the query and it gives the usual "You are about to update 83 records blah blah.... Yes or No?" But if I click Yes, nothing happens! The 83 records stay un-updated! Anyone know what's happening here? Also, I'd like this query to run in the background on startup, is there any way of suppressing the warning messages?

Thanks guys!
To stop the message confirming the query, go to the command menu and select tools>options>edit/find tab>uncheck confirm action queries.

Regarding the query, you should be using an append (not update) query if you are adding new records. When (what event procedure) are you running the query? If still a problem, please post the Sql view of your query.
Aug 3 '07 #2
MitchR
65 64KB
Can you post the SQL view of your Query ?
Aug 3 '07 #3
Here it is:

UPDATE tblInstDetails INNER JOIN tblSupplierDetails ON tblInstDetails.UniID = tblSupplierDetails.UniID SET tblInstDetails.MerchExistingClient = Yes
WHERE ((([tblSupplierDetails]![MerchandiseCurrentSupplier])="Success"));

Don't know if that's any help, might as well be Greek to me!
Aug 6 '07 #4
puppydogbuddy
1,923 Expert 1GB
Here it is:

UPDATE tblInstDetails INNER JOIN tblSupplierDetails ON tblInstDetails.UniID = tblSupplierDetails.UniID SET tblInstDetails.MerchExistingClient = Yes
WHERE ((([tblSupplierDetails]![MerchandiseCurrentSupplier])="Success"));

Don't know if that's any help, might as well be Greek to me!

Try this:
UPDATE tblInstDetails INNER JOIN tblSupplierDetails ON tblInstDetails.UniID = tblSupplierDetails.UniID SET tblInstDetails.MerchExistingClient = True WHERE ((([tblSupplierDetails]![MerchandiseCurrentSupplier])='Success'));
-----------------------------------------------
Also, were you able to change the setup to uncheck "confirm action queries" like I previously discussed and is that working for you?

Let me know.
Aug 6 '07 #5
Nah, it's still not working! I'm sure the syntax of the query is correct but for some reason the database is springing back to its old shape as soon as it's applied. I just don't understand it! The DB is stored on a shared network drive but I don't see how that can be affecing things - it's not set to read-ony or anything. I'll have a crack at setting that option to suppress the confirmation box when (or if!) I get this query working!

Cheers!
Aug 7 '07 #6
puppydogbuddy
1,923 Expert 1GB
Nah, it's still not working! I'm sure the syntax of the query is correct but for some reason the database is springing back to its old shape as soon as it's applied. I just don't understand it! The DB is stored on a shared network drive but I don't see how that can be affecing things - it's not set to read-ony or anything. I'll have a crack at setting that option to suppress the confirmation box when (or if!) I get this query working!

Cheers!
Your syntax is correct, but the query is not finding any data that meets the query criteria. Convert your query back to a select query and see if you get any output from it. If not, recheck your criteria against your data.
Aug 7 '07 #7
Hmm... I think it's actually worked but the problem is that although Access is still saying "You are about to update 90 rows...." I think what it actually means is that "Your query has found 90 rows that fall under its jurisdiction...", even though it's already updated them. Odd...
Aug 7 '07 #8
puppydogbuddy
1,923 Expert 1GB
Hmm... I think it's actually worked but the problem is that although Access is still saying "You are about to update 90 rows...." I think what it actually means is that "Your query has found 90 rows that fall under its jurisdiction...", even though it's already updated them. Odd...
No, it is about to update 90 records and is confirming that you want to go forward. You won't get that message if you uncheck the box for confirmation of action queries that I mentioned to you previously.
Aug 7 '07 #9

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

Similar topics

4
by: Surendra | last post by:
I have this query that I need to use in an Update statement to populate a field in the table by the value of Sq ---------------------------------------------------------------------------- Inline...
3
by: Falco Vermeer | last post by:
Hi, I tried to use the following query to update a value in one table with a value from another table: UPDATE tbl1 SET col1 = tbl2.col2 FROM tbl1, tbl2 WHERE tbl1. = tbl2.
2
by: Mike Leahy | last post by:
Hello all, This question is related to updating tables - is there any way to calculate or update the values in a column in a table to the values in a field produced by a query result? An...
3
by: Jeff Wilson | last post by:
Seeing an odd situation I've not seen before. Have VB that updates a field in a table based on a form value when a button is clicked. Directly after the .Update, I call another function that...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
7
by: Mark Carlyle via AccessMonster.com | last post by:
I have this update query that I am trying to run. I know the syntax is messed up but do not know how to correct it. Select 'UPDATE', Transactions,'Set = where = ' From "Get Daily Balances" ...
4
by: Andy_Khosravi | last post by:
I'm trying to build a search utility for users to find 'inquiries' in my database that involves several tables. This is normally easy to do with the query builder, but I have a unique situation...
3
by: emily.a.day | last post by:
I have a book database, and I have set up a query to search by subject. As it is now, the searcher has to know the subject and type it into the query. I wonder if there is a way to have a combo...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.