Results 1 to 7 of 7
  1. #1
    wodom is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2017
    Posts
    6

    Creating Four Cascading combo Boxes to filter subform


    Views: 10 Size: 128.6 KB">Database6.zip
    Click image for larger version. 

Name:	Capture.PNG 
Views:	29 
Size:	33.6 KB 
ID:	31239Click image for larger version. 

Name:	Capture1.PNG 
Views:	28 
Size:	27.4 KB 
ID:	31240Click image for larger version. 

Name:	Capture2.PNG 
Views:	27 
Size:	57.0 KB 
ID:	31241Database6.zipHi Everyone,
    I am new to access, but I am trying to learn. My problem is that I have a form (frmBagRecords) with a subform (frmBagsSpecs_subform). in the header of the frmBagRecords I have four unbound combo boxes called (cboBagType, cboBagForm, cboWidth, and cboJawType). I am trying to use these combo boxes to find a specific record in the subform frmBagSpecs_subform. It is used to wiew only the specifications of a particular bag, so that when tests are done the user knows whether the bag meets specifications. I have tried some code and can get each combo box to filter the subform, but not to work together.

    Attachment 31235Attachment 31236
    I have included snips of both the form and the design. What I am trying to do is fill in the combo boxes in the header and pull up the correct record in the subform. The table associated with all the records is tblBagSpec. The fields BagType, BagFormulation, JawType, and Width are lookup fields pulling from corresponding tables. I don't know if this is the best setup, but I have tried several different combinations of tables and nothing seems to work.
    Attachment 31237
    I have also included the database. It is just a sample of what the actual database consists of.

  2. #2
    wodom is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2017
    Posts
    6
    I found a function online but cannot get it to work.
    It is
    Function SearchCriteria ()
    Dim BagType, strBagForm, strJawType, strWidth as String
    Dim task, strCriteria as String

    If IsNull (Me.cboBagType) Then
    BagType = "[BagType] = like '*' "
    Else
    BagType = "[BagType] = " & Me.cboBagType
    End IF

    If IsNull (Me.cboBagForm) Then
    strBagForm = "[BagForm] = like '*' "
    Else
    strBagForm = "[BagForm] = '" & Me.cboBagForm & "' "
    End If

    strCriteria = BagType & "AND" & strBagForm
    task = "Select * From tblBagSpecs Where " & strCriteria

    Me.frmBagSpecs_subform1.Form.Recordsource = task
    Me.frmBagSpecs_suborm1.Form.Requery

    End Function


    Private Sub cboBagType_AfterUpdate()
    Call SearchCriteria
    End Sub

    Private Sub cboBagForm_AfterUpdate()
    Call SearchCriteria
    End Sub

    It keeps hanging up at Me.frmBagSpecs_subform1.Form.Recordsource = task
    frmBagSpecs_subform1 is the name of the subform.
    Any help would be great.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    There's a bunch of issues. Mainly, AFAIK, you cannot use Me in subform reference syntax. There are 2 syntaxes for subform references; I use one to reference the form controls:
    Code:
    Forms!MainFormName!SubformControlName.Form!ControlName
    . For subform properties such as RecordCount, I use
    Code:
    Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount
    Then there is the incorrect multiple declarations. Everything whose type is not specifically declared is a variant (e.g. BagType and every one like this).
    You must write BagType as String, strBagForm As String,... As written, your code may work fine, but it's risky. Access tries to interpret the needed type and treat it accordingly, but you can raise errors trying to perform operations or actions with the values.
    Mainly, you need to concatenate the entire WHERE part of your sql string and use it in whatever method you want to filter the form. This can mean applying an entire sql statement (right from the word SELECT) and requerying the form, or applying a filter to a form. It looks like you are trying to build a recordsource for a form, so it has to be written like a query.
    Not real important, but you don't need the Call keyword for your code.
    Hope this gets you started.

    Note: please use code tags and indentation for anything more than a few lines of code. I used them even for only one line because the forum will insert a space after 50 characters.
    EDIT: neither of your attachments seem accessible but the database is.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Follow up: just took a quick look. Typically, if what I call a parent control is updated, the child controls should be cleared. You are not doing this, but maybe it's due to the nature of the business, which I have no knowledge of. F'rinstance, if I pick A and that governs what's shown in B and C, it is fine to continue down the line. However if I alter A, the values in B and C quite likely are invalid, so any control "downstream" should be cleared, otherwise the filter or requery either won't display any records or the display would be invalid.
    Also, it seems that your db code and post code don't match in terms of what's going on. In the db, you're applying a requery based on what is selected in a combo without regard to what the other combos contain. That is, you're applying a new filter that disregards the other values. My understanding of the issue posted is that you want the performance of what's commonly referred to as cascading combos, which is what my previous posts were about. I would also requery the form on a button click, using the combo values rather than requery based on singular combo updates.
    Last edited by Micron; 11-12-2017 at 09:12 PM. Reason: added info

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have a demo I created a long time ago, to help me learn about cascading combo boxes, but it doesn't use a subform. You'll have to change the code to use a subform. See Form and Subform properties and controls


    There is a form for combo boxes and a form for list boxes. Choose the top item the first time to see the results of the selection. Then, in the first combo box/list box, change the selection to the second item.
    Look at the code for the combo boxes/list boxes.
    Attached Files Attached Files
    Last edited by ssanfu; 11-13-2017 at 05:35 PM.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    wodom, I'm thinking it might help to also mention a significant aspect of search forms that seems to not be understood. For a search form, you typically base the input controls on your actual data since that is what you want to search on. Your db combos row sources are based on their 'lookup' tables (i.e. tables that provide the list items for a combo or listbox). The way you did it, it's possible to choose values that don't exist in the data set, which can result in many combinations of values for records that don't exist.

    BTW, after my last post:
    - I also saw that you're using lookup fields in your tables - bad idea. http://access.mvps.org/access/lookupfields.htm
    - wondering why the datasheet has combos for displaying the record fields. You want people to be able to edit the searched records with only values provided by the combo lookup tables? Or should you be disabling text boxes so that records can be found but not altered?
    - why are number type fields defined as text?

  7. #7
    wodom is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2017
    Posts
    6
    Thanks for all the help. I obviously need to do some more research on construction of my database

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

Similar Threads

  1. Replies: 18
    Last Post: 07-16-2017, 04:52 AM
  2. Replies: 4
    Last Post: 05-28-2012, 09:39 AM
  3. Replies: 4
    Last Post: 01-22-2012, 10:48 PM
  4. cascading combo boxes in continuous subform
    By ayamali in forum Programming
    Replies: 1
    Last Post: 03-29-2011, 06:33 PM
  5. Using Cascading Combo Boxes to Filter a Query
    By skiptotheend in forum Queries
    Replies: 0
    Last Post: 10-13-2009, 06:57 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