Why would a Microsoft Access form create records for one query but not another?

Question

I have an Access database. Let's pretend it's for a pet store.

There's a table for animals.

Animals (animal_id, birth_date, price)

And then specific tables for the different types of animals we sell.

Dogs (animal_id, bark_volume)
Cats (animal_id, collar_size, shedding_rate)
Fish (animal_id)

Fish aren't interesting so they don't have any special fields. The Fish table just exists so you know which records in the Animals table are fish.

Now, I have a general purpose form for adding animals to the pet store. Before you get the form, you first have to say what kind of animal you're adding. Based on that, the form shows/hides fields, changes its recordsource, and binds the fields to the appropriate data columns. The form pulls its data from the queries DogInfo, CatInfo, and FishInfo.

Now, when you enter a dog or a cat, everything is fine. A record is added to both Animals and either Dogs or Cats.

However, when you enter a Fish, all you get is an Animal, no Fish.

What could be causing this? Is it somehow caused by the lack of other columns on the Fish table?

(Let's leave aside the fact that updating tables with a select query makes no sense at all. I didn't expect Access to let me do it at all in the first place, but Access's motto seems to be "Make the wrong thing easy to do and the right thing awkward to do." The database is relatively simple and infrequently used, though, and it's at least 100 times better than it was before I started working on it, so I'm not really too worried about this issue as long as I can make it work.)

Answer 1

Do you not have an separate IDs for the Dogs/Cats/Fish tables? Assuming the only difference is the number of columns, I'd be curious if that suddenly fixed it.

Answer 2

"Is it somehow caused by the lack of other columns on the Fish table?"

Yes - when you enter data on child records (Dogs and Cats) Access will automatically fill in the parent ID (animal_id)

Since there is no data entry for the fish record it does not get created. You have to do that in code. Not sure how your form and data source is setup but you would do something like this on one of the form events:

Fish![animal_id] = Animal![animal_id]

Edit

In your FishInfo query you must give the Fish.[animal_id] an alias - you can't have two fields with the same name - call it Fish_animal_id

Then in the Form_BeforeUpdate event put this:

Me.Fish_animal_id = Me.animal_id

Answer 3

Have you thought about configuring relationships on the different tables? Given the design above, I would start by adding an identifying column to the specific-animal tables, and setting it as the primary key. E.g.:

Dogs(DOG_ID, animal_id, bark_volume)
Cats(CAT_ID, animal_id, collar_size, shedding_rate)

etc. In the relationships view, you'd then define a one-to-many (one-to-one?) relationship from Animals.ANIMAL_ID to Dogs.animal_id. You could then hook up the Animals table to a combo/listbox control on your form to select a record from that table. I think if you configure the control correctly, you can even create new records from that control (or you could use a subform).

Answer 4

Bad design aside, did you set up a relationship between the various tables? And did you set the tables to enforce referential integrity?