This is a copy of the SQL statement used to update records from the tblContentImport Table to the tblContent Table.
UPDATE tblContent INNER JOIN tblConentImport ON (tblContent.Code = tblConentImport.Code) AND (tblContent.Type = tblConentImport.Type) AND (tblContent.Title = tblConentImport.Title) AND (tblContent.LoginName = tblConentImport.LoginName) SET tblContent.[Date Assigned] = [tblconentimport].[Date Assigned], tblContent.[Date Started] = [tblconentimport].[Date Started], tblContent.[Last Accessed] = [tblconentimport].[Last Accessed], tblContent.[Date Completed] = [tblconentimport].[Date Completed], tblContent.[Time Spent (min)] = [tblconentimport].[Time Spent (min)], tblContent.Score = [tblconentimport].[Score], tblContent.Result = [tblconentimport].[Result]
WHERE (((tblContent.LoginName)=[tblconentimport].[Loginname]) AND ((tblContent.Title)=[tblconentimport].[Title]) AND ((tblContent.Type)=[tblconentimport].[Type]) AND ((tblContent.Code)=[tblconentimport].[Code]));
Now I want to Delete these same records from the ContentImport Table. I change this statement to start with the word DELETE and Access want me to identify which table to delete the records from. What is wrong with my statement. Please help?
I have a problem with a delete query i am trying to set up and hopefully one of you experts can help me out.
I have a table called [PMI] and a table called [Referrals]
The tables have a one to many relationship and are linked by the primary key field Patient_No.
I want to delete all records in [PMI] that have no records in [Referrals] and this is what i'm having problems with. I have created a select query where i can return the records i want to delete but its the removing of them from PMI that i cannot figure out.
Hope that makes sense, all help is greatly appreciated.
when I try and run the following query it says "please specify the table", and for some reason in the clause I cannot change the expression WHERE to FROM. Any ideas?
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?
:confused: Anyone know how to trap/prevent the msg "The DoMenuItem action was cancelled" after answering no to deleting a record? I followed the instructions at the following thread but no luck:
---------------------------------- Private Sub Delete_Click() On Error GoTo Err_Delete_Click DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 Exit_Delete_Click: Exit Sub Err_Delete_Click: MsgBox Err.Description Resume Exit_Delete_Click End Sub
----------------------------------- Private Sub Form_Delete(Cancel As Integer) DoCmd.SetWarnings False If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbNo Then Cancel = True Else MsgBox "Record deleted!" End If End Sub -------------------------------------
Why does this sql not work? It brings up the records which satisfy the criteria if one clicks on the view button. Yet does not delete the records! No warning signs appear at all
DELETE pupils.*, pupils.[Pupil ID] FROM pupils WHERE (((pupils.[Pupil ID])=[Forms]![Parents]![cmboPupilsDelete])); Any ideas? Thanks
I'm back with Access again for a short time (hurray) where I'm picking up a problem for one of Col's dbs. And I'm really stuck! :(
It's a car parking db and you go into the record from a form with a list box on.
There is a delete button, which has the usual wizard code behind it:
On Error GoTo Err_Delete_Click Dim rsp As Variant
rsp = MsgBox("You will delete the entire record and associated permit and car details. Are you sure you want to do this?", vbQuestion + vbYesNo, "Warning!!!")
Basically all this did was bring up the msgbox, bleep and not do anything else. When I debugged, it appeared to go through the code no problem.
I looked at an older db of mine and changed a few bits:
added docmd.setwarnings false and true before and after code changed the Variant to Integer Added docmd.close and requery of the form that the listbox is on, after the DoCmd. .... ... acVer70.
This closes the form, but doesn't appear to delete or requery, and when you click on the list box, there's now an error message, "No Current Record".
Anyone know what I'm doing wrong please.
Many thanks - Ally I'm going now, but will be back on Monday.
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?
I am trying to append records from sales table to sales archive table if salesdate# is <#01/05/2006 It works fine if the sales archive table is blank else it give me an error null conversion failure.
I like having the action query warning appear with what I am doing - "You are about to update xxx rows". The problem that I am having is that when my RunSQL statement in the code executes and selects no to the warning, I get the error -
Run-time Error '2501' The RunSQL action was canceled.
How can I trap the no selection so that this vba error does not appear?
I'm told (via the non-helpful help system packaged with MsAccess) that the way to 'turn off' the helpful confirmation message related to an action query is under tools>Options>Edit/Find tab, and then uncheck the appropriate boxes.
I've done this. In fact, right now I have all three boxes unchecked which relate to confirmation messages (record changes, document changes, and action queries) - and I'm still having that same popup message every time I run the query, or have a report pull the query for me.
this wouldn't be more than just a hassle, except that one of the end users for this system isn't the most computer-savvy person in the whole world. In fact, I desperately need to "steve-proof" this system! I have this nightmare of being woken up at midnight because of this little glitch. any ideas how to disable that popup confirmation message for good??
I use a Make-Table Action query to import data from a linked table into my database. The linked table is on a network server that is automatically updated.
One column of the linked table is named "QTY/PARTIAL" and approximately 10% of the 500 records have a "P" after a number i.e. 1000 P. I would like to separate the number and the P into separate columns in the new table to faciliate being able to compute the total number "QTY".
Alright. I've tried searching, but to be honest, I'm not even sure what to search for.
I want to create some VBA that onLoad of the opening form of the database, it will take info from a query that looks for data entered for a student. It will be looking to see if there is data in two date fields. If it finds data in both fields, then I want to make a change to the EnrollmentID row of another table to changed the graduated column from No to Yes.
To expand on that, I'll want to check each ClassID row and if all of the associated students of that class have the column Graduated checked as yes, it will place the date in the date column for that particular class.
This is essentially my way of auto-archiving data as the combo box selections on my forms don't allow for class data to be shown if there is a Closed Date entered for the class and you won't be able to enter more test results for a student if the Graduated Column is checked in the Enrollment table (associates memberID, ClassID, CourseID into a single table)
Any insight on how to do this would be greatly apprecaited.
I've created a simple database, with tables, forms etc but I've got stuck when I've tried to insert a 'Button' onto the HOME page which when pressed will display an invoice and give you the option of emailing to client or printing.
I think I've gotten messed up in my relationships or the macros which I've copied from another access template.
on the home screen, I click 'new quote' then 'view quote' when the window pops up and then when it tries to generate the invoice I get the error
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.
I have two fields the have differing dates in them. I need an action query to make the date in one field the same as in the other. I have thousands of records to do this to. Is there a way via an action query (update?) to do this or does it have to be an SQL statement query?
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.
I hope someone is able to help me. I've been working on this for days. I have a report which has a 2 subreports in it. I have the subreport bound and have a query working in the recordsource. Here is the recordsource:
I have one report with the Master/Child fields filled in and set but the sponsors subform I cannot fill in the Child field. It says I need to put SponsorID in the recordsource. I thought I did have this(see above.) That's my first problem.
My bigger problem which I think the previous problem is contributing too is my subreport shows up blank. When I run my query it works perfect. But, when I run it with the rest of the program the subreport shows up blank.
Anyone know how I can go about fixing this?? Thanks!! Nick
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.
