Modules & VBA :: How To Bypass Before Update Event When Record Is Deleted
Sep 21, 2014
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.
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 have a DB with TblInspector and TblViolation Data. Here is what I am having trouble with. On my form I have a combo box named Inspector and a text box named ARNumber. What I would like to have happen is anytime that I select John Doe from combo box Inspector it pulls his ARNumber from TblInspector and saves his ARNumber in TblViolation Data.
I have fields Inspector and ARNumber in my TblViolation Data. I am pretty sure it is an After Update Event. I just don't know what code to put where. This is what I currently have on my combo box. It pulls the number and displays it in the text box on the form but does not save it to the table.
Private Sub Inspector_AfterUpdate() Me.ARNumber = Me.Inspector.Column(1) End Sub
I am trying to use a Combobox BeforeUpdate Event to achieve a required result.
I have two combo boxes inside a frame. The first is a year number 2011, 2012, 2013, 2014
The Second is the Week No ranging from 1-52, but 1-53 in some cases and this updates accordingly with the year number selected.
When the user has selected Week 53 and then changes the year to a year where only 52 weeks exist I want to Cancel their event.
I have already made a function to determine if 53 weeks exits however when I come to do the Cancel Event, the combobox for the year doesn't change back. It stays on the users new selection.
Below is the code that I am trying to use:
Private Sub cmbWeekNoFromYear_BeforeUpdate(Cancel As Integer) If UpdateWeekNoSelections Then Cancel = True Me.cmbWeekNoFromYear.Undo End If End Sub
'cmbWeekNoFromYear is the name of the combobox which holds the year number 'UpdateWeekNoSelections Simply returns True if I want the users change cancelled for cmbWeekNoFromYear
I have an order form with an order details sub form. Both are bound to their respective tables and function correctly, with the OrderID being inserted automatically into the order details table.
However I am using VBA to update and check stock and have it working when attached to a separate form called process orders. Which event would be the best place to place this code. I do not really want to have the process orders form. Would it be on the master form or the sub form. I thought of placing it in the after insert event on the subform, but there can be many order items per order and it will run before I have finished adding order items. I tried it in the after insert event on the master form, but I think that this will run the code too soon as at this stage the order details will not have been written to that table.
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 am updating a value in an unbound control on an unbound form. When the value in the control is changed I want a sequence of code to execute. Specifically changing the record source of a subform and refreshing it.
The problem I have is that when the value of the unbound text box is control is changed (I am using a button to change the value as a test) the after update event on the text box does not do anything.
I did a simple test using a button to change the value in the text box and in the afterupdate event of the text box asked it to output the value of the textbox to a message box as shown below.
The problem is this does not work, nothing happens. If I tab out of the text box or change the value with the keyboard however the msgbox appears. Just not via a vba change.
Code: Private Sub Prod_ID_AfterUpdate() Dim pid As String pid = Me.Prod_ID MsgBox pid End Sub Private Sub Button_Test_Click() Me.Prod_ID.SetFocus Me.Prod_ID = "TEST" End Sub
I have a simple data entry form on which there are about 30 to 40 data fields to be update by users. One of the field is called "Stage" which is a combo box with 2 entries "Formal Case" and "Informal Case" with no default value whien a form is first opened.
Users will normally create a data entry which updates the table and save.
However if in future users open any one existing record and try to change Stage field from Informal to Formal or vice a versa I would then like this action to
1) update a time stamp concatinated with the stage change value in a text field perhaps
2) save/close the form
3) open a new form with all the existing entries(plus changed status of "Stage Field") with a new record ID and new time stamp.
I have a form with a subform. In the form's On Current event I have the subform's visible property set to false. There is a button that when clicked sets the subform's visible property to true. I want the subform to go to the first control in a new record which is a combo box. the subform is a multiple items form. I have tried the GoToControl in the subforms OnCurrent event, but it is not working. I get an error saying the database cant find a third form.
I've got a subform that im writing code for, once a user enters a value in one of the fields i want to make sure they cant change that field (and two other fields in that same record, though the rest of the fields are fine to edit at will)
I've got a function already written to validate the data in the record and decide whether or not the fields are enabled and disabled, however im having trouble finding an event in which to activate the function
Is there an event that triggers whenever a user selects a different record?
Any chart or list with all the handlers and how they trigger (in relation to each other) and when?
I have a list box that displays the contents of a back-end table that is has a new record added roughly 45 times per hour. I'd like the listbox to refresh automatically when a new record is added and, ultimately, notify the user that the new record is there. Are there any non-form events I can use to accomplish this? Right now, my workaround is to refresh the box once a minute using the form's timer. It works fine, but it's not pretty. Ideally, I could create some sort of event based on the new record being saved in the table.
Private Sub PrimaryDisability_Change() Select Case Me.PrimaryDisability Case "Other (Specify)" PrimaryOther.Visible = True Case Else
[Code] ....
My problem is that when I go to a new record, the fields that I want hidden in the new record unless they meet the criteria are still visible. I have looked all over online and in books to determine the code I use or whatever I need to do to make the field invisible in the new record and only to appear in each record if it meets the criteria.
I have some code for a button on click event to open a form and select the record which corresponds to a value in an unbound text box. The code is:
Private Sub Command25_Click() On Error GoTo Err_Command25_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmOpenPatientRecord"
[Code] ....
This works fine when I put in a 10 digit NHS number but opens a blank record when I enter a four digit or six character/digit PatientNumber. Both patient number and NHS number are text fields in the underlying table.
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
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?
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.
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")
