1 parameter controlled by multple combo boxes on a form (1 Viewer)

[ajetrumpet]

Shapman,

The attached ZIP works the way in which you asked about, all except for the SubStatus combo box. I'm afraid your value list for the SubStatus combo box comes from the values in your "SUBstatus" table, not the "tblSTATUS", which is the one you have joined in the underlying query. Thus, until you either change the values for the substatus combo box rowsource, OR create an appropriate relationship between the contactdetails table and the substatus table, you will not be able to include the substatus combo box in your search.

Notice the way the query is written. It is an exact replica of the format in Post #4.

 

[shapman]

Thanks again for this. On your query about linking table status type to tablegeneralcontactdetails I am not entirely sure why i need to do this. the tblstatustype and tblstatus have been set up purely as row sources and are stand alone references within the database. tblmedgroups and tblpargroups are not actually attached or need to/for anything but simply there for my reference (sorry if this creates confusion, when i was making the cascading combos for the linked status box). So table statustype doesnt actually need to be connected as it is just really the values in a combo, not a record of combos?

I will, however need to change the tblmailiast ID to FK to make it clearer.

Many thanks for putting the sql sequence for me. As much as this might annoy you I still can't seem to get it working the way i need. This may again be because of the language i used. My old diplomacy professor used to say the first rule of diplomacy is 'don't negotiate in a language you don't fully understand', as simple as this may be i think it counts for Access now as well - so i think the way ive communicated things has made things a bit confusing.

The way the current SQL runs is to search for occurances of, say, pub1 AND pub2 in a record. This is not quite what i need because it makes a combination match rather then the cbo being independent searches to each other. i need it to run so any records with pub 1 OR/AND pub 2 are displayed, this will obviously create some duplicates but these can be taken away by unique values in properties. this is the same as pub3,4,5 etc.

thanks again for your work and help with this.

Shapman

 

[ajetrumpet]

Thanks again for this.

Welcome...

Take a look at the DOC.

Tell me if you can follow any of them. Or rather, if one of them is what you're looking for. You might want to test them first.

By the way, if you can understand what the difference is between SQL 2 & 3 in this attachment, you're further along than most when it comes to understanding these annoying operators...

 

[shapman]

Welcome...

By the way, if you can understand what the difference is between SQL 2 & 3 in this attachment, you're further along than most when it comes to understanding these annoying operators...

At first glance i.d. say that SQL 2 requires a Pub whereas SQL 3 does not.

I will get cracking with the testing of these, im actually watching mission impossible on TV at the same time...

 

[shapman]

At first glance i.d. say that SQL 2 requires a Pub whereas SQL 3 does not.

I will get cracking with the testing of these, im actually watching mission impossible on TV at the same time...

I think now understand the problem with the substatus... without linking these tables the query wont know that some statuses do not have a substatus and read the substatus as null which, therefore, returns no results. If i link them the query will know that some status's don't have substatus and will be expecting a null return for them... is this right?

 

[shapman]

in terms of linking the generalcontact details table - although i now understand what you mean i am stuggling to design it. I am looking at it in terms of it needs to be done through the tblstatus.

the problem is I created tblstatustype and tblsubstatus just to create cascading combo boxes in the frmgeneralcontactdetails (through the sub form subfrmstatus. these cascading combo boxes then feed tblstatus its data. the problem here is that tblstatustype was not designed to be linked to generalcontactdetails. The only way i can think of doing this is through tblstatus (as a juction tbl), but i can't see how this could be done without ruiinng other parts of the database.

It seems that sql sequence 3 would be the best option, but like you said i have to sort this little problem out. I don't really want to change the referential intergrity between tblstatustype and tblsubstatus as this will effect the cascading combo box's in subfrmstatus. but not sure how i can integrate this into tblstatus (and therfore create a link with tblgeneralcontactdetails)

thanks in advance, i can see the light at the end of the tunnel with this, its just this final hurdle i think.

Shapman

 

[ajetrumpet]

without linking these tables the query wont know that some statuses do not have a substatus and read the substatus as null which, therefore, returns no results. If i link them the query will know that some status's don't have substatus and will be expecting a null return for them... is this right?

I think in your language, YES.

I need to know if I'm right here too...

**tblStatusType holds different "statuses" and their descriptions??

**tblSubStatus holds different "substatuses" that can be coupled with each "status"??

What's the difference between the values in these two fields...

1) field = "Status" (Table = "tblstatus")
2) field = "StatusDesc" (Table = "tblstatustype")
(might be able to help with the relationship issue if I can get some answers to these)

Also Shapman,

The cbo (combo box) "SubStatus" has a RowSource = qry_frmemailsubsta. There is no point to this, you might as well just set the RowSource = "SubStatus" field from "tblSubStatus".

 

