Running SQL from an Access module

[MitchJP]

OK, I need to delete some records when a form is closed.

When the form is closed, I have a module run, which will inlude the code to delete the records.

The records to be deleted depend on what the user entered into a certain field on the form (say the form field is called "Number&quot. So if they entered in 1111 on the form, then records matching 1111 would get deleted.

This is what I have, but it does not currently work.

************
Public Function Function_Name()

strSQL = "DELETE TableName.* FROM TableName WHERE TableName.FieldName=" & Me!Number.Value & " WITH OWNERACCESS OPTION;"

DoCmd.RunSQL strSQL

End Function
**********************

It errors out stating that an Object is needed for strSQL.

Any help would be apprecaited..

MitchJP

 

[MitchJP]

OK, instead of running the code from a seperate module, I'm running it from the Form code itself.

It works, but for some reason it is not pulling the Trend Number from the field. It prompts me to enter it (as a Prameter).

I have:
******
Private Sub CommandSave_Click()

strTrendNumber = Me![Trend Number].Value

strSQL = "DELETE [Trend(Level/Release/Revision)].* FROM [Trend(Level/Release/Revision)] WHERE [Trend(Level/Release/Revision)].[Trend Number]=" & strTrendNumber & " WITH OWNERACCESS OPTION;"
DoCmd.RunSQL strSQL

End Sub
**********

Again it works, but it prompts me for Trend Number (once I enter it, the SQL runs fine). I want it to pull the value in the 'Trend Number' field on the form though... any ideas?

Thanks!
MitchJP

 

[RoyVidar]

What datatype is the trendnumber field. Just guessing, but from the declaration of the variable, it seems it's a string, then you'll need text qualifiers (apostrophe - see highlighted characters below):

[tt]...[Trend Number]=[highlight]'[/highlight]" & strTrendNumber & "[highlight]'[/highlight] WITH... [/tt]

BTW - you have declared both variables?
[tt]dim strTrendNumber as String
dim strSQL as String[/tt]

Roy-Vidar