How do I change the bound column without ruining the relationship between two combo boxes (1 Viewer)

[qabaselayan]

I have an issue where I'm trying to limit the data in one combo box based on another but when I change the bound column so that the text would be displayed in the bound table the relationship doesn't work and the second combo box displays nothing, please help.

 

[theDBguy]

I'd say don't change the bound column at all and don't look at the data directly in the table. Instead, create a query and use the query in forms or reports to view the data.

 

[qabaselayan]

I'd say don't change the bound column at all and don't look at the data directly in the table. Instead, create a query and use the query in forms or reports to view the data.

The thing is I can't create a report from a form so I bound the form to a table and the table to a report

 

[theDBguy]

The thing is I can't create a report from a form so I bound the form to a table and the table to a report

Like I said though, try binding your form and report to a query that combines all the data you want to display.

 

[Pat Hartman]

Forms and reports should be bound to queries rather than tables. This allows you to include joins to lookup tables so that you don't need to use combos at all on reports. It also allows you to add selection criteria to better control what you see.

So, in your report for example, you want to show the departmentName rather than the DepartmentID which is in the main table. Create a query that joins the main table to tblDept on DepartmentID and select DepartmentName from tblDept. That allows you to show the text value for Dept on the report without having to use a combo box at all. You still need the combo on the form because you want to be able to use it to control the possible values for Department.

 

[qabaselayan]

The idea of my form is that it takes info from different tables and displays them in combo boxes and in the end I want a report that shows the chosen data

Forms and reports should be bound to queries rather than tables. This allows you to include joins to lookup tables so that you don't need to use combos at all on reports. It also allows you to add selection criteria to better control what you see.

So, in your report for example, you want to show the departmentName rather than the DepartmentID which is in the main table. Create a query that joins the main table to tblDept on DepartmentID and select DepartmentName from tblDept. That allows you to show the text value for Dept on the report without having to use a combo box at all. You still need the combo on the form because you want to be able to use it to control the possible values for Department.

 

[qabaselayan]

Like I said though, try binding your form and report to a query that combines all the data you want to display.

The data I want to display is coming from the combo boxes so I'll have the same issue if I use a query?

 

[qabaselayan]

Like I said though, try binding your form and report to a query that combines all the data you want to display.

I sent you an email with the file because I can't share it here, thank you

 

[bob fitz]

I sent you an email with the file because I can't share it here, thank you

You could share it here for the benefit of all members by simply removing all real records and replacing them with just a few fictitious records to illustrate the problem

 

[qabaselayan]

You could share it here for the benefit of all members by simply removing all real records and replacing them with just a few fictitious records to illustrate the problem

it says the file is too large

 

[bob fitz]

it says the file is too large

Compact and repair. Then zip it. Attach the zipped file.

 

[qabaselayan]

This is the database, I removed some tables so it would fit

 

[qabaselayan]

Compact and repair. Then zip it. Attach the zipped file.

I did that thank you

 

[bob fitz]

I did that thank you

I have looked briefly at your db. IMHO it would be better to deal with the problems in your table design first.
e.g. in table "Titles" you have an auto-number field but you have a text field set as the Primary Key
In table "Objectives" you have field "Outcome ID" as a text field
The use of spaces in object names (fields, tables, controls etc) should be avoided (use OutcomeID rather than OutcomeID
Although I have no idea what your db is actually about, the use of numbers in field names (e.g. Outcome 1, Outcome 2 etc) suggests to me that the db is not properly normalized.
There may be other concerns, these were just the things I spotted having a quick look around

 

[qabaselayan]

I have looked briefly at your db. IMHO it would be better to deal with the problems in your table design first.
e.g. in table "Titles" you have an auto-number field but you have a text field set as the Primary Key
In table "Objectives" you have field "Outcome ID" as a text field
The use of spaces in object names (fields, tables, controls etc) should be avoided (use OutcomeID rather than OutcomeID
Although I have no idea what your db is actually about, the use of numbers in field names (e.g. Outcome 1, Outcome 2 etc) suggests to me that the db is not properly normalized.
There may be other concerns, these were just the things I spotted having a quick look around

the database is used to create a lesson plan, and the user needs to choose outcomes based on the title, the text title is the pk because when I use the id number the cascading combo boxes don't work

 

[bob fitz]

the database is used to create a lesson plan, and the user needs to choose outcomes based on the title, the text title is the pk because when I use the id number the cascading combo boxes don't work

In the attached db I have made the Auto-number fields the PK's and adjusted the combo box properties accordingly.
However, the other issues I mentioned still need to be addressed
EDIT:
Sorry, just noticed that I missed the table "Titles" which still needs the auto-number field to be make the PK

 

[Pat Hartman]

Your database needs a lot of work. I would have tried to fix it but the intent is still not clear. Looking at your report gives more insight into what you want though. Clearly, in the real world, there won't always be two of everything. In the world of relational databases, when you have more than one of something, you have "many" and many means you need a separate table. Something we cal all relate to is a person's dependents. If you are creating an employee application and you want to store information about the employee's dependents, would you add some number of columns to the employee table to hold the names of the dependents? How many columns would you add? Most American families have fewer than 5 children so would you add, 6 columns? How about 7 to be safe? What happens if the employee has 9 children? Rare but certainly possible. What about other dependents? What if you needed to also store their birth dates? That means another 7 columns. How about gender? another 7,etc.

The answer is that the solution is not to create columns to hold "many" data, the answer is to create a table where each row represents ONE instance. So, each employee has 0 or more rows in the dependent table and each row has a foreign key that points to the employee record so you know whose dependent this is and the table contains as many columns as needed to hold the other attributes:
tblDependents:
DepID (autonumber PK)
EmpID (foreign key to tblEmployee)
FirstName
LastName
DOB
Gender
Relationship (you might need this to accommodate foster children and parents, etc. if dependents other than children are allowed..

That's the kind of thinking you need to do to design this application.

Looking at the report, it looks like Procedures and Assignments are dependent on Objectives and Outcomes are dependent on Assignments. I have no idea what Trainee work means in this context. So if you create a hierarchy of tables, you can have as many objectives as each plan needs. Each objective can have as many Procedures/Assessments as are needed and the tree continues so you can have as many outcomes as you need.

Although you can use text fields as primary keys, most people prefer autonumbers. But text fields are fine as long as this isn't going to be an app with hundreds of thousands of records. Joins are more efficient on numbers than on strings. If you go with natural keys, do NOT include autonumbers. There is no reason for them. When you have an autonumber in a table, the autonumber should be the primary key. The primary key of a table is what is used as the foreign key in a "child" table. Many newbies prefer natural keys because they like looking at related tables and seeing data rather than just numbers. the choice is yours but you need to use them correctly. I would NOT use a text PK that is longer than about 10 characters. Objectives for example are long strings so I would always use an autonumber. Same for Outcomes, etc.