We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Object variable or with block variable not set
Posted on 2001-06-13
I am trying to use a qry as a recordsource. The qry uses a query(qryVATLett) based on 2 tables + 2 other tables. I think I am getting this error message because of the query. Any ideas?
Sicando
Dim rpt As Report, rst As Recordset, db As Database, rst1 As Recordset, strSQL As String
Dim strQry As String
Dim strDate1 As Date, strDate2 As Date
strDate1 = Forms!frmreports!txtDate1
strDate2 = Forms!frmreports!txtDate2
strSQL = "SELECT tblMember.ReferenceNo, qryVATlett.AccountName, qryVATlett.AccountNumber,"
& " qryVATlett.SortCode, tblMember.FinalAcceptance,
& " tblMember.MethodofPayment,
& " qryVATlett.Address1, qryVATlett.Address2, qryVATlett.Address3," _
& " qryVATlett.Town, qryVATlett.County, qryVATlett.Postcode, " _
& " tblSubscriptionDetails.Net
& " FROM (tblMember INNER JOIN qryVATlett ON tblMember.ReferenceNo = qryVATlett.ReferenceNo) INNER JOIN tblSubscriptionDetails ON tblMember.LevelofMembershi
& " WHERE (tblMember.FrequencyofPaym
& " AND tblMember.RenewalDate Between #" & Format(strDate1, "mm\/dd\/yyyy") & "# And #" & Format(strDate2, "mm\/dd\/yyyy") & "#" _
& " AND (qryVATlett.Bank Is Not Null) AND tblMember.Status In ('LM1','LM2','LM3)" _
& " ORDER BY qryVATlett.Bank"
rpt.RecordSource = strSQL
Sicando
Dim rpt As Report, rst As Recordset, db As Database, rst1 As Recordset, strSQL As String
Dim strQry As String
Dim strDate1 As Date, strDate2 As Date
strDate1 = Forms!frmreports!txtDate1
strDate2 = Forms!frmreports!txtDate2
strSQL = "SELECT tblMember.ReferenceNo, qryVATlett.AccountName, qryVATlett.AccountNumber,"
& " qryVATlett.SortCode, tblMember.FinalAcceptance,
& " tblMember.MethodofPayment,
& " qryVATlett.Address1, qryVATlett.Address2, qryVATlett.Address3," _
& " qryVATlett.Town, qryVATlett.County, qryVATlett.Postcode, " _
& " tblSubscriptionDetails.Net
& " FROM (tblMember INNER JOIN qryVATlett ON tblMember.ReferenceNo = qryVATlett.ReferenceNo) INNER JOIN tblSubscriptionDetails ON tblMember.LevelofMembershi
& " WHERE (tblMember.FrequencyofPaym
& " AND tblMember.RenewalDate Between #" & Format(strDate1, "mm\/dd\/yyyy") & "# And #" & Format(strDate2, "mm\/dd\/yyyy") & "#" _
& " AND (qryVATlett.Bank Is Not Null) AND tblMember.Status In ('LM1','LM2','LM3)" _
& " ORDER BY qryVATlett.Bank"
rpt.RecordSource = strSQL
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
-
2
-
2
7 Comments
Nah. You are getting the error becuase you have not set the report to anything
I have a code called Company. Here is my code
Dim rpt As Report
DoCmd.OpenReport "Company", acViewPreview
Set rpt = Reports("Company")
rpt.RecordSource = strSQL
See how that goes!
Greg
I have a code called Company. Here is my code
Dim rpt As Report
DoCmd.OpenReport "Company", acViewPreview
Set rpt = Reports("Company")
rpt.RecordSource = strSQL
See how that goes!
Greg
greg is right. where is this code that is running. is it on the report that is opening? is it behind a button? where is it?
as Greg points out, you never set the report variable (rpt) to anything. you need to set rpt = to the report you are trying to open, but depending on where your code is located there are different ways to do it.
i don't think greg's code will work for you though, as his idea is to open the report in preview mode first. if you do this, THEN set the recordsource, the report won't requery.
i think that you should move your code to the On Open event of your report. then, simply change your
rpt.RecordSource = strSQL
to
Me.RecordSource = strSQL
enjoy
dovholuk
as Greg points out, you never set the report variable (rpt) to anything. you need to set rpt = to the report you are trying to open, but depending on where your code is located there are different ways to do it.
i don't think greg's code will work for you though, as his idea is to open the report in preview mode first. if you do this, THEN set the recordsource, the report won't requery.
i think that you should move your code to the On Open event of your report. then, simply change your
rpt.RecordSource = strSQL
to
Me.RecordSource = strSQL
enjoy
dovholuk
i've never gotten a c before...
for my benefit, how could i have given you a better answer?
thanks for helping me out,
dovholuk
for my benefit, how could i have given you a better answer?
thanks for helping me out,
dovholuk
Guess I'm just a bit pissed off. I thought the problem was in the SQL, which it was.
Give yourself a A+ if it makes you feel better
Thanks for your help
Give yourself a A+ if it makes you feel better
Thanks for your help
Guess I'm just a bit pissed off. I thought the problem was in the SQL, which it was.
Give yourself a A+ if it makes you feel better
Thanks for your help
Give yourself a A+ if it makes you feel better
Thanks for your help
Featured Post
Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses
Course of the Month7 days, 18 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.