Automatically add a field to a form (1 Viewer)

Evening all,
I have a form "training record tableview1" which at the moment has 2 subforms, which each give details of training for each employee.
When a new training type is created, using form "training type" this type is added to the relevant crosstab queries, but I need this new training type be added to the relevant subform.
I don't know if this is possible, but any guidance would be very much appreciated.
PS, I know some of the naming of fields and tables is not ideal, but this is a throw back to when I started building the db as a complete novice.
Many thanks

Don't build a form for a Crosstab. They should only be queries.
Build forms to enter data. The fields won't change.

You've gone back off the rails in your table structure--and it looks like you didn't fix a few things I pointed out prior (https://www.access-programmers.co.uk/forums/showthread.php?t=294501&page=2). So, here's what I see wrong with your tables now:

1. Spaces in names. Tables nor fields should have spaces in their names. Coding/querying is more difficult because of this. You cleaned up a bunch, but still have a few.

2. Improper use of ID fields. In your relationships tool [employee grades] is linked to operators via [gradeID], however that's not actually the case. When I open operators I see that no data is in that field and you are still using [grade] to link those tables. [grade] needs to be removed from operators and [gradeID] is the field that needs to connect these tables. Same issue for [employees team].

3. Using values as field names. [Operators] shouldn't have a field named for every day of the week, instead you need a new table for this data. That table will not have a field for every day of the week, but instead a field called [DayOfWeek] where you will store those values.

4. Eliminate [currentstaff] or [leftteam]. These fields are mutually exclusive--one must be true and one must be false--that means you only need one of those fields.

Get the tables right first before moving on to forms.

Ranman256, thanks for your reply. I am still struggling to understand how I can get the results I require. There must be a way of adding an extra training type automatically.

Plog, I think I have made the changes based on your suggestions. If you have suggestions they would be most welcome.

I have imported training_rec_tableview from another bd, as an alternative.

Many, many thanks

I agree with plog that there are various things you need to sort out before worrrying further about forms.

However, once you have done so, there is absolutely no reason why you can't use crosstab queries as the basis for forms or reports.
In fact I do so regularly and as long as you manage the related issues with these, they are a very powerful tool.
Having said that, not all Access users agree about their merits

The first thing to understand is that forms based on crosstab queries will be READ ONLY.
If you need the form to be editable you will need to save the query output to a temporary table then use that for the form.
Of course you will then need to save the edited data back to the underlying table(s)

As far as adding an additional control to the form when a new training type is added, the simple answer is NO - don't even attempt it.
However this is easily resolved by having additional controls which are populated as needed.
For example you create 20 controls for training types but only use 8 currently.
The first 8 controls will be populated and visible. The remaining 12 controls are hidden.
When a new type is added, the 9th control becomes populated and visible.

This is often called a dynamic crosstab form / report

NOTE: Error trapping is necessary to cover the situation when you have more fields than controls e..g. 21 fields in the above example

See the following links for some examples of crosstab forms or reports I have created based on these principles.

https://www.access-programmers.co.uk/forums/showthread.php?t=294548

http://www.utteraccess.com/forum/index.php?s=&showtopic=2043357&view=findpost&p=2649158

http://www.accessforums.net/showthread.php?t=66521&page=2&p=360858#post360858 - see post 12 for example of an 'updateable crosstab form'

https://www.access-programmers.co.uk/forums/showthread.php?t=293770

There are many more posts by other authors

EDIT: I posted this just after your latest reply so haven't looked at that updated version

1. You still have spaces in names.

2. You are still using the wrong field as foreign keys.

3. What happened to the values in the fields named after Days?

4. Good job, this is the one thing you completely fixed.

ridders, the underlying form and the ssubforms do not have to be editable, this is done elsewhere. The subforms will conditionally formatted so that the text goes red or orange depending on whether the trainingdate is overdue. I will look into the links you have supplied, to see if I can make some sense of them.

plog, I will attend to 1 and 2. As for 3, I need to do some work to get what you explained. I understand where your coming from, it may be some what unauthodox but it does work

the underlying form and the ssubforms do not have to be editable

Click to expand...

OK - that makes it easier

Here's another link - not mine this time
https://support.microsoft.com/en-us/help/328320/how-to-create-a-dynamic-crosstab-report-in-access-2002