0

I'm looking for help with my error on the last DoCmd.RunSQL... I need user input to define the values but after the user input the last RunSQL does not properly run and gives me an error or improperly deletes my data.

Private Sub AddModNote_Click()
     Set db = CurrentDb()
     Dim ModNo As String
     Dim ModNoT As String
     Dim ModT As String
     Dim UpQ As String
     Dim ModNum As String
     Dim SecMod As String
     Dim Equip As String
    
    
    ModNo = (InputBox("Enter in format : MOD XXX", "What is your MOD Number?"))
    ModNum = " [" & ModNo & "]"
    
    DoCmd.RunSQL "SELECT SITELIST.id, SITELIST.EQP, SITELIST.[SITE NAME], SITELIST.[ORG CODE], SITELIST.SID INTO" & [ModNum] & " FROM SITELIST;"

     UpQ = "UPDATE " & [ModNum] & " SET " & [ModNum] & ".[MAT_MOD_NO]= '" & (ModNo) & "'"
     DoCmd.RunSQL UpQ
   
    'rename field names
    db.TableDefs(ModNo).Fields("id").Name = "SITELIST ID"
    db.TableDefs(ModNo).Fields("EQP").Name = "EQUIPMENT AFFECTED"
    db.TableDefs(ModNo).Fields("SITE NAME").Name = "SITE"
    db.TableDefs(ModNo).Fields("ORG CODE").Name = "ORG_CODE"


    'Alter Column
    CurrentDb.Execute "ALTER TABLE " & ModNum & " ALTER COLUMN [EQUIPMENT AFFECTED] Text(5)"
    CurrentDb.Execute "ALTER TABLE " & ModNum & " ALTER COLUMN [SITE] Text(50)"
    CurrentDb.Execute "ALTER TABLE " & ModNum & " ALTER COLUMN [ORG_CODE] Text(10)"
    CurrentDb.Execute "ALTER TABLE " & ModNum & " ALTER COLUMN SID Text(5)"
    CurrentDb.Execute "ALTER TABLE " & ModNum & " ALTER COLUMN MAT_MOD_NO Text(7)"
      
        EqpEntry = (InputBox("What is your Equipment Affected?"))
        Equip = "'" & EqpEntry & "'"
   
        DoCmd.RunSQL "Delete " & [ModNum] & ".[SITELIST ID], " & [ModNum] & ".[EQUIPMENT AFFECTED], " & [ModNum] & ".SITE, " & [ModNum] & ".ORG_CODE, " & [ModNum] & ".SID, " & [ModNum] & ".MAT_MOD_NO FROM " & [ModNum] & " WHERE [MOD 556].[EQUIPMENT AFFECTED]<> '" & Equip & "'"

I've altered the code in numerous ways at one point I believed to have it correct but I that's clearly not the case.

Any help helps! Thank you

JNevill
  • 46,980
  • 4
  • 38
  • 63
Joe Beck
  • 3
  • 4
  • 2
    When you set `equip` you add single quotes to it. The string looks like `'something'`. In your sql, you then add two more single quotes to it in the code `" WHERE [MOD 556].[EQUIPMENT AFFECTED]<> '" & Equip & "'"` which will read `" WHERE [MOD 556].[EQUIPMENT AFFECTED]<> ''something''"` and that is likely throwing an error. – JNevill Aug 28 '23 at 13:04
  • Thank you, That solved my problem. I feel ridiculous! – Joe Beck Aug 28 '23 at 13:12
  • Code-inception (so to speak) tricks us all. No reason to feel bad. – JNevill Aug 28 '23 at 13:15
  • For future reference: [How to debug dynamic SQL in VBA](https://stackoverflow.com/a/1099570/3820271) – Andre Aug 28 '23 at 13:46

0 Answers0