Results 1 to 10 of 10
  1. #1
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8

    Troubleshooting ACCDE - Crashes opening


    I have a contact database I have built (originally MDB and migrated to ACCDB), and when I create a ACCDE version of the front-end - the database crashes on load. The weird thing is if I bypass the startup form by holding SHIFT and then manually open the form that would have launched - it opens without issue and the database performs as expected.

    As far as I can find, there is no way to step through the code in the ACCDE, but is there a way to capture the exception that caused the crash? I have looked in the ACCDB and there are no missing references I can see, and when I step through the code there are no errors thrown.

    Thank you in advance!

    Allan

  2. #2
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8
    I am really not sure why, but it seems to be caused by code in the Form Current procedure, in the ACCDB version, all the lines below are un-commented and it works, but in the ACCDE, I must change the code to the following:

    Code:
    Private Sub Form_Current()    
        MyCriteria = "(((True)<>False) AND ((tbl_contacts.archive)=No)) "
    
    
        '  Create SELECT statement.
        MyRecordSource = MySQL & strSearchFrom & "WHERE " & MyCriteria & strSearchOrder & ";"
     
        ' set record source to Subform
        'lstContacts.RowSourceType = "Table/Query"
        'lstContacts.RowSource = MyRecordSource
        'MyCriteria = ""
        'ArgCount = 0
        'txt_SearchFirst.SetFocus
        'lstContacts.Selected(0) = True
        'txtContactID = lstContacts.Column(0, varItm)
        'txtContactCount.Value = lstContacts.ListCount
    End Sub
    I really don't see anything that would cause a crash, any advice is appreciated. I also tried adding Me. to specify the control name but it doesn't make a difference.

    Allan

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Do you use
    Option Compare Database
    Option Explicit

    in your code modules
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8
    I had Compare Database but not Explicit. I added that and corrected the compilation errors but still get the same results.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what is strSearchOrder?

    Also, what is your code now, corrected for compilation errors

  6. #6
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8
    Thank you for the reply, is a variable I use for a multiple search routine. The code is as follows now:

    Code:
    Option Compare DatabaseOption Explicit
    Dim strSearchCompany As String
    Dim strSearchFirst As String
    Dim strSearchLast As String
    Dim strSearchMissionDept As String
    Dim boolFullSearch As Boolean
    Dim ArgCount As Integer
    Dim MyCriteria As String
    Dim strSQLCommand As String
    Dim strSearchSelect As String
    Dim strSearchCriteria As String
    Dim MyRecordSource As String
    Dim varItm As Variant
    
    
    Const MySQL = "SELECT tbl_contacts.contact_ID, tbl_contacts.[First] & ' ' & tbl_contacts.[Middle] & ' ' & tbl_contacts.[Last] AS [Name], tbl_contacts.jobtitle AS [Professional Title], tbl_company.company_name AS [Company], tbl_contacts.[Department], tbl_company.company_address1 AS [Address] "
    
    
    'Const MySQL = "SELECT [contact_ID], [Name], [jobtitle] AS [Professional Title], [company_name] AS [Company], [department_initials] AS [Department], [contact_address1] AS [Address] "
    'Const strSearchFrom = "FROM tbl_categories INNER JOIN ((((tbl_contacts Left JOIN tbl_company ON tbl_contacts.Company = tbl_company.company_id) INNER JOIN tbl_contact_dept ON tbl_contacts.contact_ID = tbl_contact_dept.contact_ID) INNER JOIN tbl_category_contact ON tbl_contacts.contact_ID = tbl_category_contact.contact_id) INNER JOIN tbl_mission_dept ON tbl_contact_dept.department_id = tbl_mission_dept.department_id) ON tbl_categories.catergory_ID = tbl_category_contact.category_id; "
    Const strSearchFrom = "FROM tbl_contacts Left JOIN tbl_company ON tbl_contacts.Company = tbl_company.company_id "
    'Const strSearchFrom = "FROM qry_ContactList "
    
    
    Const strSearchOrder = " ORDER BY tbl_company.company_name, tbl_contacts.Last"
    Code:
    Private Sub Form_Current()    
        MyCriteria = "(((True)<>False) AND ((tbl_contacts.archive)=No)) "
    
    
        '  Create SELECT statement.
        MyRecordSource = MySQL & strSearchFrom & "WHERE " & MyCriteria & strSearchOrder & ";"
     
        ' set record source to Subform
        Me!lstContacts.RowSourceType = "Table/Query"
        Me!lstContacts.RowSource = MyRecordSource
        MyCriteria = ""
        ArgCount = 0
        Me!txt_SearchFirst.SetFocus
        Me!lstContacts.Selected(0) = True
        Me!txtContactID = lstContacts.Column(0, varItm)
        Me!txtContactCount.Value = lstContacts.ListCount
    End Sub

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    in your first code this should be

    Option Compare Database
    Option Explicit

    in your mySQL and strSearchOrder definition, first, last and name are all reserved words (First and Last as used in sql statements, name as used for the name of an object in VBA)

    have you tried to see the sql string you have created and run in the query window

    e.g.

    debug.print MySQL & strSearchFrom & "WHERE " & MyCriteria & strSearchOrder & ";"


    copy and paste the result in the immediate window into the sql window and run

  8. #8
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8
    Thank you for the reply, the Option Compare DatabaseOption Explicit was only an error in posting, in the code it is two lines. I will try to change the names so they are not reserved, but when I run the code manually it works fine. As I mentioned, this code only fails when I launch the ACCDE file and let the Display Form load automatically. If I run the ACCDB version, and try to debug, there are no errors. If I hold shift with ACCDE and then launch the form that normally automatically loads - it runs without error.

    Allan

  9. #9
    agiercke is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2014
    Posts
    8
    I have done additional testing and this only seems to occur on my dev workstation with full MS Access installed. On a client workstation with RunTime only, the ACCDE launches without issue - very weird...

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    so to summarise

    1. .accdb automatically opening form with full version - no error
    2. .accdb manually opening form with full version - no error
    3. .accde automatically opening form with full version - errors
    4. .accde manually opening form with full version - no error
    5. .accde automatically opening form with runtime - no error

    runtime does not have error messaging as such, it will either freeze, close or carry on. You have to create your own error handling routines

    Another thing to be aware of is that if the form has subforms, the current event can run multiple times when a form is first opened - once for each subform and once of the main form.

    Also not clear to me now which code you are running to create your rowsouce. This is commented out

    'Const strSearchFrom = "FROM tbl_categories INNER JOIN ((((tbl_contacts Left JOIN tbl_company ON tbl_contacts.Company = tbl_company.company_id) INNER JOIN tbl_contact_dept ON tbl_contacts.contact_ID = tbl_contact_dept.contact_ID) INNER JOIN tbl_category_contact ON tbl_contacts.contact_ID = tbl_category_contact.contact_id) INNER JOIN tbl_mission_dept ON tbl_contact_dept.department_id = tbl_mission_dept.department_id) ON tbl_categories.catergory_ID = tbl_category_contact.category_id; "
    but if used would cause a fail when concatenated here

    MyRecordSource = MySQL & strSearchFrom & "WHERE " & MyCriteria & strSearchOrder & ";"


    Reason is your strSearchFrom has an unnecessary ';' at the end

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Help Troubleshooting a Database Setup
    By Russellh in forum Access
    Replies: 9
    Last Post: 12-07-2015, 03:59 PM
  2. Troubleshooting unmatched query
    By accessmatt in forum Queries
    Replies: 2
    Last Post: 09-29-2014, 01:52 PM
  3. Need Troubleshooting
    By athyeh in forum Programming
    Replies: 10
    Last Post: 09-26-2013, 12:16 PM
  4. Tab Control Troubleshooting
    By MayaMia in forum Forms
    Replies: 10
    Last Post: 10-09-2012, 12:32 PM
  5. Opening Report Crashes Access 2003
    By GT3000 in forum Reports
    Replies: 1
    Last Post: 05-09-2011, 11:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums