CODEWITHSUNDEEP
vbams-access
Csharp Newbie
Csharp Newbie

Reputation: 323

Runtime Error 3075 while excution vba query ms access

Hi guys iam getting this error when i try to Excute RunSql method , This is my query

Dim x As Integer
Dim strSQL As String
    x = Me.PONumber
    strSQL = "DELETE TblPoMaterials.PONumber, TblPoMaterials.ProductionDate, TblPoMaterials.AcceptedNumber, TblPoMaterials.RejectedNumber, TblPoMaterials.Shift " & vbCrLf & _
    "FROM TblPoMaterials " & vbCrLf & _
    "WHERE (((TblPoMaterials.PONumber)=" & x & ") AND ((TblPoMaterials.ProductionDate)=[Forms]![FrmProductionEdit]![Zdate]) AND ((TblPoMaterials.AcceptedNumber)=[Forms]![FrmProductionEdit]![AcceptedProduct]) AND ((TblPoMaterials.RejectedNumber)=[Forms]![FrmProductionEdit]![RejectedProduct]) AND ((TblPoMaterials.Shift)=[Forms]![FrmProductionEdit]![Shift]));"
    DoCmd.RunSQL strSQL

The main idea is to pass the variable x as criteria for the previous query , I tried to use

DoCmd.OpenQuery strSQL

I know it’s not right but i just tried to see what happens , it produced a different runtime error. Thanks in advance

Upvotes: 0

Views: 74

Answers (2)

Parfait
Parfait

Reputation: 107567

Simply include the x value as parameter in your query like you do other form controls. This way you avoid any concatenation. Below assumes Me.PONumber is a control on FrmProductionEdit. If not, adjust to correct form name. Also, the columns in DELETE clause is redundant as rows are deleted according to WHERE logic regardless of listed columns. Finally, table alias is used to avoid repetition of long table name and redundant parentheses are removed.

strSQL = "DELETE " & vbCrLf & _
         "FROM TblPoMaterials t " & vbCrLf & _
         "WHERE t.PONumber = [Forms]![FrmProductionEdit]![PONumber] " & vbCrLf & _
         "  AND t.ProductionDate = [Forms]![FrmProductionEdit]![Zdate] " & vbCrLf & _
         "  AND t.AcceptedNumber = [Forms]![FrmProductionEdit]![AcceptedProduct] " & vbCrLf & _
         "  AND t.RejectedNumber = [Forms]![FrmProductionEdit]![RejectedProduct] " & vbCrLf & _
         "  AND t.Shift = [Forms]![FrmProductionEdit]![Shift];"
    
DoCmd.RunSQL strSQL

Better yet (and more efficient), save the long SQL as a stored query object without VBA grammar (&, ", _, or vbCrLf) and simply run (no need to close an action query):

DoCmd.OpenQuery "mySavedDeleteQuery"

Upvotes: 1

Csharp Newbie
Csharp Newbie

Reputation: 323

About the error message it’s gone after i added dim x as integer the declaration was in wrong place in the code thanks

Upvotes: 0

Related Questions