error 3061 with insert into sql

  • Thread starter Thread starter chanu
  • Start date Start date
C

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
 
chanu said:
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.
Click to expand...


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.
 
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.
 
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
 
chanu said:
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.
Click to expand...


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???
 
chanu said:
is there any way to check that the records are added.
Click to expand...


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)
 
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
 
Back
Top