Thursday, 9 April 2015

Run-time Error '2342' Can't run a SQL Query - "A RunSQL action requires an argument consisting of an SQL Statement"

Run-time error '3061'. Too few parameters. Expected 1
Generally , we use the DoCmd.RUNSQL Command to performs the SQL  Action queries like UPDATE, DELETE and INSERT.

But in case of Running a SELECT statement , the DoCmd.RUNSQL Command may fails and throws the Run-time errors '2342','3061'.
Example :
Suppose I am performing a Access Form Event , where the the EMP_Name is populating automatically into a Text box of Form after Updating the EMP_ID through Combo Box selection, as follows...

Private Sub Cmbo_EMP_ID_AfterUpdate()

E_ID = Me.Cmbo_EMP_ID.Value

Str_NAME = "SELECT EMP_NAME FROM MyTeam WHERE MyTeam.EMP_ID=" & """" & E_ID & """" & ";"
Me.txt_EMP_NAME.Value=DoCmd.RunSQL( Str_NAME )

End Sub

For the above case , DoCmd.RunSQL method will not workout , so , we have to follow the Recordset method as in the following syntax :

Private Sub Cmbo_EMP_NAME_AfterUpdate()
On Error Resume Next
Dim SQL_Str As String
Dim DB As Object
Dim RS_NAME As DAO.Recordset

E_ID = Me.Cmbo_EMP_ID.Value 'Storing the EMP_ID  from the Combo box Input.

Str_NAME= "Select EMP_NAME From MyTeam Where MyTeam.EMP_ID=" & """" & E_ID & """" & ";"

Set DB = CurrentDb
Set RS_NAME = DB.OpenRecordset(Str_NAME)

Me.txt_EMP_NAME.Value = RS_NAME.Fields("EMP_NAME").Value
End Sub

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts