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

View Replies


ADVERTISEMENT

Modules & VBA :: Hide Subform Footer Controls If No Records Present (AC2007)

Mar 14, 2014

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.

View 6 Replies View Related

Modules & VBA :: Delete Records From A Table Based On Records In Another

Feb 7, 2014

I have a table InvPrice and Updated Pricing

Need to delete all records from InvPrice that Match UpdatedPricing

InvPrice.StockCode = UpdatedPricing.StockCode
InvPrice.PriceCode = UpdatedPricing.StockCode

I have tried something like this...

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

View 14 Replies View Related

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.

View 5 Replies View Related

Modules & VBA :: Query To Delete Records From Table

Jul 22, 2015

I have two tables Table1,Table2. I want to delete records from Table1 whose ID= CoventionalID field value from Table2.

Table1 has two fields (ID,PolicyNumber)

Table2 has 5 fields(ID,PolicyNumber,ConventionalID,Conventional PolNo)

The following query doesn't delete anything.

Code:
DELETE *
FROM Table1 WHERE ID=(Select ConventionalID from Table2);

View 3 Replies View Related

Modules & VBA :: Reset Autonumber Each Time Delete / Append Records In A Table?

Oct 25, 2013

I need to reset the autonumber each time I delete/append records in a table. Best way?

View 5 Replies View Related

Modules & VBA :: Delete Of A Record From A List Of Records In A Subform Deletes Wrong Record

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

Tab Controls On Subform For Sequential Records

May 19, 2006

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.

View 1 Replies View Related

Modules & VBA :: Adding All Records From A Subform To Table?

May 26, 2015

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

[code]....

View 4 Replies View Related

Modules & VBA :: Reference Field On Datasheet - Unbound Subform Controls

Jun 2, 2014

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

View 3 Replies View Related

General :: Cannot Delete Records Added Through The Subform

Feb 19, 2013

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.

View 2 Replies View Related

Forms :: Delete Records In Subform Based On Check Box In Parent Form

Mar 1, 2015

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.

View 5 Replies View Related

Modules & VBA :: Delete A Selected Record In A Subform By Using A Button

Jan 12, 2014

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

View 14 Replies View Related

Modules & VBA :: How To Delete Multiple Records

Sep 7, 2014

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.

View 4 Replies View Related

Forms :: Delete Record From Subform And Table

Oct 23, 2014

I am getting a problem with delete button..I want to delete record from subform and Table..

Table:Payroll
Subform:PayrollSearchQuery
Form:Payrollog

I am giving the code is ....

Private Sub Command58_Click()
'check existing selected record
If Not (Me.PayrollsearchQuery.Form.Recordset.EOF And Me.PayrollsearchQuery.Form.Recordset.BOF) Then
'confirm delete

[code]....

View 3 Replies View Related

Forms :: Add And Delete Lines From Table Directly On Subform

Dec 6, 2013

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.

View 2 Replies View Related

Delete Records From A Table

Oct 26, 2005

Hi

I'm trying to deleterecords from one table. In the query there are 2 tables both joined. I get an error message come on saying

"Specify The Table Containing The Records you want to Delete"

Below is the query in SQL

DELETE tblStockRequired.StockCode, tblStockRequired.StockQTY, tblStockRequired.OrderNumber
FROM tblOrderCompleted INNER JOIN tblStockRequired ON (tblOrderCompleted.OrderNumber = tblStockRequired.OrderNumber) AND (tblOrderCompleted.StockCode = tblStockRequired.StockCode) AND (tblOrderCompleted.QTY = tblStockRequired.StockQTY);

I want ot delete all records from tblStockRequired which match up with tblOrderCompleted.

Thanks in Advanced

View 2 Replies View Related

Queries :: Delete Records In One Table Dependent On Another Table

Apr 17, 2015

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],

[Code] .....

View 3 Replies View Related

Modules & VBA :: Command Button To Delete Records In Sub And Main Forms

Jun 24, 2013

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.

View 2 Replies View Related

Delete Records From Linked DBF Table

Oct 17, 2005

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?

Thanks,

Sup

View 1 Replies View Related

Delete Records Automatically Frm Table

Jul 26, 2006

How do you have Access delete records automatically from a temp table?

View 1 Replies View Related

Moving Records To Another Table Then Delete

Mar 15, 2007

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!

Heres what I got:

Private Sub cmdLoad_Click()

On Error GoTo Err_Load_Record_Click

UploadHistory

Exit_Load_Record_Click:
Exit Sub

Err_Load_Record_Click:
MsgBox Err.Description
Resume Exit_Load_Record_Click

End Sub

Sub UploadHistory()

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)

On Error GoTo Err_UploadHistory

'Execute the add.
strSql = "INSERT INTO [Training] " _
& "(TaskNumber, Date, Hours, TrainerLast, TraineeLast, Qualified) " _
& "SELECT " & "Schedule.Task, Schedule.Date, Schedule.Hours, Schedule.Trainer, " _
& "Schedule.Trainee, Schedule.Qualified FROM [Schedule] " _
& "WHERE (((Schedule.Trainee) = " & Me.TraineeCombo & " AND (Schedule.Completed)= 1));"

DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

'Execute the delete.
strSql = "DELETE FROM [Schedule] WHERE Trainee = " & Me.TraineeCombo & " AND Completed = 1;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

'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

Err_UploadHistory:
MsgBox Err.Description, vbExclamation, "Upload failed: Error " & Err.Number
Resume Exit_UploadHistory

End Sub

View 4 Replies View Related

Queries :: Specify Table Containing Records That Want To Delete?

Mar 26, 2015

delete Query How do i Specify the table containing the records that I want to delete?

View 1 Replies View Related

Queries :: Delete Records From A Table

May 1, 2015

I have two tables as follows;

Master file:Vessel_master
Vessel_code
Vessel_name

main data table:Main
Vessel_code
container_no
size
Voyage
weight

by using above two table have made a query as follows;

Vessel_name:Vessel_master
Vessel_code:Main
container_no:Main
size:Main
Voyage:Main
weight:Main

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.

View 1 Replies View Related

Delete Duplicate Records In Another Table

Apr 12, 2012

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?

View 2 Replies View Related

Modules & VBA :: Copy Record To History Table And Then Delete It From Main Table

Jul 9, 2014

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

[Code] ....

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved