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.
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..
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!
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
Humm, been going over this and can't figure where I am going wrong. Linked SQL server table. SQL permissions are correct (cause every thing works via QA using pass through security). Form with a CBO to select Item1, once selected list box populates with Items tied to it. Select item from list box click button to break the tie between the two. 3 tables, Item1 Table, Item2 table and cross reference table that ties them together. The Break Tie basically (should) remove the entry from the cross ref. table. Form allows me to insert a new tie (same premis as break only allows you to select items not tied currently) and will insert a row into the cross ref. table. But it will not allow me to delete, or as a backup plan I tried to zero out the FK values. Delete says not allowed (bad permissions, table read only, etc.) and Update says must use an updatable query. NOW the queries I have used are basically DELETE FROM CrossRef Where PK = nnnn OR UPDATE CrossRef SET FK1 = 0, FK2 = 0 WHERE PK = nnnn I mean it does not get any easier than that. So what am I missing on this? Why won't it let me delete/update that stupid table?
I 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!
I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?
Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?
I need to create a form that inserts people's details into a table
When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.
What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
Hi, I was wondering why the following code would give me an invalid SQL statement message:
Dim Rs As New ADODB.Recordset Rs.Open "Manzanero # 450", CurrentProject.Connection, adOpenKeyset, adLockBatchOptimistic
The error message is:
"Invalid SQL Statement; expected 'Delete', 'Insert', 'Procedure', 'Select', or 'Update" I'm just trying to open up the table "Manzanero # 450" so that I might add to its contents. I have Microsoft Active X Data Objects 2.6 library included as well. I find it strange since this is basically a line for line copy of a sample I found in a MS Access book. Please help. Thanks! =)
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.
DELETE dbo_MP_Audit.*, dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF FROM dbo_MP_Audit INNER JOIN dbo_CAD_SINV_HEADER ON (dbo_MP_Audit.Usr = dbo_CAD_SINV_HEADER.USR_CODE) AND (dbo_MP_Audit.Sin_DocNum = dbo_CAD_SINV_HEADER.SIN_NUMBER) AND (dbo_MP_Audit.company = dbo_CAD_SINV_HEADER.CMP_CODE) WHERE (((dbo_CAD_SINV_HEADER.SIN_CANCELLED_YNF)=1));
but receiving error message: "could not delete from specified tables".
I have a table that has records listed more than one time. Some of these multiple records have in the score field a zero for the score. I would like to remove all records that are in the table more than once that have a zero for the score in the score field. Any help is greatly appreciated!
i have set up a delete query to delete items ticked on a query. the form related to the query only shows the items with a "False" value in the criteria. anything set as "true" ( ticked ) is reserved for deleting. the delete query looks at the query and selects all items set to true. it works ok but i dont know how to get this work automatically without going trough the access prompts of deleting.
can this be done with code behind a button whereas if clicked, it runs the query and deletes all of the true items?
I have created a query that append all the records to another table3 that are older than two years. But at the same time delete from the original table1.
However it will be moving several records at a time any ideas?
Secondly Table1 has a 1toMany Relationship with the table2, how can i move the records from table2 to table 4 at the same time. Will the data keep its relationship?
How can I automatically bypass the messages "You are about to run a delete query that will modify data in your table."? I don't want an end user to see that message, I just want the delete query to run. Is there a way to get rid of that message without supressing all the messages in the entire database?
Dim db As DAO.Database Set db = currentdb db.execute delete master.* FROM master WHERE workitem=" " and corpid=" "
Please tell me how should i write this code( whcih should execute when a form is loaded)... i want to delete all records where workitem is blank and corpid is blank.
I'm trying to delete records from Table1 where the field "Name" is equal to the field "Name" in Table2. Please see below what I tried and is not working, any ideas?
DELETE table1.* FROM table1 INNER JOIN table2 ON table1.Name = table2.Name;
I'm trying to delete some records out of a table. I have a query that acts as a filter and is linked to two fields of the table that I want to delete from. I have put the * field from the table in the query and set the "Unique Values" property to "Yes". Access tells me "You cannot delete from the specified table".
The filter query is not an aggregate one, and if I remove it and simply have the table I want to delete from (i.e. delete all records) it will let me. I have done this loads of times before but this time it won't budge.
I have two tables. tbl_Job_Master w/ Job_No, and other fields tbl_Job_Desc w/Job_No, Status and other fields
The Select query seems to work fine
I have tried. Right Join, Left Join, Inner Join
All with no luck. If I go to datasheet view I see records returned but if I try to run it 'Could not delete from specified tables'.
DELETE tbl_Job_Master.*, tbl_Job_Desc.Status FROM tbl_Job_Master RIGHT JOIN tbl_Job_Desc ON tbl_Job_Master.Job_No = tbl_Job_Desc.Job_No WHERE (((tbl_Job_Desc.Status)=0));
I could realy use help on this. If anyone has any idea I would appreciate it.
