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

Compile Error when form opens

P: 2
Hello Gurus,

I have been tasked with correcting issues with our upgrade(Access97/2000 to Access2007) and essentially know very little about Access (any version) or Visual Basic. Here is my current issue: when we open one of our .accdb it throws this error:

"Compile error: Method or data not found"

CODE:

Private Sub Form_Load()
'20010105 JBT

Dim RS As Recordset
Dim SQL As String

DoCmd.Maximize

' *** AUTO STATUS REPORT GENERATOR
Set RS = CurrentDb.OpenRecordset("tblMonthlyTrackings", dbOpenSnapshot)
RS.FindFirst "(([Printed] = 0) AND (Month([CallDt]) = Month(Date())-1))"
If Not RS.NoMatch Then
If MsgBox("You have Monthly Status records for last month. Do you wish a report printed?", vbYesNo, "UNPRINTED RECORDS FOUND") = vbYes Then
RS.Close
DoCmd.OpenReport "rptMonthlyStatusReport", acViewPreview

SQL = "UPDATE tblMonthlyTrackings SET tblMonthlyTrackings.Printed = -1 " & _
"WHERE (((tblMonthlyTrackings.Printed)=0));"
CurrentDb.Execute SQL
End If
End If
CurrentDb.Close
' *** end AUTO STATUS REPORT GENERATOR

End Sub

(see attached jpeg)

Can anyone pass on some pointers of how to fix this? A good tutorial would be nice, I'm learning Access 2007 & VB by troubleshooting these errors, so it's peice by peice.

THANK YOU in advance for your assistance!!
Attached Images
File Type: jpg HMGPImage.jpg (25.0 KB, 185 views)
Apr 1 '10 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Try running the following code making sure to set a Reference to the Microsoft DAO X.X Object Library:
Expand|Select|Wrap|Line Numbers
  1. 'Make sure that a Reference is set to the Microsoft DAO X.X Object Library
  2. Dim RS As DAO.Recordset
  3. Dim SQL As String
  4.  
  5. DoCmd.Maximize
  6.  
  7. ' *** AUTO STATUS REPORT GENERATOR
  8. Set RS = CurrentDb.OpenRecordset("tblMonthlyTrackings", dbOpenSnapshot)
  9.  
  10. RS.FindFirst "([Printed] = 0) AND (Month([CallDt]) = Month(Date())-1)"
  11.  
  12. If Not RS.NoMatch Then
  13.   If MsgBox("You have Monthly Status records for last month. Do you wish a report printed?", _
  14.              vbYesNo, "UNPRINTED RECORDS FOUND") = vbYes Then
  15.     RS.Close
  16.     Set RS = Nothing
  17.  
  18.     DoCmd.OpenReport "rptMonthlyStatusReport", acViewPreview
  19.  
  20.     SQL = "UPDATE tblMonthlyTrackings SET tblMonthlyTrackings.Printed = -1 " & _
  21.           "WHERE (((tblMonthlyTrackings.Printed)=0));"
  22.  
  23.     CurrentDb.Execute SQL
  24.   End If
  25. End If
Apr 1 '10 #2

P: 2
Wow, Thank you ADezii for your quick response. As soon I get to make the correction I will let you know what happens.

Again Thank you and I appriciate you time and assistance!
Apr 1 '10 #3

ADezii
Expert 5K+
P: 8,597
Hope it all works out for you.
Apr 1 '10 #4

Post your reply

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