ADO code Error

[balvinder]

Hi,

I'am using below code to extract fields from my split database table to VBA form. But getting below error.

Error #: 3061
Too Few Parameters. Expected 2

I have checked through Debug.Print help. It is giving error at "STRSQL" text which is highlighted in blue color.

I'am quite new in VBA programming hence would appreciate detailed help.

Private Sub Telecaller_Name_Change()

If (DCount("[Follow_up_date]", "Telecalling_database", "DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Year([Follow_up_date]) = Year(Date()) And Month([Follow_up_date]) = Month(Date()) And DatePart('d',[Calling_end_date_time]) <> DatePart('d',Date()) AND [Paid_Unpaid_Status] <> 'Paid' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Paid_Unpaid_Status] <> 'Paid' AND DatePart('d',[Calling_end_date_time]) <> DatePart('d',Date()) And DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Year([Follow_up_date]) = Year(Date())")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'MFYP' AND [Paid_Unpaid_Status] = 'Unpaid' AND [Calling_end_Date_time] Is NULL AND [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND BANK_NAME NOT LIKE '%DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_end_Date_time] is null AND [MFYP_FRYP]='MFYP' AND [Paid_Unpaid_Status]  = 'Unpaid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] ASC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'FRYP' AND [Paid_Unpaid_Status]  = 'Unpaid' AND [Calling_end_Date_time] Is NULL AND [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND BANK_NAME NOT LIKE '%DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_end_Date_time] is null AND [MFYP_FRYP]='FRYP' AND [Paid_Unpaid_Status]  = 'Unpaid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] ASC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
If (DCount("[MFYP_FRYP]", "Telecalling_Database", "[MFYP_FRYP] = 'RYP' AND [Paid_Unpaid_Status]  = 'Unpaid' AND [Calling_end_Date_time] Is NULL AND [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND BANK_NAME NOT LIKE '%DEBIT'") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Calling_end_Date_time] is null AND [MFYP_FRYP]='RYP' AND [Paid_Unpaid_Status]  = 'Unpaid' AND [BANK_NAME] NOT LIKE '%DEBIT' ORDER BY [Bounce_date] DESC, [No_of_Premium_Required] DESC, [Modal_Premium] DESC;")(0)
Else
MsgBox "There are no more cases for you to do Tele-Calling, Kindly contact Anumeet Kaur/Balvinder Rayat for more details", vbInformation
End If
End If
End If
End If


Dim dbstelecalling_database As DAO.Database
Dim rsttelecalling As DAO.Recordset
Dim strsql As String

On Error GoTo ErrorHandler

Set dbstelecalling_database = CurrentDb

[COLOR="Blue"]strsql = "SELECT POLICY_No, GO_CODE_Ingenium, Modal_Premium, Frequency, Account_Holder_Name, Account_Number, Bank_Name_Ingenium, Client_Name, Mobile_No, Home_No, Work_No, Issue_Date, Calling_Code, Customer_comments, Policy_Type, Policy_Paid_To_Date, MFYP_FRYP, SERVICING_AGENT_ID, Agent_Mobile_No_1, Agent_Mobile_No_2, Agent_Home_No, Agent_Work_No, INSTRUMENT_NUMBER, INSTRUMENT_AMOUNT, Bounce_date, BANK_NAME, BOUNCE_REASON, SERVICE_PROVIDER, DRAW_DATE, Policy_Paid_to_date, BASEPLANNAME, New_Mobile_No, New_Landline_No, Txn_Reference_No FROM Telecalling_database WHERE Instrument_Number = Form_S_No;"[/COLOR]

Set rsttelecalling = dbstelecalling_database.OpenRecordset(strsql, dbOpenDynaset)

If rsttelecalling.EOF Then Exit Sub

rsttelecalling.MoveFirst
Policy_Number = rsttelecalling!Policy_No
GO_Code = rsttelecalling!GO_CODE_Ingenium
Modal_Premium = rsttelecalling!Modal_Premium
Frequency = rsttelecalling!Frequency
ACt_Holder_Name = rsttelecalling!Account_Holder_Name
Account_Number = rsttelecalling!Account_Number
Bank_Name_Ingenium = rsttelecalling!Bank_Name_Ingenium
Client_Name = rsttelecalling!Client_Name
Mobile_No = rsttelecalling!Mobile_No
Home_No = rsttelecalling!Home_No
Work_No = rsttelecalling!Work_No
Issue_date = rsttelecalling!Issue_date
Calling_Code = rsttelecalling!Calling_Code
Customer_comments = rsttelecalling!Customer_comments
Policy_Type = rsttelecalling!Policy_Type
Policy_paid_to_date = rsttelecalling!Policy_paid_to_date
MFYP_FRYP = rsttelecalling!MFYP_FRYP
Servicing_Agent_ID = rsttelecalling!Servicing_Agent_ID
Agent_Mobile_No_1 = rsttelecalling!Agent_Mobile_No_1
Agent_Mobile_No_2 = rsttelecalling!Agent_Mobile_No_2
Agent_Home_No = rsttelecalling!Agent_Home_No
Agent_Work_No = rsttelecalling!Agent_Work_No
INSTRUMENT_NUMBER = rsttelecalling!INSTRUMENT_NUMBER
Instrument_amount = rsttelecalling!Instrument_amount
Bounce_date = rsttelecalling!Bounce_date
Bank_name = rsttelecalling!Bank_name
BOUNCE_REASON = rsttelecalling!BOUNCE_REASON
Service_provider = rsttelecalling!Service_provider
Draw_date = rsttelecalling!Draw_date
New_Mobile_No = rsttelecalling!New_Mobile_No
New_Landline_No = rsttelecalling!New_Landline_No
Transaction_Sequence_Number = rsttelecalling!Txn_Reference_No

rsttelecalling.Close
dbstelecalling_database.Close

Set rsttelecalling = Nothing
Set dbstelecalling_database = Nothing

Exit Sub

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Sub

Thanks in advance..!!!

 

[JANR]

Since your variable Form_S_No is inside your string expression acces can't interperet that variable, you have to put it outside your string expression.

If it's a number then:
"Select ..... WHERE Instrument_Number =" & Form_S_No

If it's text then:
"Select ..... WHERE Instrument_Number ='" & Form_S_No & "'"

JR

 

[balvinder]

Thanks JANR for quick reply...

Form_S_No is actually number value hence I've done it as per your suggestion.

Now error has been changed as per below:

Error #: 3061
Too Few Parameters. Expected 1

Also I've checked Debug.print for Strsql putting post Select Statement & it is giving below output.

SELECT POLICY_No, GO_CODE_Ingenium, Modal_Premium, Frequency, Account_Holder_Name, Account_Number, Bank_Name_Ingenium, Client_Name, Mobile_No, Home_No, Work_No, Issue_Date, Calling_Code, Customer_comments, Policy_Type, Policy_Paid_To_Date, MFYP_FRYP, SERVICING_AGENT_ID, Agent_Mobile_No_1, Agent_Mobile_No_2, Agent_Home_No, Agent_Work_No, INSTRUMENT_NUMBER, INSTRUMENT_AMOUNT, Bounce_date, BANK_NAME, BOUNCE_REASON, SERVICE_PROVIDER, DRAW_DATE, Policy_Paid_to_date, BASEPLANNAME, New_Mobile_No, New_Landline_No, Txn_Reference_No FROM Telecalling_database WHERE Instrument_Number =5541979

Overall What i think is Instrument number is coming correctly in last for Form_S_No field but it is not executing the same in SELECT statement for Instrument_Number which is same field in database table.

 

[JANR]

It could be a mispelled fieldname, copy the statement and paste it in a new query and see if it gives you the corret result, if not you will be promted on which field it errors on.

JR