Delete Query with VBA

[fatboy95]

found my problem!

I have a form that has a subform on it that contains my data I am working with. For the most part the data is based on one table (tblMissile). I am creating another table that uses the ID from tblMissile in tblMPartOnOrderSort. I added both tables to my relationships and set cascade update/delete. I have been trying to use query's, sql, and just use the delete button in access so that I may be able to delete any data that I don't need. The problem is that when I run the delete off of the form it removes the data from the form but doesn't take it from the database like I want it to. I removed the data field from tblMPartOnOrderSort and the delete works fine. I really don't know where to go with this. I am posting the following sql to show you what I am doing so that hopefully this all makes since.

Dim strSQL as String

strSQL = DELETE "tblMissile.MissileID, tblMissile.MissielTypeID, tblMissile.MLocationID, tblMissile.StatusID, tblMissile.MissileNum, tblMissile.Pri, tblMissile.EngDate, tblMissile.INEDate, tblMissile.DiscA, tblMissile.DiscB, tblMissile.DiscC, tblMissile.MNote" & _
"FROM tblMissile" & _
"WHERE tblMissile.MissileID)=forms!qryMType![qryMissileInfo Subform];"

docmd.runsql (strSQL)

the table that is linked to this is called tblMPartOnOrderSort and the only data that is in it is the MissileID and ComboPartName. I set the link to allow all data on tblMissile and only data that join fields are equal on tblMPartOnOrderSort.

Sorry to post so much but this is the last thing I need to get working to get my database to be operational.

Thanks in advance.


I changed the code to look like this:

Dim strSQL as String

strSQL = DELETE * "FROM tblMissile WHERE tblMissile.MissileID=forms!qryMType![qryMissileInfo Subform]![MissileID];"

docmd.runsql (strSQL)

I forgot to add the MissileID onto the end of the strSQL statement. It all works fine now.

 

[Mile-O]

Dim strSQL as String

strSQL = DELETE "tblMissile.MissileID, tblMissile.MissielTypeID, tblMissile.MLocationID, tblMissile.StatusID, tblMissile.MissileNum, tblMissile.Pri, tblMissile.EngDate, tblMissile.INEDate, tblMissile.DiscA, tblMissile.DiscB, tblMissile.DiscC, tblMissile.MNote" & _
"FROM tblMissile" & _
"WHERE tblMissile.MissileID)=forms!qryMType![qryMissileInfo Subform];"

docmd.runsql (strSQL)

Well, that won't work.

Reason 1

DELETE is part of the SQL and needs to be enclosed within the string.

Therefore:

strSQl = "DELETE tblMissile.MissileID......."

Reason 2

tblMissile.DiscC, tblMissile.MNote" & _
"FROM tblMissile" & _


When that whole string is concatenated it will read like this:

tblMissile.DiscC, tblMissile.MNoteFROM tblMissile


Note the lack of a space between .MNoteFROM . The FROM keyword won't be differentiated. You make this mistake on two lines.


Reason 3

WHERE tblMissile.MissileID)=

There's an erroneous bracket here. It must close something but there's nothing open. It can be removed.

Dim strSQL as String

strSQL = "DELETE tblMissile.MissileID, tblMissile.MissielTypeID, tblMissile.MLocationID, tblMissile.StatusID, tblMissile.MissileNum, tblMissile.Pri, tblMissile.EngDate, tblMissile.INEDate, tblMissile.DiscA, tblMissile.DiscB, tblMissile.DiscC, tblMissile.MNote " & _
"FROM tblMissile " & _
"WHERE tblMissile.MissileID=Forms!qryMType![qryMissileInfo Subform];"

DoCmd.RunSQL strSQL

As a further note, why do you have a form called qryMType? The qry prefix is used for queries. frm is preferred for forms.

Further more, there's no need to build this DELETE query in VBA anyway. A standard query (as this is not dynamic) will be faster and more appropriate.

And next time, please give your post a more descriptive title as found my problem don't know how to fix it is not descriptive and rather annoying.

 

[namliam]

I just wanted to add 1 thing....

you can use Delete * from .... where ....

instead of naming all them fields.....

Regards

 

[Mile-O]

That can be Reason 4.

 

[namliam]

It should work tho using names, its just that its a b*tch if your reading code like that....

I am working on a couple of dbs right now that are awfull, unstructured etc...
Lost of Dim x
no as String for example... no DAO.Recordset Headaches.... migranes even, (lucky me i dont get them)

I believe in KISS... So use Delete * ... and such alike (like deleting the brackets where not needed and/or deleting the explicit tablenames Access is allways adding when taking a lazy copy off the SQL builder)

BR

 

[fatboy95]

Sorry for being annoying... I was really tired when I posted this last nite.

Only reason the forms has qry at the beginning is I used autoform to create the form. I know I should change it but this form is just the one I am playing around with to try and get things to work.

I guess the website that I read on about sql was incorrect.
http://www.fontstuff.com/access/acctut15.htm#5mincourse

I did create a standard delete query and it works fine if I run it by itself but when ever I attach it to a button on the form it does not delete the record from the database. This is why I was trying it by SQL in VBA.

Again thanks for the help.

 

[Mile-O]

I've never liked that site. A lot of his solutions are imperfect, longwinded, or advise bad habits.

If you can, upload an example of where you've got to and I'll have a look at it.