Modules & VBA :: Delete Records From A Table Using Controls On A Subform
Jun 20, 2013
I have a sub form that allows users to add staff to a project team, once added it populates a table which updates the subform showing the selected employee. I am trying to enable a delete function that allows users to remove an employee from the project team in the subform showing selected staff. Here is the code I have so far, but it doesn't work;
Code:
Private Sub Command4_Click()
Dim dbs As Database
Dim rs As Recordset
Dim sqlstr As String
Set dbs = CurrentDb
sqlstr = "DELETE tbl_CapexStaff.* FROM tbl_CapexStaff WHERE CAP_ID = Forms!frm_Switchboard.CAP_Live"
dbs.Execute (sqlstr)
End Sub
I have a subform which lists a set of records. In the footer section of the subform, I have a number of controls which display calculations based on the records present.
The number of records in the subform will change (reduce). Basically, this is a review / approval function, the idea being that each record will be checked and approved, which will then remove it from the list.
So eventually, all of the records in the subform should disappear (the subform is requeried each time the user approves a record)
I want to be able to hide the controls in the footer section once the subform has been cleared of all records.
But not sure
a) how to determine when the subform's recordset reaches zero and
b) what event to use to fire the code to hide the footer controls.
This is my basic attempt :
Code: Private Sub Form_Current()Dim ctl As Control For Each ctl In Me.FormFooter.Controls ctl.Visible = Not (Me.Recordset.EOF) Next ctl End Sub
But it doesn't work (it only seems to fire when the subform is loaded, not when it is requeried after each approval?)
I've tried Form_Query, Form_DataChange and Form_DataSetChange but no joy with those either.
Dim dbs As DAO.Database, sql As String, rCount As Integer Set dbs = CurrentDb sql = "DELETE * dbo_InvPrice Inner Join (dbo_InvPrice Inner Join UpdatedPricing on dbo_InvPrice.StockCode = UpdatedPricing.StockCode ) ON on dbo_INvPrice.PriceCode = UpdatedPricing.PriceCode " dbs.Execute sql, dbFailOnError
I have a very simple 2010 Access database with only one table which contains a few fields (username, email, phone....)
I have a combobox with unbound controls to search/find users (by the lastname field). The combobox is in the header section which populates the fields (controls?) in subform below.
I have a delete button with some VBA code which allows the user to delete the currently displayed record however when the form refreshes, I end up with two issues:
1: I see #deleted# in the combobox dropdown until the dbase is closed/reopened (it's only typically used by one person at a time.) Compact/Repair doesn't seem necessary, especially since i think it's pretty difficult to do this programatically (?)
2: There are blank rows in the combobox dropdown from where the data used to reside after deleting the record.
I haven't been able to figure out how to remove those blank rows and refresh the combobox to display the remaining records (with the blank rows removed). So I have dozens of blank rows. Me.Requery doesn't seem to work.
My assumption is that the blank rows exist because I'm deleting the content from the combobox's initial creation?
Is there a better way to allow user's to see the list of user's and then be able to select that record and delete it?
If I use a simple search box on a form where people have to type a (last name for example), if it's spelled incorrectly, then nothing would be found.
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
I have a form with 3 tabs...each tab has the same subform embedded in it.
There are 3 sequential records in the database, and my desire is to have each record display on its' proper tab.
How can I set filter criteria for each subform to append the record sequence identifier to the link master/child fields to ensure that only the appropriate record displays on each tab?
Basically, I trying to use tab controls with embedded subforms to display the many side of a one to many relationship. The parent form contains the data for the parentrecord, and I want the subform, with a tab for each related record, to show the child side of the data relationship. One child record per tab, utilizing the same identical sub-form for each.
I am working with a database that deals with trailers. What is happening is a salesman takes an order for a make and model for a trailer for a customer. The customer can then add some customization to the trailer such as more tail lights or tie down straps. They add the customization on a form called frmCustomQuote. On frmCustomQuote there is a subform called sfQuoteConfigs. On sfQuoteConfigs there is fields such as category and sub catergory that get populated bases on what was selected.
On frmCustomQuote there is a button called "Copy Quote" this will allow the salesman to make an exact copy of the trailer and customization. The quotes are held in a table called tblQuotes and the customization is held in tbQuoteConfigs. Now my problem is when I click on Copy Quote it only copies the first record into tbQuoteConfigs. I can't figure out a way to tell my code to move to the next record within sfQuoteConfigs.
My code:
Code: Option Compare Database Option Explicit 'Setup ADODB connection to the tblQuotes Dim adoQuotesCustomQuote As New ADODB.Recordset 'setup ADODB connection to the tbQuoteConfigs Dim adoQuoteConfigsCustom As New ADODB.Recordset 'Dim the Variables
I have placed an unbound subform on a form. I have the following code which loads in different datasheet to the subform:
'Loads in NewQuery Me.data.SourceObject = "query.NewQuery"
'Loads in NewQuery2 Me.data.SourceObject = "query.NewQuery2"
etc.... this works fine to show these queries but i want to know how to reference the fields on the datasheet.
for example one of the fields in NewQuery is "ID"... When clicked i want to run code... normally on bound subform i could go for example ID_Click()... msgbox(me.ID)
how can i reference these other fields on unbound???
I have 2 sub-forms attached to a main form. Each of these forms are based on different tables. Whenever I input the record, it goes into respective tables. However when I try to delete the record, it only deletes the record from the main form. I tried cascade delete. However, since referential integrity has to be established, I cannot add the record to my subform because there has to be a corresponding record in the parent table for me to add the record in the subform.
I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.
What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.
For school I have to make a application in access how to delete a selected record in the table in a subform by using a button. The subform is in the main form and the button is also in the main form
If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this
given the following with one record:
Private Sub Form_Delete(Cancel As Integer) DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)" Me.Refresh End Sub
PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.
Private Sub Command58_Click() 'check existing selected record If Not (Me.PayrollsearchQuery.Form.Recordset.EOF And Me.PayrollsearchQuery.Form.Recordset.BOF) Then 'confirm delete
I have a bound subform that lists all of the relationships from my unbound txtJobNum control and I can add and delete lines from the table directly on the subform. What I really need is to NOT update the table unless I click on my save command button.
I have a table which lists amendments (amendments history file) which need to take effect of another table (M0070), I can select the records fine and so the sub query looks to be okay but I'm missing something (probably obvious) in the deletion query where by it does only delete the records selected but everything (I know that the asterisk is wrong in the code below but I'm not sure what to replace it with).
At this point I'm trying to delete all records for a specific employee on a specific contract (they can be 1 or more employee records for that individual on a contract - and they could exist on multiple contracts).
DELETE * FROM M0070 WHERE EXISTS (SELECT M0070.[Contract No], M0070.[Contract Name], M0070.[Employee No], M0070.Forename, M0070.Surname, M0070.Branch, M0070.[Long Desc], M0070.[Emp Post Start Date], M0070.[Days per week],
I am trying to create a command button that will delete the current records in both the subform and main form.
I know how to create a command button that will delete the current record of which ever form I'm in (either the sub or main form) but I wish to do so both at the same time with one event procedure. I currently have been experimenting with a button in the subform but I don't really care if it's in the subform or main form.
I am having difficulty deleting records in a linked DBF (standalone) table. The table links fine. I can run a delete query and the records appear to be deleted when I view the table from within Access. However, when I view the table outside of Access, the records that I thought were deleted are still there. The only way I can actually delete the records, is to import the table, delete the records and then export the table as a new DBF.
Can someone tell me why deleting from the linked table isn't working?
OK guys, ive done some searching and saw some stuff on this, but everytime I try something I get a INVALID SQL error message. So Im hoping you guys can help out cause Im lost right now.
I have a 2 tables, Training and Schedule. Training contains all the employees training records. Schedule is meant to be a temp table where the supervisors can enter the training and then once completed, can check the COMPLETED checkbox and hit the LOAD TO TRAINING RECORD command button and it moves ONLY the files for that trainee (a combo box) with a completed checkmark to the training table.
Ive tried this code sooooooooo many ways and its giving me a headache. My original way didnt work at all (had a lot of Do..Loop and With Statements, and then someone posted something about Archiving which is the code Ive gone off of now). Any help you guys can off would be awesome!
Dim DB As Database Dim WS As Workspace 'Current workspace (for transaction). Dim strSql As String 'Sql Code. Dim strMsg As String 'MsgBox message. Dim bInTrans As Boolean 'Flag that transaction is active.
Set DB = CurrentDb() Set WS = DBEngine(0) WS.BeginTrans bInTrans = True Set DB = WS(0)
'Get user confirmation to commit the change. strMsg = "Upload " & DB.RecordsAffected & " record(s) from " & Me.TraineeCombo & "?" If MsgBox(strMsg, vbOKCancel + vbQuestion, "Confirm") = vbOK Then WS.CommitTrans bInTrans = False End If
Exit_UploadHistory: 'Clean up On Error Resume Next Set DB = Nothing If bInTrans Then 'Rollback if the transaction is active. WS.Rollback End If Set WS = Nothing Exit Sub
Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
I have two tables that have duplicate order numbers. I would like to compare the first table with the second and then delete the records from the second that match.Can I set this up in a macro and run it every day?
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------ ' Master_tbl_sub_fm ' '------------------------------------------------------------ Function Master_tbl_sub_fm() On Error GoTo Master_tbl_sub_fm_Err With CodeContextObject On Error Resume Next
