I'm trying now to implement the UseVariablesInSQL as described in the http://www.access2base.com/access2base. ... lesInSQL]].
Code: Select all
Sub updateArt_click(e as Object)
Dim rs As Object
Dim frm As Object
Dim sName As String 'search Parameter
Set frm = Application.Forms("frm_Etiketten_drucken")
sName = frm.Controls("cmbSorte").Value
MsgBox sName 'shows the selected value from cmbSorte
Set rs = Application.CurrentDb().RunSQL("SELECT art FROM artikel WHERE sorte_de = " & sName )
MsgBox rs 'Returns Error#1523
'further code will follow, when the RunSQL-code works
End Sub
Code: Select all
Error #1523 (SQL Error, SQL statement = 'SELECT art FROM artikel WHERE sorte_de = Bittenfelder') aufgetreten in ein Funktionsaufruf 'Database.RunSQL'
In the original description on access2base the following code is used to add quotes :
Code: Select all
Sub updateArt_click(e as Object)
Dim rs As Object
Dim frm As Object
Dim sName As String 'search Parameter
Const cstQuote = "'" ' Single quote
Set frm = Application.Forms("frm_Etiketten_drucken")
sName = Join(Split(frm.Controls("cmbSorte").Value, cstQuote), cstQuote & cstQuote) 'Replace single ' by ' '
MsgBox sName 'shows the selected value from cmbSorte
Set rs = Application.CurrentDb().RunSQL("SELECT art FROM artikel WHERE sorte_de = " & cstQuote & sName & cstQuote)
MsgBox rs
'further code will follow, when the RunSQL-code works
End Sub
I've tried already all possible variations to quote out the string, but without success.