473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SQL - INSERT INTO access2003

1
hello,

I'm trying to fill the table named "ALBARANES" which is based on a query. The query use table "structureOK". 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
-------------------------------------------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. Dim objDB As DAO.Database
  2. Dim objRS As DAO.Recordset
  3. Dim strSQL As String
  4.  
  5. Dim str_text As String
  6. Dim str_subject As String
  7. Dim str_date As String
  8.  
  9.  
  10.  
  11.  
  12. str_text = gei_texto.Value ' to give the content of textbox (texto) to str_text
  13. str_subject = gei_asunto.Value ' to give the content of textbox (asunto) to str_subject
  14. str_date = gei_fecha.Value ' to give the content of textbox (fecha) to str_date
  15.  
  16. 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'));"
  17.  
  18.  
  19.  
  20. Set objDB = CurrentDb
  21.  
  22. Set objRS = objDB.OpenRecordset(strSQL, , DAO.dbSQLPassThrough)
  23.  
  24.  
  25. Call objRS.Close
  26. Set objRS = Nothing
  27.  
  28. Set objDB = Nothing
-------------------------------------------------------------------------
Nov 27 '07 #1
2 9570
NeoPa
32,556 Expert Mod 16PB
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).
Expand|Select|Wrap|Line Numbers
  1. Private sub Blah_blah_blah()
  2.   Dim objDB As DAO.Database
  3.   Dim objRS As DAO.Recordset
  4.   Dim strSQL As String
  5.  
  6.   strSQL = "INSERT INTO ALBARANES (" & _
  7.            "[sales unit], unit, [jvperson], [jzperson], name, " & _
  8.            "street, zipcode, city, city2, country, employee, " & _
  9.            "phone, deliverydate, subject, Text) " & _
  10.            "SELECT [sales unit], unit, [jvperson], [jzperson], " & _
  11.            "[name], [street], [zipcode], [city], [city2], " & _
  12.            "country, customer, [phone 1], " & _
  13.            Format(Me.gei_fecha,'\#m/d/yyyy\#') & ", " & _
  14.            "'" & Me.gei_asunto & "', " & _
  15.            "'" & Me.gei_texto & "' " & _
  16.            "FROM structureOK " & _
  17.            "WHERE ([unit]='18-000')"
  18.   Set objDB = CurrentDb
  19.   Set objRS = objDB.OpenRecordset(strSQL, , DAO.dbSQLPassThrough)
  20.   Call objRS.Close
  21.   Set objRS = Nothing
  22.   Set objDB = Nothing
  23. End Sub
Nov 28 '07 #2
NeoPa
32,556 Expert Mod 16PB
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.
Nov 28 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Dennis | last post by:
If I create a database in AccessXP-2000mode, can someone with Access2003 run it? Do either of us have to do anything special? Thanks! Dennis
9
by: Lauren Quantrell | last post by:
Hoping someonce can tell me what compatibility problems I might run into when I have to have my Access Project developed on Access2K run in a user environment where users are using Access2003 as...
6
by: Ryan Bounds | last post by:
Hi All We have upgraded from Access2000 to Access2003 with all service packs. The problem that we have is: When a user try's to filter a client the database crashes and close's Access2003,...
9
by: Zlatko Matić | last post by:
Hello. Could somebody explain the following situation: I have a .mdb on my notebook with Windows XP SP2 and Access 2003. I prepared ..mde and tried to use it on another computer with Windows XP...
9
by: 2D Rick | last post by:
With the help from members in the VB forum I've pieced together code that works in VB6 to create radial text similar to "text on a path" seen in graphics programs.(on a circle only) I use an...
3
by: Vera | last post by:
I built a class in VB.NET that has an overloaded constructor. It can either accept nothing, a string or an object Public Sub New( MyBase.New( End Su Public Sub New(ByVal strName As String...
13
by: usenet | last post by:
How and where can one find out about the basics of VB/Access2003 syntax? I am a died in the wool C/C++/Java Linux/Unix programmer and I am finding it difficult to understand the program format...
3
by: emlimeng | last post by:
hi everyone, I need help on an error message. I am working on an environment that Access2003 (adp) as a front end and links to SQL server as a back end. I am creating a table, which requests users...
3
by: Ramchandar | last post by:
Hi, I am creating reports using VBA code. I have the same query in a querydef residing both in Access97 and Access2003. The result of this querydef is then moved to a table in Access97 and...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.