How can I make a combo box value on a subform based on the combo value on a main for

[mrsgwen90]

I have a main form with a subform on the main form. The main form have a couple of text boxes and a combo box. The subform have a couple of text boxes and a combo box. I want the values in the combo box on the subform to be based on the values in the combo box on the main form.

For instance, the combo box (cboCartStatus) on the main form will have the values: INSERVICE, AVAILABLE, UNKNOWN, DISPOSED and
the combo box (cboCartEvent) on the subform will have the values: DELIVERY, DISPOSAL, FOUND, MISSING, REMOVAL, REPAIR, STOLEN, UNKNOWN.

if the cboCartStatus is "INSERVICE", the only values that need to show in the cboCartEvent on the subform is "DELIVERY", "REPAIR"

if the cboCartStatus is "AVAILABLE", the only value that need to show in the cboCartEvent on the subform is "REMOVAL"

[NeoPa]

That can be done a number of ways, but which way to choose depends on information you haven't included in your question.

Such as :

1. How are you populating the ComboBox on the main form?
2. How are you populating the ComboBox on the sub-form?
3. When and how are the values of these ComboBoxes changed (under what circumstances)?

[mrsgwen90]

On both the Main Form and the Sub Form the combo box is being populated from a table.
A table called tbl_CartInfoCodes. it is using the fields: Code_Desc and Code_Type.

Here is the Query that I am using right now for the combo box on the Main form :

SELECT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
FROM dbo_tbl_CartInfoCodes
WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartStatus"))
ORDER BY dbo_tbl_CartInfoCodes.Code_Type;

here is the query that I am using right now for the combo box on the Sub form which shows all of the values :

SELECT DISTINCT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
FROM dbo_tbl_CartInfoCodes
WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartEvent"));

PS. The user chooses a value from the combo box

[sierra7]

Hi
The query in your first combo does not make sense to me. I don't believe that you need the WHERE clause

WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartStatus"))
 

This will only allow values in your main combo where [Code_Type] equals the literal text "CartStatus". This may be right but I am guessing not.

I believe that the bound field in cboCartStatus should be your second column, [Code_Desc] and this should also be its Control_Source. The query can be simply;-

SELECT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
FROM dbo_tbl_CartInfoCodes
ORDER BY dbo_tbl_CartInfoCodes.Code_Type;
 

You are doing cascade filtering but your problem is exacerbated by needing to filter for the child record in your subform.
I believe that the query in cboCartEvent should be

