Missing Reference

[teebumble]

Hi,

How do I check on a computer that only has Runtime 2007 if a reference is missing or not?

Here is my problem. I have a 2003 db that works when I run it on a computer that has the full Access 2003 version. But when I run it (using .mde database) on a system that only has runtime 2007 i get the following error. I know the error means that I am missing a reference, but where do i check to see which reference is missing on the computer with runtime 2007?

" The expression On Timer you entered as the event property setting produced the following error: The OpenForm action was canceled.
*The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].


* There may have been an error evaluating the function, event, or macro.


Thank you!

[GinaWhipp]

Make a COPY...

Have a look at...
http://access.mvps.org/access/modules/mdl0022.htm

[teebumble]

I am finally back on this database again. I fixed the timer problem stated above. BUT I am still getting a similar message.

"The expression On Click you entered as the event property setting produced the following error: The OpenForm action was canceled."

any suggestions i should try?

ginawhipp - I don't understand what you mean by "Make a copy".

thanks!

[pdebaets]

"Make a copy" = Make a backup of your database file before you proceed. The easy way to do this is to highlight your .mdb or .accdb file in Windows Explorer, then press Ctrl-C, then Ctrl-V. This will copy and paste your database file into the same folder with a new name.

"The openform action was cancelled" = It sounds like you were trying to use the Docmd.OpenForm method to open a form, but the form didn't open, or some code in the form OnOpen event procedure closed the form, or some user action caused the form to close before it finished completely opening. The error code for this is 2501. You can check for this error (trap it) and ignore it, or deal with the reason why the form did not open completely. Post back here if you need additional help.

[GinaWhipp]

Please post the References you have selected and see...

Repair (or Remove) Broken References

[teebumble]

I have selected the following references:

Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Outlook 11.0 Object Library

I don't get the error if i remove the Microsoft Outlook 11.0 object library and all code relating to email and outlook. But I need the outlook code to work.

[GinaWhipp]

For the Outlook library you are using early binding which is why you have to set the reference. You should switch that to Late Binding...

http://www.granite.ab.ca/access/latebinding.htm

...then Access will associate to the correct version of Outlook depending on the machine the database is on (and which version is loaded).

[teebumble]

I read over the site you linked above and i don't know where to add it in my code.

When my database opens, it shows the user a switchboard. In the switchboard there is a "email report" button. When user clicks that button, the following code runs.....

 
Private Sub Report_Page () 
   Dim objol As Object
    Dim objmail As MailItem
    Set objol = CreateObject("Outlook.Application")
    Set objmail = objol.createitem(olMailItem)
    
    Dim savename, apath, CollectName, MgrName, rsSRC, rsCCName, CCName As String
    Dim rs As DAO.Recordset
    
    MgrName = Me.Text28
    
    CollectName = "SELECT DISTINCT EmpEmail FROM LatestRev WHERE LatestRev.ReportEmail = '" + MgrName + "' AND LatestRev.DateCompleted is null "
    
    Set rs = CurrentDb.OpenRecordset(CollectName)
    
    Do Until rs.EOF
        rsCCName = rs!EmpEmail & "@ABC123.com, "
        CCName = rsCCName + CCName
    rs.MoveNext
    Loop
        
    savename = "Daily Report3"
    apath = "C:/Users\ABC\Documents\Daily Report3.rtf"
    
    DoCmd.OutputTo acOutputReport, "Daily Report", acFormatRTF, "Daily Report3.rtf", False
    DoCmd.Close acReport, "Daily Report"
    
    With objmail
        .To = MgrName & "@ABC123.com"
        .CC = CCName
        .BCC = ""
        '.From = 
        .Subject = "Weekly Training Updates"
        .Body = "Please see the attached document for training updates"
        .attachments.Add (apath)
        .display
    End With
    
    Kill apath
    
    Set objmail = Nothing
    Set objol = Nothing
    rsCCName = vbNullString
    CCName = vbNullString
    MgrName = vbNullString
    savename = vbNullString
    apath = vbNullString
    Exit Sub
    
