Queries :: Deleting Records Based On Selections From Another Table

Apr 1, 2013

I am trying to create a Delete query.

I am trying to delete a specific part from multiple BOMs in my database.

I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.

So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.

I am able to select the records with:

Code:
SELECT SYSADM_REQUIREMENT.*
FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID
WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));

Now how do I delete these same records.

I am getting error saying I have to select a table to delete from....

View Replies


ADVERTISEMENT

Form Based On Queries - Deleting Field From Main Table

Mar 28, 2014

In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).

View 1 Replies View Related

Reports :: Coloring Records Based On Form Combobox Selections

Apr 12, 2014

Using Access 2007. I have a form (Broker), with a combobox (cboClassifier), that I use to select 1 of 4 phrases, display the selected phrase and color the field with a color that I've assigned to each phrase.

Next, I have a report that contains all of the Broker names, addresses, etc. based on a query (qryBroker). I need the vba or whatever that colors the matching records based on the phrase selected by the combobox on the form, when the report is opened.

View 1 Replies View Related

Queries :: Populate Text Box Based On 2 List Box Selections

Feb 10, 2014

I have 2 tables.

tblOrderType

1 - Maintenance Order
2 - Breakdown Order
3 - Greasing Order

On my form I have two list boxes: An Order Type List box, and an Area Listbox.What query criteria or VBA code would I use so that I could populate a text box with the relevant order number based on the selections of the list boxes. i.e. MaintenanceOrder & Area Z would display MaintenanceOrderNoZ.

View 2 Replies View Related

Queries :: Calculate Totals Column Based On Check Box Selections

Mar 15, 2013

I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.

What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).

Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.

View 2 Replies View Related

Deleting Subform Records Based On Checkbox

Jun 21, 2005

I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.

I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.

Sub DeleteRecordButton_Click()

'Delete the selected record
dim strSQL as String
strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]

DoCmd.RunSQL strSQL

MsgBox("Confirmed")

End Sub


Thank you!

View 1 Replies View Related

Deleting Records Based On A Field Content?

Nov 16, 2004

Hi all!

I'm rapidly beginning to get a little confused... I'm trying to mass-delete records based on the content of a field.

I want to run either a query or a button on a form (or anything, for that matter) that will delete any records when there is an X in the N_Disp field.

Any ideas?

Thanks,

Barry

View 3 Replies View Related

Modules & VBA :: Deleting Records Based On Month

Jul 26, 2013

I want to write an sql or vba code to delete records based on the month...For example I have a field called date which contains a date and I want to delete all Dates that are In April

Ive tried
Delete from LinkTable Where month(Date) = 'April'
Still Nothing

View 3 Replies View Related

Forms :: Populate A Table Based On Selections Made Within A Form

Apr 27, 2015

So I'm trying to populate a table based on selections I make within a form.

The form is based of a query that pulls a new product category that needs to be added to my first reference table for products.

The form shows all the new unique product codes, and there is a drop down box with product categories whose source is also the first reference table, so a new code would be:

'JBL - TRX - FVB - TRZ' And based on that new info, I would select from the drop down to select the corresponding category and click 'Add Record' button.

I'm building the event for the button and the code is as follows so far :

Private Sub Add_Record_Click()
End Sub
Private Sub cmbAdd_Record_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblPVMTable(PVMJoinField, SummaryPVMCategory) " & _

[Code] ....

I keep getting errors with the main portion of code.

Name of table fields :

PVMJoinField
SummaryPVMCategory

Name of Query Fields Populating the Form:

PVM_JOIN_FIELD
cboSummaryPVMCategory

View 4 Replies View Related

Queries :: Selects Active Records For Three Different Selections - Concatenate Query Results

Nov 3, 2014

I have a query that selects the "Active" records for three different selections, A, B or C.. There may be 1, 2 or 3 results for a particular selection. That is Selection A may have one result or active records, but Selection B may have three results.

I want to use data from the query to populate a field on a form. For example, if the results for Selection A, having one "Active" record would be RecordAData. But for Selection B with three "Active" records the result would be RecordBData & " " & RecordBData2 & " " & RecordBData3

My query is:
SELECT tblSomething.ID, tblSomething.D1, tblSomething.D2, tblSomething.D3, tblSomething.D4, tblSomething.D5, tblSomething.D6, [D3] & " " & [D4] & " " & [D5] & "-" & [D6] & " " & "SomeText" AS Header
FROM tblSomething
WHERE (((tblSomething.D1)=Forms!frmSomethingHeaders!D1) And ((tblSomething.D2)=True));

