By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

How to use SQL Insert with variable instead of value

100+
P: 134
Everytime I copy records from 1 table to another table, I created a Unique number using Rnd, so this value should also be copied into the 2nd table column called UniqNumber.

How can I achieve by storing the UniqNumber into the table-2 column ?

Any Expert out there could help me ?


Here below statement will created an error -

Expand|Select|Wrap|Line Numbers
  1. dim uniqNumber as Long
  2. Dim sql As String
  3.  
  4.  
  5.  sqt = "insert into tblinvoicelines(custID, ProductID, UniqNumber) select custID, 
  6.          ProductID,  UniqNumber= Int((100000 * Rnd)  from tblorderlines where  po = "  & lstSearch.Column(0) '   
  7.  
  8.  DoCmd.RunSQL strstmt
Dec 15 '06 #1
Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,186
That doesn't look like a unique number :(.
Dec 15 '06 #2

NeoPa
Expert Mod 15k+
P: 31,186
Also your DoCmd.RunSQL refers to strstmt but the string you set up with the SQL is called sqt.
Dec 15 '06 #3

100+
P: 134
Sorry, it just my typing error, but is there a better way to create a unique number and how do I use sql to insert in the below statement

dim POID long

POID = "using some method to create unique nos and store into this variable"

Dim strstmt As String

strstmt = "insert into tblinvoicelines(custID, customer, ProductID, POID) select custID, customer, ProductID,POID from tblorderlines where po = " & lstSearch.Column(0) ' FrmOrderSubform.Form!PO

DoCmd.RunSQL strstmt

My question is I cannot use POID as it a variable inside the sql statement right ?
Dec 15 '06 #4

Expert 100+
P: 218
James,


Please excuse me if I missed the point...

If you are inserting data into a table, couldn't you use a GUID if you really want the number to be unique? Otherwise, perhaps an Autonumber field would do.

Is this data in SQL Server, Access format, or what?

HTH

Steve
Dec 15 '06 #5

NeoPa
Expert Mod 15k+
P: 31,186
Sorry, it just my typing error, but is there a better way to create a unique number and how do I use sql to insert in the below statement

dim POID long

POID = "using some method to create unique nos and store into this variable"

Dim strstmt As String

strstmt = "insert into tblinvoicelines(custID, customer, ProductID, POID) select custID, customer, ProductID,POID from tblorderlines where po = " & lstSearch.Column(0) ' FrmOrderSubform.Form!PO

DoCmd.RunSQL strstmt

My question is I cannot use POID as it a variable inside the sql statement right ?
Firstly, consider CyberDwarf's response.
Secondly, to answer your question but not necessarily recommend it's usage...
You are building up a string strstmt.
Using concatenation (&) you can put in here whatever you (as the VBA persona) have available to you. You can add the POID as a string literal, just as you have lstSearch.Column(0). For multiple records though this could not be unique.
DMax()+1 in the SQL or a function call may provide that.
Dec 15 '06 #6

100+
P: 134
It a Microsoft Access format, When user click the button, the POID value alway change, this is to control a set of records using the same POID, don;t ask me why should I do that, it complicated to explain, the question is it possible to use the SQL Statement to insert this variable called - POID like the below statement.Beacuse when I run this statement, it give me an error, because POID is not a field column, it a variable, but table - tblinvoices does have a column POID. Thank for your patience to understand my questions.



dim POID long

POID = "using some method to create unique nos and store into this variable"

Dim strstmt As String

strstmt = "insert into tblinvoicelines(custID, customer, ProductID, POID) select custID, customer, ProductID,POID from tblorderlines where po = " & lstSearch.Column(0) ' FrmOrderSubform.Form!PO

DoCmd.RunSQL strstmt
Dec 15 '06 #7

NeoPa
Expert Mod 15k+
P: 31,186
This seems to be the same question that has already been answered.
I can only refer you to the previously posted answers.
I appreciate it's difficult to explain, but if we don't understand you (I don't) then we can't help.
You don't refer in your last post to either of the posted answers - have you considered them properly?
If not then I'm afraid I can't continue to help you.
If you have, then please explain why they did not help your situation.

Otherwise we are simply back where we started.
Dec 15 '06 #8

100+
P: 134
First, I really appreciate your help, and thank that you never give up hope on me.
I have also show you my statement, your answer was -

Using concatenation (&) you can put in here whatever you (as the VBA persona),

I tried, but would't work out, I may mistaken what you wrote, Would you please give me an example on how to use concatenation (&), with that POID variable, I think Picture is louder than word, forgvie me If I am asking too much.

Let me explain - I have created some PO to customer, when it due for deilvery, A list of PO will be shown, and user will click on the PO Number , it will then copy into 2 tables called - 1-Invoice and 2-Invoice-detail together with the PO number. Invoice Number is an Auto Number.

The Main Point is here, user may select the same PO number over again to create another set of Invoice, Therefore I have duplicate set of PO records BUT it must with a different Invoice Number.Before I copy into the Invoice and Invoice Detail table, I need to generate a Unique Number in the variable POID will dependend on PO NO as a control. So that I would not become accumulate like this - cos it depend on PO

[ 1ST SET OF INVOICE ]=INV-123
PO NO ITEM INVOICE
123456 A-12345 INV-123
123456 A-12349 INV-123


[ 2ND SET OF INVOICE ] =INV-223 (become accumulated from prev records)

PO NO ITEM INVOICE
12346 A-12345 INV-223
12346 A-12349 INV-223
12346 A-12345 INV-223
12346 A-12349 INV-223

[THIS IS WHAT I WANT, THE CORRECT WAY AS SHOWN ]

PO NO ITEM INVOICE POID
123456 A-12345 INV-123 000001
123456 A-12349 INV-123 000001


PO NO ITEM INVOICE POID
123456 A-12345 INV-229 XXXXX
123456 A-12349 INV-229 XXXXX

Hope you understand what I mean and please advise.
Dec 16 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
I've been looking at this for half an hour and I find I can't post anything as there is so much here which is contradictory (1 thing says 1 thing then another cancels it out).
I looked at your SQL in the first post and then your example data in the last and they don't share any fields other than the POID?!?
Can you post the MetaData of your tables (forget calling them invoices or transactions or anything else - just the Access Table Names).
Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
I need to know also what you have available on your form(s) and where the data should be coming from to add to your table(s).
I suspect we can find an answer for you, but first we need a clear question.
I'm sure this is difficult to do for you but keep trying - I'm sure we'll get there :).

Last question, is it true you are trying to add one record at a time with your SQL - not appending a whole bunch at once?
Dec 16 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

[/quote]

dim POID long

POID = "using some method to create unique nos and store into this variable"

Dim strstmt As String

strstmt = "insert into tblinvoicelines(custID, customer, ProductID, POID) select custID, customer, ProductID, " & POID & " from tblorderlines where po = " & lstSearch.Column(0) ' FrmOrderSubform.Form!PO

DoCmd.RunSQL strstmt[/quote]

Mary
Dec 16 '06 #11

100+
P: 134
I've been looking at this for half an hour and I find I can't post anything as there is so much here which is contradictory (1 thing says 1 thing then another cancels it out).
I looked at your SQL in the first post and then your example data in the last and they don't share any fields other than the POID?!?
Can you post the MetaData of your tables (forget calling them invoices or transactions or anything else - just the Access Table Names).
Posting Table/Dataset MetaData
Expand|Select|Wrap|Line Numbers
  1. Table Name=tblStudent
  2. StudentID; Autonumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. MaxMark; Numeric
  7. MinMark; Numeric
I need to know also what you have available on your form(s) and where the data should be coming from to add to your table(s).
I suspect we can find an answer for you, but first we need a clear question.
I'm sure this is difficult to do for you but keep trying - I'm sure we'll get there :).

Last question, is it true you are trying to add one record at a time with your SQL - not appending a whole bunch at once?

Oh Dear!, thank for putting an affort to solve my post, Anyway I have solved the problem by using VBA, IF not you, I would not have put in that little more energy, it you that inspire me to do more. Once again thank for never giving up hope on me, Thank God, I have come to the right forum.
Dec 16 '06 #12

100+
P: 134
Try this ...

Expand|Select|Wrap|Line Numbers
  1. dim POID long
  2.  
  3. POID = "using some method to create unique nos and store into this variable"
  4.  
  5. Dim strstmt As String
  6.  
  7. strstmt = "insert into tblinvoicelines(custID, customer, ProductID, POID) select custID, customer, ProductID, " & POID & " from tblorderlines where po = " & lstSearch.Column(0) ' FrmOrderSubform.Form!PO
  8.  
  9. DoCmd.RunSQL strstmt

Mary
OK, I will try this method to see if it work, thank so much
Dec 17 '06 #13

NeoPa
Expert Mod 15k+
P: 31,186
Oh Dear!, thank for putting an affort to solve my post, Anyway I have solved the problem by using VBA, IF not you, I would not have put in that little more energy, it you that inspire me to do more. Once again thank for never giving up hope on me, Thank God, I have come to the right forum.
I'm pleased to hear you're sorted (curious about your last post though).
Let us know if you run into any more difficulties.
Dec 17 '06 #14

P: 9
Hello,

I am having the same proble as described here above, I tried to find a solution but after 4 days, my head is a spinning and I really don't see the solution anymore. Is there somebody here who can help me please?


Thanks a lot for your assistance and patience!

Tom

Expand|Select|Wrap|Line Numbers
  1. Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
  2.     lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
  3.  
  4. On Error GoTo Err_AuditEditBegin
  5.  
  6.     Dim db As DAO.Database           ' Current database
  7.     Dim sSQL As String
  8.     'Dim audReason As String
  9.  
  10.     'audReason = "help"
  11.  
  12.     'Remove any cancelled update still in the tmp table.
  13.     Set db = DBEngine(0)(0)
  14.     sSQL = "DELETE FROM " & sAudTmpTable & ";"
  15.     db.Execute sSQL
  16.  
  17.     ' If this was not a new record, save the old values.
  18.     If Not bWasNewRecord Then
  19.         sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _
  20.             "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
  21.             "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
  22.             'Debug.Print sSQL
  23.         db.Execute sSQL, dbFailOnError
  24.  
  25.     End If
  26.     AuditEditBegin = True
  27.  
  28. Exit_AuditEditBegin:
  29.     Set db = Nothing
  30.     Exit Function
  31.  
  32. Err_AuditEditBegin:
  33.     Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
  34.     Resume Exit_AuditEditBegin
  35. End Function
Mar 27 '08 #15

Post your reply

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