SELECT DISTINCT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
FROM dbo_tbl_CartInfoCodes
WHERE (((dbo_tbl_CartInfoCodes.Code_Type)='" & _
Me.Parent.Code_Type & "'));" 

You may need to force a Save Record after making your choice in you main form

Public Sub cboCartStatus_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord

and you may need to do a Me.Requery in the On_Enter event for cboCartEvent.
See how you get on.
S7

[mrsgwen90]

OK,I forgot to explain. The CartInfoCode tables looks like this:

Code_Types:
CartDisposal
CartEvent
CartRepair
CartStatus
ServiceEvent
ServiceStatus

and for each Type there is a Code_Desc

CartDisposal - Damaged, Fell In Truck, Burned, Other Missing, Stolen
CartEvent - Disposal, Stolen, Delivery, Removal, Repair, Missing, Found, Unknown
CartRepair - Lid, Wheel, Axle, Pin, Bar, Hinge, Other
CartStatus - InService, Available, Disposed, Unknown
ServiceEvent - Service Start, Service Stop, Bill Cart, Bill Final
ServiceStatus - Active, NotActive, Vacant, Private, Suspended

[NeoPa]

It seems clear that there was a lot more important detail missing from the question than I'd even realised. I'm guessing that what determines the values you wish [cboCartEvent] to include is actually the [Code_Type] of the row selected in [cboCartStatus].

As S7 says in his post, you could handle this with cascaded filtering, but I expect doing that properly (I would never advise replacing the whole SQL in code when all that needs changing is the WHERE clause as that is a recipe for problems when any changes are made later to the design.) might be a bridge too far for you right now (It would involve manipulating the existing SQL string and changing just the relevant part of the WHERE clause).

With this in mind I would suggest that you include a DLookup() call using a reference to [cboCartStatus] in your SQL for [cboCartEvent] of the form :

SELECT DISTINCT 
       [Code_Desc]
     , [Code_Type]
FROM   [dbo_tbl_CartInfoCodes]
WHERE  ([Code_Type] = DLookup('[Code_Type]',
                              '[dbo_tbl_CartInfoCodes]',
                              '[Code_Desc] = ''' & Forms!MainForm!cboCartStatus & '''')

Admittedly, this is all based on the assumption I made earlier as to what your question should have been, but if that isn't the case then I'm sure you can clarify by asking your question properly so that it does make sense.

PS. This was prepared over the time you last posted, but thankfully your post confirms my guess rather than contradicting it so it should work.

PPS. You may well need to call a .Requery of [cboCartEvent] in the AfterUpdate event procedure of [cboCartStatus].

[mrsgwen90]

ok I see your point but I guess I'm still not explaining it correctly. I need to basically tell it what values to use in the Subform combo box based on the value selected in the main form combo for instance.

If INSERVICE is selected in the main form combo box. I need the subform combo box to only show the values DELIVERY, REPAIR (which are CartEvent type descriptions).

If AVAILABLE is selected in the main form combo box. I need the subform combo box to only show the values REMOVAL, FOUND (which are CartEvent type descriptions).

[NeoPa]

MrsGwen90:
ok I see your point but I guess I'm still not explaining it correctly.

No. Not even now, and not even close.

I see no correlation between the data (Showing that data was the first helpful step in the thread) and what you're requesting. This means it's not only not explained, but also that it's not even guessable from all the information so far posted.

You use "CartEvent type description" in your explanation but never explain what that even refers to.

Your data shows the following [Code_Type] along with the associated [Code_Desc]s.

CartStatus - InService, Available, Disposed, Unknown

In what possible logic could that indicate a particular subset of the [Code_Desc]s found for :

CartEvent - Disposal, Stolen, Delivery, Removal, Repair, Missing, Found, Unknown

It seems you need to take a step back and consider explaining the question properly, such that when we read it we'll have a clue as to what you're trying to achieve. Please read it back to yourself before posting and ask yourself if you could understand enough about the problem just by reading what you propose to post.

[mrsgwen90]

I'm sorry, I have explained it the best way that I could....
Let me try again..

I have a Form that has a subform embedded in it. On the main form I have a combo box called "cboCartStatus". On the subform I have a combo box called "cboCartEvent". When a value is chosen in the cboCartStatus combo box, the values in the cboCartEvent (subform) has to correlate. Basically I'm trying to Cascade the combo boxes.

The RowSource for both of the combo boxes is a query from the table tbl_CartInfoCodes. The table has 3 fields: ServiceStatusID, Code_Type, and Code_Desc.

There are code descriptions for each code type. For instance, For the Code_Type CartEvent, there are 8 code_desc (Stolen, Disposal, Delivery, Removal, Repair, Missing, Found, Unknown) and for Code_Type CartStatus there are 4 code_desc (Unknown, InService, Disposed, and Available).

So if the user choses the "InService" from the cboCartStatus combobox (combo on main form), then the cboCartEvent combo box (combo on the subform) need to only show 2 of the code_desc ("Delivery" and "Repair").

Now here is where the difficulty comes in. The person who created the application did not properly normalize the CartInfoCodes table (IMO, which is the RowSource for both of the combo boxes.

I have attached a copy of the table.

[NeoPa]

MrsGwen90:
So if the user choses the "InService" from the cboCartStatus combobox (combo on main form), then the cboCartEvent combo box (combo on the subform) need to only show 2 of the code_desc ("Delivery" and "Repair").

This repeats the instructions you gave us already. What is still missing is the link between the two.

Let me put that another way :
How do I, an outsider, know which [Code_Desc] values should be available in [cboCartEvent]? What is the correlation between a selection of "InService" from [cboCartStatus] and the values available in [cboCartEvent]?

No-one can put logic into code if that logic is never explained in the first place. Does that start to make sense? Can you see now what you need to tell us?

[mrsgwen90]

ok let me try and explain......

The entire form deals with Carts. The correlation is When a Cart has a status of "InService" then the event that can take place with the Cart is it has been "Delivered" OR it has been "Repaired" while it is still in Service.

On the Other hand, if a cart has a status of "Available" that means the cart has either been "Removed" or if the cart was lost and they found it - the CartEvent will be "Found" which will make it Available again to use.

I can easily put all of the CartEvents in the dropdown but I want to make sure the user doesn't choose one that doesn't relate to the CartStatus.

I have attached a copy of what the form looks like..

[NeoPa]

What I have worked out from looking at the attached document (which would have been much easier if you'd just posted the information as text in a post) is that each ComboBox uses only a small subset of the records in the table.

[cboCartStatus] uses only those records with a [Code_Type] of "CartStaus" and [cboCartEvent] uses only those records with a [Code_Type] of "CartEvent". It seems the rest of the data is not related to this question at all. It also seems, best guess, that the other types include in post #5, but not in the document attached, are equally irrelevant to the question. I say best guess as normally such a change of information would mean something (Actually, it wouldn't even occur. If it did it would be rigorously explained). If I'm right though, it is just random noise in this case.

This is some progress against the tide, but we still have no idea why the selection of "INSERVICE" from [cboCartStaus] should cause the normal list for [cboCartEvent] of "STOLEN; DISPOSAL; DELIVERY; REMOVAL; REPAIR; MISSING; FOUND; UNKNOWN" to be reduced to a list consisting simply of "DELIVERY; REPAIR".

[mrsgwen90]

The list of cboCartEvent need to be reduced to consist of only Delivery, Repair because even though they are CartEvents those are the only two relevant to INSERVICE and it would eliminate the user from chosing one of the others. Just like Repair and Found would only be relevenat to AVAILABLE and so on...

[NeoPa]

I'm sure you know which go with which, but until you explain the correlation to us then we don't. I'm struggling to see why this has become such a struggle. Am I not communicating in simple English? I really cannot see what is so confusing.

[mrsgwen90]

I guess I am not understanding what you mean by correlation.

[NeoPa]

I can work with that (I think).

The correlation is a set of rules which map the selected item from [cboCartStatus] onto the required subset of available items for [cboCartEvent]. We know already that the selection of "INSERVICE" maps to a list of "DELIVERY; REPAIR", but we don't know the subsets for any of the other possible entries.

Typically, this would be handled by an extra field in the table specially designed to handle such a correlation. The complexity of this field would depend on whether or not any individual value could belong to only one such subset or could possibly belong to more than one. I have no idea what your correlation is, so the following illustration is simply a possible scenario :

CartEvent  Correlation
STOLEN     DISPOSED
DISPOSAL   AVAILABLE
DELIVERY   INSERVICE
REMOVAL    AVAILABLE
REPAIR     INSERVICE
MISSING    DISPOSED
FOUND      AVAILABLE
UNKNOWN    UNKNOWN

With such a correlation indicated and stored in your table we could amend the source of the ComboBox to select only those records whose [Code_Type] = [CartEvent] and whose [Correlation] = [cboCartStatus].

[mrsgwen90]

ok, now I'm beginning to understand what you are saying...


cboCartStatus Correlation
1. InService Delivery, Repair
2. Available Removal, Found
3. Disposed Disposal
4. Unknown Missing, Stolen, Unknown

[NeoPa]

OK. Good.

Are you going to set these simple correlations up in your table as in my illustration then? That would certainly be a more appropriate way to handle the situation. It can be done purely in code, but I would certainly advise against it. It is a small job to add the extra ([Correlation]) field to the table, and from there the code is much more straightforward.

The following SQL should then work for you :

SELECT DISTINCT 
       [Code_Desc]
FROM   [dbo_tbl_CartInfoCodes]
WHERE  ([Code_Type] = 'CartEvent')
  AND  ([Correlation] = Forms!MainForm!cboCartStatus)

[mrsgwen90]

is this the absolute only way to do it? This access application has a sql backend and to manipulate the table I would have to go through our database administrator which would be like pulling teeth.

[mrsgwen90]

Is there a way to maybe do a Case statement that will work with how the table is designed now?

maybe something like this:

 Select Case cboCartEvent
  Case INSERVICE
       cboCartEvent = DELIVERY, REPAIR
  Case AVAILABLE
       cboCartEvent = REMOVAL, FOUND
  Case DISPOSED
       cboCartEvent = DISPOSAL
  Case UNKNOWN
       cboCartEvent = MISSING, STOLEN, UNKNOWN
 
End Select
 

just a thought.....

[NeoPa]

MrsGwen90:
is this the absolute only way to do it?

I hear you, and No - It's just the most straightforward.

An alternative oft used in such circumstances is to create your own local table to support this functionality. It does mean that any changes made to the original source data would need to be duplicated separately (and often manually) to your shadow table.

Your extra local table would look like this (Call [tblX] whatever you want) :

Table = [tblX]

Field        Type
CartEvent    String
Correlation  String

Its contents would look like :

CartEvent  Correlation
DELIVERY   INSERVICE
DISPOSAL   DISPOSED
FOUND      AVAILABLE
MISSING    UNKNOWN
REMOVAL    AVAILABLE
REPAIR     INSERVICE
STOLEN     UNKNOWN
UNKNOWN    UNKNOWN

To get the most reliable linkage (IE. when the data is not up-to-date you see a problem) use the following code :

SELECT [Code_Desc]
FROM   [dbo_tbl_CartInfoCodes]
WHERE  ([Code_Type] = 'CartEvent')
  AND  ([Code_Desc] In(SELECT [CartEvent]
                       FROM   [tblX]
                       WHERE  ([Correlation] = Forms!MainForm!cboCartStatus))

[NeoPa]

MrsGwen90:
Is there a way to maybe do a Case statement that will work with how the table is designed now?

There is a way, but not as straightforwardly as that in Jet SQL. There are very many reasons why it wouldn't be a great idea even if the code were less complicated though.

You're designing a database. Why not take advantage of that and let Access do the complicated stuff for you. It's designed that way ;-)