Cascading comboboxes issue

Zara D

Registered User.
Local time
Today, 10:17
Joined
Sep 25, 2017
Messages
10
Hello,

I am a newbie in Access and working on my first data base (cannot attach, as size exceeds limit). Basically, upon opening, a form representing the main user screen should pop up and users should be able to add project records on a blank form.

On the entry form there are three cascading boxes (cboProcure21, cboContractType and cboContractSubtype).

In cboProcure21 we should have "Yes" and "No". If user selects "Yes", then cboContractType and cboContractSubtype should be set to specific values (only one option for each).

However, if user selects "No", in cboContractType they should be able to select two values, which in turns populates the cboContractSubtype with a list of options accordingly.

I managed to set up the dependency between cboContractType and cboContractSubtype using a query based on related tables, however, I have no idea how to set up the dependency with the cboProcure21 box.

Would you be able to help me please?

Thanks a lot,
Zara
 
Zara,

It might be helpful to you and readers, if you could tell us more about the database you are building.
You can attach files(pictures or databases) without having 10 posts if you use zip format.

Welcome to AWF.
 
Hello,

Thank you both for your input! I have been through so many sample databases, codes, etc. but without success. Apparently, it will be a steep learning curve.

I have changed the approach and now I am trying to get the values of the 2nd and the 3rd box from separate tables and I have created the following VBA code (it is not yet addressing the dependency between the 2nd and the 3rd box, which I will try to add later). In all those tables referenced in the code I removed the automatic ID columns. However, once I have saved the code and tried to test it on my entry form, when I select an option in the cboProcure21, the next two cbo-s remain empty. Do you have any idea why? There are no error messages. If it is difficult to say why without a file, I will clear out the company logo and other related info from the database and attach it as zip.

Private Sub cboProcure21_AfterUpdate()

On Error Resume Next

Select Case cboProcure21.Value
Case "No"
cboContract.RowSource = "tblContractType"

cboContractSubType.RowSource = "tblContractSubtype"
Case "Yes"
cboContract.RowSource = "tblContractType2"

cboContractSubType.RowSource = "tblContractSubtype2"

End Select

End Sub
 
Hello,

Please disregard above post. I am attaching the zipped file, so that it is easier for you to see.

In the Entry form, I am trying to set up the Contract and Contract Sub-type combo boxes based on the following logic:

If Procure21 = "Yes", the only option for Contract should be "JCT", and for Contract Sub-type the only option should be "Design and Build".

However, if Procure21 = "No", there should be two option for Contract - "NEC ECC" and "JCT". Then, depending on the selected value (NEC ECC or JCT respectively), separate lists for each of these should be generated in the Contract Sub-type.

In the attached file the dependency between the Contract and Contract Sub-type combo boxes is set correctly based on queries on linked tables.

I have wasted so many days trying to make the final link to the Procure 21 combo box but I am struggling. Could you please please help??

Thank you!
 

Attachments

I recommend you describe your "business" to be supported by this database in clear, simple English.
Getting your tables and relationships clearly designed to meet your requirements is critical. Forms will come later. Spend some time to make a model of your database/business. Use the old What, Who, When, Why, How much and How often a a guide. It will simplify design and testing.

Your tables will represent the major entities in your Business... CapitalProject, Contract, ContractType, etc

Do not use embedded spaces in field and object names--they will cause syntax errors at some point.
Good luck.
 
Cascading isn't limited to two controls nor is it limited to combos.

You need to change the ContractType table to add a column that has the values for Procure21. Then the combo for contract should refer to the combo for Procure21 in its Where clause.
 
Cascading isn't limited to two controls nor is it limited to combos.

You need to change the ContractType table to add a column that has the values for Procure21. Then the combo for contract should refer to the combo for Procure21 in its Where clause.

Hello Pat,

Thank you for your guidance. I will be trying again tomorrow. Just a question please. Since the JCT option in Contract should be always be enabled (for both Procure 21 values) does this mean that in the contract table there should be two JCT rows - one matched to the Procure 21 "No" value and another one - to "Yes"?

Cheers,
Zara
 
Yes. To do otherwise would cause you to have to create exception logic. Better to "duplicate" the data.
 
Yes. To do otherwise would cause you to have to create exception logic. Better to "duplicate" the data.

Hello Pat,

I created all the tables and tried to do the connections but now the combo boxes are empty!:banghead: I checked obvious reasons, such as bound column, column width, etc. and all seems to be OK. Do you have any idea why it is not working? I am attaching the modified file.

Thank you!

Zara
 

Attachments

see if this is what
you intend to do.

A BIG AND VERY SINCERE THANK YOU!!!

This is exactly what I needed and now I can go on with the rest. As I want to learn from that, may I please ask why the Criteria for the queries feeding the combo boxes are set up this way and not the way I had done it? I watched so many tutorials and yet what has worked out at the end seems to be so different...

Cheers,
Zara
 
your comboboxes Bound Column is 2.
meaning the text portion and not
the ID (numeric), so when you
refer to the combo it returns
the text part. so nothing is returned
on your combos.

also, you cannot use then column number
of the combo in the query, ie:

"Where field = [combo].Column(0)"

since we want to get the first Column (Column(0))
to cascade the combobox, we cannot, so
we put two Unbound textbox (hidden) and set
their values whenever you select from combos.

we then use these Unbound textbox as criteria
for the other combos.

always Requery (combo.Requery) the dependent combos
so it will return the new set of list items.

you maybe wondering why on the Criteria
i did not use something like [Forms]![frmMain]![txtContract]
and simply use [txtContract]. it is because
our query is internal to the form (part of the form) and not as
stand-alone as you can see in the Navigation Pane.
 
Zara,
The CORRECT solution is not to change the criteria but to change the bound column. You need to change the CapitalProjectsDatabase to hold the ID field NOT the text field. To make the combo continue to show the text field, you set the column width to 0" for the ID field.

arnelgp's solution would have been fine if you had simply rearranged the columns of the RowSource so that the column you want to display was first and the ID was the second column.

But, what you are doing is incorrect and so it needs to be changed. Unless you correct this, you will never be able to enforce referential integrity. So.
1. Change the data types of the procedure, contract, and sub-type
2. Bind the ID column (usually the 1st column but can be any column)
3. Use the Column Width property to hide the ID column so the combo will show the text string.
 
Hello,

Thank you all for your help. Just one final question, hopefully.

As we populate the combo boxes, I am trying to make them empty, if the first combo box (Procure21) is changed for a second time. I have used this code and it works just fine.

Private Sub cboProcure21_Dirty(Cancel As Integer)
Me.cboContract.Value = Null
Me.cboContractSubtype.Value = " "

End Sub

However, I had set a rule at the main table's level that both of these fields cannot be empty. With this code it allows the user to save the input with empy fields. Are you aware of a work-around please?

Thank you!
 
For starters "" is a ZeroLengthString. Even if Subtype is a string it would be better to set the combo to null which is nothing rather than "" which is something. I don't ever allow text fields to contain ZLS because the user can't distinguish between null and ZLS. to his eyes, the field is empty. However, the database knows better and your queries and code will need to account for the difference in behavior.

Required fields should be defined as required at the table level.

Validation code belongs in the Form's BeforUpdate event. This is where you will ensure that dates make sense. Just because IsDate() returns true doesn't mean the entry is valid. For example, birtdates can never be > today. When you are dealing with employees, the DOB can't be less than 16 years ago or whatever the working age is in your state. Sale dates cannot be in the future. You should probably also validate presence even if you have declarative RI on the tables that will raise errors. You want to give the users more meaningful error messages.
 

Users who are viewing this thread

Back
Top Bottom