Help with Form sorting

[Delta729]

Bare with me, I had a stroke a couple years ago and it's really hard for me to focus. I started to relearn Access and I thought I'd start relearning with a database that I was trying create several years ago based on Access file where I use to work.

I have form called [PurchaseOrderFrm] that is created from a query called [PuchaseOrderQry]. The form has three sections that are shaded in black that filters the form. The cboRegion and cboVendor both seem to working ok, but there is also a filter for Status. This is a listbox, and not directly tied into any of the records, but should be linked to Status drop filter (which has (Active, Inactive, & Both). The form and query has a section/column for Purchase Orders that are either Active or Inactive. I'm trying to get the filter to link and filter.

The Query has a filter for each the Region, Vendor, and Inactive(Status). However, the status is not directly linked. Am I doing this completely wrong? I mean, I was barely learning how to do this before my stroke, but something just doesn't seem right. Any help would be great.

[Micron]

The cboRegion and cboVendor both seem to working ok

Actually I think not. If I select Desert as a region I get 10 records. If I select Asplund Electrical I get 17 records. Likely it is adding what you pick to the list but I think your intention is to filter as you go? Not sure as you don't really say. If that is the case, what you're wanting is called Cascading combo boxes. If you research that you'll see how it's done. Not much point in repeating it here when there are likely millions of Google hits on this subject?

Just to help you with the vernacular and such:
- queries don't create forms, you do. Queries do provide the form with a recordsource though.
- combos have lists but they are not list boxes - those are a separate thing
- combos for filtering are usually not bound (i.e. they do not have a control source) However, if you use combos as display controls in the records (the form rows) and you play with them, you will alter underlying data without warning unless you do something to prevent that. Just so you know.

Sorry to hear about your condition. If you want to learn I leave it to you to research cascading combos and apply it to your db. If you get stuck we're here to help. It's often difficult to decide whether to just hand somebody a fish (fix their db) or guide them on how to learn to fish. I try to do the latter.

EDIT - just took a look behind some of the form code. You don't want to requery, you want to create a filter and apply it.
You should be storing regionID in purchase order table, not the word for the region.
What does inactive in that table mean? Surely not that the PO is inactive? Not sure if you realize that you have 2 PO's with same number - should not be?
I think virtually always, PO numbers like that should be numeric.
Will keep looking...

[Delta729]

I will keep trying to get it to work by seeing if I can add a few cascading combo boxes. I was trying that yesterday, but they are really confusing me. (By the way, I'm not trying to learn, just messing around and trying to get a basic db to work).

[moke123]

One probable reason your having trouble is that you used LookUp fields in you tables. These hide the actual values. While it may look like your filtering for "Asplund Tree Service" your really just not seeing the VendorID because its hidden.
Do a search for "The Evils Of LookUp fields"

[moke123]

See if this is close to what you want.

[isladogs]

For more info, see Table Lookup Fields - Mendip Data Systems and The Access Web - The Evils of Lookup Fields in Tables (mvps.org)

[Micron]

There are still 3 fields that are table level lookups. @Delta729 be aware that your design will easily allow you to delete lookup values. F'rinstance, open region form, delete Desert value and then open region table. You won't find it there, thus it will no longer be available for selection for new records. If you created a cascading delete relationship between regional manager table and other tables holding that field, those tables would lose that data in every record.

[Delta729]

Hi moke123,
That's exactly what I was trying to accomplish. I started to reset up the DB in order to try the Cascading combo boxes, but this appears to work perfectly. I like that code you added for what appears to be case select option, but when I was trying that I kept messing it up. Thanks you so much. Greatly appreciated.

[Delta729]

Hi Micron, Thanks, moke123 sent over a revised file with a case select code that seems to do the job. I had started another revised file so I could mess with the cascading combo boxes, but I think I'll wait till tomorrow or the next day for that. Thanks to all for you help.

[Micron]

NP. Good luck with your db.