Hello,
i am currently trying to write a macro with access2base that reads the current value of a textfield “IDTEST” and then executes an INSERT INTO SELECT STATEMENT using the current value of this field. I can querry the field by
Forms(“Main”).Controls(“IDTEST”).Value
but how can I use this value in an SQL statement?
Thanks in advance!
Assign the value of the textfield to a variable then use this variable in the SQL statement. I am assuming you are running the SQL in the macro.
EDITED 24/02/2016
I don’t know if the syntax and punctuation is different in Access to Base but in standard Basic in Base the code snippet below adds a record to the table Table1 where the Name field value is in the variable strnew so it will contain New Name…
strnew="New Name"
strSQL="INSERT INTO ""Table1"" (""Name"") VALUES ('" + strnew + "')"
If the value was (‘strnew’) then the Name field would contain strnew, the name of the variable. To get the contents of the variable requires the value to be ("’ + strnew + '").
Looking at the info at Use Variables in SQL. this might work -
DoCmd.RunSQL("Insert Into tantworten(IDGRUPPE,IDFRAGE,IDTESTUNG) Select " & Wert & " as IDTESTUNG, tfragen.IDGRUPPE, tfragen.IDFRAGE from tfragen")How? The basic variable is not regonized as correct syntax
Sub Value Wert = Forms(“Festungen”).Controls(“IDTESTUNGEN”).Value
DoCmd.RunSQL(“Insert Into tantworten(IDGRUPPE,IDFRAGE,IDTESTUNG) Select Wert as IDTESTUNG, tfragen.IDGRUPPE, tfragen.IDFRAGE from tfragen”) END Sub
Thanks to both of you. It worked like a charm!
Thank you very much!
A word of caution: be very careful using string variables in the middle of an SQL statements.
You should ALWAYS test that the variable you are using does not contain either a single or double quote character.
because if it does … the SQL will not work!
You can use the runtime REPLACE function to replace the single or double quote character with TWO of the same and that way your SQL will work.