vba issues with requery in subform with filters

[Susy]

I use oft cascading comboboxes and they are great when getting the specific data, but in my cases, I am trying something else. And as you said, it is failing miserably...



If I come up with something I will post it; otherwise I am still open for some tips or pieces of code.

Thanks anyway.

[davegri]

The comboboxes have to function in several directions... cascading, as the term implies, one after the other.

And therein lies the problem. Each combobox's rowsource would need to be modified depending on what each of the other comboboxes' existing selections are, programatically changing after every dropdown selection. Quite an unmanagable tangle of code.
This could be simplified if the user was forced to use the comboboxes in a specific order, and not allowed to go back and change a former selection once chosen.
Again, Google "cascading comboboxes access". What you need to know is there.

[ssanfu]

My comments.....

Posting a sample dB that is not the real dB is of no help. Most of the time the sample dB has nothing to do with the actual problem(s) in the real dB.
I've seen this time and time again. And it is such a waste of time.....

If the actual dB has sensitive/confidential data, munge the data. Then there will be actual data to look at and we can see the dB structure, queries and forms.


The code in the example dB you posted needs work to clean it up. A lot of work (IMHO).
So in your form(s), you could have a option group to select simple filtering or cascading options. Code behind the combo boxes would run different subs, depending on the option selected.


As far as the cascading combo boxes, MVP A.D.Tejpal has a a sample dB, "Form_SearchByMultipleListBoxes" at http://www.rogersaccesslibrary.com/forum/topic403.html with 3 examples of cascading combo boxes (actually he used list boxes, but they are basically the same thing).

Since you want to be able to vary the selection order, look at the "Style C" example.

Be forewarned, this is VBA intensive! You have to/should know VBA very well.

[Susy]

Thanks guys...

"Style C" is what I would love to implement in my sample db but with comboboxes. The sample db is almost identical to the original db. So don't worry if you would like to play around with it. I will be able to use it.

I have looked at the code from MVP A.D.Tejpal , OMG, you guys are profis...

The code in my sample db is fine and it does its work perfectly... Again, this code is thanks to austin72406, https://www.youtube.com/watch?v=choPri7y_o4

I think it is going to take me some months to implement this functionality :-(... At least it is good to know that somehow already did it and very efficient but with list boxes :-\

I wonder if there is another way to implement it with comboboxes and more with more basic code... I am just an advanced beginner ;-) -- Who came up with this title for me? :-)

Thanks for your input.

[ssanfu]

"Style C" is what I would love to implement in my sample db but with comboboxes.

As I said, it is doable, but will take knowledge of VBA to convert to combo boxes - and having 5 combo boxes........

The code in my sample db is fine and it does its work perfectly

IMO, there are issues with the code.
- I have had problems using

If IsNull(Me.cboCustomerType) Then

because there could be a space in the control. I now use code like

    If Len(Trim(Me.cboCustomerType & "")) > 0 Then

to convert a NULL to an empty string.

- Another issue are the look up FIELDS in the table. See http://access.mvps.org/access/lookupfields.htm
I understand WHY the look up fields are there, but it is considered a bad practice.
It's easy for me to avoid look up FIELDS because I never use datasheet view.
(and if you have time http://access.mvps.org/access/tencommandments.htm)


- BTW, "State" is a reserved word in Access and shouldn't be used as an object name.

- I would not be changing the record source, I would set the filter property instead. Build just the criteria and set the filter........
See the dB....

<rant>
The last thing is that we don't know each other. We don't know how experienced you are, so questions are asked. Or your problem is not described well enough to know how to answer and more questions need to be asked.

to June7 :Why do you even bother to answer... it is not as easy as "cascading comboboxes". If you cannot do anything with the sample I provided, then what can you do, besides writing non constructive comments.

<snip>.......

To me, this is a very rude response to someone trying to help you. I responded in a previous post that you were looking for cascading combo boxes...I didn't know if you used them or had even heard of them. Apparently you have used them.

The point being, I thought long and hard weather or not I was going to try and help you.
I spent around 15 minutes searching for the dB (I forgot where I see it). Luckily, I found it.

I do have a list of people I will not respond to because of their attitude. I try to make allowances because we type and cannot talk, but still, when asking for help, it pays to be nice.

<end rant>

Anyway, I'm happy that the dB from MVP A.D.Tejpal was what you were looking for.


Good luck with your project..........

[Susy]

Hi ssanfu,

thanks for your comments. as you might be aware I am new in this vba world. Of course I cannot really tell what is good or bad practice. Thanks for your code.
Regarding my behaviour before, I went overboard, I admitted.

I see you attached a file. I am going to take a look at it.

Thanks in advance.

[orange]

Susy,
I have just read the thread and have a couple of questions.
Do you have a clear specification regarding the choices a user could make? It seems there is some uncertainty in the possible choices, the order of those choices and the ability to change the order in mid-process.
Although it seems you have a preference to use comboboxes, and the sample suggested by ssanfu uses listboxes, can you create a simpler version of your set up (say 2 or 3 choices) and get that working instead of 5 or multiple choices?. You can always expand a technique once you get "the pattern" working.

I have been on a different forum with AD Tejpal and can assure you that his samples and articles are always top notch.

Similarly, June and ssanfu have made many responses to posters and always give focused replies/suggestions to the degree commensurate with the info provided.

Good luck with your project.

[Susy]

Hi Orange,


I will focus on three comboboxes like in the sample database I attached before... because otherwise I will be impossible... it is already almost impossible for me.

the order? hmmm, the user can start with any combobox depending on what he is looking for.

the sample from MVP A.D.Tejpal is quite complicated for me, but let's see if I am able to pull it through.

Thanks to you all.