HOW TO INSERT DEFAULT N NORMAL VALUES IN MS ACCESS

[Guest]

Hi,

I have this temperory table which needs to hold values from a different
table and from access form. E.g. when i click on the variation no, this temp
table gets all the variation details from the variation table n
simultaneously should add the claim no for the variation from the form for
each record in this temp table

Eg Claim No:1
CLick Variation No 5146

Claim No: Variation No: Variation Details Variation Amt

I tried to give the temptable field claim a default value (Form Claim No)
but when i run the insert statement it gives the default value to the row
below the inserted rows

If anyone can advice how to insert default values and select statement in
one insert statement

cheers
bye

 

[JohnFol]

In NWind I made a copy of the shippers table (called MyShippers), and then
wrote this statement using a field from the original table, and a default
value

INSERT INTO MyShippers ( CompanyName, Phone )
SELECT Shippers.CompanyName, "555-1234" AS Expr1
FROM Shippers;

You'll need to construct something similar, possibly replacing my "555-1234"
with a forms reference. If this fails, can you post the SQL you are using?

PS, can you explain further this statement?

I tried to give the temp table field claim a default value (Form Claim No)
but when i run the insert statement it gives the default value to the row
below the inserted rows

which row is below which row? The rows in the table (unsorted so this is not
a problem), or does it pick the wrong row on an datasheet / continuous form
for the default?

 

[Guest]

Hi John,

Thanks for ur help. ANyways here is the code which helped me run my query.

OurefNo.Value = PrjName.Column(0)
ClaimNos=[Forms]![Claims]![ClaimID]
SiteLoc = clientrs1(0)

DoCmd.RunSQL "INSERT INTO [ClaimVariationDetailsTemp]
(VarNo,[Desc],Submitdt,VarRef,VarAmt, ClaimNO,PrjNo) SELECT
VARID,variationdesc,vardate,varef,totalvariation," & ClaimNos & "," & OurefNo
& " FROM VariationSub WHERE siteloc='" & SiteLoc & "' and status=No"

Thanks a bunch!

cheers
bye