Detect Record Delete In Vba Without Dialog Box
Oct 23, 2006
I've looked all over this forum and others and google, and am amazed that I've found nothing that addresses this issue.
I want to execute some code after a record has been deleted. If I uncheck the "Tools / Options / Confirm Record changes" option (it's extremely disruptive) then the "Before/After Del Confirm" events don't occur. The "On Delete" event occurs before the record is deleted and therefore doesn't help me.
Are the "Before/After Del Confirm" events the only events that occur after record deletion? If so, is there any way to disable the confirmation dialog and still get the events so I can handle it with my code and not disrupt the user every single time a record is deleted?
Thanks...
View Replies
ADVERTISEMENT
Apr 22, 2014
I'm wondering if it's possible to create a Query / Macro / VBA or whatever works, to detect the duplicate records in a table and delete them with the push of a command button. Or, even to do this automatically when the database opens using an AutoExec macro or something ?. I'd like for one copy of each record to be left intact in the table.
View 8 Replies
View Related
Aug 19, 2004
I have a macro setup that deletes all the records in 2 different tables, then using about 8 queries, appends several records to the two tables where data was deleted. When these macros run, several Yes/No/Cancel dialog boxes come up for each query, first asking you if you are sure you want to do this, that it is going to change data in my table, and then second telling me how many records it's going to append or delete.
Is there a way to make it automatically run Yes to all of these dialog boxes? That would really help out a whole lot. Of instead of that, but maybe some sort of VB code that could do all the deleting and appending without the need of the Macros, and that doesn't require user intervention. I have my Macro's setup on the "On Click" event in a form, so using VB would be no problem at all.
Any Suggestions?
View 10 Replies
View Related
Aug 16, 2005
I have a subform that collects the following data to put in a record in tblClass:
StudentID, Trimester, SubcatID, WorkGrade, SkillGrade
Before the record is written for the first time OR edited/updated to the table, I want to search tblClass and determine if the new/updated info will create a duplicate record.
In this specific case, a duplicate record will be defiened by a record where the only fields being considered would be StudentID, Trimester and SubcatID. The fields WorkGrade and SkillGrade should not be considered.
The code I came up with was the following and it was put in the BeforeUpdate:
Dim conn As ADODB.Connection 'Connection Object
Dim rst As ADODB.Recordset 'Recordset Object
Dim strSQL As String 'SQL statement for open statement
' Create object variables
Set conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
' Create sql to search for records in tblClass that match
'studentID, Trimester, and Subcatagory in the form record being added
strSQL = "SELECT * " & _
"FROM tblClass" & _
"WHERE fldStudentID = " & StudentID & " AND " & _
"fldTrimester = '" & Trimester & "' AND " & _
"fldSubcatID = " & SubCatID & ";"
' Open recordset
rst.Open strSQL, conn, adOpenKeyset, adLockOptimistic
If rst.RecordCount >= 1 Then
' record already exists in tblClass
msgbox "Record already exists!",, "Duplicate Record Error"
Me.cboSubcatID.SetFocus
Cancel = True
End If
' Close and disassociate object variables
rst.Close
conn.Close
Set conn = Nothing
Set rst = Nothing
This code worked great except when I went to edit an existing record. When I went to change a grade (WorkGrade or SkillGrade) on an existing record, it told me that I could not enter the record because the record already existed (ie, the record I had open and was editing). I am not sure if it is my code that I need to edit or if it is the placement of the code I need to change.
Any suggestions would be great.
View 4 Replies
View Related
Jan 31, 2008
Hey,
I have a form with 55 values entered. On closing the form, I need it to decide if this is a new record. If it is a new record, it loads all 55 values into an array. If all values are above 20,000, it goes ahead and closes the form. If any are below 20,000, it writes the record to another table and brings up that form to add add a comment.
Every thing does as it should except determining if it is a new record. What property will trigger it properly? All the properties I have tried either triggers it on all the records (even if it is an old record) or it does not trigger it and closes without prompting the code.
View 6 Replies
View Related
Oct 29, 2013
I have ASSET_TYPE, MANUFACTURER and MODEL tables.
I have a Table ASSET_COMBO that links to the above tables.
I have a form to create amend an ASSETS table. The form uses combo fields to filter the options available to the user. I.E the User Picks Manufacturer then selects from list of Asset Types provided by that Manufacturer, then selects from list of Models.
I want to create a form to allow create and amend records on the ASSET_COMBO Table.
The problem I have is that I want to ensure that there are no duplicates on ASSET_COMBO Table. I know that I can Select Multiple keys from the table with no duplicates allowed. But from what I have read this can cause problems. Is there a simple way that I can detect that a query on the ASSET_COMBO Table has returned a valid record or has not found a record.
View 3 Replies
View Related
Apr 1, 2005
I have a problem that is probably very straightforward to you Access experts.
A brief description of the salient parts of my setup are given as numbered paras below.
1... I have 2 tables: Jobs and Sessions in a defined 1 to many relationship.
2... I have defined a form in parent-child format with the child (session) records displayed in single line (continuous) mode.
3... The above works as intended, ie as different jobs are selected (via combobox or bespoke navigation arrows), so the relevant session lines appear. I have removed both record selectors and default navigation buttons from both form and subform.
4... Because all but one (a simple text description) fields are auto or calculated, I do not want the user (Yes ... it's me ... but It could be someone else ... if I drop dead) to attempt to input via the form, since it causes problems for some of the calculated fields. :(
QUESTION A. How can I suppress the record entry part (bottom line) of my session one-liners subform? (The problem doesn't arise with the main job page-mode form, since the removal of the default navigation makes it impossible to add a job via the form).
QUESTION B. What code do I need to create a new session for a defined job via a simple dialog box or bespoke form which requires only two input parameters: jobname and sessiondescription. (All other fields are calculated automatically from today's date and jobname)
5... I currently perform input using the jobs table (ie selecting the relevant job), expanding the (sub)sessions table and then adding a session description ... but this is not so user-friendly!
I would appreciate any help and/or relevant comments.
View 3 Replies
View Related
May 19, 2014
I want to put a print button on my data entry form that prints the current record in a report that is laid out all nice and pretty. I found this code that works, but it goes straight to the printer - it does not bring up the printer dialog so you can select a printer.
Code:
Private Sub RecordPrint_Click()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False
End If
[Code] ....
How do I get it to bring up the printer dialog?
View 7 Replies
View Related
May 19, 2011
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.
View 13 Replies
View Related
May 16, 2015
I am having a problem updating a record on a table called BookInTable . The field I want to update is called Engineer and the record is found using the field Barcode. I have a form with 2 text boxes BarTxt and EngTxt as well as a button called SaveBtn. on the OnClick event of the SaveBtn I put the following code
Code:
Private Sub SaveBtn_Click()
If IsNull(Me![BarTxt]) Or (Me![BarTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![BarTxt].SetFocus
Exit Sub
End If
DoCmd.RunSQL "Update BookInTable SET Engineer = " & Me!EngTxt & " WHERE BarCode ='" & Me![BarTxt] & "'"
End Sub
But this is bringing up the enter Parameter value dialog box for whatever I type into EngTxt. If I enter text into that box and then click okay it then updates to the record. how can I stop the from enter Parameter value dialog box appearing?
View 2 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
Jan 26, 2007
I use Environ function to detect the user name.
But, do we have function to detect the full Employee Name?
View 1 Replies
View Related
Aug 19, 2005
Hello,
In an empty field how to detect that the RETURN key has been pressed, i don't know hot to differentiate the RETURN KEY and the field exit by clicking the mouse in an other field because in the 2 cases the value of field is set to "", is there a way to test VbKey in a field ?
This because i want to set my field to a default value only when i press RETURN and i don't want the LostFocus or Exit set my field to this default value if nothing has been typed in my field.
(I must precise that for some reasons in my form the KeyPreview is set to TRUE.)
Thanks in advance for help.
VINCENT.
View 2 Replies
View Related
Dec 4, 2006
I have a library database that I want to delete a record in my tblStatus when the book is returned. I have found the deleteobject function but it appears that that will delete like a table or query or report or something like that. I have no need to keep track of how many times a certain book has been checked out or that John Doe has checked out books X, Y and Z. As a mater of fact that is exactly what I don't want so I just want to delete the record that says Book ZZZ was checked out by John Doe. When the book is returned I just want it available for some one else.
Thanks in advance,
Rick
View 1 Replies
View Related
Oct 1, 2007
Hello. I have form. and i need to delete record. Id is a unique key. When i have invisible textbox with source of this key, it's very easy to delete record from database. But is it possible to delete record with query without using textbox?
View 2 Replies
View Related
May 31, 2005
Hi all, facing a little problem with my forms. Access gives me headaches everyday. Haha.
In a continuous form, I have a delete button with the following code
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click
DoCmd.SetWarnings False
If MsgBox("Confirm deletion of the record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
Exit_cmdDelete_Click:
DoCmd.SetWarnings True
Exit Sub
Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click
End Sub
However, after the record is deleted, the focus is immediately set to the next field which creates a new record of null data. And since the fields are all required, I am forced to enter data into the new record, if not a pop up will inform to user to enter data.
I do not wish for this new record to be created everytime I delete a row. How do I go about it?
I'm learning more each day! Thanks in advance..
View 9 Replies
View Related
Feb 13, 2006
I have a problem with 1 of my form (FrmEditDrawing) that I use to edit a specific record from 1 of my table (TblDrawing). The user will see the Data for each drawing on a form that I called (FrmViewDrawing) and will be able to edit the data for each drawing by clicking a button that will open the form "FrmEditDrawing" on the specific record. In that case I can't edit and change the data for any fields but if I open the form on its own, I can edit and change the data ?
I guess it has something to do with the fact that I open twice the same recordsets which locks the table, I am not sure. What would be the different option to solve this problem?
View 7 Replies
View Related
Dec 4, 2006
When you want to delete a record out of MS Access, you get the default pop-up message confirming you want to delete the record [YES] [NO].
I have a few users who are not paying attention and hitting a combination of keys (I am guessing Alt + D) and getting this message. They then do not look, and confirm the deletion of the record.
I am thinking of two ways around this.
1) Change the default to [NO] when this pop-up dialog opens up (the default is [YES]).
2) Disable this pop-up box, and then create my own to confirm the deletion.
Are either of these possible, or has anyone ever had to work around this issue? I have never had an issue with this, and was not sure how to attack it.
Let me know.
Thank you,
T.J.
View 7 Replies
View Related
Nov 18, 2004
i have an order form somewhat like the northwind database one. I called mine A5_frmOrders. i have a subform called A6_sfrmOrders, which is in datasheet view. I need to add a "Delete Record" button into the subform, but when i put it in the subform footer, it doesnt appear.
Like any orders subform, i have "Item", "Product", "Quantity", "Unit Price" and "Subtotal" with their corresponding labels in the Detail Section. I tried changnig the subform to "Continuous Form" and doing this :
header : Labels for "Item", "Product", "Quantity", "Unit Price" and "Subtotal"
detail : "Item", "Product", "Quantity", "Unit Price" and "Subtotal" for user to fill in
footer : Delete Record button
but even when i change to Continuous Form, it doesnt allow me to separate "Item" from its label and put one in the header and one in the detail...
how do i add in the delete record button? Is it true i cannot use datasheet view if i want a delete button?
Thanks
View 1 Replies
View Related
Dec 22, 2006
Hi,
I create the tables and groups in SQL Server, I linked all the tables from SQL Server to MS Access database, and I assign the groups to each user in SQL Server.
In MS Access VBA, how can I detect back the user roles (group) from SQL Server?
Can anybody how to detect/check it?
Thanks.
View 1 Replies
View Related
Dec 20, 2014
The code below opens a PDF selected from a form. Whatever document number appears in the textbox (FICNo) on the form is the document that is opened when the command button is clicked.It works but I would like to add a message box to say File not found if the document number in the textbox has no corresponding document in the folder (ScannedFiles).
Code:
Option Explicit
Option Compare Database
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Sub CmdOpenDoc_Click()
ShellExecute 0, "Open", "C:UsersDavid.BallDesktopScannedFiles" & Me.FICNo & ".pdf", vbNullString, "C:", SW_SHOWNORMAL
End Sub
How can I modify the code to do this?
View 2 Replies
View Related
Dec 11, 2005
Why does this code not delete the current record?
DoCmd.RunSQL "DELETE * FROM [Tble-Readings]WHERE
[Tble-Readings].[ID]= " & Me![ID] & ";"
I have this code on the a Close Button and if the record is incomplete I want it to be deleted from the table. The ID field is an autonumber so it is the 1st field to get a value if a user starts entering data. I get a msg box saying 0 fileds will be deleted why? the id's do match but it does not delete??
jon
View 5 Replies
View Related
May 9, 2005
Hi,
Is it possible to delete a record from a table whilst viewing that table through a form. The awkward part though is because I have another table linked to it.
Basically the main table is Orders and the other table is Items (these items make up an order). So when I go to simply click on delete an error message appears saying that I cannot delete the record but there are records attached to it in another table. This is fair enough but is there a smooth way off deleting off the items?
Many thanks,
Paul.
View 3 Replies
View Related
Aug 17, 2006
Hi guys,
Could anyone give me a solution to protect a table and all the records in there so they can never be deleted when being viewed.
Many thanks, Ben
View 3 Replies
View Related
Jun 21, 2007
Hi all, whats the simplest way to delete the last record in a table? based on
DoCmd.RunSQL "DELETE * FROM
Trying to use a button to do the above
Cheers
View 5 Replies
View Related
Feb 15, 2008
i have a table1 in my msaccess and i linked another table in mysql to ms access, (table2).
when table1 matches the record in table2, it will delete the record in table1.
my query is
delete *
FROM Table1
where table1.artid = table2.artid;
but it always ask me the value of tbl2.artid, the value of tbl2.artid is in the database of mysql and it has already records...
how can i compare the records of table1 (msaccess) and table2 (mysql) and delete the duplicate records?
:D
thanks in advance
View 11 Replies
View Related
