Cascading Combo Box to List Box Issues

[waves678]

Hi! I am building an access database for our Compliance group to input their findings. I built 3 cascading combo boxes for Test Plan, Test Description and Failed Attribute. I then realized I needed a list box for Failed Attribute because, in some cases, they'll need to pick more than 1 item. So I made that change, but things aren't quite right. When I advance between records in the Form (LOD1Testing), whatever changes I make to one record looks like it is effecting the other unless I hit "Refresh" each time. The table (LOD1Testing) has the correct information though, but the form doesn't look accurate. Also, if I update the "Failed Attribute" box, it brings that item over to the next record and I have to uncheck it.

The code is below for those 3 boxes, and I've attached my database. I'd appreciate any help I can get, it's been about 15 years since I've used Access (I'm kicking myself for not keeping up with it). Thank you.

Private Sub Reset_Click()
    With Me.TestPlan
        .Value = ""
    End With
    With Me.TestDescription
        .Value = ""
        .Requery
        .Enabled = False
    End With
    With Me.FailedAttribute
 'something needs to be here to bring the required value back to "" but .Value = "" doesn't work with the listbox
        .Requery
        .Enabled = False
    End With
    Me.TestPlan.SetFocus
End Sub


Private Sub TestPlan_AfterUpdate()
    With Me.TestDescription
        .Requery
        .Value = ""
        .Enabled = True
    End With
    With Me.FailedAttribute
        .Requery
'something needs to be here to bring the required value back to "" but .Value = "" doesn't work with the listbox
        .Enabled = False
    End With
End Sub
Private Sub TestDescription_AfterUpdate()
    With Me.FailedAttribute
        .Requery
        .Enabled = True
    End With
End Sub

LOD1.zip

[Micron]

Your child combos are supposed to use parent combo values but yours doesn't.

changes I make to one record looks like it is effecting the other unless I hit "Refresh" each time

Not sure but don't you mean it looks like record changes are not being displayed unless you refresh?

[waves678]

Hi Micron, yes, that is what I meant. How would I solve for my issue?

[Micron]

OK, after a deeper look I think I made boo boo there. Perhaps you should state what should be happening from the point at which you open the form because I suspect you're got this set up wrong. When you bind combos (or any other control that will accept edits) and change values, the underlying records are affected. You're doing that in code and will likely wipe out your field values - especially if the field will accept a zls (zero length string often coded as ""). Usually combos are not bound when used for search forms or filtering records, otherwise you make a selection to do some finding or filtering and end up changing the bound field value. Point is, we need to know how your form is supposed to be used.

[kd2017]

I didn't know you could make a multiselect listbox lookup field in a table... neat!

Anyway, before you go too much further down this path you might consider that lookups are generally frowned upon http://access.mvps.org/access/lookupfields.htm

[Micron]

Here is a form with control filtering that seems to work for the combos. Usually, filtering controls are placed in the header.
I suspect that the listbox should be blank when description is blank but I'm not sure so I left it. Sorry I had to squash it all to make it visible on my laptop - just look at the control properties and the slight code mods (added current event, otherwise your 2 combos will remain populated when you navigate records).

Just before posting I noticed all the table level lookup fields - yikes!

If you need to see the filtering values in the records below, add textbox on form detail, bind it to the field but disable or lock the control.
FWIW I would never allow 2 objects to have the same name (table and field in your db). There are also a lot of other things I wouldn't do that you are doing. If you want a bunch of primary design links and tips I can post them.

LOD1micron.zip

[waves678]

Thanks Micron. I think I got some bad advice there at the beginning on separating out the data in my tables. I might just start from scratch. But ideally, I want the person to be able to choose Test Plan, and then Test Description will only show items related to Test Plan. Then when they choose Test Description, only items related to it will be offered in Failed Attributes. If there is a better way to achieve this, I am all ears. Should I list Test Plan, Test Description and Attributes all in 1 table?

[Micron]

Should I list Test Plan, Test Description and Attributes all in 1 table?

You're filtering so it implies that these choices are not attributes of the same thing, so no. To provide focused guidance requires that we understand what process the db supports and that includes understanding the relationships among "things". Perhaps review the following topics if you are going to start again. There are tons of other sources for these topics, including vids if you prefer learning that way.

If you look at how the 2nd combo choices are governed by the first selection, then you apply that idea to the listbox, except listbox rows will need to be based on records that relate to both of the other 2 fields (SELECT ... WHERE [somefield]=combo1 AND [otherfield] = combo2). Not exactly as written though. Maybe research and then post a pic of your new table relationships window? The problem with Access is that it is too easy to make a poorly designed db.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[mike60smart]

Hi

Take a look at the attached.