Ok guys,

I have got the query below. What I want is that: 
WHERE [ref no] = 55, the 55 is a variable which is whatever (ref no) 
number is active on my form (frm_transaction).

Any suggestions?

Thanks

Dinis



Private Sub duplicate_Click()
    Dim db As Database
    Dim rstInfo As Recordset
    Dim SQL As String
  
    
    Set db = CurrentDb()
    Set rstInfo = db.OpenRecordset("all information", dbOpenDynaset)
    
    SQL = "INSERT INTO [all information] ( [trade date], amount, 
[transaction type],   country ) 
SELECT [trade date], amount, [transaction type], country 
From 
[all information] WHERE [ref no] = 55 "

db.Execute SQL
       
End Sub

WHERE [ref no]=Forms!frm_transaction![ref no]

John V. Ruff - The Eternal Optimist :-)
Always Looking for Contract Opportunities

www.noclassroom.com

Home:  xxx.xxx.xxxx
   Cell:  xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 
 
"Commit to the Lord whatever you do,
    and your plans will succeed."  Proverbs 16:3


-----Original Message-----
From: dfuas@h... [mailto:dfuas@h...] 
Sent: Thursday, June 20, 2002 4:46 PM
To: Access
Subject: [access] Having problem with a Query


Ok guys,

I have got the query below. What I want is that: 
WHERE [ref no] = 55, the 55 is a variable which is whatever (ref no) 
number is active on my form (frm_transaction).

Any suggestions?

Thanks

Dinis



Private Sub duplicate_Click()
    Dim db As Database
    Dim rstInfo As Recordset
    Dim SQL As String
  
    
    Set db = CurrentDb()
    Set rstInfo = db.OpenRecordset("all information", dbOpenDynaset)
    
    SQL = "INSERT INTO [all information] ( [trade date], amount, 
[transaction type],   country ) 
SELECT [trade date], amount, [transaction type], country 
From 
[all information] WHERE [ref no] = 55 "

db.Execute SQL
       
End Sub

Build the SQL statement as you do, completing it with the reference to your 
control.

sSQL = "INSERT INTO [all information] ( [trade date], amount, " & _
   "[transaction type],   country ) " & _
   "SELECT [trade date], amount, [transaction type], country " & _
   "FROM " & _
   "[all information] WHERE [ref no] = " & txtRefNo.Value

assuming the control that contains the value you need is a text edit called 
txtRefNo. 

    Claudio de Biasio
      Team 97 S.r.l.


Quoting dfuas@h...:

> Ok guys,
> 
> I have got the query below. What I want is that: 
> WHERE [ref no] = 55, the 55 is a variable which is whatever (ref no) 
> number is active on my form (frm_transaction).
> 
> Any suggestions?
> 
> Thanks
> 
> Dinis
> 
> 
> 
> Private Sub duplicate_Click()
>     Dim db As Database
>     Dim rstInfo As Recordset
>     Dim SQL As String
>   
>     
>     Set db = CurrentDb()
>     Set rstInfo = db.OpenRecordset("all information", dbOpenDynaset)
>     
>     SQL = "INSERT INTO [all information] ( [trade date], amount, 
> [transaction type],   country ) 
> SELECT [trade date], amount, [transaction type], country 
> >From 
> [all information] WHERE [ref no] = 55 "
> 
> db.Execute SQL
>        
> End Sub
> 
> 
>