Results 1 to 4 of 4
  1. #1
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31

    Arrow Access Mangles my Query (expands to unmanageable size and crashed Access)

    Hey y'all. This is a follow up to a thread that Bob Fitz helped me solve. Here is my working SQL query to populate my listbox:




    Code:
    SELECT Items.ID, Items.QBItemNumber, MFGList.MFG, Items.Model, Items.ModelNumber, Items.HasUniqueID, Items.IsPhysical, Categories.Category, Categories.SubCategoryFROM MFGList INNER JOIN (Categories INNER JOIN Items ON Categories.ID = Items.Category) ON MFGList.ID = Items.ManufacturerWHERE (Items.ID=txtFilterItemNumber.Value OR Items.ID=txtFilterItemNumber.Value IS NULL) AND (Items.QBItemNumber=txtFilterQB.Value OR Items.QBItemNumber=txtFilterQB.Value IS NULL) AND (MFGList.MFG=cboFilterMFG.Value OR MFGList.MFG=cboFilterMFG.Value IS NULL) AND (Items.Model=txtFilterModel.Value OR Items.Model=txtFilterModel.Value IS NULL) AND (Items.ModelNumber=txtFilterModelNumber.Value OR Items.ModelNumber=txtFilterModelNumber.Value IS NULL) AND (Items.HasUniqueID=chkFilterUnique.Value OR Items.HasUniqueID=chkFilterUnique.Value IS NULL) AND (Items.IsPhysical=chkFilterPhysical.Value OR Items.IsPhysical=chkFilterPhysical.Value IS NULL) AND (Categories.Category=cboFilterCat.Value OR Categories.Category=cboFilterCat.Value IS NULL) AND (Categories.SubCategory=cboFilterSubCat.Value OR Categories.SubCategory=cboFilterSubCat.Value IS NULL);
    It's the WHERE element that kills Access. I can put in this SQL and the form will run just fine... but then when I go back in to re-edit it, Access has hundreds of entries in the Criteria/OR sections, and the SQL itself is so big that Access locks up for several minutes trying to display it. I've attached it in a zip file:

    Access Is Crazy.zip

    Does anyone know why Access is doing this, and if so, how I can stop it?

    Thank you,

    Dan

  2. #2
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    Ahh I know your pain! you are tying to create it via the inbuilt access query builder correct?

    I find the access query builder to be fine for very simple things, however it quickly tends to over complicate matters, it really does it when you go it to edit it because it converts your SQL, into SQL that it understands (i have a copy on my desk of 15 a4 pages of an access built query, compared to a 2 line SQL statement generated by a function on my form).

    In your case, I would place a

    Code:
    Private Sub Form_Open()
    dim lsql as string
    lsql="SELECT Items.ID, Items.QBItemNumber, MFGList.MFG, Items.Model, Items.ModelNumber, Items.HasUniqueID, Items.IsPhysical, Categories.Category, Categories.SubCategoryFROM MFGList INNER JOIN (Categories INNER JOIN Items ON Categories.ID = Items.Category) ON MFGList.ID = Items.ManufacturerWHERE (Items.ID=txtFilterItemNumber.Value OR Items.ID=txtFilterItemNumber.Value IS NULL) AND (Items.QBItemNumber=txtFilterQB.Value OR Items.QBItemNumber=txtFilterQB.Value IS NULL) AND (MFGList.MFG=cboFilterMFG.Value OR MFGList.MFG=cboFilterMFG.Value IS NULL) AND (Items.Model=txtFilterModel.Value OR Items.Model=txtFilterModel.Value IS NULL) AND (Items.ModelNumber=txtFilterModelNumber.Value OR Items.ModelNumber=txtFilterModelNumber.Value IS NULL) AND (Items.HasUniqueID=chkFilterUnique.Value OR Items.HasUniqueID=chkFilterUnique.Value IS NULL) AND (Items.IsPhysical=chkFilterPhysical.Value OR Items.IsPhysical=chkFilterPhysical.Value IS NULL) AND (Categories.Category=cboFilterCat.Value OR Categories.Category=cboFilterCat.Value IS NULL) AND (Categories.SubCategory=cboFilterSubCat.Value OR Categories.SubCategory=cboFilterSubCat.Value IS NULL);"
    me.recordsource=lsql
    End Sub
    In your forms on open event, and then edit it manually.

    By the looks of things though you have several controls you use to construct the query for your form
    You might benefit from generating it by a function

    I enclose an example here...

    Code:
    Private Function Gen_SQL()
    'Declare variable
    Dim lsql As String
    'Set the constant beginning of the SQL
    lsql = "SELECT * FROM [CONTROLLER TABLE] WHERE [SERVICING NO]=" & [Forms]![activeindex]![Servicing No]
    'Assign extra bits of SQL as required from the combo boxes
    If IsNull([SNOW]) = False Then
    lsql = lsql & " AND [Snow]=" & [SNOW]
    End If
    If IsNull([Sheet]) = False Then
    lsql = lsql & " AND [Sheet]=" & [Sheet]
    End If
    If IsNull([Combo28]) = False Then
    lsql = lsql & " AND [Stat code]='" & [Combo28] & "'"
    End If
    If IsNull([cc status]) = False Then
    lsql = lsql & " AND [CC STATUS]='" & [cc status] & "'"
    End If
    If IsNull([Planned pulse]) = False Then
    lsql = lsql & " AND [Planned Pulse]='" & [Planned pulse] & "'"
    End If
    If IsNull([Status]) = False Then
    lsql = lsql & " AND [STATUS]='" & [Status] & "'"
    End If
    If IsNull([Planner Seen]) = False Then
    lsql = lsql & " AND [Planner Seen]='" & [Planner Seen] & "'"
    End If
    If IsNull([Zone Code]) = False Then
    lsql = lsql & " AND [Zone Code]='" & [Zone_Code] & "'"
    End If
    If IsNull([Actual_Pulse]) = False Then
    lsql = lsql & " and [actual pulse]='" & [Actual_Pulse] & "'"
    End If
    'add the constant ending to the SQL
    lsql = lsql & " ORDER BY [SNOW] ASC, [SHEET] ASC, [LINE] ASC"
    Gen_SQL = lsql
    End Function
    I then use a 'refresh' command button to allow the user to manually requery based on the newly generated SQL from the function by using.

    Code:
    Private Sub Cmd_ref_results
    dim msql as string
    msql=Gen_SQL
    me.recordsource=msql
    End Sub
    I know this goes a bit beyond what you asked but I think you may find it useful (and if I'm really lucky someone may even be able to optimize my code for me, also note the names arent great as this is an early project I havent got round to renaming yet!)
    Last edited by R_Badger; 04-24-2012 at 08:09 AM. Reason: Forgot code tags

  3. #3
    dipique is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    31
    Thanks R Badger, I think that answers my question. I'm trying to move away from all my SQL statements being set by code, but in this case it might be worthwhile. I guess I was hoping there was a property somewhere that I could set to "let user manage his own damn SQL." No such luck, apparently.

    Thank you,

    Dan

  4. #4
    R_Badger is offline Knows a few tricks
    Windows XP Access 2003
    Join Date
    Feb 2012
    Location
    Suffolk, UK
    Posts
    262
    If there is, I haven't found it!

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

Similar Threads

  1. Replies: 0
    Last Post: 03-10-2012, 11:35 PM
  2. ACCESS 2010 DB size
    By jscriptor09 in forum Access
    Replies: 1
    Last Post: 02-21-2012, 10:32 AM
  3. Replies: 0
    Last Post: 02-28-2011, 10:26 PM
  4. [Error]Access Crashed Issues
    By ckhaos in forum Security
    Replies: 1
    Last Post: 06-23-2009, 02:08 PM
  5. Fix the size of a form in Access 2007
    By Divardo in forum Forms
    Replies: 1
    Last Post: 06-02-2009, 04:23 PM

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