By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,648 Members | 1,665 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,648 IT Pros & Developers. It's quick & easy.

Open a Report to the Last Record and Only that Record

P: 21
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Load()
  2.      DoCmd.GoToRecord record:=acLast   
  3. End Sub
  4.  
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.
May 21 '15 #1
Share this Question
Share on Google+
3 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:="Report_Name", View:=acViewPreview, WhereCondition:="EmployeeID = " & GetEmployeeID
  2.  
May 21 '15 #2

P: 21
Okay so I am running into a problem understanding the end of line of code. right now I have,
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command51_Click()
  2. DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]=" & [ID]
  3. End Sub
  4.  
I get the error "Data type mismatch in criteria expression"
May 21 '15 #3

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:="[ID]='" & [ID] & "'"
if it is just Numeric then the Syntax is:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim sTemp As String
  2. sTemp = "[ID]='" & Me!ID & "'"
  3. DoCmd.OpenReport ReportName:="LastRecord", View:=acViewPreview, WhereCondition:= sTemp 
May 21 '15 #4

Post your reply

Sign in to post your reply or Sign up for a free account.