Friday, March 30, 2012

Run-Time Error

Private Sub cmd_DeleteTables_Click()
MsgBox "Click yes when prompted to delete records"
Dim sqldel As String
sqldel = "DELETE tbl_physical_main.*, tbl_physical_main.[Material Number]
FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_noDataForTag.* FROM tbl_noDataForTag"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_SAPmaterials.* FROM tbl_SAPmaterials"
DoCmd.RunSQL sqldel, no
MsgBox "All previous inventory data has been deleted and the next inventory
load may continue."
End Sub
--
Run-Time Error '94'
Invalid use of Null
Please help me!

On the first delete, try removing the tbl_physical_main.[Material Number] from the delete section of the query.

BobP

|||sqldel = "DELETE FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"|||

When you come upon these types of problems, prind out the commands that you are trying to execute without executing them. Then format them in a more readable manner, like:

DELETE tbl_physical_main.*,
tbl_physical_main.[Material Number]
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag.*
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials.*
FROM tbl_SAPmaterials

Then try parsing/running them in SSMS or QA. The first problem is the bolded text. You delete from tables, not from columns. So:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials
FROM tbl_SAPmaterials

I would lose the superfluous parens too:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE tbl_physical_main.[Material Number] Is Not Null

I can't see any reason for the message (you should have gotten syntax errors) so you should be good.

No comments:

Post a Comment