NotSent:
    MsgBox "Error" & Str$(Err.Number) & _
        "sending report." & vbCrLf & _
        Err.Description
End Sub

Please tell me where i am supposed to add the code for the late binding. Should it be part of this sub?

Thank you!

[GinaWhipp]

At the top, this site gives examples...

http://word.mvps.org/FAQs/InterDev/E...ateBinding.htm

[teebumble]

So i followed the site and got the late binding code to work. But I am still getting that same error in the MDE mode. This is getting so frustrating. Not sure what else I can do. I think I am missing something very simple. My old database works in the MDE, but I am not sure what it is that I added that caused the database not to open the switchboard.

Any suggestions would be much appreciated! Thanks so much for helping me this far.

[GinaWhipp]

Did you remember to remove the Reference now that you have the Late Binding working?

[teebumble]

Yes, I did remove the Outlook reference.

Just a thought - don't know if it is valid or not.. but how about using DAO incorrectly? In my last database, for some "recordset" I just write "recordset", but in this new datbase I decided to use DAO.recordset for all recordset and DAO.Database for all database.

Not sure if that is something I should look into?

[GinaWhipp]

Hmmm, perhaps ypu should make sure they prefix DAO. That could be the problem that Access is confused...

[teebumble]

I think my problem is still the late binding.. maybe it is not correct. If possible, can you review my code below and let me know. Thank you.

 
Private Sub Report_Page() 'Email using Outlook - late binding
    
    Const olMailItem = 0
    Const ExcelRef = 0
    
    Dim ref As Reference
    
    '0 if Late Binding
    '1 if Refrence to Excel set
    
    If ExcelRef = 0 Then
        Dim objol As Object
        Dim objmail As Object
        Set objol = CreateObject("Outlook.Application")
        Set objmail = objol.createitem(olMailItem) '<-- maybe something wrong with this line?
        On Error Resume Next
        Set ref = References!Excel
        If Err.Number = 0 Then
            References.Remove ref
        ElseIf Err.Number <> 9 Then ' Subscript out of range meaning reference not found
            MsgBox Err.Description
            Exit Sub
        End If
    On Error GoTo NotSent
    
    Dim savename, apath, CollectName, MgrName, rsSRC, rsCCName, CCName, MrgFirst, MrgLast, MrgFull As String
    
    MgrName = Me.Text28
    
    'get Manager Name
    CollectName = "SELECT DISTINCT EmpEmail FROM LatestRev WHERE LatestRev.ReportEmail = '" + MgrName + "' AND LatestRev.DateCompleted is null "
    
    Set rs = CurrentDb.OpenRecordset(CollectName)
    
    Do Until rs.EOF
        rsCCName = rs!EmpEmail & "@ABC123.com, "
        CCName = rsCCName + CCName
    rs.MoveNext
    Loop
        
    savename = "Daily Report3"
    apath = "C:/Users\ABC\Documents\Daily Report3.rtf"
    
    DoCmd.OutputTo acOutputReport, "Daily Report", acFormatRTF, "Daily Report3.rtf", False
    DoCmd.Close acReport, "Daily Report"
    
    With objmail
        .To = MgrName & "@ABC123.com"
        .CC = CCName
        .BCC = ""
        .Subject = "Weekly Training Updates"
        .Body = "Please see the attached document for training updates"
        .attachments.Add (apath)
        .display
    End With
    
    Kill apath
    
    Set objmail = Nothing
    Set objol = Nothing
    rsCCName = vbNullString
    CCName = vbNullString
    MgrName = vbNullString
    savename = vbNullString
    apath = vbNullString
    Exit Sub
    End If
NotSent:
    MsgBox "Error" & Str$(Err.Number) & _
        "sending report." & vbCrLf & _
        Err.Description
End Sub

[teebumble]

Also, my references:

Visual Basic for Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library


Option Compare Database
Option Explicit

Public LoginUserName As String
Public db As DAO.Database
Public rs As DAO.Recordset
Public dbt As DAO.Database
Public rst As DAO.Recordset

DB doesn't work when opening as MDE on Computer using MS 2007 runtime, however it works on computer with the full Access 2003. Sorry for having so many posts, but this is the last item i have left and I need to release this db.

