VBA/SQL issue access

Question

I keep recieving compiling errors saying that txtlln in the where line cannot be found. I am fairly new to SQL/VBA so I am not sure I am using the correct expressions to have this work.

Private Sub btnlledit_Click()
Dim strSQL As String
SQL = "UPDATE tblll " & _
      "SET [Component/Product] = '" & Forms!frmaddll!txtllcomponent & "',[HN] = '" & Forms!frmaddll!txtllhn & "' " & _          
      "WHERE [LLN] = '" & Forms!frmaddll!txtlln.value & "';"
debug.print sql
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.Requery

Me.Refresh 
End Sub

Can you try to add a .Value to the end of Forms!frmaddll!txtlln? — Ian-Fogelman
– Ian-Fogelman, Commented Jul 14, 2018 at 21:16
added .value now I recieve the error Sub or Function not defined for Where LLN = Forms!frmaddll!txtlln.Value — Kewl147
– Kewl147, Commented Jul 14, 2018 at 21:39
You seem to be using & "" instead of & "' in your string concatenation. — user4039065
– user4039065, Commented Jul 14, 2018 at 21:49

user4039065 · Accepted Answer · 2018-07-14 21:55:03Z

2

You seem to have a few issues swith your string concatenation.

Private Sub btnlledit_Click()
    Dim strSQL As String

    SQL = "UPDATE tblll " & _
          "SET [Component/Product] = '" & Forms!frmaddll!txtllcomponent & "' " & _
          "WHERE [LLN] = '" & Forms!frmaddll!txtlln.value & "';"
    debug.print sql
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    DoCmd.Requery

    Me.Refresh

End Sub

answered Jul 14, 2018 at 21:55

user4039065

Sign up to request clarification or add additional context in comments.

5 Comments

Kewl147 Over a year ago

what does the debug.print sql do? I haven't inputted the code yet because I went home and I am going to try it tomorrow.

user4039065 Over a year ago

Please see vba immediate window.

user4039065 Over a year ago

btw, it's pronounced 'kuh-ool' to the best of my knowledge.

Kewl147 Over a year ago

That code works great, however I tried to copy/past set for other lines of input because of have multiple things to update and put in difference places from the form for the table to use and I am getting syntax errors.

Kewl147 Over a year ago

nvm, i figured it out for the multiple updates! Thank you for the sub.

Collectives™ on Stack Overflow

VBA/SQL issue access

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related