error 3061 with insert into sql

[chanu]

i have the following function.
Public Function sqltest( )
Dim strSQL As String
strSQL = "INSERT INTO [tblEmpOldDA] " & _
"SELECT 1 AS EmpSerialID ," & _
"[tblOldDA].FromDate ," & _
"[tblOldDA].ToDate ," & _
"[tblOldDA].Rate " & _
"FROM [tblOldDA] "
CurrentDb.Execute strSQL
End Function
when i use this query it is throwing runtime error 3061 too few parameters.
Expected 1
when i tried this with docomd.runsql, it is asking me for RATE parameter.
Actually i am creating tblEmpOldDA as a duplicate table of tblOldDA with
EmpSerialID as an extra field. Why is it not working? I have already asked a
question about creating a duplicate table two days ago but i found no answer
from this forum. So, friends if at all i am unclear in representing the
problem, pl. ask me follow-up question to make yourself clear about it . but
don't ignore it please. I will be hopeful of your reply

 

[Marshall Barton]

i have the following function.
Public Function sqltest( )
Dim strSQL As String
strSQL = "INSERT INTO [tblEmpOldDA] " & _
"SELECT 1 AS EmpSerialID ," & _
"[tblOldDA].FromDate ," & _
"[tblOldDA].ToDate ," & _
"[tblOldDA].Rate " & _
"FROM [tblOldDA] "
CurrentDb.Execute strSQL
End Function
when i use this query it is throwing runtime error 3061 too few parameters.
Expected 1
when i tried this with docomd.runsql, it is asking me for RATE parameter.

That error means that the field Rate does not exist in
tblOldDA. Are you sure you spelled it correctly?

What do you want the 1 AS EmpSerialID in every record to do?
Whatever it is, I don't see how a constant can do it.

 

[chanu]

Hai Marsh,
you are right that i've spelled it wrong. the field name is not Rate but
DArate. Thanks a lot. i actually don't want '1' all the time but i want to
replace with the primary ID of the mainform. The idea is to create a
duplicate of the default table[tblOldDA] and bind it with the an employee by
assigning his ID to it. could you help me how to replace it with the Main
form's recordset primaryID.

 

[chanu]

one more doubt please!
is there any way to check that the records are added. Pl. excuse me because
i am posting this as a postscript

 

[Marshall Barton]

you are right that i've spelled it wrong. the field name is not Rate but
DArate. Thanks a lot. i actually don't want '1' all the time but i want to
replace with the primary ID of the mainform. The idea is to create a
duplicate of the default table[tblOldDA] and bind it with the an employee by
assigning his ID to it. could you help me how to replace it with the Main
form's recordset primaryID.

I do not understand what you mean by "replace it with the
Main form's recordset primaryID".

Taking a stab in the dark I would guess something like:

strSQL = "INSERT INTO [tblEmpOldDA] " & _
"SELECT " & Me.[primary key field] & " AS EmpSerialID," &
_
"[tblOldDA].FromDate ," & _
"[tblOldDA].ToDate ," & _
"[tblOldDA].Rate " & _
"FROM [tblOldDA] "

BUT, that will copy every record in tblOldDA, I strongly
suspect that you should have a WHERE clause to select only
one record. Normally, the criteria would be the primary key
field, but you said that doesn't exist in tblOldDA???

 

[Marshall Barton]

is there any way to check that the records are added.

You can get the number of records that were added by using
the RecordsAffected property.

Dim db As Database
Set db = CurrentDb()
db.Execute strSQL
MsgBox db.RecordsAffected & " records were added."

Note that you can not use CurrentDb.Execute because
CurrentDb is recreated every time you use it.
(RecordsAffected would not be set in the new instance of
CurrentDb)

 

[Access infant]

Thanks Marsh
because of my unfamiliarity with the technical terms, i could not express
myself. but what you guessed is right. i want to fill all the records with
only one primary ID. Thanks a lot