Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one.
I have code which grabs values from a list box (which has as its source "tblNewModPart" ) then uses them in a Make-Table query which creates a table called "tblRemovedPart s." I would like to delete all values from "tblRemovedPart s" from "tblNewModPart. "
I've tried setting up a Delete-query which contains the two tables joined by part number, but when I try to run the query, it pops up an error message saying "Specify the table containing the records you want to delete."
The code for the delete query is: - DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
-
FROM tblRemovedParts INNER JOIN tblNewModPart
-
ON tblRemovedParts.PartNum = tblNewModPart.PartNum;
How do I deal with this?
I've found references elsewhere saying to set the Unique Records property to Yes, but that doesn't seem to have helped.
Any assistance would be great. Thanks!
10 2682 nico5038 3,080
Recognized Expert Specialist
Joining tables won't work in this case.
Best to make first a backup before running a query like this !
One general approach is to use a subquery like:
delete * from tblRemovedParts where PartNum in (Select PartNum from tblNewModPart)
This requires that all PartNum's to be removed are found in tblNewModPart.
Getting the idea?
Nic;o)
Joining tables won't work in this case.
Best to make first a backup before running a query like this !
One general approach is to use a subquery like:
delete * from tblRemovedParts where PartNum in (Select PartNum from tblNewModPart)
This requires that all PartNum's to be removed are found in tblNewModPart.
Getting the idea?
Nic;o)
Thanks for the quick reply. I think I get it. Instead of trying to join the two tables, I'll instead just select all part numbers from the other table. I suppose thats what I was trying to do in the first place, but my knowledge of MS Access is pretty limited. Thanks!
Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one.
I have code which grabs values from a list box (which has as its source "tblNewModPart" ) then uses them in a Make-Table query which creates a table called "tblRemovedPart s." I would like to delete all values from "tblRemovedPart s" from "tblNewModPart. "
I've tried setting up a Delete-query which contains the two tables joined by part number, but when I try to run the query, it pops up an error message saying "Specify the table containing the records you want to delete."
The code for the delete query is: - DELETE tblNewModPart.ModuleNum, tblNewModPart.PartNum, tblNewModPart.Description, tblNewModPart.Quantity
-
FROM tblRemovedParts INNER JOIN tblNewModPart
-
ON tblRemovedParts.PartNum = tblNewModPart.PartNum;
How do I deal with this?
I've found references elsewhere saying to set the Unique Records property to Yes, but that doesn't seem to have helped.
Any assistance would be great. Thanks!
Try it this way after you have made backups of your mdb: - DELETE tblNewModPart.* FROM tblNewModPart INNER JOIN tblNewModPart ON tblRemovedParts.PartNum = tblNewModPart.PartNum;
oops! Sorry Nico........did not see your response.
nico5038 3,080
Recognized Expert Specialist
Keep me posted nickvans :-)
No problem PuppyDogBuddy, I see we agree on the backup :-)
Nic;o)
Keep me posted nickvans :-)
No problem PuppyDogBuddy, I see we agree on the backup :-)
Nic;o)
Thanks for the advice. I got it working. I ended up needing to revisit the code that grabbed values from the list box (because I had screwed it up, inadvertently), and ended up simply passing the same values to a delete query as the make-table query.
If you're interested, here's the code. - Private Sub buttonRemovePartFromNewModPart_Click()
-
-
DoCmd.SetWarnings False
-
-
'POPULATE AND RUN qryAppend_RemovedParts
-
'THIS SECTION OF CODE RUNS THE FIND QUERY BASED ON THE NEWMODPART MULTI-SELECT LIST
-
'IT REQUIRES "qryAppend_RemovedParts" WHICH CONTAINS "partnum/description/quantity"
-
Dim Q As QueryDef, DB As Database
-
Dim Criteria As String
-
Dim ctl As Control
-
Dim Itm As Variant
-
-
' Build a list of the selections.
-
Set ctl = Me![lstNewModPart]
-
-
For Each Itm In ctl.ItemsSelected
-
If Len(Criteria) = 0 Then
-
Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34)
-
Else
-
Criteria = Criteria & "," & Chr(34) & ctl.ItemData(Itm) _
-
& Chr(34)
-
End If
-
Next Itm
-
-
If Len(Criteria) = 0 Then
-
Itm = MsgBox("You must select one or more items from the" & _
-
" list above.", 0, "No Selection Made")
-
Exit Sub
-
End If
-
-
' Modify the Query.
-
Set DB = CurrentDb()
-
Set Q = DB.QueryDefs("qryAppend_RemovedParts")
-
Q.SQL = "INSERT INTO tblRemovedParts ( PartNum, Description, Quantity) SELECT tblParts.PartNum, tblParts.Description, tblModPart.Quantity From tblParts INNER JOIN tblModPart ON tblParts.PartNum = tblModPart.PartNum Where (tblParts.[PartNum] In(" & Criteria & _
-
") AND (tblModPart.ModuleNum)=[Forms]![frmModSearch]![lstModList]);"
-
Q.Close
-
' Run the query.
-
DoCmd.OpenQuery "qryAppend_RemovedParts"
-
-
'END qryAppend_RemovedParts
-
-
'Delete all records matching "Criteria" from tblNewModPart
-
' Modify the Query.
-
Set DB = CurrentDb()
-
Set Q = DB.QueryDefs("qryDelete_NewModPartSelected")
-
Q.SQL = "DELETE * FROM tblNewModPart Where tblNewModPart.[PartNum] In(" & Criteria & _
-
");"
-
Q.Close
-
DoCmd.OpenQuery "qryDelete_NewModPartSelected"
-
Me.lstNewModPart.Requery
-
-
'Delete all records matching "Criteria" from tblAddedParts
-
Set DB = CurrentDb()
-
Set Q = DB.QueryDefs("qryDelete_AddedPartsSelected")
-
Q.SQL = "DELETE * FROM tblAddedParts Where tblAddedParts.[PartNum] In(" & Criteria & _
-
");"
-
Q.Close
-
DoCmd.OpenQuery "qryDelete_AddedPartsSelected"
-
-
DoCmd.SetWarnings True
-
-
End Sub
nico5038 3,080
Recognized Expert Specialist
Hmm, some remarks:
1) Using recordset processing is some 8 to 10 times slower as queries.
2) You can run into an "illegal" query when the Criteria string gets too long :-(
When you want a 100% solution using this code, better to have an additional table and fill that instead of appending the values to the Criteria field.
Next use:
delete * from tblX where ID in (select ID from tblTemp)
Getting the idea ?
Nic;o)
nico5038 3,080
Recognized Expert Specialist
Hmm, on second thoughts, when you have a listbox, better to use a subform and add a Yes/No field to the original recordsource of the listbox.
Now the use can set the YesNo field (no hassle with keeping CTRL or SHIFT pressed) and use for the delete:
delete * from tblX where ID in (select ID from tblTempSubform where YesNoField = True)
Better ? :-)
Nic;o)
Hmm, some remarks:
1) Using recordset processing is some 8 to 10 times slower as queries.
2) You can run into an "illegal" query when the Criteria string gets too long :-(
When you want a 100% solution using this code, better to have an additional table and fill that instead of appending the values to the Criteria field.
Next use:
delete * from tblX where ID in (select ID from tblTemp)
Getting the idea ?
Nic;o)
I don't think I quite know what you're talking about. I understand what you said about the code taking much longer to process than a query, and I also understand the bit about running into problems if the string gets to long (unlikely in my case I think, as there are generally no more than two or three parts you'd want to remove from any given module, and each part number is only about 10 characters long. The string should have no problems up to 255 characters, right?)
To follow your suggestion, how would I go about adding each selection to a table, other than doing it as I am?
In regards to your second post: The reason I went with a listbox rather than a sub form is I have no experience with subforms (I'm sort of learning as I go here) and figured a list box would work just as well.
As always, your input is invaluable.
Thanks,
nickvans
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Yossi Naggar |
last post by:
Hello to everyone,
I am an experienced user in MSSQL Server.
Lately I have been started using MySQL. I managed to create my
database and tables.
When I wanted to execute the following query:
delete
from adminpages
where parentid IN ( select DISTINCT A.id from adminpages AS A where
A.name='galeries' );
|
by: Philip Mette |
last post by:
I have been searching many postings and I cant seem to find anyone
that has this answer so I decided to post. I am using SQL
(Transact-SQL).If I have 2 tables with columns
acct_num,activity_date,and pay_amt and I want to delete one instance
of a record in table 1 for every instance of that record in table 2
how could I do that. For example.
Table 1
-----------
acct activity_date pay_amt
|
by: robert |
last post by:
been ruminating on the question (mostly in a 390/v7 context) of
whether, and if so when, a row update becomes an insert/delete.
i assume that there is a threshold on the number of columns of
the table, or perhaps bytes, being updated where the engine just
decides, screw it, i'll just make a new one.
surfed this group and google, but couldn't find anything.
the context: we have some java folk who like to parametize/
|
by: John Rivers |
last post by:
Hello,
I think this will apply to alot of web applications:
users want the ability to delete a record in table x
this record is related to records in other tables
and those to others in other tables etc.
in other words we must use cascade delete to do
|
by: R.Welz |
last post by:
Hello.
I want to discuss a problem I have with my database design becourse I
feel I cannot decide wheather I am on the right way of doing things.
First of all, I am writing a literature and magazine database with web
(PHP) and C++ Interface, serving over the web and in a very fast LAN.
So my concern is about performance (and aestaetic by doing the things
as optimal as possible.) This is my first database at all, but I have
read a lot of...
| |
by: polocar |
last post by:
Hi,
I'm writing a program in Visual C# 2005 Professional Edition.
This program connects to a SQL Server 2005 database called
"Generations" (in which there is only one table, called
"Generations"), and it allows the user to add, edit and delete the
various records of the table.
"Generations" table has the following fields:
"IDPerson", NamePerson", "AgePerson" and "IDParent".
A record contains the information about a person (his name, his...
|
by: Matt |
last post by:
I am writing a DELETE statement and I want to filter the records using
another SELECT statement.
My SELECT statement is a GROUP BY query that grabs all social security
numbers from the "Data With Import Date Current" table on a given day
where there was only one transaction (count of SSN = 1). I want to
delete these records from the "Data With Import Date Current" table.
I would like to do this by joining the "Data With Import Date...
|
by: Bob Bridges |
last post by:
Start with two tables, parent records in one and child records in the other,
a one-to-many relationship. Create a select statement joining the two.
Display the query in datasheet mode. When I delete a row, only the child
record is deleted from the source tables; the parent record is still
there...which is what I wanted.
Now display fields from that query in a continuous form. When I delete a
record from that form, one of the child...
|
by: Ed Dror |
last post by:
Hi there,
I'm using ASP.NET 2.0 and SQL Server 2005 with VS 2005 Pro.
I have a Price page (my website require login) with GridView with the
following columns
PriceID, Amount, Approved, CrtdUser and Date
And Edit and Delete buttons
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |