473,326 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Delete Query for subforms

Hello again,

The background scenario on this problem is a little long so bare with me:

I have a main form frmTimesheets with 4 subforms (subEmployeeHourLog, subInternalEquipmentHourLog, subRentalEquipmentHourLog and subDailyProduction).

The first 3 subforms, the user inputs either the EmpNumber or EquipNumber with a CostCode and HoursLogged for that day.

The 4th subform display everything in tblDailyQuantity (P.K. QuantityID) where essentially qryTimesheetCostCodeDisplay queries all the unique CostCodes inputed in the above 3 subforms and sends its to tblDailyQuantities linked by the TimesheetID.

Now I have managed to create a working append query sending only the unmatched CostCodes for each respective timesheets but I cannot get my delete query do to the same. At the moment it deletes every unmatching CostCodes in tblDailyQuantities, therefore deleting all the Quantities from all the other timesheets and keeping only the ones in the displayed/active mainform.

I know my problem revolves around getting both the CostCode and TimesheetID to match in the SQL language of the query but I am not comfortable enough in SQL.

Below is the SQL behind my delete querry DqryUpdateQuantities (All done through the querry wizard except for my added underline statement (if that statment even makes sense in SQL), not good with the syntax of this expression):

Expand|Select|Wrap|Line Numbers
  1. DELETE tblDailyQuantities.QuantityID
  2. FROM tblDailyQuantities
  3. WHERE ((tblDailyQuantities.QuantityID)
  4.    IN (SELECT tblDailyQuantities.QuantityID
  5. FROM tblDailyQuantities 
  6.    LEFT JOIN qryTimesheetCostCodesDisplay 
  7.       ON tblDailyQuantities.[CostCode] 
  8.          = qryTimesheetCostCodesDisplay.[CostCode]
  9.    AND  tblDailyQuantities.[TimesheetID] 
  10.          = qryTimesheetCostCodesDisplay.[TimeSheetID]
  11. WHERE (((qryTimesheetCostCodesDisplay.CostCode) 
  12.    Is Null))));

Also, attached is a quick look at the main form for better perspective on the issue

Attached Images
File Type: jpg frmTimesheets.jpg (31.4 KB, 352 views)
Jun 17 '14 #1
15 1281
twinnyfo
3,653 Expert Mod 2GB
Beaudry93,

First, you cannot delete one field in a DELETE query, you can only delete the entire record. So, the first line of your DELETE query will not work. The First line should be:

Expand|Select|Wrap|Line Numbers
  1. DELETE tblDailyQuantities.*
The Query Design View, should also show whether or not you've joined your query correctly. I use several dozen Delete queries in my DB, but none of them have left Joins, which can be tricky to use.

However, that aside, I would probably delete using a Recordset in VBA. Just search for the records that "positively match" certain criteria, rather than searching for recordsd that don't match in a Left Join.
Jun 18 '14 #2
Now is there a proper syntax to match 2 or more criterias in just one statement or would I have to write multiple IF statements?
Jun 18 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Perhaps the best way would be to write a separate Query that identifies your desired QuantityIDs, which I think you have that so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT tblDailyQuantities.QuantityID 
  2. FROM tblDailyQuantities LEFT JOIN qryTimesheetCostCodesDisplay ON tblDailyQuantities.[CostCode] = qryTimesheetCostCodesDisplay.[CostCode] AND tblDailyQuantities.[TimesheetID] = qryTimesheetCostCodesDisplay.[TimeSheetID] 
  3. WHERE qryTimesheetCostCodesDisplay.CostCode Is Null
You could save this a qryDeleteQuantityIDs.

Then your code to delete would look similar to this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub DeleteQuantityIDs()
  2.     Dim db As Database
  3.     Dim rst1 As Recordset
  4.     Dim rst2 As Recordset
  5.     Dim strSQL As String
  6.     Set db = CurrentDb()
  7.     strSQL = "SELECT * FROM qryDeleteQuantityIDs;"
  8.     Set rst1 = db.OpenRecordset(strSQL, dbOpenDynaset)
  9.     If Not rst1.EOF Then
  10.         rst1.MoveFirst
  11.         Do While Not rst1.EOF
  12.             strSQL = "SELECT * FROM tblDailyQuantities " & _
  13.                 "WHERE QuantityID = " & rst1!QuantityID & ";"
  14.             Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
  15.             If Not rst2.EOF Then
  16.                 rst2.MoveFirst
  17.                 With rst2
  18.                     .Delete
  19.                     .MoveNext
  20.                 End With
  21.             End If
  22.             rst1.MoveNext
  23.         Loop
  24.     End If
  25. End Sub
