Cascading Filtering

[datsandgirl]

Okay, I'm sorry, but I lied. I need to ask one more question regarding my database. This has to do with the three cascading combo boxes mentioned in my original question. I mimicked my way through creating three cascading combo boxes from solutions to questions others asked. They work great for the most part, even though I don't really understand why or how they work. However, there is one problem.

I need to be able to choose a blank for the second or third combo box if there are no applicable choices available for those companies, which happens often. For example, if there are no division choices for the second combo box and I pick the blank, upon tabbing to the third combo box, I get a error popup that says, “Syntax error (missing operator) in query expression ‘[DivisionName]=’

And if there are no choices applicable for the third combo box [cboLocations], the dropdown list shows choices from the last entry that did have choices even though they are not applicable. I need both combo boxes to allow me to choose a blank choice if that is the appropriate answer for that company. Below is what I currently have:

Private Sub cboCustomers_AfterUpdate()
       ' Update the row source of the cboDivisions combo box
    ' when the user makes a selection in the cboCustomers
    ' combo box.
    Me.cboDivisions.RowSource = "SELECT DivisionName FROM" & _
                               " DivisionTable WHERE Customers = " & _
                               Me.cboCustomers & _
                               " ORDER BY DivisionName"
 
    Me.cboDivisions = Me.cboDivisions.ItemData(0)
    Me.cboDivisions.Requery
    Me.Customer = Me.cboCustomers.Column(1)
End Sub
 
Private Sub txtSendKeys_GotFocus()
Private Sub cboDivisions_AfterUpdate()
  With Me![cboLocations]
    If IsNull(Me!cboDivisions) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Locations] FROM LocationsTable WHERE [DivisionName]=" & _
                    DLookup("[DivID]", "DivisionTable", "[DivisionName] = '" & Me![cboDivisions] & "'")
    End If
    Call .Requery
 
  End With
End Sub

Is it possible to fix this problem? Please, please help again!

**Edit**
This new question has been split from How to combine five data fields into one string for a new field.

[NeoPa]

01:00 is not a good time to drop this on me. I will need to move it to a separate thread and add all the linking between threads that you should have done. When I've done all that I will certainly look at your question. It'll all have to wait until tomorrow though I'm afraid.

[datsandgirl]

@NeoPa
I am so sorry NeoPa. Looks like I did everything wrong after you were so wonderful in helping me the first time. I am new to this website and it's rules. I am in California and didn't realize that you are in the UK when I sent the email at 5:00 p.m. PST, plus I certainly didn't realize that any communication I sent would wake you. Yikes! I've even been afraid to send this apology for fear of doing something wrong again. Please let me also add my regrets for not knowing I should have moved my question to a "new thread", but I don't even know what a "thread" is. Please accept my sincere apologies.

[NeoPa]

Ha ha. Very good :D You'll give me a complex.

I was being somewhat tongue in cheek (I hope you realised) about the time thingy, and I was just explaining why I wasn't able to get to it there and then. I don't really expect posts only to come in during my time-frame. It would make a strange forum if it worked that way. Sometimes I remember too late that joking around is harder to see when :
A. It comes from an Administrator
B. It's visible only in text, without any tonal or visual clues.

I also expect to have to nudge new members a few times before they are fully aware of all the main rules.

Rest assured, as far as I'm concerned you're still a very pleasant member to deal with and incoming posts don't wake me in the middle of the night. I was up anyway, just feeling the need for some zzz by then.

I still intend to get to this shortly. I expect to have some time free this evening for Bytes work (I rather ran out of lunch-hour some time back).

When I get around to it you'll be notified about the change and you should be able to find the new thread easily so there shouldn't be any need to worry.

[NeoPa]

Right, I can start to look at this properly now.

I think the first thing we'll need to do is tidy up some of the code, and maybe clarify exactly what you're after. Line #15 starts a procedure, then line #16 starts another one (without the previous one being completed anywhere). Also there seems to be code only for cboCustomers & cboDivisions. Nothing for cboLocations, and nothing to show what the eventual filtering is for (I imagine the three ComboBoxes are there to filter something).

I'd like to know what the ComboBoxes are designed to filter.

Lastly, this concept (of filtering when something exists but also handling non-existent items where necessary) is perfectly manageable. It's only the lack of understanding of what the actual issue is that stops me suggesting a solution now.

[datsandgirl]

@NeoPa
Thank you NeoPa for tackling my clumsy questions again. I’ll try my best to answer your questions and make sense at the same time, but can only hope for total success at the "make sense" part. ;-)

Our company makes component parts for other companies (our “customers”). The purpose of this database is to track the heritage of those components, as well as the amount and type of business we do with each customer. I am lucky in that although our company has been in business for decades, this database will be the first centralized tracking tool utilized for this purpose and I am therefore free to organize it as I see fit. In the past, without a central data depository, every individual in the company kept their own records in their own personalized format. As a result, it’s nearly impossible to tell for instance how many different components we have made over the years for Acme Widget Corporation because some people have recorded the customer’s name as either “Acme”, “Acme Widget”, “Acme Widget Corp.”, or even “AWC”. Additionally, some customers have multiple divisions that we contract with separately from the rest of the client company. The Submarine Division of Acme has little or no connection to the Satellite Division, however, not everyone in our company bothers to specify which division, if any, we are contracted with when recording data. Then as a final complication, some customers have multiple physical locations, but only one division, and some might have multiple divisions that are all in one location, but do business separately from each other. Clear as mud?

Anyway, my goal is to standardize, specify, and identify each client company uniformly across the board. I want anyone using the database to be forced to use only the predetermined authorized format to identify a customer. So I thought cascading combo boxes that limit a user to a list would work best. I don’t want anyone to be able to skip making a deliberate choice in all three combo boxes. When recording a new component in the database, I need a user to be forced to first choose a client company’s name from the initial dropdown list [cboCustomers], then either have to choose a division name or choose a blank when there is no division name from the second combo box [cboDivisions], and lastly to choose a location if one is applicable or choose the blank option if appropriate from the third combo box [cboLocations]. The three boxes together should force a consistent and clear identification of each customer we are contracted with for each component. All three combo boxes have their Control Source in the main table named ComponentsAndAssemblies.

I’m afraid that I cannot answer your question about there being no code for cboLocations. I copied and pasted code from a number of other cascading combo box examples, substituting their combo box names, etc., with my names in order to get these cascading combo boxes to work at all. If I saw that someone else online was having the same problem as me, I would copy and paste that little bit of solution code into mine to see if it would help fix my problem. So I’m not surprised at all if there is code in my combo boxes that does nothing or creates a problem. I can’t even pretend to understand the code in any way, shape, or form. It’s like having learned to sing along with a song being sung in Latin verse even though I don’t understand one word of Latin!

Thanks again NeoPa for your patience with me. Please let me know if I need to clarify anything further. I will certainly give it the old Latin try!

[NeoPa]

That was very clearly explained, and gives me almost all I need to know.

Normally, I wouldn't get in to quite so much detail as I'm about to on this. The fact that I choose to on this occasion is that you've been clear and honest with me about your problem as well as your understanding of the issues. Hopefully, I can help with the particular issue as well as help you to understand the issues a little better.

With this in mind I will go off and prepare some initial questions about your form etc, and what you're hoping to end up with. Can I suggest to you though, in the mean time, to have a look through the following tutorial (Example Filtering on a Form) and have a bit of a play with it. It has a database you can download. See if you can get to grips with some of the concepts outlined in the tutorial as I believe this will help directly in understanding what we are about to work on in your own database.

[NeoPa]

So far, we have little or no information about the layout of the [ComponentsAndAssemblies] table. We will need some metadata I'm sure.

This will work much better if you can post the meta-data (info about the layout / structure) of the table in the same way as I use in my example. Click on the Reply button (and then Advanced) and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine (Remembering of course, that BB Code takes up no space in the resultant post).
Table Name=[tblStudent]

Field           Type      IndexInfo
StudentID       AutoNumber    PK
Family          String        FK
Name            String
University      String        FK
Mark            Numeric
LastAttendance  Date/Time

Moving on. I get that you want to identify the customer at the proper detailed level (including division and/or location where they exist), but I don't know how this would fit onto the form as I know nothing about the table your form is bound to (other than it's for entering new components). If you could provide the name and metadata for that table too, that would be quite helpful.

Lastly, I would need the names of any relevant controls on the form. I can only think of a control for storing the ID for the Customer/Division/Location for now. If anything else occurs to me I'll let you know. Actually, depending on how your data is structured it may be the case that cboLocation reflects this value, but I expect otherwise. let me know anyway.

[datsandgirl]

@NeoPa
NeoPa:

I have been trying all day to no avail to get a chance to respond to your requests because I will be out of the office until next Monday. I have now run out of time and have to leave. I hope you don't mind that I will not be able to respond to your requests until Monday at the earliest. You have certainly been more than patient with me already, and I thank you. Hope you have a good weekend!

[NeoPa]

I have a very busy weekend ahead and I'm struggling to keep up with a bunch of threads as it is due to various Admin level discussion going on heavily this week.

So, no. I'm not remotely upset to hear you won't be responding until Monday :D

Have a great weekend and relax about this. I have plenty to keep me busy.