Multi-Tier Design

[mike60smart]

I would have my Data Input form as shown in the attached.
This is just one way of doing this.

[doubleJ]

I would have my Data Input form as shown in the attached.
This is just one way of doing this.

I see you changed my casing from PascalCase to camelCase.
Hehehe...
JJ

[doubleJ]

I would have my Data Input form as shown in the attached.
This is just one way of doing this.

First of all, thanks for taking the time to build a form and restructure some of my data.
I noticed you did some things differently and am just wondering if it's a "you way", a best practice, or if there is some technical reason why that way benefits this particular workflow.

1: tblEpisodes has SeriesID instead of SeriesName.
I guess that makes sense as it's pulling linked to another table (I did that same thing in other places).

2: tblEpisodes SeriesID doesn't use the query for Row Source.
I just do that to make it more readable for myself and it automates the drop-down for forms.
It looks like this is repeated in multiple tables.
Do you just prefer seeing the IDs (the data should be the same, regardless).

3: tbluScenenames broke the scene names into its own table (which I started out doing).
Will tbluScenenames just be a super-long list of individual records that are mostly just used one time?
I usually break data into tables when they have often-repeated data.
Things like Welcome and Worship will be used every episode, but most of the entries will be on-offs over the course of a year.
I would, definitely, agree to doing it this way if the majority of the records were used many times.

4: Does the "u" in tbluScenenames signify something or was it a typo?

I'm currently adding a new episode.
It took a few minutes to figure out how things work, but I think I have a good handle on it.
I appreciate the work you did.
I'll see how to implement a similar workflow into my database.
JJ

[mike60smart]

Hi JJ

In answer to your questions:-

1: tblEpisodes has SeriesID instead of SeriesName.
I guess that makes sense as it's pulling linked to another table (I did that same thing in other places).

A1 - SeriesID is the PK autonumber in tblSeries. This is linked to SeriesID - Number DataType - FK which will automatically populate when
you add a new Record in the tblEpisodes.
You were using Lookup Fields in your tables to achieve this which is not a recommended method.

2: tblEpisodes SeriesID doesn't use the query for Row Source.
I just do that to make it more readable for myself and it automates the drop-down for forms.
It looks like this is repeated in multiple tables.
Do you just prefer seeing the IDs (the data should be the same, regardless).

A2 - I always name the PK & FK's the same in all Related tables. ID indicating it is an Autonumber (PK) and a Number (FK) DataType

3: tbluScenenames broke the scene names into its own table (which I started out doing).
Will tbluScenenames just be a super-long list of individual records that are mostly just used one time?
I usually break data into tables when they have often-repeated data.

A3 - If Scenenames is not a repeating name then they should just be entered into the Control as required and NOT looking up a predetermined List.
Things like Welcome and Worship will be used every episode, but most of the entries will be on-offs over the course of a year.
I would, definitely, agree to doing it this way if the majority of the records were used many times.

4: Does the "u" in tbluScenenames signify something or was it a typo?

A4 - The u is just my method of indicating it is a Lookup Table.

[doubleJ]

I would have my Data Input form as shown in the attached.
This is just one way of doing this.

I just want to thank you, again, for making this form.
It really sparked some ideas, for mine, and it helped me really jump forward on this project.
I'll post a new version of the database, when it's finished, so you can see the changes I made.
JJ

[doubleJ]

I think this is about finished.
Functionally, it does everything that I currently want.
VirtualChurch-20211115.zip

I am having one issue where the subform's (scenes) Add buttons open a new form.
When you tab through the fields and get to a new record (or just click in the new record), it forcibly creates a new record.
It does that, because there is a hidden field that links the new form window to the subform's window that opened it.
Before I did that, you could add records all day long and when you went back, they would be gone (in the database, but not linked).
I created a public variable that I insert into that field and everything works.
As mentioned, if you accidently go into a new record, that empty record is created.
You can't Escape out, like you normally can.

In case you want to mention acDialog, I was using that, but the form opening in a little window in the corner wasn't intuitive for the other users (especially data entry).
The window opening full just works better.

As a secondary point, I'm surprised that there isn't built-in functionality to create a new combo box entry by just typing into the combo box.
I've seen various ways of doing it, and I understand that linked tables complicates things, but none of them seemed any more user-friendly.

Again, thanks for the help.
I'll continue making this better, but it's doing what was intended.
JJ