Can't edit via query

[Jeff H]

Can anyone tell me what’s wrong with this query? When I try to add records using it, the Date and SourceID fields give the error: “Cannot add record(s); join key of table ‘Viewings’ not in recordset.”

I use this query as the record source for a subform. I open the form from a dashboard button that first prompts for a program name to filter the list by. The ProgramName parameter is

Code: Select all

[Forms]![frmSeriesParameter]![cmbSeries]

where ‘cmbSeries’ is a combo box of all program names.

Here’s the SQL:

Code: Select all

SELECT Viewings.DateWatched, Programs.ProgramName, Episodes.EpisodeName, Episodes.Season, Episodes.Episode, Viewings.SourceID
FROM Programs INNER JOIN (Sources INNER JOIN (Episodes INNER JOIN Viewings ON Episodes.EpisodeID = Viewings.EpisodeID) ON Sources.SourceID = Viewings.SourceID) ON Programs.TitleID = Viewings.TitleID
WHERE (((Programs.ProgramName)=[Forms]![frmSeriesParameter]![cmbSeries]))
ORDER BY Viewings.DateWatched, Programs.ProgramName, Episodes.EpisodeName, Episodes.Season, Episodes.Episode;

Query.png

BTW, I have tried adding the primary keys (ViewingID, EpisodeID, and TitleID), both in the query design and in the record source property of the subform, but in both cases they disappear after I save the changes.

Thanks,
- Jeff

[HansV]

Such a query should only be used to display records, not to edit them. For example, EpisodeName, Season and Episode are uniquely determined by EpisodeID, so there should be no need to edit them in the same query where you edit Viewings records - choosing an EpisodeID is sufficient.

But you could try the following:

- Click in an empty part of the upper pane of the query design window.
- If you don't see the Property Sheet, press F4.
- Select "Dynaset (Inconsistent Updates)" from the dropdown list in the "Recordset Type" property.

[Jeff H]

That didn't seem to work. But even if it had I was going to ask what the correct way to set this up is. I thought the principle of normalized tables was that you could combine them by query and work with the data that way.

Actually, the main link is the Viewings table which is the the many-to-many connection between Programs and Episodes.

[HansV]

Editing fields from multiple tables in a single query is seldom a good idea.

On a form with the Viewings table as record source, you can use combo boxes to let the user select an episode, program and source (actually, an EpisodeID, TitleID and SourceID).

For example, the episode combo box, named EpisodeID, could have the following properties:

Control Source: EpisodeID
Row Source: the Episodes table, or a query based on the Episodes table that sorts the records the way you want.
Column Count: 4
Column Widths: 0";1";0";0"

The combo box will store the EpisodeID field, but display the EpisodeName field.
To display the Season and Episode fields on the form, use text boxes with Control Source

=[EpisodeID].[Column](2)

and

=[EpisodeID].[Column](3)

respectively. The column index is zero-based, so Column(2) is the 3rd column etc.

[Jeff H]

I'm not sure I understand that, but I'll try it out as you describe and see what I get.

Thanks Hans.

[HansV]

Let me know if you run into problems.

[Jeff H]

I'm afraid I'm spinning my wheels here. I'm trying to apply your instructions but apparently I'm doing it in the wrong context. When I select a program name from the drop down and add a text box with [TitleID].[Column](1), I just get #Name?.

The main thing is the Viewing events, not Programs or Episodes.

Here's what I'd like the form to do:
1. The Form Detail (Continuous Forms) should initially display all Viewing events sorted by DateWatched > ProgramName > Season > Episode. [Note: this is a change from what I mentioned I'm doing now, where I use a form-based parameter before opening the form.]
2. In the Form Header, I want a drop down on the Programs table sorted by ProgramName.
3. When a Program is selected I want the Form Detail to display all episodes for that program, if any. (The programs also include individual events that don't have episodes, so that's just one record in the Form Detail).
4. The fields to display in the Form Detail are: DateWatched; ProgramName; [EpisodeName (if any)]; [Season (if any)]; [Episode (if any)]; and Source.
5. Then I want to be able to add a new Viewing event or edit existing entries in the form.

This is doable, isn't it? Before normalizing the tables I was able to do this when all the data was in one table.

- Jeff

[HansV]

Could you attach a zipped copy of the database (without sensitive information)?

[Jeff H]

You bet. I took a break to built a compost bin ... had to get away from the computer for a while! I'll have to set it up after the mother's day meal planning here.

- Jeff

[HansV]

Enjoy the meal!

[Jeff H]

...Thanks, but actually we were just looking for a restaurant open for curbside pick-up. The meal is yet to come.

Anyway, here's the db. I removed a lot of the failed trials in tables and forms. But I left the table Raw Input. That is the entire list that I imported from 4 Excel sheets and then parsed into separate tables. BTW, there's nothing sensitive here. This is actually a practice db. I just read Hernandez' Database Design for Mere Mortals and I'm trying out his design methods on what I thought was a simple application. I'm using this to try and understand how to implement an Access database so I can apply it to the hospice database that I've built in Excel.

Uh oh! I'm being told the zip is too large. It's only 554kb, which is a 78% compression. I ran Compact and Repair before zipping, too. I zipped in File Explorer using Send To > Zipped Folder. Is there a better way?

[HansV]

You could upload the database to Dropbox, Google Drive, OneDrive or similar, share the file and post a link to the uploaded and shared file in a reply.
Or, if you prefer, send it to me: hans dot vogelaar at gmail dot com

[Jeff H]

I just sent it via email...

[HansV]

Thanks, I'll take a look.

[HansV]

Hi Jeff, therow source of the combo box cmbSeries on the form frmSeriesParameter refers to a table Titles that is not present in the database that you sent me,

[Jeff H]

Sorry. I changed the name of that table from Titles to Programs. Seemed to make more sense.

However, I just checked my version of the "Sample" db and I don't see that reference. On mine it refers to the field ProgramName in the Programs table. (Although the primary field is still TitleID.)

I hope I didn't somehow send you the wrong file. I'm pretty sure I only made one stripped down "Sample" file.

- Jeff

[HansV]

I don't know what happened, this is what I saw:

S3299.png

I have sent you the modified database by email.