Thank you for your time.


And I don't know if this will help, but here is my Switchboard (i.e. named as Main Screen codes):

 
Option Compare Database
Option Explicit
Private Sub chgpass_Click()
DoCmd.RunMacro "mcrHide"
DoCmd.OpenForm "Change Password", acNormal
End Sub
Private Sub Form_Close()
DoCmd.Quit
End Sub
Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
' Move to the switchboard page that is marked as the default.
'MsgBox "I am in the main screen open form"
 
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
 
Dim strGetLev As String
strGetLev = "SELECT * FROM EmpInfo WHERE EmpInfo.EmpEmail = '" + LoginUserName + "'"
 
Set db = CurrentDb
Set rs = db.OpenRecordset(strGetLev)
 
If rs!AccessLev = "USER" Then
Me.Option2.Enabled = False
Me.Option3.Enabled = False
Me.Option4.Enabled = False
Me.Option5.Enabled = False
'Me.Option9.Enabled = False
'Me.Option7.Enabled = False - comment when complete --to allow users this option
Me.Option10.Enabled = False
Me.Option11.Enabled = False
Me.Option8.Enabled = False
End If
 
If rs!AccessLev = "ADMIN" Then
Me.Option1.Enabled = True
Me.Option2.Enabled = True
Me.Option3.Enabled = True
Me.Option4.Enabled = True
Me.Option5.Enabled = True
Me.Option6.Enabled = True
Me.Option7.Enabled = True
Me.Option9.Enabled = True
Me.Option10.Enabled = True
Me.Option11.Enabled = True
Me.Option8.Enabled = True
End If
 
If rs!AccessLev = "SUPERUSER" Then
DoCmd.SelectObject acTable, , True
Else
DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide
End If
Set rs = Nothing
Set db = Nothing
strGetLev = vbNullString
 
End Sub
Private Sub Form_Current() ' Update the caption and fill in the list of options.
'MsgBox "I am in the main screen form current"
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub
Private Sub FillOptions() ' Fill in the options for this switchboard page.
'MsgBox "I am in the main screen filloptions"
Const conNumButtons = 11 ' The number of buttons on the form.
 
Dim con As Object
Dim rso As Object
Dim stSql As String
Dim intOption As Integer
 
' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption
 
' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard_Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rso = CreateObject("ADODB.Recordset") '<---- can this be the problem?
rso.Open stSql, con, 1 ' 1 = adOpenKeyset
 
' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rso.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rso.EOF))
Me("Option" & rso![ItemNumber]).Visible = True
Me("OptionLabel" & rso![ItemNumber]).Visible = True
Me("OptionLabel" & rso![ItemNumber]).Caption = rso![ItemText]
rso.MoveNext
Wend
End If
' Close the recordset and the database.
rso.Close
Set rso = Nothing
Set con = Nothing
End Sub
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9
' An error that is special cased.
Const conErrDoCmdCancelled = 2501
 
Dim con As Object
Dim rso As Object
Dim stSql As String
On Error GoTo HandleButtonClick_Err
' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rso = CreateObject("ADODB.Recordset")
stSql = "SELECT * FROM [Switchboard_Items] "
stSql = stSql & "WHERE [SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn
rso.Open stSql, con, 1 ' 1 = adOpenKeyset
 
' If no item matches, report the error and exit the function.
If (rso.EOF) Then
MsgBox "There was an error reading the Switchboard Items table."
rso.Close
Set rso = Nothing
Set con = Nothing
Exit Function
End If
 
Select Case rso![Command]
 
' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rso![Argument]
 
' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rso![Argument], , , , acAdd
' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rso![Argument]
' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rso![Argument], acPreview
' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions
' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase
' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rso![Argument]
' Run code.
Case conCmdRunCode
Application.Run rso![Argument]
' Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rso![Argument]
' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."
 
End Select
' Close the recordset and the database.
rso.Close
 
HandleButtonClick_Exit:
On Error Resume Next
Set rso = Nothing
Set con = Nothing
Exit Function
HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If
 
End Function