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

Update query not sticking...

P: 27
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
Share this Question
Share on Google+
8 Replies


puppydogbuddy
Expert 100+
P: 1,923
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
P: 63
Can you post the SQL view of your Query ?
Aug 3 '07 #3

P: 27
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
Expert 100+
P: 1,923
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

P: 27
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
Expert 100+
P: 1,923
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

P: 27
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
Expert 100+
P: 1,923
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

Post your reply

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