VBA Recordset statement help

[cranebill]

I have a query that i built that consists of 2 rows. A date from the first row is to be put in 2 places on a report, and a date from the second row is to be put in 2 other places on the same report. I posted this in Other Access problems a guy wrote back and said i needed to use vba to do this. This is what he suggested:

Dim db as Database
Dim rs as Recordset
Dim Date1 As Date
Dim Date2 As Date

Set db = CurrentDB()
Set rs = db.OpenRecordset("NameOfQuery&quot

Date1 = rs!DateFieldName
rs.MoveNext
Date2 = rs!DateFieldName

Me.ReportControlName = Date1
Me.ReportControlName = Date2

rs.Close
Set rs = Nothing
Set db = Nothing

ok now that was to put the dates in one spot each on the form, but come to find out i needed two so i modified it to look like this:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim Date1 As Date
Dim Date2 As Date

Set db = CurrentDb()
Set rs = db.OpenRecordset("LastInspectionDate&quot

Date1 = rs!DateOf
rs.MoveNext
Date2 = rs!DateOf

Me.Current1 = Date1
Me.Current2 = Date1
Me.Last1 = Date2
Me.Last2 = Date2

rs.Close
Set rs = Nothing
Set db = Nothing


I get a too few parameters in the statement
Set rs = db.OpenRecordset("LastInspectionDate&quot

LastInspectionDate is the name of the query
DateOf is my Field in my Query
Current1, Current2, Last1, Last2 are the textboxes that need to be filled. Did I do this right or wrong?

Any help would be greatly appreciated.

Bill

 

[JeremyNYC]

Is LastInspectionDate a parameter query? =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cranebill]

Im not exactly sure what you mean... you help me all the time Jeremy i appreciate that... seems everytime i post a question you happen to pop in. ... anyway LastInspectionDate is a query that pulls a list of inspection for a customer and set to a decending order by date and is set to only show the top two results. Hope that answers your question.

 

[JeremyNYC]

Glad to be of so much help.

Can you post the sql from that query? That will help. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cranebill]

SELECT TOP 2 InspectionTable.InspectionID, InspectionTable.CompanyID, InspectionTable.DateOf
FROM InspectionTable
WHERE (((InspectionTable.CompanyID)=[Forms]![Main Menu]![Customer ID]) AND ((InspectionTable.DateOf)<Date()))
ORDER BY InspectionTable.InspectionID DESC;

 

[JeremyNYC]

You have to open the report only after the form has been opened and there's data in the Customer ID field.

Are you doing that?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cranebill]

Main Menu Form stays open so the data is there to refer to See the report works..... but i need the two dates instead of the one i originally thought because i originally only needed the most recent, but now this came upand i tried various query configs which just didnt work. So i posted and got the VBA does it seem like it should work?

 

[cranebill]

ok i re ran it.... this is exactly what it says

Run-Time Error '3061'

Too Few Parameters. Expected 1

 

[Dan8376]

Bill,

Your query is a parameter query. Set your parameters in your SQL statement, then get your dates. Try this:

Private Sub Report_Open(Cancel As Integer)
Dim db As Database
Dim rs As Recordset
Dim Date1 As Date
Dim Date2 As Date
Dim CompanyID As Long
Dim dt as Date

CompanyID = [forms]![Main Menu]![Customer ID]
dt = date()

Set db = CurrentDb()

set rs = db.OpenRecordset(&quot;SELECT TOP 2 InspectionTable.InspectionID, InspectionTable.CompanyID, InspectionTable.DateOf
FROM InspectionTable
WHERE (((InspectionTable.CompanyID)= &quot; & CompanyID & &quot AND ((InspectionTable.DateOf)< #&quot; & dt & &quot;#))
ORDER BY InspectionTable.InspectionID DESC;&quot


Date1 = rs!DateOf
rs.MoveNext
Date2 = rs!DateOf

Me.Current1 = Date1
Me.Current2 = Date1
Me.Last1 = Date2
Me.Last2 = Date2

rs.Close
Set rs = Nothing
Set db = Nothing

Dan

 

[cranebill]

Ok, hope you guys had a great Thanksgiving.... well Dan we have moved farther but still no Turkey lol... ok we now get down to this statement:

Me.Current1 = Date1

but unfortunatly we now get the error &quot;You cannot set a value to this object&quot;

I checked to see if the spelling was correct and it is.... so again im lost... any ideas?

 

[JeremyNYC]

me!current1 =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cranebill]

Ok i made that change and still the same Error occurs.... what does the &quot;Me&quot; mean in that statement if i may ask...

 

[JeremyNYC]

Check it out in the help files. The Me keyword refers to the form on which the code is being run.

What is Current1? =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[cranebill]

Current1 is a Text Box