SQL - INSERT INTO access2003

[logo3]

hello,

I'm trying to fill the table named "ALBARANES" which is based on a query. The query use table "structureO K". There are 3 additional values should be updated.

[date] AS Expr2
[Subject] AS Expr3
[Text] AS Expr1

I did this in access2003 and it works but each time I start the query I will be asked to enter a date, subject and text.

Now, I have created a form with textboxes, one for Date, one for Subject and one for text.

by starting the query instead of asking me to enter some values The query should take the data from the texboxes.
Therefore, as you can see below I defined 3 variables as string

str_text;
str_subject;
str_date

I getting in troulble if I try to change the SQL instrucction to include those variables.

Can someone help me out by this problem.
How I have to define the SQL to take the values from the variable?
below You have the code

thanks in advance

regards
logo3
-------------------------------------------------------------------------------

Dim objDB As DAO.Database
Dim objRS As DAO.Recordset
Dim strSQL As String
 
Dim str_text As String
Dim str_subject As String
Dim str_date As String
 
 
 
 
str_text = gei_texto.Value ' to give the content of textbox (texto) to str_text
str_subject = gei_asunto.Value ' to give the content of textbox (asunto) to str_subject
str_date = gei_fecha.Value ' to give the content of textbox (fecha) to str_date
 
strSQL = "INSERT INTO ALBARANES ( [sales unit], unit, [jvperson], [jzperson], name, street, zipcode, city, city2, country, employee, phone, deliverydate, subject, Text ) SELECT structureOK.[sales unit], structureOK.unit, structureOK.[jvperson], structureOK.[jzperson], structureOK.name, structureOK.street, structureOK.[zipcode], structureOK.city, structureOK.[city2], structureOK.country, structureOK.customer, structureOK.[phone 1], [date] AS Expr2, [subject] AS Expr3, [Text] AS Expr1  FROM structureOK WHERE (((structureOK.unit)='18-000'));"
 
 
 
Set objDB = CurrentDb
 
Set objRS = objDB.OpenRecordset(strSQL, , DAO.dbSQLPassThrough)
 
 
Call objRS.Close
Set objRS = Nothing
 
Set objDB = Nothing

-------------------------------------------------------------------------

[NeoPa]

I'm afraid you've made this harder than it needs to be, but I expect that's because there are bits you don't understand too well. Not a problem.
There are two ways to do this & I will doctor your code to show my preferred way (inserting the values from the form directly into your SQL string as literal values).

Private sub Blah_blah_blah()
  Dim objDB As DAO.Database
  Dim objRS As DAO.Recordset
  Dim strSQL As String
 
  strSQL = "INSERT INTO ALBARANES (" & _
           "[sales unit], unit, [jvperson], [jzperson], name, " & _
           "street, zipcode, city, city2, country, employee, " & _
           "phone, deliverydate, subject, Text) " & _
           "SELECT [sales unit], unit, [jvperson], [jzperson], " & _
           "[name], [street], [zipcode], [city], [city2], " & _
           "country, customer, [phone 1], " & _
           Format(Me.gei_fecha,'\#m/d/yyyy\#') & ", " & _
           "'" & Me.gei_asunto & "', " & _
           "'" & Me.gei_texto & "' " & _
           "FROM structureOK " & _
           "WHERE ([unit]='18-000')"
  Set objDB = CurrentDb
  Set objRS = objDB.OpenRecordset(strSQL, , DAO.dbSQLPassThrough)
  Call objRS.Close
  Set objRS = Nothing
  Set objDB = Nothing
End Sub

[NeoPa]

Remember, the SQL command is simply stored in a string. You prepare the string in advance and if you think of the resultant SQL code as data rather than as part of the coding, it's easier to understand and avoid confusion.