The concatenation in the query is labeled "Header". I want to be able to Concatenate the "Header" which in itself is a concatenation in the query.

I thought that this might be a looping through the query results, but I cannot figure out how to do it. But then, that is only my uneducated guess.

View 11 Replies View Related

Modules & VBA :: Training Matrix - Matching Listbox Selections To Table Records

May 6, 2015

I have a training matrix that lists employee names and certifications on various operations. The objective is to choose an operation and run a query to display everyone who is certified on that op. There are additional variables.

Code:
Name EMP ID OP1 OP2 OP3 OP4 OP5
-----------------------------------------------------------------------------
John Doe 526261 C C C
Bob Doe 555622 C C C
Sheila Doe 066600 C C C

Okay that looks about right for the data itself. The listbox has all the ops, you choose an op and hit a button and it goes and finds everyone who has a 'C' in that op column and pulls their record.

View 14 Replies View Related

Queries :: Delete Records Based On Criteria In Another Table

Jun 3, 2013

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]

View 8 Replies View Related

Queries :: Deleting Data From Selected Records

Sep 23, 2014

My Membership Database includes 3 fields in the main Table which I need to clear prior to the new subscription year, but on a selective basis.The fields are R/N for (Renewal or New), a subscription date and an amount field (Currency).

At the due date I need to clear all 3 fields except where N/R=N AND date >01/01/2014, when data in all 3 fields is to be retained.I have tried several SQL codings to achieve this but have been unsuccessful so far.

View 6 Replies View Related

Queries :: Deleting Duplicates In Non-unique Records

Feb 27, 2014

I have imported a large number of emails into a table tbl_requests.

I had intended to have unique file tbl_requests.date_opened unique, but have ended up with a lot of duplicate records (i.e. tbl_requests.date_opened is not unique !). How to delete any duplicates? I have 15,000 records...

View 3 Replies View Related

Cascading Combo Boxes - Interact And Record Selections Made In New Records Within Destination Table

Sep 12, 2012

I am trying to get a series of combo boxes to interact and record the selections made in new records within a destination table. Here is situation with respect to the tables involved:

1 table lists the names/acronyms of various research facilities and the branch to which they belong (acronym is PK)
1 table lists Financial Points of Contact for each facility (acronym is FK to relate to earlier table)
1 table lists Technical Points of Contact for each facility (acronym is FK to relate to Facility table)
1 table lists program participants and the branch they belong to (Branch in FK to relate to Facility table)

I am trying to create a form that allows me to set the participant and, from this selection, restricts the facility choices in the Facility combo box to those that fall within the branch to which the participant is assigned. I think the next step is pretty obvious too. Once the facility is selected I have combo boxes that would only display those Financial and Technical PoCs assigned to that facility.

I have watched the 4 Data Pig tutorials and I can easily make functional cascading combo boxes as long as they only refer to the look up tables. I generated perfectly functioning cascading combo boxes on a test form. How do I generate combo boxes that will then store the info selected in my destination table? Whenever I try to set a Control Source it disrupts the functioning of the queries.

View 4 Replies View Related

Db Deleting Records From A Table

May 18, 2005

Hello.

I am using a large database, which usually works fine, and is set to compact on close.

Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)

Does anyone have any ideas as to what is causing this?

Thanks.

View 14 Replies View Related

Deleting All The Records In A Table

Dec 27, 2007

Hi,
I am using Vb6.0 as a front end and msaccess as the db.
i want 2 delete all the records in a table "Register" by clicking a menu
The connection is made using ADODB
Till now i hv the code

Private Sub mnudel_Click()
Docmd.SetWarnings False
Docmd.RunSql ("DELETE * FROM Register;")
Docmd.SetWarnings True
End Sub

But when i execute this a error msg displays
Run time error "424"
Object required

Whats the problem in me
plz help me to come out from this...

View 2 Replies View Related

Deleting Records From Table

Feb 2, 2005

I have a simple form with a subform on it (see enclosed Access 2000 Database). I'm trying to add/change/delete records from table: Component Name. Adding and changing records is not a problem but deleting is. It only deletes the value of the field "Interative Component Name" and not the entire record. I have my joins defined properly on the tables. What am I missing? I wish this Access stuff was easier to learn. Any help/clues would be greatly appreciated.

Thanks

Sue

View 4 Replies View Related

Deleting All Records From A Table

Nov 23, 2004

Hello:

