Cascading Table - need to update the information

[Tamrak]

Good morning,

I successfully built the cascading table. However, I am stuck on how to update the information back from the original field.

Assumption: tblInformation, two fields -> state, city

On the data entry, I created a form to have the information input into the tblActivity, by using information from tblInformation. When I built the form, I used an unbound field for state, called cboState, in order to filter the city that belongs to the state information.

The form works correctly. However, the information (State), does not append into the tblActivity because the field is an unbound field. I would like to have the information input from this combo box, cboState, being input into the State field in the tblActivity.

I created a button. Look like there will be another update code similar to update State field in tblActivity with this cboState. Can anyone assist in providing such codes? Do I use the "on click" procedure? Thanks.

 

[PHV]

In the AfterUpdate event of cboSate:
Me![name of bound State control] = Me!cboSate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tamrak]

Thank you. I am now having another issue regarding this database. I have the cascading database challenges.

Let’s say I have a combo box, unbounded, gathered from the table -> Complexity. The values in this table are -> Minor, Medium and Major.


I have two more tables that will obtain the values from this complexity table. Samples are:

Tbl_Problems – which has the following structure and data:

Complexity, Values, Descriptions
Minor, 0, No problem (minor)
Minor, 1, some problems (minor)
Minor, 2, a lot of problems (minor)
Medium, 0, No problem (medium)
Medium, 1, some problems (medium)
Medium, 2, a lot of problems (medium)
Major, 0, No problem (major)
Major, 1, some problems (major)
Major, 2, a lot of problems (major)

Tbl_errors – which has the following structure and data:

Complexity, Values, Descriptions
Minor, 0, No error (minor)
Minor, 1, some errors (minor)
Minor, 2, a lot of errors (minor)
Medium, 0, No error (medium)
Medium, 1, some errors (medium)
Medium, 2, a lot of errors (medium)
Major, 0, No error (major)
Major, 1, some errors (major)
Major, 2, a lot of errors (major)

The field, “Values”, is numeric. “Complexity” and “Descriptions” are text fields.

There are two fields that will receive the value and be affected when the cboComplexity changes in value, based on the selection. Let’s names these two combo fields, cboProblems and cboErrors.

The issue that I have is -> these two combo fields are set to Numeric -> because I would like to have the value of 0, 1 or 2 registered, not the description. (I customized the descriptions field – so that, I could index the data with no duplication.)

When I created a combo cascade box for complexity, I named it cboComplexity. It is an unbound combo box. On the “after update” property, the codes are the following:

Private Sub cboComplexity_AfterUpdate()
On Error Resume Next

cboProblems.RowSource = "Select tbl_Problems.Descriptions " & _
"FROM tbl_Problems " & _
"WHERE tbl_Problems.Complexity = '" & cboComplexity.Value & "' " & _
"ORDER BY tbl_Problems.Descriptions;"

cboErrors.RowSource = "Select tbl_Errors.Descriptions " & _
"FROM tbl_Errors " & _
"WHERE tbl_Errors.Complexity = '" & cboComplexity.Value & "' " & _
"ORDER BY tbl_Errors.Descriptions;"

End Sub

I am uncertain whether I used the after update code correctly for these two fields.

What the users want is to have the values registered in the combo box, with the description explained when the list has been pulled down. They do not need the descriptions to be put into the field. Only, the value of 0, 1 or 2, will be entered into the field.

When I used the codes, the error generated right away -> “The value you entered isn’t valid for this field”. I think, I indexed the wrong information.

Problems:

1. I realize that there are errors on the codes, cboComplexity. I do not know whether they will accept two values in the codes.
2. The value of 0, 1 or 2 must be in there.

Can anyone assist in correcting these codes? Thank you.

 

[PHV]

For cboProblems and cboErrors add an hidden bound column and select the [Values] field in their RowSource.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tamrak]

Thank you for your advice. However, I am unclear regarding your explanation. Can you kindly explain a little bit further regarding the "hidden bound column"? Do you suggest me to create another column?

I do appreciate your time explaining further. I am very confusing regarding what information I should put in the rowsource. Thanks.

 

[PHV]

column count: 2
columns width: 0;1
bound column: 1
control source: SELECT [Values], Descriptions FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886