Cascading Form issue

[GSevensM]

---

Hi guys,


I'm new to the forum and to Access. I don't know any VBA yet.

I have a problem with a cascading form, which writes back to another table. The scenario is:

There are three tables. Users, Departments & SubDepartments.

Each table has an Autonumber set as the primary key (I've been told this is best practice, but if this is wrong then I'd appreciate a steer on that too!)

The form is to write back to the user table, and within the user table there is a Department & SubDepartment field. These are linked to the relevant tables and all that works. For the form I have two combo boxes for Department and SubDepartment. Department simply pulls from the Departments table and displays/writes back to User table correctly.

The SubDepartment is the one I am having problems with. I have got it to cascade correctly but here is the exact issue:

On the existing records the subdepartment is being displayed as a number (the primary key autonumber). The drop down list displays the text of what the subdepartment is (which is what I require) but then when that is selected it throws up the error that the value entered isn't correct for that field. I guess it's because it wants to write back the numerical record ID rather than the text from the other field.

The row source query is SELECT [qrySubDepartment].[SubDepartmentName] FROM qrySubDepartment ORDER BY [SubDepartmentName];

The table fields for the subdepartment table are SubDepartmentID, SubDepartmentName, DepartmentID and SDID.
SDID can be ignored for the purpose of this question and DepartmentID links back to the main department. Just to confirm visually the cascading form does work in terms of when you pick a department it only displays the subdepartments associated to it.

I'm stumped so would appreciate any help!

[ItsMe]

You can change the properties on your combo to view a specific column from its Rowsource. You would need to add more fields to the SELECT statement, to include ID and Name fields, as well as adjust other properties like RowCount and Column Widths.

It does not make sense that you have numbers in your main table considering the combo Rowsource you pasted here so.....


If you are using lookup fields in your tables....

It is recommended that you use Controls on forms to edit data in tables. Lookup fields in tables are not generally recommended.

Replace your lookup field in your table with a regular Text field. Use another table to "Lookup" your values (or query if qrySubDepartment is based on a separate table). In the lookup table, use Autonumber as the PK.

On your form, create a control that is a ComboBox. Adjust the ComboBox's Rowsource to look at your new "Lookup" table. Have your combo populate the relative field in your main table's Foreign Key field with the PK from the new lookup table.

[GSevensM]

Thanks for the fast response. I am (was!) using lookup fields in the tables. I'll remove them if it's not best practice.

Just a quick query regarding your response that confused me. Are you saying that I should have the full text in my main tblUser that links with the tlkpDepartment table, rather than a numerical value? I was under the impression that it's best to use numerical values where possible to link tables as it's more efficient and that normalization demanded that there should be hardly any duplicate entries in a table for the same field? Or are you saying not to link tblUser with tlkpDepartment at all, and have another table that sits inbetween the two that the form will use?

As you can tell, I am quite new to DB design in general......

[ItsMe]

Use number data types in your FK fields wherever possible/practical. The most common Number type in Access, for FK’s, is Long Integer. You can JOIN long Integer fields with Autonumber fields.

Using the various states in the U.S. as an example, a lookup table for states would have Autonumber as the PK, Text as the full name, and maybe a third column of type text for the postal abbreviation.

You would store the PK value in the relative FK field of another table. You would not store any of the Text fields as a Foreign Key.

[GSevensM]

OK, that makes sense and is what I currently have in my user table. If I remove all the field look ups I end up with numbers instead (which I guess is the FK) that correlates to the PK autonumber from the other connected tables, e.g. Department.

I'm off to find out where I can select the row source. I think it may have been the field look up that is causing my issue because in the combo drop down it displays the text correctly, but won't write back to the users table as it wants to right back the PK autonumber from the Departments table, and not the text.

[ItsMe]

After getting rid of the lookup field (option) in your main table, you can use the properties of the combo control on your form to assign a value to the FK field in the main table.

While in Design View of your form, you can select/highlight the combobox and look at the property sheet. In the Data tab you can launch the query builder to create the Rowsource. Select the Autonumber field to support the Main table's FK and select the field that has the text (to benefit the user).

.



From there it is a matter of adjusting the other settings previously mentioned.

Bound Column: to match the column number of the PK (maybe 1 if it is the first row selected)
Control Source: to equal the field name of the FK in main table
Limit To List: set to yes so the user does not try to enter bad data
Column Count: 2 (considering two columns)
Column Widths: 0, 1.5 (considering two columns and hiding the first)


I think that should do it....

[GSevensM]

Thank you very much, that nailed it

[ItsMe]

No problem.....