Storing automatically populated form field data in tables

[MrBee]

Good morning!
I am currently working on a database (Access 2016) and have a situation that I am stuck on. I have a form that I am using to collect data for people that we are linking to an education scheme. This consists of mostly generic information that needs to be entered, but there are some fields I have set up to be automatically populated based on other data we hold as an organisation (this is to help with reporting).

At the moment this is achieved using data source in 3 fields as follows:

=[cboEdData].[Column](2)
=[cboEdData].[Column](3)
=[cboEdData].[Column](4)

So, when one particular piece of information (Course Pathway) is put into part of the form, the next 3 fields are populated using data in a table that holds information related to that information and that can help us report.

My problem is, that this only shows on the form and NOT in the table I am using to store student data. So, what I want to know is how I can keep the information automatically populating and showing on the form AND then being written to appropriate fields in the student, or any other table so that I can run queries or reports.

OR....have I got this totally wrong and can queries/reports be run using fields from the forms anyway, and we don't need the data to be written to the tables???

Any advice would be gratefully received.

[NeoPa]

There are two ideas in here you may need to consider.

First, do you really need it stored?
If the data from [cboEdData] changes over time and it's important that you keep an accurate copy of what it was at the time it was set - then maybe you do. If not then you don't. You simply need to look that data up when you need it. This is Database Normalisation (Database Normalisation and Table Structures) & it's a very important concept when working in databases of any flavour or description.

Second, if you decide you do need the data stored then you can't have formulas in the Controls (Presumably TextBoxes.) that are used for handling that data. You can have separate, unbound, Controls with these formulas in them if you choose, but I see no benefit in this scenario.

To handle such a situation you would want some VBA code in the AfterUpdate event of the [cboEdData] ComboBox (Not the same event of the Form itself of course!) which populates the other three Controls which would be bound to the Fields where you want this data populated.

[MrBee]

Thanks for your reply. Well, the thing I guess I need to consider is that the three attributes that are populated automatically can change over time - can be renamed and the statistical characteristics altered as policies change, so actually the best thing I can probably do is update the Tables as necessary when these changes occur and then run the reports and save them as we need them and just have explanatory notes to back up the reports.

I am not very experienced with the queries and reports side of things, I have been learning A LOT on a VERY STEEP CURVE to get the data tables and forms set up for this and have tried to put together a really tight rationale as I go.

Am I right in thinking I can run queries and generate reports straight from the fields in the form that include these pre-populated fields? So for a particular client or by the options pre-populated?

[NeoPa]

I'm not sure I understand your question very clearly so let me just make a few statements that may help & you can let me know if you still need help to understand.

Data in a database is stored in Fields within Tables. Data isn't stored anywhere else but it can be shown elsewhere.

Queries (The term for stored queries in an Access database is QueryDef.) can bring data together from mltiple tables and provide it for use in showing the data, as well as for including data in Forms & Reports. Queries often link data between tables based on the matching of one or more Fields that match between two tables. These matches often follow the Relationships between tables that are defined in your Relationships window.

An example of that, considering this is a pertinent area relating to your question, is a Customer Order with its detailed Order Lines. If you have tables called [tblCustomer], [tblOrder] & [tblOrderLine] then the unique reference defined as the Primary Key (PK) for [tblCustomer] may be a Field called [CustID]. [tblOrder] would also need that same Field as a Foreign Key (FK). this Field may have the same name as the PK of [tblCustomer] but it may not. For this example I'll work with FKs of the same name as the PKs so [tblOrder] has an FK of [CustID] too. All records in [tblOrder] that have a value in [CustID] that matches one of those in [tblCustomer] are considered to be linked to that record. The same is true of [tblOrderLine] records whose FK of [OrderID] matches the [OrderID] which is the PK of [tblOrder]. Notice then that a [tblOrderLine] record can be linked to a [tblCustomer] record via the [tblOrder] table without any direct reference itself to the PK Field [CustID].

A query can show data for linked data that includes all three tables all together in a single output record and the same customer may appear in many of them with different order data.

Forms & Reports can both display data based on raw tables as well as queries. Using ComboBox Controls they can also include data that isn't even in the query the Form or Report is based on.

I hope that answers your question, but if not feel free to re-present in a clear way so I can.