Using this method, we avoid the possible problems with an non-updateable query, which can often happen with some types of joins. We first get the list of QuantityIDs that should be deleted (becuase they have no corresponding record), and use that list to directly access the record in tblDailyQuantities.

Please let me know if this hepps or if you run across any other snags.
Jun 18 '14 #4
zmbd
5,501 Expert Mod 4TB
It sounds as if you are storing the same data multiple times and if so then the database may need some optimization. Please read thru the following article:[*]> Database Normalization and Table Structures.

You really shouldn't need to be moving data between tables, instead you should be able to pull the required data from the tables into queries and manipulate the data there.
Jun 18 '14 #5
Hi zmbd,

The database that I am trying to create for my supervisor goes way over my head based on the Access/VBA/SQL knowledge I have in programming and the database has a crazy amount of relantionships everywhere. I have so many different levels of tables that have relantionships between each other that I'm really only looking for quick fixes because I wouldn't know where to restart given the deadline I have for this database to be running.
Jun 18 '14 #6
Hi twinnyfo,

I tried incorporating your codes into my database and its returning Run-time error '3061' (Too few parameters. Expected1.) I beleive this is possibly linked to the fact that I added an extra parameter in the querry "tblDailyQuantities.TimesheetID)=[forms]![frmTimesheets]![TimesheetID]"

Should I incorporate it in the Recordset code instead? And if so, how should it go?
Jun 18 '14 #7
twinnyfo
3,653 Expert Mod 2GB
While line are you getting that error on?

First step may be to determine if the Query qryDeleteQuantityIDs returns the records you want. It's just very difficult to follow exactly what you are trying to do.

The best way to trouble shoot is take a look at each step along the way and determine where the break happens. Your "extra paramenter" of "tblDailyQuantities.TimesheetID=[forms]![frmTimesheets]![TimesheetID]" is now also new to the discussion. Joins can be tricky when writing off the cuff.

I understand, also, the quick-fix issue. I know you just want it to work right now, but Z is correct, if the db is built properly the first time, mods and changes run much more smoothly in the future.

We'll do what we can to walk you through this, but realize that we don't have your system in front of us.
Jun 18 '14 #8
No I definitely agree with both that doing it correctly the first time would be the proper way of engineering the database but I have been building this database from no knowledge of both access and SQL and been only relying on these forums and youtube tutorials. ha

2) the querry returns exactly what needs to be deleted, 'it being all the changed or deleted CostCodes from the 3 subforms in the Active Timesheet' and the error goes off at 'line 8'.

the link below is why I beleive it has something to do with the added parameter:

http://support.microsoft.com/kb/209203
Jun 18 '14 #9
twinnyfo
3,653 Expert Mod 2GB
Try using TempVars (Thanks Z) in your current module when the TimeSheetID is known:

Expand|Select|Wrap|Line Numbers
  1. TempVars.Add "tvTimesheetID", [forms]![frmTimesheets]![TimesheetID]
Then, in your query you can use it this way:

Expand|Select|Wrap|Line Numbers
  1. tblDailyQuantities.TimesheetID = TempVars("tvTimesheetID")
Not sure if this will work, but it just may.......

Let me know if it hepps!
Jun 19 '14 #10
After multiple changing and re-arrangement your proposition, I finally got it to work.

Here is the final querry and module code

DqryUpdateQuantities
Expand|Select|Wrap|Line Numbers
  1. SELECT tblDailyQuantities.*
  2.  
  3. FROM tblDailyQuantities LEFT JOIN qryTimesheetCostCodesDisplay ON (tblDailyQuantities.[CostCode] = qryTimesheetCostCodesDisplay.[CostCode]) AND (tblDailyQuantities.[TimesheetID] = qryTimesheetCostCodesDisplay.[TimeSheetID])
  4.  
  5. WHERE (((qryTimesheetCostCodesDisplay.CostCode) Is Null) AND ((tblDailyQuantities.TimesheetID)=[Forms]![frmTimesheets]![TimesheetID]));
  6.  
