Hi,
I hope someone can help.
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.
Any suggestions??
After running an append query, the form created displays a blank/new record. How can I make the form display and ready to edit the appended record.
View 3 Replies View RelatedDisplaying the 'Last Modified' dates and time on a form. How to go about doing what I want to do.
I would like to display on a form in one single field the date & time that any of the fields on that form were changed and. I would like to display it in such a way that it cannot be altered by anyone. If it is possible to capture and display on the form in a separate field which user account accessed and changed the record then all the better. Obviously if nothing is updated then the date and time stays the same.
I will have to create two new fields in the table that relates to the form and this I have done - one of format Date/Time and one of format Text.
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB
bob 19800201
Sam 19761211
Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
Hi All,
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?
With thanks in advance.
Ronnie
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?
View 9 Replies View RelatedHi All,
Is there an easy way to show that a record has been deleted (besides "audit trail")?
for example:
if record_id 2 was deleted, the fields can show "deleted"
thx
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?
Thanks,
-Eric
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.
I have a peculiar problem.
The sequence of events is:
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.
Any ideas very welcome
Hello,
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")
RemoveManufacturer.Edit
RemoveManufacturer("Manufacturer").Value = ManufacturerList
RemoveManufacturer("DownloadPage").Value = URLBox
RemoveManufacturer.Delete
Me.cbo_Manufacturers = Me.cbo_Manufacturers.ItemData(0)
Me.cbo_Manufacturers.Requery
Me.URL.Requery
Me.Recalc
Me.cbo_Manufacturers.Value = ""
Me.URL = ""
Any help would be appreciated.
Thanks
I have a standard Select query up with about 20 fields and 1 custom field:
FullName: [LastName] & ", " & [FirstName]
If I try to Sort this or any field by Ascending or Descending, i get an error : Record Has been Deleted. What is causing this???
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?
I was wondering if deleted records to be copied to a new table?
View 2 Replies View RelatedWe 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.
View 12 Replies View RelatedFor anyone that might be able to help me out, I'd very much appreciate it, as this is now number 2 stupid workaround that I'd like to resolve before I need counselling...;)
I have a Date/Time field that I'm importing into my database via .csv files. The field is setup like the example below in every .csv file:
4/2/2007 8:30:00 AM
Access keeps throwing errors and deleting all the field values in this field whenever I try to import. I know that Access does this when a Date/Time field includes data that is not delimited, but these field values seem to be perfectly formatted to me... what can I do to stop Access from chucking these on import?
Right now I'm just importing the data into a "text" field, and then changing it to "date/time" afterwards, but I'm worried that once the table gets too long I won't be able to re-index all those records anymore and my database will be useless since it depends on that field being "date/time" format...
All those better than I feel free to show me how and where I've been stupid:D
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:
After Update DoCmd.OpenQuery Me!OMSQueries
Thanks in advance
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.
While executing this query, I get this error code:
Record is deleted.
UPDATE [MDL-10] SET [MDL-10].[File Path] = "Download#\192.168.4.40h driveNTPC SIPATDMSSDocuments" & [ProjectNo] & "" & [Client Drg No] & ".pdf#"
WHERE ((([MDL-10].[REV 00 SUBMISSION]) Is Not Null));
I tried all but could not find any reason. Please help
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?
View 3 Replies View RelatedI 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 have attached the database ...
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?
Three tables:
Employee, Sessions, EmployeeSessions.
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 appended multiple tables to one table by using the copy, paste, append data to existing table. I now have 1,691 line items in one table. However, when I run a report off this new table and export it into Excel I only receive 1,300 line items.
View 3 Replies View RelatedHello! My Small access aplication has a tables appended from dbf database program. I am coping with an issue to set access to this tables as read only.For me its very vital that source dbf application won't be changed from MS Access.Thanks for help!
View 3 Replies View RelatedHi,
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
Can anyone please help ?