Open a Report to the Last Record and Only that Record

[JamesHalko]

Hello Everyone,

I want a command button to open a report to the last record in my database and only that report. Right now My button opens up the rport and the report has the code:

Private Sub Report_Load()
     DoCmd.GoToRecord record:=acLast   
End Sub
 

When I try to open the report I get the error:
Run-time error '2105':
You can't go to the specified record.

I am an access rookie so not sure where to go from here.

Thanks for all your help.

[jforbes]

DoCmd.GoToRecord is really only for Navigation on a Form or Query.

Typically, people use a WhereCondition for a DoCmd.OpenReport to open a Report and Filter it to only what they want to show. An example would be to Open a Report of Employee information to show only information for the Employee that is selected on a Form. I borrowed this example from Seth and it would limit a Report to only show information for the Employee with the ID that is stored in the Field or Control Named GetEmployeeID:

DoCmd.OpenReport ReportName:="Report_Name", View:=acViewPreview, WhereCondition:="EmployeeID = " & GetEmployeeID
 

[JamesHalko]

Okay so I am running into a problem understanding the end of line of code. right now I have,

Private Sub Command51_Click()
DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]=" & [ID]
End Sub
 

I get the error "Data type mismatch in criteria expression"

[jforbes]

The WhereCondition is a SQL Where Clause which means that it should be written using SQL Syntax, which varies a bit on which datatype is being compared.

So if the [ID] Column in the database is Alphanumeric, it will need to be surrounded by Single Quotes if it is Numeric then the Single Quotes will cause an error. This is a common stumbling block when first starting out. It still bothers me. I would be happy to stuff everything into quotes and not worry about it, but it doesn't work that way.

If [ID] is an Alphanumeric Column, the Syntax is:

DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]='" & [ID] & "'"

if it is just Numeric then the Syntax is:

DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]=" & [ID]

Also, you might need/want to define your ID a bit differently. What you have, might work, but I think you'll find it a little work with:

DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]=" & Me!ID

Lastly, ZMDB would like to see things like this as he believes, and I agree, it is easier to troubleshoot:

Dim sTemp As String
sTemp = "[ID]='" & Me!ID & "'"
DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:= sTemp