[shapman]

I think in your language, YES.

I need to know if I'm right here too...

**tblStatusType holds different "statuses" and their descriptions??

**tblSubStatus holds different "substatuses" that can be coupled with each "status"??

What's the difference between the values in these two fields...

1) field = "Status" (Table = "tblstatus")
2) field = "StatusDesc" (Table = "tblstatustype")
(might be able to help with the relationship issue if I can get some answers to these)

Also Shapman,

The cbo (combo box) "SubStatus" has a RowSource = qry_frmemailsubsta. There is no point to this, you might as well just set the RowSource = "SubStatus" field from "tblSubStatus".

Hi


glad to see i understood the relationships issue

yes, your descriptions of tables statustype and substatus are correct. The relationship is set up as one status can have many substatus. (in realiaty only two Statuses have any substatus at all).

"Statusdesc" in tblstatustype feeds the drop down list for the status in tblstatus. Therefore, one is a list of values whilst the other uses these values as possible status's for each contacts. i.e. provider and user of a list.

The qry_frmemailsubstatus is the key to the cascading combo boxes in frm email. it takes the value in the status combo box in frmsubtatus and filters for the appropriate substatus. this was the only way i could get the cascading combo boxes to work. (I really did try a lot of VBA, i think some is still in there under after click event procedure which is currently switched off).

With the risk of writing an Essay for this particular post i have also come up with a potential problem with the search. This is only applicable to those status's which do have different substatus and where I would use SQL3 for the search (Media and Parliamentary). I also need the status to be search independently of substatus. i.e all media or all parliamentary. I don't think this would be possible with SQL3. However, because the relationship between status and substatus must ensure a filter for records with just those combinations in can't see how to get round this (SQL1 with give both records with that status or with that substatus). so here is the combantions i can think of:

Status
Status and Substatus (this is the sticky issue)
Substatus
None or any of the cbopub

the easiest way i thought of getting around this is to have a seperate search button for just status type. also, thought about doing this for 'ALL' email addresses (these would be easy but it feels like i would be cheating somewhat)

thanks in advance
Shapman

 

[shapman]

I think in your language, YES.

I need to know if I'm right here too...

**tblStatusType holds different "statuses" and their descriptions??

**tblSubStatus holds different "substatuses" that can be coupled with each "status"??

What's the difference between the values in these two fields...

1) field = "Status" (Table = "tblstatus")
2) field = "StatusDesc" (Table = "tblstatustype")
(might be able to help with the relationship issue if I can get some answers to these)

Also Shapman,

The cbo (combo box) "SubStatus" has a RowSource = qry_frmemailsubsta. There is no point to this, you might as well just set the RowSource = "SubStatus" field from "tblSubStatus".

Hi


glad to see i understood the relationships issue

yes, your descriptions of tables statustype and substatus are correct. The relationship is set up as one status can have many substatus. (in realiaty only two Statuses have any substatus at all).

"Statusdesc" in tblstatustype feeds the drop down list for the status in tblstatus. Therefore, one is a list of values whilst the other uses these values as possible status's for each contacts. i.e. provider and user of a list.

The qry_frmemailsubstatus is the key to the cascading combo boxes in frm email. it takes the value in the status combo box in frmsubtatus and filters for the appropriate substatus. this was the only way i could get the cascading combo boxes to work. (I really did try a lot of VBA, i think some is still in there under after click event procedure which is currently switched off).

With the risk of writing an Essay for this particular post i have also come up with a potential problem with the search. This is only applicable to those status's which do have different substatus and where I would use SQL3 for the search (Media and Parliamentary). I also need the status to be search independently of substatus. i.e all media or all parliamentary. I don't think this would be possible with SQL3. However, because the relationship between status and substatus must ensure a filter for records with just those combinations in can't see how to get round this (SQL1 with give both records with that status or with that substatus). so here is the combantions i can think of:

Status
Status and Substatus (this is the sticky issue)
Substatus
any of the cbopub

the easiest way i thought of getting around this is to have a seperate search button for just status type. also, thought about doing this for 'ALL' email addresses (these would be easy but it feels like i would be cheating somewhat)

thanks in advance
Shapman

 

[shapman]

Hi


frm email. Shapman

sorry this should be frmsubstatus

 

[ajetrumpet]

The qry_frmemailsubstatus is the key to the cascading combo boxes in frm email.

No, it's not.

it takes the value in the status combo box in frmsubtatus and filters for the appropriate substatus.

No, it doesn't. It takes the values from the STATUS TABLE and LISTS them in the dropdown just as they are stored in the table.

this was the only way i could get the cascading combo boxes to work.

Actually Shapman, you don't even need this form. It is a redundant relation to the query "qry_frmemail..."

