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

"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