I've searched and searched but I cannot find a solution to my problem.
I have a combobox with a list of manufacturers. When you select one, the URL of their website shows up in a text box below.
I have a 'remove' button on the form and when you click it, it removes the manufacturer from the database. This works fine, but '#deleted' gets displayed in the text box. I've tried Me.Refresh, Me.Recalc, and requerying both the form and the field (I'm using Me.requery on the combo box, and it works fine there). I've also tried assigning the text box a blank value, but this doesn't work either.
I have the control source of the text box set to the URL field of the table that serves as the source of the form.
Here is my code:
Dim ManufacturerList As Control
Set ManufacturerList = Forms!frm_EditManufacturerList!cbo_Manufacturers
Dim URLBox As Control
Set URLBox = Forms!frm_EditManufacturerList!URL
Dim networkequipmentdb As DAO.Database
Dim RemoveManufacturer As DAO.Recordset
Set networkequipmentdb = CurrentDb
Set RemoveManufacturer = networkequipmentdb.OpenRecordset("ManufacturerSites")
I have a main form with several linked by ID worksheet sub-forms. I would like to add a on-click function to delete the main form and all related record on each sub-form. I have tried several methods but nothing is working correctly. The cascading delete function is not available as part of the form relationships. How I can accomplish this?
1. Persons (list of persons) 2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
This is my first post here and I imagine there will be many more as I have only just started out using Access to create a database at work and I am sure that I'm breaking many rules along the way.
Anyway, over the weekend, my boss tried to add some information (to the "Memo" field) in a form that I have created from a table, went to close the form and got a message saying something along the lines of "this record cannot be saved, if you close it, information will be deleted.
Now, in the Memo field, is the text "#Deleted" - when I try to overwrite this and come out of the record, it comes up with the message "record is deleted". If I put information in any other field in the record, there is no problem.
Also, in the table, if I try to sort by the Memo field, it comes up with same message, "record is deleted".
Does anyone know what I've done and how I can solve this?
I have a table with a blank record at the begining and populated records thereafter. I used the cmd button wizard to create a delete button for that table. Is there a way to prevent the blank record (record 1) from being deleted by the command button but still allow all other records to be deleted?
Hello, I've got a table that looks something like this: deviceID | name | ... 1 | a 2 | b 3 | c 4 | d 6 | e
where deviceID is an autonumber. Clearly device 5 got deleted, but if I go to "add record" I end up adding record 7. There are still other records in the db which need to link to device5, and I have all the info needed to recreate them, how do I override the autonumber and create a record with a specific deviceID?
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click() Dim CmdDelReturnMsg As String CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
I have an ordering db that utilyzes the typical Form / Subform layout. I would like to be able to delete a record from the Main form and maintain the same postion relative to the other records. For example, if I am scrolling through the records and want to delete record #45, After I delete it I would like to be able to have record #44 visible to the user.
I am currently using (2) SQL statements to delete the records from the main Order table and the Order Details table which are linked without Cascade Delete Related Records being on.
I had set-up a recordset procedure to try and find the bookmark set before deletion but the only way I can get the sub-form to not show a blank form (no controls visible for the record just deleted and the #deleted in controls on the main form) is to requery after the delete, which loses the bookmark. If i place the ReQuery at the end it displays the records properly, (without the blank record) but returns the record to the first record. I have tried turning off any sorting references to OrderBy, etc. but it still returns to the first record.
I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables.
By: Tony Hine (mail@TonyHine.co.UK) Tel: +44 1635 522233 My profile on ecademy (http://www.ecademy.com/user/tonyhine)
I had a problem importing just one table I kept getting the message “Record is deleted” and no records were imported.
First I thought it may be because there were quite a few columns in the table so I tried importing just one column, however I got the same message “Record is deleted“ .
I considered it could be because the database was originally in a Company office workgroup. I wondered if the “Work group security” was causing the problem. I read up on this and found that importing the objects directly into a new database was one of the recommended ways of circumventing this problem. This was what I was already doing! So what was wrong?
Next idea, I exported all of the records into an excel spreadsheet. This was successful! So I then re-imported the records from the spreadsheet back into a table in the database.
There were a couple of problems, in that all of the text fields were converted to 255 character length. Although annoying I thought I could handle this problem. I proceeded making a few changes to this database, then I noticed that one of the fields that had been imported was originally a “Memo field” it had been converted to a text field with 255 characters. As soon as I noticed, I checked to see if any data had been lost by comparing this field with the original data. I found a significant loss of data so back to the drawing board!
All of the other tables had imported correctly, only this one seemed to be giving problems, I had tackled the size problem it was not a big table by MS Access standards, but I felt it could be the size, In particular the number of fields could be causing this error. But just trying to import one field at a time hadn’t worked?
Then it dawned on me what if I tried to import one record at a time --- that worked! So I wrote the query with a between statement I tried 10, then a 100, then a thousand records it worked fine! However when I tried 5000 records the error re-occurred?
I realized that I probably had some corrupt data in the table, so I set about systematically extracting a limited number of records until I found the area where the corruption was.
This worked but returned “0” records --- Between 11001 And 12000
“Between 12001 And 13000” Got error message “Record is deleted”
I got to 12800 all ok
12900 caused error
Error is in a record between 12800 and 12900
I will continue to whittle it down!
OK to 12819
The corrupt record is: 12820
I had found the corrupt record! Now all I had to do was create two queries one each side of this corrupt record to extract the information.
On inspection of this corrupt record I discovered that the memo field contained the following: “#Deleted “ I am going to attempt to modify the memo field and see if it fixes the corruption of the database.
My attempt at modifying the corrupt record, in particular the memo field containing “#Deleted” caused the error message to re-occur. “Record is deleted”
I have made a copy of the actual record 12820 and pasted it into a spreadsheet to send to the customer so that it can be checked in the existing data on their system.
Fortunately there were no related records in other tables affected.
1. The user reports that on clearing (deleting) data in a memo field within a table an error (sometimes) occurs which results in all fields for that record containing the word #deleted. The table can no longer be opened for update via a form. (Prior to deleting the data in the memo field the user had been using copy/paste data from the memo field into other fields within the record)
2. When I open the table in datasheet view, there is a record filled with # signs usually at the beginning of the table. I delete that ( I believe that is all that is left of the original record - but there is no other identifying data).
3. The primary key (autonumber) of that table is reset i.e. it is no longer designated as the primary key. I recreate it in table design.
4. All relationships with that Primary key are deleted.
5. I recreate the relationships, run a compact and repair and all is well except that the original record is lost and has to be recreated as well.
All this because a user deleted some data in a memo field!
The DB is split with the BE on a Windows 2003 Server and the users running their FEs on a Lan. Only Access 2003 is used.
I cant replicate the problem on my development system.
When I copy an entire record and paste it in a new record , I first get a duplicate issue with one of the columns(unique key constraint) and when I attempt to change the column value so as not to be duplicate ...I get a #deleted for the whole record.
This does not happen when I copy the unique column from a record and paste it then change (copying that column alone).
What could be the best explanation for this and how can I attempt this issue?
We developed a simple database that operates across a network at work (max 15 intermittent users). We split it into a front and backend and made it a .accde file format of which through a desktop short cut we all access. We are recently getting the error message Record is Deleted.
I have a macro that appends and deletes records to a new table once the the record has been closed. I need to record the date this was done in my original table and the new table and have had brain freeze as to how to achieve this.
Help!! I have a form with a drop down list that is pulling Query names from a table. When I select on of the Query names on the dropdown list it is running the appropriate Query. At least it did until today. Now when I select the Query name from the dropdown list I am gett a Run Time Error 3167 Record is Deleted.
Can anyone tell me what is happening? I have changed nothing in the code. The code is:
In access (2003) I am setting up a table with Employee # (Pri. Key), senority numbers..and so on. However I cannot figure out how to reset the senority numbers when one leaves. For example Bob is #235 senority and leaves (By retiring, quits or gets fired)..well Mary was #236 and now should be #235 (because Bob is no longer at the company)..and so on with everyone else below Bob getting their Senority number changed by one.> Do you have any ideas..I tried autonumber, but it will not do that.
I have a table that is showing #Deleted in several fields, not all of them. When I select the record and try to delete it I get the message that the record id deleted. How do I remove this record?
I have a database which is slowly evolving. Users needed a feature to delete some records without a trail and some with a trail from the form. So I added an apply action field in the subform using which they can delete a record without a trail and if they wanted to keep a trail they could do that too. When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted.
After six months of use now I am asked to add an audit trail. I managed to do that also. I also looked at Browne's method but my data structure does not match the requirements for that method. I used an alternate method. It works as intended except when a record is completely deleted using the code I mentioned above. Then it goes in the infinite loop. I somehow need to bypass the before update event so that the function to write the audit trail is not called.
I've designed a DB in access which has a BackEnd and 2 FrontEnds (one person insert all the records and the others just keep inserting infos till the process is finished.The DB has 12 tables and we used it for about 6 months without having any trouble but recently (2 weeks ago) i've add 3 new tables and then related them to one table that already exist.
The DB was running smoothly for a week after the changes but last monday (09/15) the "Record is deleted" appeared. I've compacted and repaired an the following errors descriptions appeared:
ErrorCode: -1017 ErrorDescription: Record is deleted. ErrorTable: tblFatura
ErrorCode: -1053 ErrorDescription: Index or primary key cannot contain a Null value. ErrorTable: tblFatura
ErrorCode: -1630 ErrorDescription: You cannot add or change a record because a related record is required in table 'TblExpense'. ErrorTable: tblFatura
I've restored the file via IT using the Backup2 days before the error occurred but after 30 minutes the same error appeared! I dont know if it is related to the new tables that i have add or no?
I am new to access and I recently encountered a double click issue
My form loads perfectly on double click event but it shows the first record instead of selected record.
My search is based on a PersonID but each PersonID has different WorkID that I wish to display on double click but it always shows the first WorkID instead of my selected record
I have tried changing the filters in the form properties but it still doesn't work for me.
Here's my code:
Private Sub SearchResults_DblClick(Cancel As Integer) DoCmd.OpenForm "WorkForm", , , "[PersonID]=" & Me.[Searchresults], , acNormal End Sub
[Searchresults] draws information from my Query
Query information:
PersonID... WorkID... Type......Location 1234..........1............Paint .....Address A 1234..........2............Electric...Address B 1234..........3............Floor..... Address C
Many sessions can have many employees - thus the joining table has been included.
When trying to delete an employee from the database using a form, I encounter the error:
The record cannot be deleted or changed because table 'tblEmployeeSessions' includes related records
Is there a problem with my table relationship structure? Or is it 'correct' that as the employee is supervising a session he/she cannot be deleted as this would interfere and maybe mess up the session record?
I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database.
The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried DoCmd.SetWarnings False If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then DoCmd.RunCommand acCmdDeleteRecord DoCmd.SetWarnings True Else DoCmd.SetWarnings True End If but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?