delete records via code 1

[ninash]

Hi All,

I have posed this question before but have not had a working answer as yet please some one help

I need to select a record set using criteria then delete the records from the table.

My code line I am using is as follows
CurrentDb.Execute "DELETE * FROM [RiskCodes with ReportKey] WHERE ReportKey = me![ReportKey];"

But I get the following error

Too Few Parameters. Expected 1

Both fields are text fields and I am suspecting it is just a formating problem.

If there anyone that can help me

Tony

 

[FancyPrairie]

I believe your code should look like this:

CurrentDb.Execute "DELETE * FROM [RiskCodes with ReportKey] WHERE ReportKey = " & me![ReportKey] & ";"

However, if ReportKey is text rather than a number, then your code should look like this:

CurrentDb.Execute "DELETE * FROM [RiskCodes with ReportKey] WHERE ReportKey = '" & me![ReportKey] & "';"

 

[ninash]

Hi FancyPrairie,

You are a very handy person to know
Thanks for that. Do you have any tips for being able to remember how the formating goes for lines like that one, I always can't remember how to do it correctly.

By the way have a star

Thanks again
Tony

 

[FancyPrairie]

One thing to remember is that Access treats everything within the double quotes as it appears. So, in your orginal post (ReportKey = me![ReportKey]), it tryed to look for me![ReportKey] rather than the value of me![ReportKey].

Another thing you should know (if you don't already) is how to use the debugger (or in this case a message box would have sufficed). You would have seen your problem. For example, had you written your code (for testing purposes only) as this:
Dim strTemp as string

strTemp = "DELETE * FROM [RiskCodes with ReportKey] WHERE ReportKey = me![ReportKey];"
msgbox strTemp
CurrentDb.Execute strTemp

Prior to executing "CurrentDb.Execute" you would have seen what Access was actually attemping to execute via the value of strTemp.

Another thing I do is use the Query Builder to initially build my query statement. Once I have it working like I want, I view the query in SQL view and copy and paste it in my code.

To get started using the debugger, you only need to know a couple things. To pause executing your code at a certain line of code, either put the cursor on that line and toggle the breakpoint button on/off, or put the word Stop above the line. Then execute your code. The program will pause execution of your code when it encounters the breakpoint or a line containing the Stop statement. Once paused, you can examine variables within your code by either placing the cursor over the value you want to examine (i.e. like a tooltip) or typing (for example) ?strTemp in the Debugger's Immediate window. After examining your values, press F8 to execute your code one line at a time. Press F5 to execute your code until it finds the next breakpoint or stop statement.