Modules & VBA :: Combobox To Delete Records From Table?
Sep 6, 2013
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.
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 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
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.
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
I want to Delete all records from the Table Named "CustomerWiseRotaryTillerRates" (Given Below) if the State in the State Filed in found in the "Customer_Master" Table (attached Herewith). What will be the correct procedure ?
I followed the steps referred in page no. 67 & 68 in the Book "Microsoft Access 2007 Data Analysis" by Mr. Michel Alexender.
Customer Name State Product_Code Product_Name Price_perunit
TSI Engineering Indutries (P) Ltd. Assam P_05 1500_Rotary_Tiller_220_RPM Rs. 63,722.00
Kishan Krishi Engineering Works Jharkhand P_05 1500_Rotary_Tiller_220_RPM Rs. 62,997.00
TSI Engineering Indutries (P) Ltd. Assam P_06 1750_Rotary_Tiller_220_RPM Rs. 66,663.00
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 am trying to create a delete query that, for a given person, deletes records in Table B that do not have a corresponding record in Table A.
Here are the relevant tables:
tblStates holds StateID, StateName, and RegionID (RegionID is a FK to tblRegions). tblPeopleStates is a junction table between tblPeople and tblStates.
It lists states assigned to people. It has 3 fields: PersonStateID, PersonID, StateID.
tblPeopleRegions is a junction table between tblPeople and tblRegions.
It lists regions assigned to people. It has 3 fields: PersonRegionID, PersonID, RegionID.
For a given PersonID, I need to delete records (i.e., states) in tblPeopleStates whose RegionID is *not* in tblPeopleRegions.
For example, pretend that tblStates shows that State IDs 1, 5, and 6 are all in Region ID (i.e., all have a RegionID = 10).
If Joe (PersonID = 200) has StateIDs 1, 5, and 6 in tblPeopleStates, but doesn't have a record for RegionID = 10 in tblPeopleRegions, I need to delete his three records in tblPeopleStates (i.e., the ones where StateID = 1, 5, and 6).
PersonID will be found on [Forms]![frmMain]![subform1].[Form]![subform2].Form]![PersonID]
I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
I have a form to add, edit, and delete Records from a table. I am using the following VBA
Private Sub cmdEdit_Click() If Not (Me.frmlEmpDetailsSub.Form.Recordset.EOF And Me.frmlEmpDetailsSub.Form.Recordset.BOF) Then With Me.frmlEmpDetailsSub.Form.Recordset Me.txtAddEditname = .Fields("Name") Me.cboRoster = .Fields("Roster") Me.cboPermFctn = .Fields("PermFctn") End With End If End Sub
Instead of referring to the Subform to load the data i would like to refer to a combobox:
cboSearchName
Its not an issue but This Combobox contains 5 columns...
I'm trying to run this very very simple code but I get a Run-time error '3450' Syntax error in query. Incomplete query clause. For sure the problem is the variable I used as Table parameter but I cannot understand where my error is :
Private Sub cmdRunCheck_Click() Dim strSQL As String Dim strTempTbl As String strTempTbl = "tblCheckDoubles" strSQL = "DELETE * FROM " & "'" & strTempTbl & "'" CurrentDb.Execute strSQL, dbFailOnError End Sub
What is clear is that if I use this line the Sub is working smoothly
strSQL = "DELETE * FROM tblCheckDoubles"
Please do not tell me that it is not possible to use a variable as Table parameter....
How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code: Private Const strNomeTblFonte as string = _ "SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;" Private Sub Class_Initialize() Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
I have a master client table and a table of clients that have ordered from us, so don't want to be called again.
I want to delete the clients record from the master table by comparing the two tables and sayi that 'everytime you find a phone number from the 'ordered' table that exists in the master table, delete the record from the master table.
That way I will end up with a master table of clients who haven't placed an order.
Could anyone tell me the quey to run that could do that.