The "qry_frmemailsubstatus" pulls values from the TABLE (as I mentioned above). And so does the form "frmsubstatus". This is redundant, as it just adds more clutter to sift through in the database.

I know that sounded negative, but this is clearly a hangup for you that should not even be part of the equation....

Let me know if you understand this, or what you think of it. I am going to see if I can relate the two tables that were unrelated (that we were talking about a few posts ago). This may be what you need, but I'll take a look and see what I can do with it.

 

[shapman]

No, it's not.No, it doesn't. It takes the values from the STATUS TABLE and LISTS them in the dropdown just as they are stored in the table.Actually Shapman, you don't even need this form. It is a redundant relation to the query "qry_frmemail..."

The "qry_frmemailsubstatus" pulls values from the TABLE (as I mentioned above). And so does the form "frmsubstatus". This is redundant, as it just adds more clutter to sift through in the database.

I know that sounded negative, but this is clearly a hangup for you that should not even be part of the equation....

Let me know if you understand this, or what you think of it. I am going to see if I can relate the two tables that were unrelated (that we were talking about a few posts ago). This may be what you need, but I'll take a look and see what I can do with it.

Hi, thanks for this. i have just noticed my mistake in my last post. I was getting a bit confused, my database is full of clutter as you noted and i think it would be a good idea to clean it up. there arn't any cascading combo boxes in frmemail, they are only in subfrmstatus (which is the form that fills the tblstatus).

do you mean i don't even need the subfrmstatus for the wholedatabase or just for this query? because it is within the generalcontactsdetails. im getting a little more confused because you've noted the form "frmsubstatus" of which there isnt one. but i guess you meant subfrmstatus.

I think you mean i don't need all these extra queries within the database as it adds extra clutter, doing things like cascading combo boxes through queries has added extra clutter, as has the queries i used to pick the combo lists from other tables where i need the second column instead of the first (i.e. there was an ID and a status and i used the query for a row source so it would just give me the status in the combo) i guess ive been doing things the long way?

Is this what you mean?

thanks again
Shapman

 

[ajetrumpet]

Yeah, some of the stuff you have in there has been done the long way, but we're starting to get off track here...back to the problem at hand.

I am still in the process of relating the two tables that need to be, and then bringing that relationship in on the frmemail.

I will probably let you know by tomorrow what happened with it.
I will post a revised file as well.

 

[shapman]

Yeah, some of the stuff you have in there has been done the long way, but we're starting to get off track here...back to the problem at hand.

I am still in the process of relating the two tables that need to be, and then bringing that relationship in on the frmemail.

I will probably let you know by tomorrow what happened with it.
I will post a revised file as well.

Thats great! many thanks in advance.

Regards
Shapman

 

[ajetrumpet]

Shapman,

After 32 posts on this subject, I'm a bit weary, aren't you??

Anyway, attached you will find the test DB. In this one, you will notice that all of your VB code is gone from "frmemail". I deleted all of it, and just added the "OnClick" of the button and an "AfterUpdate" of the status cbo.

You will also notice that two of the four queries have been deleted. These were just duplications of field values, and they were not needed, as that was redunant (and it was confusing the *********** out of me too!).

Note:You said earlier in a post something about a TABLE getting it's values from a QUERY....This is backwards, it can't happen. QUERIES ASK sources (the tables) for data, not the other way around. I'd suggest just memorizing that fact. I personally think you are confusing yourself there.

The purpose of this DB was just to simplify things in order to get to the root problem, which is STILL the way the query criteria needs to be written (right??).

Here is what this file does...

1) Queries records based on ANY form controls that have values (i.e. SQL 1 is used). Note: The two combo boxes are cascaded, but because most of the values in the STATUS table are NULL in this field, cbo SUBSTATUS will only populate with two values in the first combo (Media and Parlimentary).
2) cbo SubStatus lists only values that ARE NOT NULL (in tblstatus.substatus), because NULL values are irrelevant in a search.
3) cbo SubStatus pulls VALUES FROM "tblSTATUS", NOT tblSUBSTATUS. Why?? Because you are querying PEOPLE (contacts) and PUBLICATIONS, not substatus values. The only situation in which you would pull values from tblSUBSTATUS is if the "substatus" values were DIRECTLY related to the search. In this case they are not.

The search is centered around PUBLICATIONS, right?? In other words, the publications are the reason you created this query in the first place.

If this is all correct, SUBSTATUS values have nothing to do with it, they simply "tag along" with their parent value ("status"), which also has a parent that it follows, and that is "contact").

Tell me what you think. Again, the file is just for clarification only, not to solve the problem of the SQL criteria. You have to crawl before you can walk.


That's enough talk from me, I'm out of breath...