How to use SQL Insert with variable instead of value

[jamesnkk]

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 -

dim uniqNumber as Long
Dim sql As String
 
 
 sqt = "insert into tblinvoicelines(custID, ProductID, UniqNumber) select custID, 
         ProductID,  UniqNumber= Int((100000 * Rnd)  from tblorderlines where  po = "  & lstSearch.Column(0) '   
 
 DoCmd.RunSQL strstmt

[NeoPa]

That doesn't look like a unique number :(.

[NeoPa]

Also your DoCmd.RunSQL refers to strstmt but the string you set up with the SQL is called sqt.

[jamesnkk]

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 ?

[cyberdwarf]

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

[NeoPa]

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.

[jamesnkk]

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

[NeoPa]

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.

[jamesnkk]

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.

[NeoPa]

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

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
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?

[MMcCarthy]

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

[jamesnkk]

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

Table Name=tblStudent
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
MaxMark; Numeric
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.

[jamesnkk]

Try this ...

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

Mary

OK, I will try this method to see if it work, thank so much

[NeoPa]

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.

[Tomino]

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

Function AuditEditBegin(sTable As String, sAudTmpTable As String, sKeyField As String, _
    lngKeyValue As Long, bWasNewRecord As Boolean) As Boolean
 
On Error GoTo Err_AuditEditBegin
 
    Dim db As DAO.Database           ' Current database
    Dim sSQL As String
    'Dim audReason As String
 
    'audReason = "help"
 
    'Remove any cancelled update still in the tmp table.
    Set db = DBEngine(0)(0)
    sSQL = "DELETE FROM " & sAudTmpTable & ";"
    db.Execute sSQL
 
    ' If this was not a new record, save the old values.
    If Not bWasNewRecord Then
        sSQL = "INSERT INTO " & sAudTmpTable & " ( audType, audDate, audUser, audReason ) " & _
            "SELECT 'EditFrom' AS Expr1, Now() AS Expr2, NetworkUserName() AS Expr3, " ' & audReason & '" AS Expr4, " & sTable & ".* " & _
            "FROM " & sTable & " WHERE (" & sTable & "." & sKeyField & " = " & lngKeyValue & ");"
            'Debug.Print sSQL
        db.Execute sSQL, dbFailOnError
 
    End If
    AuditEditBegin = True
 
Exit_AuditEditBegin:
    Set db = Nothing
    Exit Function
 
Err_AuditEditBegin:
    Call LogError(Err.Number, Err.Description, conMod & ".AuditEditBegin()", , False)
    Resume Exit_AuditEditBegin
End Function