Access 2003

So this is a doozy........ We a a database that we've used for a couple of years now. Someone went in today, accessed a form, clicked on a toolbar option, proceeded to click on a person's name and boom. Microsoft error that wants to be reported to Microsoft. Doesn't matter how I try to open it. I also can't find the report that it says it is sending to them. So that is part of my issue.

Next. I try to import all the tables, macros, reports, queries, and forms into a new database to try to get it to work there and that is giving me a completely different problem. It is now giving me the Compile erro: user-defined type not defined.

I've checked, double, and triple checked my references and they all seem fine. I have other databases that have less references and more things in them that are working perfectly.

I have my Declarations:
Option Compare Database
Option Explicit
Public Function CreateMenu()
Dim mBar As CommandBar
Dim mBarCtl As CommandBarControl
Dim mbarSub As CommandBarControl
Dim mBarSub1 As CommandBarControl

Then my section that is giving me the error:
Private Sub Delete_Toolbar()
'  Delete the toolbar
Dim mBar As CommandBar
On Error Resume Next
Set mBar = CommandBars("Main_Toolbar")
mBar.Delete


End Sub

Any thoughts as to why this section is giving me the error?

Thanks!
dminx13Asked:
Who is Participating?
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
If you are modifying the command bars you will need to set a reference to the Office object library.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

Option Compare Database
Option Explicit
Public Function CreateMenu()

Are you sure the Public Function shown in your post is in the Declarations section?  Because that would not compile.  You cannot do that in the Declarations section.

mx
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
Normal;l;y to fix this you would just restore a backup of your front en created since the last edits.

<<ext. I try to import all the tables, ...>
Tables?  

It sounds like you have not properly split your database.

I owudl restore a backup and check to be sure you have the correct references set.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
dminx13Author Commented:
TheHiTechCoach: The backup is pooched too otherwise I would. Currently trying to get one from last week. Would that be the Microsoft Access 11.0 Object Library or a different one?

Also FYI this is not a compiled database. Don't know what the difference would make? Here is all the code for the form...........

Option Compare Database
Option Explicit
Public Function CreateMenu()
Dim mBar As CommandBar
Dim mBarCtl As CommandBarControl
Dim mbarSub As CommandBarControl
Dim mBarSub1 As CommandBarControl

'  Create the menu bar
Set mBar = CommandBars.Add("Main_Toolbar", msoBarTop, True, False)
mBar.Visible = True
mBar.Protection = msoBarNoMove
'***************************************
'  Exit Application
'***************************************

Set mBarCtl = mBar.Controls.Add(msoControlButton)
With mBarCtl
    .Style = msoButtonCaption
    .Caption = "E&xit"
    .OnAction = "=Exit_DB()"
    .BeginGroup = True
End With
   
'  Add shortcuts for Staff and company maintenance
Set mBarCtl = mBar.Controls.Add(msoControlButton)
With mBarCtl
    .FaceId = 607
    .Style = msoButtonIconAndCaption
    .Caption = "&Staff"
    .OnAction = "=OpenForm(""frmSearchStaff"")"
    .BeginGroup = False
    .ToolTipText = "Open Staff"
End With
   
Set mBarCtl = mBar.Controls.Add(msoControlButton)
With mBarCtl
    .FaceId = 2580
    .Style = msoButtonIconAndCaption
    .Caption = "&Company"
    .OnAction = "=OpenForm(""frmCompany"")"
    .BeginGroup = False
    .ToolTipText = "Open Company Maintenance"
End With
   
   
'XXXXXXXXXXXXXXXXXXXXXXXXX
'   DATA MENU ITEM
'XXXXXXXXXXXXXXXXXXXXXXXXX
'  Create the Data control popup

Set mBarCtl = mBar.Controls.Add(msoControlPopup)
With mBarCtl
    .Caption = "&Data"
End With

    '  Companies
    Set mbarSub = mBarCtl.Controls.Add(msoControlPopup)
    With mbarSub
        .Caption = "&Company"
    End With
   
    Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
    With mBarSub1
        .Caption = "&Add Company"
        .OnAction = "=OpenForm(""frmAddCompany"")"
    End With
   
    Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
    With mBarSub1
        .Caption = "&Maintain Company"
        .OnAction = "=OpenForm(""frmCompany"")"
    End With
   
    '  Contract Staff
    Set mbarSub = mBarCtl.Controls.Add(msoControlPopup)
    With mbarSub
        .Caption = "&Staff"
    End With
   
    Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
    With mBarSub1
        .Caption = "&Add New Staff"
        .OnAction = "=OpenForm(""frmAddNewStaff"")"
    End With
   
    Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
    With mBarSub1
        .Caption = "&Maintain Staff"
        .OnAction = "=OpenForm(""frmSearchStaff"")"
    End With
   
    Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
    With mBarSub1
        .Caption = "&Search Staff By Position"
        .OnAction = "=OpenForm(""frmSearchStaffDepartment"")"
    End With
   
    '  Maintain base table data
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "&License Table"
        .BeginGroup = True
        .OnAction = "=OpenForm(""frmLicenseTable"")"
    End With
   
'XXXXXXXXXXXXXXXXXXXXXXXXX
'   PROCESS MENU ITEM
'XXXXXXXXXXXXXXXXXXXXXXXXX
   
'  Create the Process control popup
Set mBarCtl = mBar.Controls.Add(msoControlPopup)
With mBarCtl
    .Caption = "&Process"
