Delete Table

Oct 9, 2007

little question here:

1) table deleting
i have a table in which temporary data is stored;
With a command button i want to delete the table (or maybe just the data in it);

2) information from table in textbox
In a form i have a textbox in which the data is mentioned from the table, after clicking the delete table button, i want also the texbox to be empty

First i tried to put the following code behind the onclick event but doesn't seem to work(?)

DoCmd.DeleteObject acTable, tTable

It should be quite simple (i think?); please advise,

Thanks

View Replies

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

Queries :: Delete Main Table Row Depending On Sub-table Row If It Is Null

Dec 25, 2014

i have two data tables, one is depending on the other. now i need to delete the main table row depending on the subtable row if it is null.

View 3 Replies View Related

Delete All Rows In Master Table If Identical In Second Table - SQL

Jan 13, 2006

A SELECT works:

SELECT *
from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield

But none of these do:

DELETE
FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**

DELETE
FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**

DELETE
FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**

DELETE
FROM mastertable
WHERE IN (SELECT *
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**

Can anyone help? This should be easy, shouldn't it?!
Thank you.

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

Delete Record From One Table And Have It Automatically Added To Another Table?

Oct 28, 2011

I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?

View 4 Replies View Related

Need To Move Data From Table A To Table B And Delete It

Sep 20, 2006

Well I have two tables, one is the workorder register and the other is the closed work orders. This is for maintenance. I have a form for creating and veiwing workorders. I need to link a command button to the form that will delete the record being viewed in the WO REG table and paste it in the Closed WO table. I think I need to use two append queries but have no idea what I am doing. Here is what I have so far.

INSERT INTO [CLOSED WO] ( WO, MMCN, TECH, NOMIN, FUALTS, TYPE, [SECTION], CLOSEDATE, OPENDATE )
SELECT [WO REG].[WO], [WO REG].[MMCN], [WO REG].[TECH], [WO REG].[NOMIN], [WO REG].[FUALTS], [WO REG].[TYPE], [WO REG].[SECTION], [WO REG].[CLOSEDATE], [WO REG].[OPENDATE]
FROM [CLOSED WO], [EQUIP REG] INNER JOIN [WO REG] ON [EQUIP REG].[MMCN]=[WO REG].[MMCN]
WHERE ((([WO REG].[WO])=[WHAT WO TO CLOSE?]));

Like I said I have no idea what I am doing, this is out of the SQL view for my "move to closed WO" query. the fields I need to move are WO, MMCN, TECH, NOMIN, FUALTS, TYPE, SECTION, CLOSEDATE, and OPENDATE.

Is this even remotly right? I know nothing of VBA an all these symbols mean nothing to me.

View 4 Replies View Related

Help Needed :If Table Exists, Then Delete Table

Jun 6, 2006

I need urgent help, I am required at my job to come up with a command that will check to see if a table exists, and if so, delete the table. The whole process goes like this:

There's a form, and a listbox. 2 buttons...1 is import a file and 2nd is generate report. In the listbox theres 3 excel files. You select one excel file then click on Import, followed by clicking on Generate Report button.

The whole technical process is this:
1. A csv file is imported into a temp table
2. A temp table is created and named "_ImportedSKUS".
3. An append query is exectued to add the data from the "_ImportedSKUS" temp table to the final table, "Imported SKUs".
4. Then the temp table, "_ImportedSKUs" is closed and deleted.

I have a delete command at the end of the sub, which goes like this:

DoCmd.DeleteObject acTable, "_ImportedSKUS".

But I'm also need to generate an If statement before the loop that will check to see if the "_ImportedSKUs" table exists, if so, delete it (using same code listed above) and end if.

Can anyone help me out here???

View 4 Replies View Related

DELETE From Table

Apr 9, 2008

if I had tblOrders OrderNo, CustomerNo, Date, OrderTotal
tblCustomers CustomerNo, Name, Street, Country, Postcode

and I was asked to delete orders placed by customer Smith, would I be correct if I put this:

DELETE tblOrders*
FROM tblOrders INNER JOIN tblCustomers ON tblOrders.CustomerNo = tblCustomers.CustomerNo
WHERE Name = "Smith"

I wasnt sure about the DELETE tblOrders to have a '*' after it or not . .

View 3 Replies View Related

Delete A Record From Table

May 9, 2005

Hi,

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?

Many thanks,

Paul.

View 3 Replies View Related

Delete Fields From Table

Aug 30, 2005

Hi

Is there a automatic way i can delete Fields from a Table? This table gets input into the database very frequently as updates come in and i'd like something like a Query or Macro, where i can define which fields i dont want, to delete the 30+ columns of useless data.

Can't see how to do it with a Query or macro though

Anyone have any ideas?

Thanks

View 5 Replies View Related

How Do I Delete Last Record In Table?

Jun 21, 2007

Hi all, whats the simplest way to delete the last record in a table? based on
DoCmd.RunSQL "DELETE * FROM

Trying to use a button to do the above

Cheers

View 5 Replies View Related

Delete Table Confirmation

Aug 2, 2005

is there any way to get rid of deletion confirmation when you run a make-table query. though I unchecked all boxes under Confirm on Edit/Find tab, still popping up.

thanks in advance

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

Delete From One Table With Key Linked To Another

Oct 26, 2006

Hi all.

I kind of got stuck with this one.

I'm trying to use a Delete query to delete some records from Table A.

Let's say Table A has three fields: A1, A2 and A3

Then there's another table, Table B, with two fields: B1 and B2

Within one Delete Query, i need to delete all the records from Table A for which: A1=B1

Thanks for your help.

Alex

View 6 Replies View Related

How To Delete Rows From Table?

Nov 7, 2006

Hi.
How I can delete all rows from my table whit on query? Also I want that when I delete rows from my table index and other settings will stay in table.

View 1 Replies View Related

Delete Table If Exists

Feb 1, 2006

How to delete table if exist using VBA. I know this was in forum but I can't find the answer. Please help its urgent.

View 8 Replies View Related

Delete FIRST Record From A Table

Mar 17, 2015

How do I DELETE the FIRST RECORD FROM A TABLE ?? I am using MS Access 2003

View 4 Replies View Related

Delete Table Record With VBA?

Jul 7, 2013

This is my code:

my pro is string

prID is autonumber field

Code:

CurrentDb.Execute "delete from protbl where prID='" & mypro & "'"

shows this error:

Data type mismatch in criteria expression

View 6 Replies View Related

Delete Records From One Table That Appear In Another Table

Apr 27, 2006

Hi all,

Quick question...

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.

Thanks in advance - Lee

View 4 Replies View Related

Access 2010 - Delete Form Command Button With A Password To Confirm Delete

May 6, 2014

All I am trying to do is insert to have a form with a "Delete Record" button on it. The problem is I don't want anyone to be able to delete a record, I would like someone to have to insert a password to confirm the delete.

View 13 Replies View Related

Delete Duplicate In A Related Table

Dec 14, 2004

Hello,
I have two related table: table_Company and table_contact. Now i have:
Table_Contact Table_Company
James Bolton -----------> MacDonalds
Hellen Kidman ----------> MacDonalds
Now, i wanna have one "MacDonalds" in my Company table, and delete the others "MacDonalds" and still have the relationship between my contacts and their company.

View 4 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

How To Delete Duplicate Values In A Table

Mar 5, 2006

Good morning all!
I have a problem, I have a contact list table in my database such as name, addr, company, street, country etc. The problem is that I have multiple duplicates of the same data.
How do i delete the duplicates? Is there an SQL query that I can run to delete? How do I do it. Please help. Thanks a bunch!

View 2 Replies View Related

A Way To Delete A Field In A Table Safely

Sep 2, 2005

I'm using Access 2000. I want to remove a field from an existing table and I'm not sure if this field is used in any of the existing queries, I really don't want to have to manually check all 100 queries to see if it's been used. Is there a query, macro or ??? I can write to do this? How?

Thanks
Martina

View 1 Replies View Related

Delete Query Based On Another Table

Jul 15, 2006

I am trying to run the following Delete Query

DELETE Master.*
FROM Master
WHERE (((Master.glm_series)=506) AND ((Master.glm_account) In (SELECT glm_account from glj )) AND ((Master.glm_prft_ctr) In (SELECT glm_prft_ctr from glj)));

The problem is it is trying to delete all the records with the account numbers = and ALL the records with profit center equal. I am getting 2564 records instead on 147 records.

The two tables are related by glm_account AND glm_prft_ctr - BOTH must be equal in order to delete

If I do an INNER Join or add the other table to the Query it gives me "Cannot Delete Records from Specified Table"

Help

TIA
Nique

View 1 Replies View Related