DoCmd.RunSQL 1

[sply]

Hi

Please excuse me but I'm new to this tool, as you will soon appreciate by my question. I want to do the following command

delete from
Table1
where
Table1.film_fk = forms!this_form!film_fk

on the Sub OK_Click() event of a command button, where
forms!this_form!film_fk is a text box on the current form that is a display of a field in the returned dataset of a query.

This should be extremely basic I know, but I've tried DoCmd.RunSQL, and I've tried something.execute I think, and I've tried referring to text box contents in the long hand (as above) and also in the short hand as just
film_fk and I've tried using & to denote variable rather than variable_name and I've followed the help specifically as to how to place the arguments for the command, but I just cant make it do this simple command. What am I doing wrong exactly? Should I be using DoCmd to execute SQL based on an argument present in the control source of the form?

Any Help greatly appreciated.

My Regards

Sply

 

[hsitz]

I take it that you're issuing the command:

DoCmd.RunSQL "DELETE FROM Table1. . . "

The problem is that the SQL engine cannot resolve the final reference to forms!this_form!film_fk. So you have to construct a string that resolves this reference BEFORE you issue it in the the RunSQL command:

dim strSQL as string
strSQL = "DELETE FROM Table1 ... " & forms!this_form!film_fk
DoCmd.RunSQL strSQL

This is a technique that has to be used all the time, since although VBA can resolve references to variables and/or values on forms, the engine that runs the SQL commands can't. It will become natural once you do it a few times.

Make sure that you're putting a space in between the strings you're concatenating into strSQL; that's one possible source of errors.

 

[sply]

thanks hsitz - thats great, Delphi comes leaping back from the far flung caverns of the mind !!!

Can you tell me then how to stop access asking me if I want to delete the record Ive just specifically asked it to delete? (You are about to delete 1 row etc etc)

Thanks again

Sply

 

[THoey]

Sply,

Look in the help for SETWARNINGS. Make sure you turn the warnings back on once you have done your delete...

HTH Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.