How do I convert Delete query sql to VBA sql

[oxicottin]

Hello, I created the below delete query to delete records in my subform and I want to use it frolm a button on my form and change it to vba sql, basicly thats why I used "Me.". Anyway, how is this done? I tried and i get a to few parameters error. Is there a good tutorial on what quotation you take out and what to leave or a free online converter that does it for you? Thanks!



DELETE tblsub_RejectedHoldData.HoldID, tblsub_RejectedHoldData.RejectLotNumber, *
FROM tblsub_RejectedHoldData
WHERE (((tblsub_RejectedHoldData.HoldID)=[me].[txtHoldID]) AND ((tblsub_RejectedHoldData.RejectLotNumber)=[me].[txtLotNumber]));

[alansidman]

I use a form that I put in each db that does it for me.

Here is the result


strSql = "DELETE tblsub_RejectedHoldData.HoldID, tblsub_RejectedHoldData.RejectLotNumber, * " & vbCrLf & _
"FROM tblsub_RejectedHoldData " & vbCrLf & _
"WHERE (((tblsub_RejectedHoldData.HoldID)=[me].[txtHoldID]) AND ((tblsub_RejectedHoldData.RejectLotNumber)=[me].[txtLotNumber]));"


And here is the db with only the form in it for you to experiment with.

[oxicottin]

I get an error 3061 to few peramaters expected 2.

I cut down the sintax to:


strSql = "DELETE * FROM tblsub_RejectedHoldData WHERE ((tblsub_RejectedHoldData.HoldID = me.txtHoldID) AND (tblsub_RejectedHoldData.RejectLotNumber = me.txtLotNumber))"


And I tried it alansidmans way and got the same error. Thoughts?

[HiTechCoach]

Hello, I created the below delete query to delete records in my subform and I want to use it frolm a button on my form and change it to vba sql, basicly thats why I used "Me.". Anyway, how is this done? I tried and i get a to few parameters error. Is there a good tutorial on what quotation you take out and what to leave or a free online converter that does it for you? Thanks!



DELETE tblsub_RejectedHoldData.HoldID, tblsub_RejectedHoldData.RejectLotNumber, *
FROM tblsub_RejectedHoldData
WHERE (((tblsub_RejectedHoldData.HoldID)=[me].[txtHoldID]) AND ((tblsub_RejectedHoldData.RejectLotNumber)=[me].[txtLotNumber]));


If you are using Access 2007 and later you have another option. Use the new TempVars collection. It allows you to easily pass form values (Me.txtControlName) to a query.


Save your query like this:

DELETE tblsub_RejectedHoldData.HoldID, tblsub_RejectedHoldData.RejectLotNumber, *
FROM tblsub_RejectedHoldData
WHERE (((tblsub_RejectedHoldData.HoldID)=[me].[txtHoldID]) AND ((tblsub_RejectedHoldData.RejectLotNumber)=TempVars!RejectLotNumber));


Your VBA code for the command button would look like this:




' Store the Reject Lot Number in a TempVar to pass to queryTempVars.Add "RejectLotNumber", Val(Me.txtLotNumber)



' RunDelete Query
CurrentDB.Execute "qyrEmpty_tblsub_RejectedHoldData"



see:
Power Tip: Maximize the use of TempVars in Access 2007 and 2010 (https://blogs.office.com/2010/09/27/power-tip-maximize-the-use-of-tempvars-in-access-2007-and-2010/)

[oxicottin]

After reading everywhere and messing with the SQL I finally got it to work...


strSql = "DELETE * FROM tblsub_RejectedHoldData WHERE [HoldID] = " & Me.txtSubHoldID & " AND [RejectLotNumber] = '" & Me.txtLotNumber & "';"

[HiTechCoach]

Thanks for the update and sharing your solution!