Updatable & editable subform?

[jfn15]

I am completely new to db/access. I am trying to build my 1st db in access2003. I have tables created, and linked as below. I need to create a main form that users can enter data about new permit holders, and within that form I also want to be able to assign data to each permit holder, from other tables (specifically tbl_animal, tbl_species, tbl_category) I need the user to be able to enter permit holder (or applicant) details and also list the various animals (and their relevant species and category). I imagine I'll need to use a combo box where users can select species by the scientific name, and then have 2 text boxes that populate with common name and category id. I also want to be able to enter how many of each species they own, and gender, animal identification etc. As this data comes from 3 other tables (tbl_animal, tbl_species & tbl_category), how do I create a subform that can be edited, updated and which also updates when a new application is added?
Hope this makes sense - and I hope I have the tables set up correctly. Tables are linked as follows:
tbl_main (app-id), other contact details columns etc.
tbl_animal has animal_id as primary key, also species_id, and other relevant fields (quantity, gender, desexed, etc.)
tbl_species has species_id as primary key, also category_id, and other relevant fields (scientific_name, common_name etc.)
tbl_category has category_id and category_desc.



I've tried creating a subform from a query bringing together the animal and species tables, that seems okay, but cannot be edited and does no update as I move through records in the main form. The basic combo box seemed fine when I created the subform, but doesn't seem to be linked correctly to the table? I have a combo box on it that allow me to select a scientific name and have 2 text boxes autofill with the common name and category id,but the data doesn't link to the main form correctly(?)

[NTC]

I don't follow your post completely; but the topic generically is a nonupdateable query. I am presuming your subform is based on a query - - and if you run that query stand alone - you find that it is nonupdateable i.e. you can not change or add a record.

This is a classic db issue. You can google the topic. it is when there is uncertainty or duplication of records as a result of the overall join of multiple tables..... or where there is an aggregation or a distinct method used.

It can be frustrating, and a bit confusing at first. But it is not a bug nor defect. So build your query 1 table at a time - and test along the way until you see which join makes it non updateable. You will have to figure out an alternative - - possibly a sub table of a sub table - - although that becomes very cumbersome to manage.

Hope it helps.

[kbrown]

Concur generally with NTC. Getting subforms to behave within a form can be challenging, particularly when you have several queries in between the two - i.e., your subform is linked to a query, which itself is linked to other queries.

Sometimes in trying to build the subform, it is best to build and test each step along the way - change one variable, test the subform. Change another variable, recreate and test the subform.

I also have found it helpful to link the dependant fields in two related tables directly using a combo box (for example), then build the queries, then build the subform. The initial link tends to help keep things straight. Once you get more experienced, trying experimenting with the "relationships" interface, where you can view visually, the relationships between all of your tables. Again, do one step at a time, as the relationships can get tricky if you aren't familiar with the language used.

Good luck.