Append query to add current rec

[princess15s]

Hiya,
what I want to do is; Append the current record to a table from the form. Would someone help me code an SQL statement for a macro or for a query?

I already have this code but its got errors in it, and I don't seem to know how to get it right as I've tried to correct it so many times:
INSERT INTO tblSubTrade ( [Trade Ref], Type, Amount )
SELECT qryDisplayMainTrade.[Trade Ref], qryDisplayMainTrade.[Client Fund],
qryDisplayMainTrade.[Net Price]
FROM qryDisplayMainTrade
WHERE qryDisplayMainTrade.[Trade Date] =
(SELECT Max([Trade Date] FROM qryDisplayMainTrade As T2
WHERE T2.[Trade Ref] = qryDisplayMainTrade.[Trade Ref])

 

[princess15s]

PLEASE HELP!

 

[aqif]

I am not sure y r u using this clause

WHERE T2.[Trade Ref] = qryDisplayMainTrade.[Trade Ref])

because u r refering to the same table to the same filed which access will not accept. Try using wthout where clause or brief me a little more about ur requirement. Selecting Max(date) in nested query is ok. But according to my limited experience that where clause is not making any sense to me

Cheers!
Aqif

 

[princess15s]

Ok, forget that code. What I want to do is:
I want to add the current record on a form to a table other than the source table. The form saves the data to a table called “tblMainTrade”, two queries are used to calculate few field of this table. The data on the source table and the calculations are combined in “qryDisplayMainTrade” Now what I want to do is select the most recent data from this form and add it to the table which is the source of the sub form Im using. How can I retrive the current data in a query?

 

[princess15s]

Hey I've done it with help from jatfill.

In the above code change the SELECT to SELECT TOP 1 and sort the query in desending and there you have it but if you press this key more than once you'll end up with more than one copy of the record. Does anyone know how to find out whether that record exists in a certain table before actually saving it to that table?