End With
    '  License and TrainingManagement
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
    .Style = msoButtonIconAndCaption
        .Caption = "Staff Requirement Management"
        .BeginGroup = True
        .OnAction = "=OpenForm(""frmOpenPastDueData"")"
    End With
   
    '  Term Management
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
    .Style = msoButtonIconAndCaption
        .Caption = "Term Date Management"
        .BeginGroup = True
        .OnAction = "=OpenForm(""frmTermDateMangement"")"
    End With
   
    '  Update the department table
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
    .Style = msoButtonIconAndCaption
        .Caption = "Refresh Department Table"
        .BeginGroup = True
        .OnAction = "=RefreshDepartmentData()"
    End With
   
    '  Update the Job Class Code table
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
    .Style = msoButtonIconAndCaption
        .Caption = "Refresh Job Class Code Table"
        .OnAction = "=RefreshJCCData()"
    End With
   
'XXXXXXXXXXXXXXXXXXXXXXXXX
'   REPORTS MENU ITEM
'XXXXXXXXXXXXXXXXXXXXXXXXX

'  Create the Reports control popup
Set mBarCtl = mBar.Controls.Add(msoControlPopup)
With mBarCtl
    .Caption = "&Reports"
End With
   
    '  Reports Sub Menu
    Set mbarSub = mBarCtl.Controls.Add(msoControlPopup)
    With mbarSub
        .Caption = "&Statistics"
    End With
   
        Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
        With mBarSub1
            .Caption = "Active By &Department"
            .OnAction = "=ReportStaffCounts(""D"")"
        End With
   
        Set mBarSub1 = mbarSub.Controls.Add(msoControlButton)
        With mBarSub1
            .Caption = "Active By &Company"
            .OnAction = "=ReportStaffCounts(""C"")"
        End With
       
    '  Print List of Contract Staff
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "Contract Staff &List"
        .BeginGroup = True
        .OnAction = "=OpenForm(""frmPrintStaffList"")"
    End With

    '  Print List of Contract Staff
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "&Travelers"
        .BeginGroup = True
        .OnAction = "=PrintReport(""rptTravelers"")"
    End With
   
    Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "&All Contract Staff"
        .BeginGroup = True
        .OnAction = "=PrintReport(""rptContractStaffAll"")"
    End With
   
     Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "&All Contract Staff By VP"
        .BeginGroup = True
        .OnAction = "=PrintReport(""rptContractStaffVP"")"
    End With
   
    '  Print report of future contract starts
     Set mbarSub = mBarCtl.Controls.Add(msoControlButton)
    With mbarSub
        .Caption = "&Future Start Contract Staff"
        .BeginGroup = True
        .OnAction = "=PrintReport(""rptTravelerStaff_FutureStarts"")"
    End With
'XXXXXXXXXXXXXXXXXXXXXXXXX
'   HELP MENU ITEM
'XXXXXXXXXXXXXXXXXXXXXXXXX
'  Create the Help control popup
Set mBarCtl = mBar.Controls.Add(msoControlPopup)
With mBarCtl
    .Caption = "&Help"
End With

End Function

Private Sub Form_Close()
'  delete thew toolbar
Call Delete_Toolbar
End Sub
Private Sub Delete_Toolbar()
'  Delete the toolbar
Dim mBar As CommandBar
On Error Resume Next
Set mBar = CommandBars("Main_Toolbar")
mBar.Delete


End Sub
Private Sub Form_Open(Cancel As Integer)
Call Delete_Toolbar
Call CreateMenu
'  Maximize the form
DoCmd.Maximize

'  Check to see if the JCC and the Department
Call UpdateBaseData

End Sub
Public Function OpenSearch(SearchType As String)
    gSearchType = SearchType
    DoCmd.OpenForm "frmSearch"
End Function
Public Function CreateToolbar()
'  Add a toolbar that will allow the user to maintain the companies or staff from one click


End Function






0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Also FYI this is not a compiled database"
Open the VBA Editor >> Debug >> Compile
See if you get any compile errors ...

mx
Capture1.gif
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
dminx13
Did you note my first post about the Public Function statement in the Declarations section ?

mx
0
 
dminx13Author Commented:
DatabaseMX: yes, and that is why I posted the whole form code for everyone to see......

Compiled and getting the User-defined type not defined error............ And this is on a different form now........ SO a lot of my forms are giving me the User-Defined type not defined error..... which is what I don't know how to fix......
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What line of code is the error occurring on?

mx
0
 
dminx13Author Commented:
Right now I have these references in use:
Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft Visual Basic for Applications Extensibility 5.3
OLE Automation

In that priority order.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
What line of code is the compile error occurring on?
0
 
dminx13Author Commented:
Dim mBar As CommandBar

Under the Private Sub Delete_Toolbar()
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
dminx13,

<< The backup is pooched too otherwise I would. >>
You only have one backup?  Surely  you jest.

0
 
dminx13Author Commented:
One within my reach, we are working with IT to get an older than yesterday backup........
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If the error is here:

Dim mBar As CommandBar

then you are missing the Reference to Microsoft Office 11.0 Object Library, as noted by HTC.

Somehow that Reference has become toast ...

mx
Capture1.gif
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
dminx13,

Glad to here you have more backups.

I did this:
1)  create a blank new database in Access 20003.
2) I created a new form
3)  pasted your code into the form's code module.
4)  compileed
5)  Got an error on the line: Dim mBar As CommandBar
6) Added the reference to Office O=object lib
7) Compiled. Got error ans on a few UDF's
8) commented then out
9) code compiles fine.
0
 
dminx13Author Commented:
Reference issue which was noted by both.......
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
whew lol.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.