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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:

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 MVPDesigner and DeveloperCommented:
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
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
If you are modifying the command bars you will need to set a reference to the Office object library.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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 Architect / Systems AnalystCommented:
"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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 MVPDesigner and DeveloperCommented:
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 Architect / Systems AnalystCommented:
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 MVPDesigner and DeveloperCommented:
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 Architect / Systems AnalystCommented:
whew lol.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.