By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,072 Members | 1,200 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,072 IT Pros & Developers. It's quick & easy.

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

P: 37
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"
Nov 16 '11 #1
Share this Question
Share on Google+
21 Replies


NeoPa
Expert Mod 15k+
P: 31,709
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)?
Nov 17 '11 #2

P: 37
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
  2. FROM dbo_tbl_CartInfoCodes
  3. WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartStatus"))
  4. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
  2. FROM dbo_tbl_CartInfoCodes
  3. WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartEvent"));
PS. The user chooses a value from the combo box
Nov 17 '11 #3

Expert 100+
P: 446
Hi
The query in your first combo does not make sense to me. I don't believe that you need the WHERE clause
Expand|Select|Wrap|Line Numbers
  1. WHERE (((dbo_tbl_CartInfoCodes.Code_Type)="CartStatus"))
  2.  
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;-
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
  2. FROM dbo_tbl_CartInfoCodes
  3. ORDER BY dbo_tbl_CartInfoCodes.Code_Type;
  4.  
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
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT dbo_tbl_CartInfoCodes.Code_Desc, dbo_tbl_CartInfoCodes.Code_Type
  2. FROM dbo_tbl_CartInfoCodes
  3. WHERE (((dbo_tbl_CartInfoCodes.Code_Type)='" & _
  4. Me.Parent.Code_Type & "'));" 
You may need to force a Save Record after making your choice in you main form
Expand|Select|Wrap|Line Numbers
  1. Public Sub cboCartStatus_AfterUpdate()
  2. 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
Nov 17 '11 #4

P: 37
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
Nov 17 '11 #5

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.        [Code_Desc]
  3.      , [Code_Type]
  4. FROM   [dbo_tbl_CartInfoCodes]
  5. WHERE  ([Code_Type] = DLookup('[Code_Type]',
  6.                               '[dbo_tbl_CartInfoCodes]',
  7.                               '[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].
Nov 17 '11 #6

P: 37
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).
Nov 17 '11 #7

NeoPa
Expert Mod 15k+
P: 31,709
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.
Expand|Select|Wrap|Line Numbers
  1. CartStatus - InService, Available, Disposed, Unknown
In what possible logic could that indicate a particular subset of the [Code_Desc]s found for :
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 17 '11 #8

P: 37
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.
Attached Files
File Type: zip CartInfoCodes.zip (49.8 KB, 71 views)
Nov 18 '11 #9

NeoPa
Expert Mod 15k+
P: 31,709
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?
Nov 18 '11 #10

P: 37
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..
Attached Files
File Type: zip Doc1.zip (62.7 KB, 68 views)
Nov 18 '11 #11

NeoPa
Expert Mod 15k+
P: 31,709
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".
Nov 18 '11 #12

P: 37
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...
Nov 18 '11 #13

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 18 '11 #14

P: 37
I guess I am not understanding what you mean by correlation.
Nov 18 '11 #15

NeoPa
Expert Mod 15k+
P: 31,709
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 :

Expand|Select|Wrap|Line Numbers
  1. CartEvent  Correlation
  2. STOLEN     DISPOSED
  3. DISPOSAL   AVAILABLE
  4. DELIVERY   INSERVICE
  5. REMOVAL    AVAILABLE
  6. REPAIR     INSERVICE
  7. MISSING    DISPOSED
  8. FOUND      AVAILABLE
  9. 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].
Nov 18 '11 #16

P: 37
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
Nov 21 '11 #17

NeoPa
Expert Mod 15k+
P: 31,709
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.        [Code_Desc]
  3. FROM   [dbo_tbl_CartInfoCodes]
  4. WHERE  ([Code_Type] = 'CartEvent')
  5.   AND  ([Correlation] = Forms!MainForm!cboCartStatus)
Nov 21 '11 #18

P: 37
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.
Nov 21 '11 #19

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

maybe something like this:
Expand|Select|Wrap|Line Numbers
  1.  Select Case cboCartEvent
  2.   Case INSERVICE
  3.        cboCartEvent = DELIVERY, REPAIR
  4.   Case AVAILABLE
  5.        cboCartEvent = REMOVAL, FOUND
  6.   Case DISPOSED
  7.        cboCartEvent = DISPOSAL
  8.   Case UNKNOWN
  9.        cboCartEvent = MISSING, STOLEN, UNKNOWN
  10.  
  11. End Select
  12.  
just a thought.....
Nov 21 '11 #20

NeoPa
Expert Mod 15k+
P: 31,709
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]
Expand|Select|Wrap|Line Numbers
  1. Field        Type
  2. CartEvent    String
  3. Correlation  String
Its contents would look like :
Expand|Select|Wrap|Line Numbers
  1. CartEvent  Correlation
  2. DELIVERY   INSERVICE
  3. DISPOSAL   DISPOSED
  4. FOUND      AVAILABLE
  5. MISSING    UNKNOWN
  6. REMOVAL    AVAILABLE
  7. REPAIR     INSERVICE
  8. STOLEN     UNKNOWN
  9. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Code_Desc]
  2. FROM   [dbo_tbl_CartInfoCodes]
  3. WHERE  ([Code_Type] = 'CartEvent')
  4.   AND  ([Code_Desc] In(SELECT [CartEvent]
  5.                        FROM   [tblX]
  6.                        WHERE  ([Correlation] = Forms!MainForm!cboCartStatus))
Nov 21 '11 #21

NeoPa
Expert Mod 15k+
P: 31,709
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 ;-)
Nov 21 '11 #22

Post your reply

Sign in to post your reply or Sign up for a free account.