why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!
View RepliesI am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!
View 1 Replies View RelatedDoCmd.SetWarnings False
Dim stDocName As String
stDocName = "Tupdate1"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings Ture
If included the two setwarning statements, the update query won't work but when running without them, the update query worked well.
I am trying to run a query that appends data into a table.. however, the way im setting it up.....lets say i have a table named Accouts, and under accounts there is #1 to 30. When I try to add a new account through a form, and append it to that, it takes the 31 there nwo are after i added one, and adds it to the 30 alraedy there, creating 61 instead of 31. is there anyway i can take into account for duplicates when im creating a query. im new to databasing, just got stuck with this assignment for the summer, and have no idea what im doing :( and i dont have time restart after learning normalization. i wish i did, but i dont. if it works it works, thats the bottom line. thanks everyone.
View 2 Replies View RelatedI have 2 databases which are mirror images of each other. one db is named rent and the other is named renthistory. in the db i have 2 tables, tblcustomer and tblradio. i would like a button on my form to append the current record to the renthistory db and then delete the current record. how can i do this?
Thanks in advance!
Tim
I have developed a form and used code to disable the append messages Access displays the user such as: 'You are about to append data, do you want to continue'.
The form works great on my machine and I don't see the messages. When i use it on another machine the messages appear?? Please help.
Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.
I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.
Here's the code for Query A.
INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] )
SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]))
ORDER BY MasterFileEdit.section;
Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.
I need help writing the DELETE query. Here's what I've got thus far:
DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));
I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.
Thoughts: does the TOP 100 modifier work with Deletes?
Is this query even possible?
Thanks in Advance for your Help.
Matt
I don't know if this is the right place for my post. Since is in regard to queries...
I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.
Thanks in advance for the help
hey guys,
I'm new to this site, I have search a lot in this form regarding Delete and Append query used at the same time and couldn't find answer.
I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.
Thanks in advance for the help
Windows Vista
Access 2007
I have added a button to a form that would delete a record. This works just fine.
I would like to add a confirmation that this is something that i'd like to do.
I have code that loops through a lot of objects, and adds them to a table. Right now I have suppressed the warnings via the DoCmd.Setwarnings = False command, in order to avoid the user seeing the message confirming that they want to make changes to the table.
However, I WOULD like them to see a warning if any of the table additions were unsuccessful for some reason. Is there a way to eliminate the user needing to confirm adding or modifying records, but NOT lose the warnings related to errors with adding these records? Even if it's a separate table that contains those warnings that they could look at afterward?
I've been creating a simple macro which will append data and then delete data from a table.The criteria for the queries is found in a form, and this is used as a paramter.However, when i run this all as a macro...the append and delete queries somehow can't read the criteria in the form. Rather...i have to type in the criteria again...and then it works.I have attached a copy of my DB.
1. Go to frmArchiveStock
2. Select a stock from the combo box (this the criteria for the append and delete queries)
3. Click the button to run macro mcrStockArchive.
How can i get the queries to pick up the value entered in the form to act as a criteria when they run?
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
Hi
Can someone please explain to me what I'm doing wrong.
I'm trying to update my table called Portfolio with information sent to me via spreadsheet. I've been able to import the data from Excel into a table called PortfolioUpdate and the data types are all the same, but when i try to run the append query it keeps coming up with the message below
Switchboard can't append all the records in the append query.
Switchboard set 0 field(s) to Null due to a type conversion failure, and it didn't add 1889 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
I've checked that all the data types are the same and I've also removed/added primary keys from the PortfolioUpdate table to see if that was the problem but to no avail.
Am I even doing this right?
If anyone could help that could be great
Thanks
Trish
I need an append query to also update a yes/no box or a text box with "yes" "no" in the field.
I don't know how to do this in SQL. Please help.
Query 1 is the name of the query I want updated from "yes" to "no"
Something like:
AFTER APPEND (I am not sure how to do the "after append" in sql)
UPDATE Query 1
SET Query 1.DueToday = "No"
Where DueToday = "Yes"
Hello,
I am trying to develope a database that calculates and accrues vacation leave monthly.
I am trying to write a query that will add 2.5 days to each employee every month and I would like to automate this update process but I am not sure how can I get this query to know each beggining of the month and do the update. Does any body have a better understanding or suggestion of going about this issue
Your help is much appreciated
Hi,
I am working on a scheduling database and have got stuck with what I thought would be a simple update or append query (It probably is very simple for non-newbies).
I am trying to create an update query to update the "Cell_ID" field in tbl_ScheduledAssays with the "Cell_ID" field from tbl_Machines.
The tbl_ScheduledAssays stores each assay that needs to be run. The tbl_Machines stores both the "Machine_ID" on which the assays are run and which "Cell_ID" that machine belongs to.
Some assays have a "Machine_ID" assigned up front as they have to be run on a certain machine, however other assays can be run on any machine so are assigned "Machine_ID" 51 which is a blank.
For all records in the tbl_ScheduledAssays table where the machine ID is <>51 I want the Query to check the "Machine_ID" and read from the tbl_Machines which "Cell_ID" that machine is in and then add that "Cell_ID" to the "Cell_ID" in the tbl_ScheduledAssays table.
I hope this makes sense! I have tried to search the forum but everthing I have tried so far has failed. Have also tried to do an append query to no avail.
This stuff can sure be stressful. :)
Anyway!
This is all linked to a form, but it's queries I'm sure I'm having the problem with. I'm hoping someone can help me out here.
I have fields in a subform which I've got an update query running after the focus is lost on a field, and it puts these values in a table I created. I'll call this table A. This is so that I may run another update query on the form when this is done, and have it take the values from this table created and add them to a total in another. I'll call this second table B.
I know this design behind this is flawed because in theory I don't need the 'temporary store' (A), but it's in the design of the thing to show that the values are infact being stored. As I'm sure you can understand, in order to keep doing this each time I open the form, I need to clear A. Preferably on a button press on the same form so that I may have the totals all calculated on B and then clear A right after.
The problem I'm having is that I'm using a delete query on a button press, and instead of just clearing the fields I need it to, it's deleting everything in A. I should mention that in A I have an ID field which should be telling the update/delete query which records to update/delete from in the criteria. This is a problem because it's deleting all the records on the form as I have them linked to A.
Any suggestions?
Thanks.
Could someone please try to give me a step by step method for using an append query to update a table. Gary gave me a ton of help before but im still a little stuck. Thanks in advance. :)
View 1 Replies View RelatedI have an expense table and I want the expenses to be written to a new table every month, quarter, half-year, or year depending on what the user has selected. I want to group the expenses somehow and I want it to show all monthly expenses, then show which ones have +1 month. All those will get put into a append query and added to a new table that stores all the expenses from hence forth.
Alright, I have started to figure it out ( a problem from earlier) but I think I need some help. I want an if statement but I am not familiar with date functions. In an append query I want to look at my original table and add records if it is past a certain date.
For a monthly expense (entry date is 01/01/2006)
If (dateentered) month, +1
Then write the record.
I have two tables: tblModels and tblParts. tblModels has primary key ModelID; tblParts has primary key PartID, and also has a ModelID field that's hooked to tblModels's ModelID with referential integrity (cascade all). I have a button on my form that duplicates the model (creating a new record in tblModels and getting a new ModelID), and when that happens, I'd like for the duplication process to run an Append Query that captures all records in tblParts that matches the FIRST ModelID (the original one), and makes new copies of them in the same table, but then sets the ModelID for each new record to the NEW ModelID (that was created with the button press).
I have done this using TempVars in my Append Query, and everything is working great, except for when all the Parts records get copied and added, they all have the OLD ModelID on them, which essentially duplicates the records in the old Model and leaves the new Model empty of associated records!The Append Query is using the ModelID field to find the records I want, so how do I then get it to write a NEW ModelID to each record after they're added to the table?
EDIT: I thought of maybe doing the old TempTable, Append Query dumps to that, Update Query changes ModelID's, Append Query dumps them back to the original table... but that seems like the long way around.
I am trying to link to a url from a message box with difficulty.
eg the user opens the form and a message box appears asking have the latest datasets been downloaded? If answered yes the form opens, if no links to web address.
has anybody any examples.
Thanks
In access Im working with two tables, this is my setup
tableA.documentnr
tableA.revison
tableB.documentnr
tableB.revision
Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).
Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)
Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.
I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables.
is possible to run an Append Query and update a field at the sametime?
i.e. I would like the MealDate field to be incremented by 1, just not sure how to write the code within the query!!!
This is what I have so far!!! not working
MealDate: ([MealDate](Date()+1))
I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..
View 1 Replies View Related