Runtime Error 3075 while excution vba query ms access

Question

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

Edit question to show the exact error message. Is PONumber a number type field? If not, need apostrophe delimiters: `='" & x & "')`. Or reference PONumber parameter same way as the other parameters. — June7, Mar 15 '21 at 11:18
PoNumber Field Is number , About the error message it’s gone after i added dim x as integer the declaration was in wrong place in the code thanks — Csharp Newbie, Mar 15 '21 at 11:38
Does this answer your question? [Microsoft Access VBA - Run time error '3075'](https://stackoverflow.com/questions/14064203/microsoft-access-vba-run-time-error-3075) — Absinthe, Mar 15 '21 at 13:24

score 1 · Answer 1 · answered Mar 15 '21 at 21:47

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"

score 0 · Answer 2 · answered Mar 15 '21 at 11:38

0

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

answered Mar 15 '21 at 11:38

Csharp Newbie

303
1
10

Runtime Error 3075 while excution vba query ms access

2 Answers2