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?
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.
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.
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.
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.
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.
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.
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
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.
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?
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
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.)
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.
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?
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 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?
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.
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
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?
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
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??
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?
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