I have created a form to record addresses, phone numbers and other information. How do I go about creating a DELETE button that when pressed, deletes ALL records from the table.

Many thanks in advance and for those of you who celebrate Thanksgiving, have a Happy Thanksgiving!

Regrards,

Dion

View 1 Replies View Related

Deleting All The Records In A Table

Dec 22, 2004

Is it possible to have all the records deleted from either a command button on a form or from the switchboard...

View 6 Replies View Related

Queries :: Return Records Between Dates Based On 2 Date Fields In A Table

Apr 24, 2013

I have a table which includes a start date field and completion date field for housebuilding.

I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.

It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.

View 7 Replies View Related

Queries :: SELECT Records From A Table Based On IN Clause And Sort Them In Order

Jan 4, 2014

WinXPPro Sp3
Access 2007

After some research I thought I had found a neat way to SELECT records from a table based on an 'IN' clause and sort them in the same order as the values for the 'IN' clause... i.e.

Code:
SELECT Unique_No, Table_Name, List_Order FROM My_Table
WHERE Table_Name = 'Titles'
AND List_Order IN (3,1,15,4,5,12,7,2)
ORDER BY INSTR('3,1,15,4,5,12,7,2', List_Order)

Unfortunately, this returns list_order 5 just after 15 and list_order 2 just after 12, thus

List_Order
3
1
15
5
4
12
2
7

View 3 Replies View Related

Deleting Records From A Table In Access

Jul 27, 2006

I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated

View 1 Replies View Related

Deleting Matching Records From Another Table

Dec 6, 2005

Hi

Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.

I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:

DELETE tblB.* from tblB
INNER JOIN tblA ON tblB.Field1 = tblA.Field1
AND tblB.Field2 = tblA.Field2
AND tblB.Field3 = tblA.Field3...

but I get "Could not delete from specified tables".

What am I doing wrong? Or is there an easier way?

Dave

View 2 Replies View Related

Forms :: Deleting Records On The Table Through Form

Oct 12, 2014

I have a form linked to a table and in my form there is a listbox reflecting the records in my table. Evereytime I tick a record in the list box the information on its corresponding columns appears on the text field.

So far I am able to add, modify and save record to my table using this form but I am having a problem on deleting a record which I selected on the listbox. I created a delete command button but it is not working. Please see attached database.

View 2 Replies View Related

Deleting Records From A Table Through A Form Button Event

Nov 14, 2014

I have a FrmCadastro which updates my master table TblCadastro. Primary key of the TblCadastro is an auto numbered field named CADID.Fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ of the FrmCadastro are mandatory. My rule is that If any of these fields is null then the record cannot be saved (cannot be added into TblCadastro).

FrmCadastro has two distinct buttons, as follows: BtSave (to save either newly included record or changed record) and BtDelete (to delete a chosen record from my master table).To delete a record has not been a problem because under the click event of the BtDelete I use the following command lines to successfully eliminate an existing record (previously saved) from the TblCadastro:Dim numRecord As Integer

numRecord = Me.CADID
Dim SQL As String
DoCmd.SetWarnings False
SQL = "DELETE * FROM tblcadastro WHERE cadid = " & numRecord
DoCmd.RunSQL SQL
DoCmd.SetWarnings True

To save a complete record (properly filled in) has not been a problem either since the code takes care of that very well.When the user is in the process of adding a new record through the FrmCadastro two different user behaviors can take place:

1. Irregular entering - The user for some reason just give up saving the record (he decides to add the new record later, for instance) by selecting the option Save = No, or
2. Incomplete record - By mistake the user try to save an incomplete record (a record that shows any mandatory field as null).

I coded the following command lines under the click event of the BtSave to take care of deleting those records... BUT it does NOT work. The system reads each line of the code, find the record properly, close the form as expected, but the incomplete record STILL REMAINS in the TblCadastro. I do not understand why, since the code is pretty much the same of the one in the BtDelete.

Code:
Private Sub bt_save_Click()
If MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbYes Then
If MsgBox("WARNING! Incomplete record will be automatically deleted from database. If you did not fill in the fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ, the record will not be saved.", vbOKOnly, Me.Caption) Then
If Not IsNull(CPF) And Not IsNull(Name) And Not IsNull(FIN) And Not IsNull(NAT) And Not IsNull(TIP) And Not IsNull(MOT) And Not IsNull(INSTREQ) Then

[code]...

What should I code to exclude both the incomplete record and irregular entering thru the button Save (options Yes and No) ?

View 14 Replies View Related







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