VBA Code
Expand|Select|Wrap|Line Numbers
  1. Function UpdateDailyQuantities()
  2.  
  3. Dim db As Database
  4. Dim rst1 As Recordset
  5. Dim rst2 As Recordset
  6. Dim strSQL As String
  7. Dim qd As QueryDef
  8.  
  9. Set db = CurrentDb()
  10. Set qd = db.QueryDefs("DqryUpdateQuantities")
  11.  
  12. qd.Parameters(0) = [Forms]![frmTimesheets]![TimesheetID]
  13.  
  14.  
  15. Set rst1 = qd.OpenRecordset
  16.  
  17.     If Not rst1.EOF Then
  18.         rst1.MoveFirst
  19.  
  20.         Do While Not rst1.EOF
  21.             strSQL = "SELECT * FROM tblDailyQuantities " & _
  22.                 "WHERE QuantityID = " & rst1!QuantityID & ";"
  23.  
  24.             Set rst2 = db.OpenRecordset(strSQL, dbOpenDynaset)
  25.  
  26.             If Not rst2.EOF Then
  27.                 rst2.MoveFirst
  28.  
  29.                     With rst2
  30.  
  31.                         .Delete
  32.                             .MoveNext
  33.  
  34.                     End With
  35.             End If
  36.  
  37.         rst1.MoveNext
  38.  
  39.         Loop
  40.  
  41.     End If
  42.  
  43. End Function
  44.  
  45.  
Another quick question though, how come Access does not consider the action of deleting a record in the AfterUdpadte(). As in, when I put this macro in the AfterUpdate event of the form, it does not run when I delete a record
Jun 19 '14 #11
twinnyfo
3,653 Expert Mod 2GB
I am not certain about this, but I don't believe the Form's AfterUpdate event fires when you delete a record, only after you update any of the data in any of the controls on a Form.

I would add our (now) working code, after your "Delete Record" code. That way, this will fire only when you delete a record (which is the only time I think you want it to run, correct?).

Hope this hepps!
Jun 19 '14 #12
Yes that is correct. It is NOT firing when you delete a record.

I would need it also when the user were to change the CostCode only in the previous subforms also. Therefore, I should keep the code in the AfterUpdate and add it as well in the OnDelete?
Jun 19 '14 #13
twinnyfo
3,653 Expert Mod 2GB
Since it is a function, yes, just call that function from those two subs. That should work.
Jun 19 '14 #14
Thanks again twinnyfo for all your help.

Can't promise you it's going to be the last one though ha.

Cheers
Jun 19 '14 #15
twinnyfo
3,653 Expert Mod 2GB
We'll do our best to keep you up and running!
Jun 19 '14 #16

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: r | last post by:
I am trying to do a delete query like this one: DELETE FROM pending WHERE pages.url=pending.url AND pages.date=pending.date; If I run it (via PHP) I get the following error: Unknown table...
2
by: Joe Del Medico | last post by:
I have two tables A & B and I want to delete all the records in A that are not in B. Can I do this in the query builder? It seems like a simple problem. I can easily find the records in A that...
1
by: Mark Reed | last post by:
Hi All, I have a table with a date field and 6 number fields. The number fields are not in numerical order across the fields and I want number 1 to be the lowest number and number 6 to be the...
6
by: Mark Reed | last post by:
Hi all, Please help. I have a table with 2 fields of which I am trying to change a select query into a delete query. the select query is: SELECT Table1.Date, Min(Table1.Ball) AS MinOfBall...
2
by: Dalan | last post by:
Having searched the archives regarding a Delete Query, I found nothing specific to my need, although there seems to be a plethora of ideas and suggestions on queries in general. I have used Delete...
14
by: Darin | last post by:
I have a table that I want to delete specific records from based on data in other tables. I'm more familiar with Access '97, but am now using 2003, but the database is in 2000 format. In '97, I...
13
by: forbes | last post by:
Hi, I have a user that used the Query Wizard to create a query in Access. Now she claims that her master table is missing all the data that was excluded from the query. Can you create anything...
1
by: JC | last post by:
Hello, I am trying to change a select, find duplicates, query into a delete query. I want to get rid of the records in the main table "tblHurnsHistory." I changed the Find Duplicates query to...
3
by: Kevin M | last post by:
I have one table and have created a form from that table. Also, I have created a delete query. I want to but a button on the form which will delete all records from the table; however, I cannot get...
1
by: austin1539 | last post by:
-Access 2002 -Windows XP Pro Trying to run a DELETE Query to delete each entry in the table 'Data' where the field 'AssocID' matches the field 'AssocID' in the table 'Queries' and the date is...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.