By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,516 Members | 1,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,516 IT Pros & Developers. It's quick & easy.

Delete Query for subforms

P: 14
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, 225 views)
Jun 17 '14 #1
Share this Question
Share on Google+
15 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
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

P: 14
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
Expert Mod 2.5K+
P: 3,482
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
Expert Mod 5K+
P: 5,397
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

P: 14
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

P: 14
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
Expert Mod 2.5K+
P: 3,482
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

P: 14
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
Expert Mod 2.5K+
P: 3,482
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

P: 14
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
Expert Mod 2.5K+
P: 3,482
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

P: 14
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
Expert Mod 2.5K+
P: 3,482
Since it is a function, yes, just call that function from those two subs. That should work.
Jun 19 '14 #14

P: 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
Expert Mod 2.5K+
P: 3,482
We'll do our best to keep you up and running!
Jun 19 '14 #16

Post your reply

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