I created a join on the two tables just as required, on one of several fields that sometimes duplicates.
I have two criteria from the Distinct Records query that I joined the delete query with, which are a <>MaxOfOrgDate OR <>MaxOfHistID (the primary key).
The reason for that is I know that some of the OrgDates are duplicates, so if they are the same I just want to delete the lower HistID.
When I preview the query it all works perfectly and displays exactly what I want to delete, but when I try to run the query it says 'could not delete from specified tables.' The table is not read only so I don't know what the problem is.
Note: On this particular table, if one field is a duplicate, they all are, execpt the HistId field. This is because I append data into the table from another and then I want to delete duplicates, leaving only the data that has changed since I last appended.
I am creating a form that includes an add and delete button on the form. I have two other databases that these buttons work with. Using the same VB code (and name of the button) in my current database gives me the following error:
"The express On Click you entered as the event property setting produced the following error: A problem occurred while Microsoft Office Access was communicating with the OLE server or ActiveX Control.
*The express may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
*There may have been an error evaluating the function, event, or macro."
My VB code is as follows for the two buttons:
Option Compare Database Private Sub add_Click() On Error GoTo Err_add_Click
I'm trying to delete data (no archiving required) from 5 tables that all have a one-to-many relationship between them and keep getting the following error: "Could not delete from specified tables".
I've tried everything I can think of including the following:
* Set unique records property to 'Yes' * Enable referential integrity and cascade delete records in the relationship diagram * Checked the DB isn't read only * Have correct permissions to delete records
I'm the only person in the database but I'm at a loss and short of me actually deleting the 23891 records manually I can't think of what else to do!
i keep getting an error "could not delete from specified tables"
I have a table with this data
Main table bub|12345|1 bub|45678|2 bub|91011|3
Child Table bub|45678|2
result in Main table
bub|12345|1 bub|91011|3
match from child table and delete from main table.. but i keep getting error "Could not delete from specified table main table has unique records. and it is primary key..
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 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 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.
Im new to asp and access and have been having this problem for serveral weeks.
Every couple of days, all the asp pages on my site that communicate with the database start having 500 internal errors. i turned off the "Show friendly error messages" and one page gave me this specific error:
Microsoft JET Database Engine error '80004005'
Unspecified error
/admin/submitlogin.asp, line 8
I have tried a million things and have no idea why this is happening. Im not sure what other information i should post in order to see the problem. Any help would be greatly appreciated. Thank you,
Private Sub NextApplication_Click() On Error GoTo Err_NextApplication_Click DoCmd.GoToRecord , , acNext
Exit_NextApplication_Click: Exit Sub
Err_NextApplication_Click: If Err.Number = 2105 Then MsgBox "Cannot navigate to the next record. This is the last record." Else MsgBox Err.Description End If
Resume Exit_NextApplication_Click
End Sub
but even when this error occurs nothing is being properly handled the way i specified - any ideas ?
Does anybody know what this error message refers to?
"Reserved Error (-1517); there is no message for this error."
It just started happening today, and I haven't even made any changes to the database. It occurs when I hit a button I have to run a macro.
the macro does the following: 1) Shows all records 2) Requery 3) ApplyFilter. The Where Condition for the filter is: Right([tblContracts].[JobNum],4)=Right([Forms]![FrmContProc].[txtFindJobNum],4)
The weird thing is that it only occurs if the Form window is taller than 1/2 of my viewable area. If the Form window is 1/2 the viewable area or shorter, it works OK. This was running fine earlier today, but about 4:00 pm (03/31/06) this started happening.
If anybody knows what this error means, or how to get rid of it (I really need to use this window in full-screen) then please let me know.
Okay, I'm kind of stumped here.I have a subform that has a button that sends a user to a "sibling" subform on another tab page, pass some information to ensure they are adding more details to the same records rather than creating two separate record.First time I programmed it, I got an error 3022 (keys cannot have duplicate values). I checked the query of the sibling subform and saw that the ID is from the one side table. I changed it so many table's foreign key is used. Second try, I got an error 3341 (there isn't a matching key in one side table).After some thinking, it also occured to me that I had set the query this way to allow addition of new record which wouldn't be possible if I had the query pulling the many side key, not the one side key.How do I get the subform to accept the ID that is being passed and create a new record using that ID?:confused:
Private Sub Consolidate_Click() Dim temp As Variant Excel.Application.Visible = True temp = Dir(CurrentProject.Path & "Inputs") Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
Is it enough for error handling to code the on error property for each form? With enough I mean error handling which lets you resume the program.
Ontherwise I have to code (or call a procedure) for each coded event which i wouldn't prefer
For instance now I'm putting error handling in each event but would consider it more efficient if it can be placed once in each form Private Sub cmdReport_Click() On Error GoTo Err_cmdReport_Click
Not sure how to work in the '* ROLL *' into this SQL statement. The query statement works fine ... I have tried different quotation methods ( Not Like " & " ' * ROLL * & ' " & " ) AND .... )
sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([DT: InventoryExtend].Description) Not Like '* ROLL *') AND (([forms]![signpro sign estima parameters]![combo14])=1));"
hi guys i was wondering if you can help me this is my code: i have a main form with this code, this form contains a subform linked by the All_PricingID
Set rst = CurrentDb.OpenRecordset("tblAll_Pricing") 'main table ' adding data to the table rst.AddNew ' Main table rst!All_PricingID = Me.txtPricingID 'Main table pk rst!MainContract_ID = Me.cmbMainContract 'combo box in parent form rst!ItemNumber = Me.txtItem 'Main form text rst.Update
'sub Table Set rst2 = CurrentDb.OpenRecordset("tblPricing") 'sub table For varItem = 0 To Me.lstsubContracts.ListCount - 1 'this is a list in the main form '--- loop through all the items in the list box and create a new row in the subform for each subcontract in the listbox lstSubcontracts. rst2.AddNew rst2!ID = Me.All_PricingID 'sub table foreign key rst2!SubContractID = Me.lstsubContracts.Column(0, varItem) 'sub table rst2.Update Next varItem '--- close the tables rst.Close rst2.Close Set rst = Nothing Set rst2 = Nothing
the subform appears correctly with the rows i wanted added but i need the user to be able to edit a column in the subform for the rows just created (my form is on datasheet view). but everytime i move to cursor into the subform, i can't even scroll up and down.
i keep getting an error that says :
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)
but when i check my tables tblAll_Pricing and tblPricing , everything is inserted correctly according to my recorset above, do you know why this is happening? and why i am not able to edit my subform. my subform allowsedits and additions.
I have an issue where I need all but 2 users out of my DB on Wednesday morning for billing reasons.
I thought of writting a batch file that would delete the frontend DB from all computers from 8:00 - 10:00 every wed morning. However if a user comes in before 8:00 and have the DB open then it will not delete the front end from their desktop.
Can anyone think of a way to keep users out of a system from 8-10am every Wed?
have searched forum for this but not getting what i want (or maybe i just don't understand very well) but here's my question anyway:
the table is related to several other tables and the cascade delete property is on.
on a master form (with subfrm for related tbls) i want cmdbutton to do this:
when the user DELETE a record, the deleted record (and! it's subdata) must be removed from the current table(s) but copied(?) to another table(s) which won't be accessed by the user, so that i can keep track of deleted records.
do i have to setup DUPLICATE tables (and relate them again) to capture deleted records ? and then have a qry that will cut(?) paste records that gets deleted?
i think i need only one qry that will do all this, but not sure how ?
I have a table with several fields and I am trying to delete one of the fields. It tells me the field in related to one or more tables and to use Database Relationships to delelet the relation. I have taken every table out of the Relation Database, Deleted every query, form that ever related to the table and still can't delete the field from the table. I have done Database repair, delete the entire table and rebuilt it and I still have the problem. Any help out there? Thanks.
ok ive made myself a DELETE query, that should Delete all records in a table that are 'TRUE'.... however i cant add it to a command button in my form...it wont appear in my 'command button wizard' as part of my queries, its not there...
I have two tables. table A and table B. Both tables have a field called CASE. What I want to do is delete from table A the CASE where table A and table B CASE is the same. example:
Table A Table B Case Case 1 2 2 4 3 4 5 I want the results of the query to delete CASE 2 & 4 from Table A
I have a field that if another field matches the criteria then a user must fill in that field. If that makes sense!!
I have some code that if a user exits without entering any information on it asks them if they want to and if not undoes what the user has entered in the form. This works fine however a user can enter something into the field exit the field then go back in and delete it. When the user then goes to exit it doesnt flag up as it thinks it has been updated. Can anyone help with this.
My Code so Far:
If Me.No2Release = "Cayman Certificate of Release to Service" And Me.Eng2_Approval = "" Then If MsgBox("You haven't entered an approval number. Do you want to?", vbQuestion + vbYesNo) = vbYes Then Me.AC_Ser_No.SetFocus Me.Eng2_Approval.SetFocus Else 'user clicked no DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70 Me.Base.SetFocus End If End If
hello, can anyone point me to a thread that deals with vba code to delete. i am trying to delete a file path in my table and at the same time it would delete a physical file in a directory that corresponds with the deleted file path.
