Recordset use..!!! (1 Viewer)

B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Hi,

I'am using MS-access 2007 to create record-set. Problem is that I'am using lot of Dlookup's to extract data which is increasing time to extract details on VBA form. I want to use record-set because I've heard from someone that it will reduce cycle time to extract record details drastically.

Actual problem is that "I don't know about how to create & use recordset"

I'am using saved query to extract data using Dlookup's.

Code till now I've used is as per below. I know it's like hell but any help to make desired output will be highly appreciated.

My Old code with lot of Dlookup's
Code: 
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 Month([Follow_up_date]) = Month(Date()) And Year([Follow_up_date]) = Year(Date())")(0)
Else
If (DCount("[Debit_date]", "Telecalling_database", "Date()>[Debit_date]+10 AND [Paid_Unpaid_Status] <> 'Paid' AND [Calling_Code]='DEBIT' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM telecalling_database WHERE Date()>[Debit_date]+10 AND [Calling_Code]='DEBIT' AND [Paid_Unpaid_Status] <> 'Paid' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """")(0)
MsgBox "Old Debit Calling Code case showing unpaid after 10 days of debit date", vbInformation
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
End If

DoCmd.OpenQuery ("Finding_Telecalling_details")



Plan_Name = DLookup("BASEPLANNAME", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Plan_Type = DLookup("Plan_Type", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
If (Me.Plan_Type.Value = "New ULIP") Then
Me.Plan_Type.BackColor = 10092543
Else
Me.Plan_Type.BackColor = 16777215
End If

Policy_Number = DLookup("Policy_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
GO_Code = DLookup("GO_CODE_Ingenium", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Modal_Premium = DLookup("Modal_Premium", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Frequency = DLookup("Frequency", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
ACt_Holder_Name = DLookup("Account_Holder_Name", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Account_Number = DLookup("Account_Number", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Bank_Name_Ingenium = DLookup("Bank_Name_Ingenium", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Client_Name = DLookup("Client_name", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Mobile_No = DLookup("Mobile_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Home_No = DLookup("Home_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Work_No = DLookup("Work_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
No_of_Prem_required = DLookup("No_of_Premium_Required", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Issue_date = DLookup("Issue_Date", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Calling_Code = DLookup("Calling_code", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Customer_comments = DLookup("Customer_comments", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Policy_Type = DLookup("Policy_type", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Agent_Name = DLookup("Agent_Name", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Transaction_Sequence_Number = DLookup("Txn_Reference_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)

If (IsNull(DLookup("Policy_Paid_to_date", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)) = True) Then
Me.Policy_paid_to_date.Value = "No PTD Concept"
Else
Policy_paid_to_date = DLookup("Policy_Paid_to_date", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
End If

MFYP_FRYP = DLookup("MFYP_FRYP", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Servicing_Agent_ID = DLookup("SERVICING_AGENT_ID", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Agent_Work_No = DLookup("Agent_Work_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Agent_Mobile_No_1 = DLookup("Agent_Mobile_No_1", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Agent_Mobile_No_2 = DLookup("Agent_Mobile_No_2", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Agent_Home_No = DLookup("Agent_Home_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
INSTRUMENT_NUMBER = DLookup("INSTRUMENT_NUMBER", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Instrument_amount = DLookup("INSTRUMENT_AMOUNT", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Bounce_date = DLookup("Bounce_date", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Bank_name = DLookup("BANK_NAME", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
BOUNCE_REASON = DLookup("BOUNCE_REASON", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Service_provider = DLookup("SERVICE_PROVIDER", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Draw_date = DLookup("DRAW_DATE", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
New_Mobile_No = DLookup("New_Mobile_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
New_Landline_No = DLookup("New_Landline_No", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)
Policy_Status = DLookup("Status", "Finding_Telecalling_details", "Instrument_number=" & Me.Form_S_No)

If (IsNull(DLookup("Instrument_Number", "History_Telecalling", "Instrument_Number=" & Me.Form_S_No)) = False) Then
DoCmd.OpenForm ("History_Query")
End If


Total_calls_for_the_day = DCount("Calling_end_Date_time", "Telecalling_Database", "[Calling_END_date_Time]>=Date() And Caller_Name = Telecaller_Name")
Total_calls_for_the_month = DCount("Calling_end_Date_time", "Telecalling_Database", "Year([Calling_END_date_Time])=Year(Now()) And Month([Calling_END_date_Time])=Month(Now()) And Caller_Name = Telecaller_Name") + (DCount("Follow_up_date", "History_Telecalling", "Year([Follow_up_date])=Year(Now()) And Month([Follow_up_date])=Month(Now()) And Caller_Name = Telecaller_Name"))
Repeat_calls_for_the_day = DCount("Follow_up_date", "History_Telecalling", "DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Year([Follow_up_date]) = Year(Date()) And Month([Follow_up_date]) = Month(Date()) And Caller_Name = Telecaller_Name")
Repeat_calls_for_the_month = DCount("Follow_up_date", "History_Telecalling", "Year([Follow_up_date])=Year(Now()) And Month([Follow_up_date])=Month(Now()) And DatePart('d',[Follow_up_date]) = DatePart('d',Date()) And Caller_Name = Telecaller_Name")
Pending_Calls_for_the_Month = DCount("Caller_Name", "Telecalling_database", "Calling_code is null And Caller_Name = Telecaller_name")
End Sub

My new code which I've tried after learning from Internet:
Code: 
Dim dbstelecalling_database As dao.Database
Dim rstinst_no As dao.Recordset

Set dbstelecalling_database = CurrentDb
Set rstinst_no = dbstelecalling_database.OpenRecordset("Finding_telecalling_details")

rstinst_no.MoveFirst

strplan_name = rstfinding_telecalling_details!BASEPLANNAME
strplan_type = rstfinding_telecalling_details!Plan_Type
strpolicy_number = rstfinding_telecalling_details!policy_no
strGO_code = rstfinding_telecalling_details!GO_CODE_INGENIUM
strModal_Premium = rstfinding_telecalling_details!Modal_Premium
strfrequency = rstfinding_telecalling_details!Frequency
strAct_Holder_Name = rstfinding_telecalling_details!Account_holder_name
strAccount_Number = rstfinding_telecalling_details!Account_Number
strbank_name_ingenium = rstfinding_telecalling_details!Bank_Name_Ingenium
strClient_Name = rstfinding_telecalling_details!Client_Name
strMobile_no = rstfinding_telecalling_details!Mobile_No
strHome_No = rstfinding_telecalling_details!Home_No
strWork_No = rstfinding_telecalling_details!Work_No
strNo_of_Prem_Required = rstfinding_telecalling_details!No_of_Premium_Required
strIssue_date = rstfinding_telecalling_details!Issue_date
strCalling_code = rstfinding_telecalling_details!Calling_Code
strCustomer_comments = rstfinding_telecalling_details!Customer_comments
strPolicy_type = rstfinding_telecalling_details!Policy_Type
strAgent_name = rstfinding_telecalling_details!Agent_Name
strTransaction_Sequence_Number = rstfinding_telecalling_details!Txn_reference_no
strPolicy_paid_to_date = rstfinding_telecalling_details!Policy_paid_to_date

rstinst_no.Close
dbstelecalling_database.Close

Set rstinst_no = Nothing
Set dbstelecalling_database = Nothing

Thanks in advance..!!!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
Change this line to:

Code: 
Set rstinst_no = dbstelecalling_database.OpenRecordset("SELECT * FROM Finding_telecalling_details WHERE Instrument_number=" & Me.Form_S_No)
Drop this line:

rstinst_no.MoveFirst

And actually refer to rstinst_no when getting values. ;)
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Thanks Pbaldy for such a quick response...!!!

I have changed code to suggested & got below error:

Run Time Error '3061':
Too few parameters. Expected 1.


I have debugged the code & it is showing error at new code line you suggested.

Code: 
Set rstinst_no = dbstelecalling_database.OpenRecordset("SELECT * FROM Finding_telecalling_details WHERE Instrument_number=" & Me.Form_S_No)

New changed code:

Code: 
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 Month([Follow_up_date]) = Month(Date()) And Year([Follow_up_date]) = Year(Date())")(0)
Else
If (DCount("[Debit_date]", "Telecalling_database", "Date()>[Debit_date]+10 AND [Paid_Unpaid_Status] <> 'Paid' AND [Calling_Code]='DEBIT' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """") > 0) Then
Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM telecalling_database WHERE Date()>[Debit_date]+10 AND [Calling_Code]='DEBIT' AND [Paid_Unpaid_Status] <> 'Paid' AND [CALLER_NAME] =""" & Me.Telecaller_Name & """")(0)
MsgBox "Old Debit Calling Code case showing unpaid after 10 days of debit date", vbInformation
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
End If

DoCmd.OpenQuery ("Finding_Telecalling_details")

Dim dbstelecalling_database As dao.Database
Dim rstinst_no As dao.Recordset

Set dbstelecalling_database = CurrentDb

Set rstinst_no = dbstelecalling_database.OpenRecordset("SELECT * FROM Finding_telecalling_details WHERE Instrument_number=" & Me.Form_S_No)

Plan_Name = rstfinding_telecalling_details!BASEPLANNAME
Plan_Type = rstfinding_telecalling_details!Plan_Type
Policy_Number = rstfinding_telecalling_details!policy_no
GO_Code = rstfinding_telecalling_details!GO_CODE_INGENIUM
Modal_Premium = rstfinding_telecalling_details!Modal_Premium
Frequency = rstfinding_telecalling_details!Frequency
ACt_Holder_Name = rstfinding_telecalling_details!Account_holder_name
Account_Number = rstfinding_telecalling_details!Account_Number
Bank_Name_Ingenium = rstfinding_telecalling_details!Bank_Name_Ingenium
Client_Name = rstfinding_telecalling_details!Client_Name
Mobile_No = rstfinding_telecalling_details!Mobile_No
Home_No = rstfinding_telecalling_details!Home_No
Work_No = rstfinding_telecalling_details!Work_No
No_of_Prem_required = rstfinding_telecalling_details!No_of_Premium_Required
Issue_date = rstfinding_telecalling_details!Issue_date
Calling_Code = rstfinding_telecalling_details!Calling_Code
Customer_comments = rstfinding_telecalling_details!Customer_comments
Policy_Type = rstfinding_telecalling_details!Policy_Type
Agent_Name = rstfinding_telecalling_details!Agent_Name
Transaction_Sequence_Number = rstfinding_telecalling_details!Txn_reference_no
MFYP_FRYP = rstfinding_telecalling_details!MFYP_FRYP
Servicing_Agent_ID = rstfinding_telecalling_details!Servicing_Agent_ID
Agent_Work_No = rstfinding_telecalling_details!Agent_Work_No
Agent_Mobile_No_1 = rstfinding_telecalling_details!Agent_Mobile_No_1
Agent_Mobile_No_2 = rstfinding_telecalling_details!Agent_Mobile_No_2
Agent_Home_No = rstfinding_telecalling_details!Agent_Home_No
Instument_number = rstfinding_telecalling_details!INSTRUMENT_NUMBER
Instrument_amount = rstfinding_telecalling_details!Instrument_amount
Bounce_date = rstfinding_telecalling_details!Bounce_date
Bank_name = rstfinding_telecalling_details!Bank_name
BOUNCE_REASON = rstfinding_telecalling_details!BOUNCE_REASON
Service_provider = rstfinding_telecalling_details!Service_provider
Draw_date = rstfinding_telecalling_details!Draw_date
New_Mobile_No = rstfinding_telecalling_details!New_Mobile_No
New_Landline_No = rstfinding_telecalling_details!New_Landline_No
Policy_Status = rstfinding_telecalling_details!Status


rstinst_no.Close
dbstelecalling_database.Close

Set rstinst_no = Nothing
Set dbstelecalling_database = Nothing

End Sub
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
On what line? Does Finding_Telecalling_details have a parameter in it? You're still refering to a non-existent recordset in the lines setting values.
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
"Finding_Telecalling_details" is my saved query which I'am using to extract details.

this query has "Instrument_Number" field which is exactly same value to "me.Form_s_No" on VBA form.

Error showing on below line:

Code: 
Set rstinst_no = dbstelecalling_database.OpenRecordset("SELECT * FROM Finding_telecalling_details WHERE Instrument_number=" & Me.Form_S_No)
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Too few parameters expected 1 error msg is ok but I'am not able to understand, when I'am using debug.print command to get value of "Instrument_Number" field before the code you suggested from my saved query "Finding_Telecalling_details" then it is showing nothing in immediate window.

Atleast it should reflect that Instruement_number which matches me.form_s_no value.

Correct me if I'am wrong.

i'am quite new to record-set & really doesn't know about this part. little more detailed help will really help me to understand how this works !!!
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
Until you fix the cause of the error it's not going to get anything from the recordset, if that's what you mean. Can you post the db, or a representative sample?
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Dear Pbaldy...

I would have attached but database size is quite heavy (300MB). Hence attaching my query SQL statement if this can help to solve the issue.

Code: 
SELECT Telecalling_Database.POLICY_No, Telecalling_Database.GO_CODE_Ingenium, Telecalling_Database.Modal_Premium, Telecalling_Database.Frequency, Telecalling_Database.Account_Holder_Name, Telecalling_Database.Account_Number, Telecalling_Database.Bank_Name_Ingenium, Telecalling_Database.Client_Name, Telecalling_Database.Mobile_No, Telecalling_Database.Home_No, Telecalling_Database.Work_No, [ECS-Renewals].NO_OF_PREMIUM_REQUIRED, Telecalling_Database.Issue_Date, Telecalling_Database.Calling_Code, Telecalling_Database.Customer_comments, Telecalling_Database.Policy_Type, Telecalling_Database.Policy_Paid_To_Date, Telecalling_Database.MFYP_FRYP, Telecalling_Database.SERVICING_AGENT_ID, Telecalling_Database.Agent_Mobile_No_1, Telecalling_Database.Agent_Mobile_No_2, Telecalling_Database.Agent_Home_No, Telecalling_Database.Agent_Work_No, Telecalling_Database.INSTRUMENT_NUMBER, Telecalling_Database.INSTRUMENT_AMOUNT, Telecalling_Database.Bounce_date, Telecalling_Database.BANK_NAME, Telecalling_Database.BOUNCE_REASON, Telecalling_Database.SERVICE_PROVIDER, Telecalling_Database.DRAW_DATE, Telecalling_Database.Policy_Paid_to_date, [ECS-Renewals].AGENT_NAME, Plan_Name.BASEPLANNAME, Telecalling_Database.New_Mobile_No, Telecalling_Database.New_Landline_No, Telecalling_Database.Txn_Reference_No, [ECS-Renewals].STATUS, Plan_Name.Plan_Type
FROM (Telecalling_Database LEFT JOIN [ECS-Renewals] ON Telecalling_Database.POLICY_No = [ECS-Renewals].POLICY_NUMBER) LEFT JOIN Plan_Name ON [ECS-Renewals].PLAN_ID = Plan_Name.BASEPLANID
WHERE (((Telecalling_Database.INSTRUMENT_NUMBER)=[Forms]![Telecalling_Entry]![Form_S_No]));

If anything more required, then please let me know...
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
Like I said, option 1 is taking the criteria out of the query (ie no WHERE clause). The recordset will be applying that restriction, so the query doesn't need it.
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Thanks Pbaldy..

Got some understanding about this & removed query from code & used my table name instead of "Finding_Telecalling_details".

Finally Error which i was getting earlier has been changed to below:

Run-time error 3265
Item not found in this collection

Now error is showing at Plan_Name field. I guess there is some error in this line syntax.

Code: 
Set rstinst_no = dbstelecalling_database.OpenRecordset("SELECT * FROM [COLOR="Blue"]Telecalling_database[/COLOR] WHERE Instrument_number=" & Me.Form_S_No)
Debug.Print INSTRUMENT_NUMBER

Plan_Name = rstinst_no!BASEPLANNAME
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
That error would imply that BASEPLANNAME is not the name of a field in that table; is it? From your earlier SQL, that would appear to be in the Plan_Name table. If you want fields from both tables, you can use the query without a criteria.
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Dear Pbaldy,

Thanks for your help...I guess this is not going to help...can you please share your e-mail ID where i can send it my database so that you can check it for real errors & solutions.

:)
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
You should be able to post it (or a representative sample) here, but did you try taking the criteria out of the query and leaving it as the source for the recordset?
 
B

balvinder

Registered User.
Local time
Today, 08:32
Joined
Jun 26, 2011
Messages
47
Just wanted to know what is the error in below code. I have made it after searching on net for almost 3-4 hours.

While executing this I'am getting below Error:

All I'am trying to do with below code is to reduce cycle time of Dlookup's with SQL command using DAO.

Please correct me if I'am wrong somewhere..

run time error 91
object variable with block variable not set

Code: 
Dim dbstelecalling_database As DAO.Database
Dim rst As DAO.Recordset
Dim JetSQL As String

JetSQL = ("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, New_Mobile_No, New_Landline_No, Txn_Reference_No FROM Telecalling_Database WHERE Instrument_Number=" & Me.Form_S_No)

Set rst = dbstelecalling_database.OpenRecordset(JetSQL, dbOpenSnapshot, dbForwardOnly)

Plan_Name = rst!BASEPLANNAME
Plan_type = rst!Plan_type
Policy_number = rst!policy_no
GO_code = rst!GO_CODE_INGENIUM
modal_premium = rst!modal_premium
Frequency = rst!Frequency
Act_holder_name = rst!Account_holder_name
account_number = rst!account_number
Bank_name_Ingenium = rst!Bank_name_Ingenium
Client_name = rst!Client_name
Mobile_no = rst!Mobile_no
Home_no = rst!Home_no
Work_no = rst!Work_no
No_of_Prem_required = rst!No_of_Premium_Required
issue_date = rst!issue_date
Calling_code = rst!Calling_code
Customer_comments = rst!Customer_comments
Policy_type = rst!Policy_type
Agent_Name = rst!Agent_Name
Transaction_Sequence_number = rst!Txn_reference_no
MFYP_FRYP = rst!MFYP_FRYP
Servicing_agent_ID = rst!Servicing_agent_ID
Agent_work_no = rst!Agent_work_no
Agent_mobile_no_1 = rst!Agent_mobile_no_1
Agent_mobile_no_2 = rst!Agent_mobile_no_2
Agent_home_no = rst!Agent_home_no
Instument_number = rst!Instrument_number
Instrument_amount = rst!Instrument_amount
bounce_date = rst!bounce_date
Bank_name = rst!Bank_name
Bounce_reason = rst!Bounce_reason
Service_provider = rst!Service_provider
Draw_date = rst!Draw_date
New_mobile_no = rst!New_mobile_no
New_landline_no = rst!New_landline_no
Policy_status = rst!Status


rst.Close

Set rst = Nothing
Set dbstelecalling_database = Nothing
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
You didn't set dbstelecalling_database.
 
pbaldy

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 20:02
Joined
Aug 30, 2003
Messages
36,140
Happy to help.
 
You must log in or register to reply here.

Similar threads

B
Fed up with Dlookup's
Replies
12
Views
1,714
Thales750
T
B
ADO code Error
Replies
3
Views
1,794
JANR
J
B
Update Command Error
Replies
5
Views
681
boblarson
boblarson
B
Concatenating Date & Text using Currentdb.execute
Replies
2
Views
1,448
balvinder
B

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom