472,993 Members | 3,169 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,993 software developers and data experts.

Storing automatically populated form field data in tables

4 2Bits
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:


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.
May 10 '21 #1
3 2099
32,550 Expert Mod 16PB
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.
May 10 '21 #2
4 2Bits
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?
May 10 '21 #3
32,550 Expert Mod 16PB
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.
May 10 '21 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

by: reneeccwest | last post by:
Different user input boxes are automatically populated based on a value of the dropdown box. Can anyone help me on that?
by: Nige | last post by:
Is it possible to convert an item of form field data to uppercase using the toUpperCase() method? What I'm really asking is, how do I reference the data item? -- Nige Please replace YYYY...
by: nntp-service.ohio-state.edu | last post by:
Hey folks - I'm a newbie to java script. I'm trying to make a portable data-validator for fields in an HTML form. Ideally, it would work something like this: <input type="text" name="test"...
by: Tony | last post by:
Hello, I have a form that has a field that needs to be updated from data on another table. The field that needs updating is named RFF #. The table that contains the data is in...
by: Earthling | last post by:
Any help would be appreciated to solve the following simple problem that I will describe. *** There is a form called "red chocolate form". The form has a particular subform field that has a...
by: brian kaufmann | last post by:
Hi, I had sent this earlier, and would appreciate any suggestions on this. I need to make calculations for unemployment rate for three different data sources (A,B,C) for many countries and age...
by: amanatio | last post by:
I have a huge form with many data bound controls on it and 34 tables in database (and of course 34 data adapters and 34 datasets). The form is extremely slow to design (huge delay when I go to code...
by: Yeah | last post by:
I have a simple fill-out form with three fields, in this order: Name, E-mail, and Comment. But when I receive the E-mail containing the form data, the fields are listed backwards (Comment, E-mail,...
by: Brett_A | last post by:
I have a form where the first field is a dynamic drop-down that pulls from a db (Access). The fields associated with the query are task_id, task_name and task_rate. The field has the value of...
by: Tara Small | last post by:
I am tring to create a form in which a expiration date and pre-expiration reminder dates are calculated from a user defined certification date. I then want this calculated expiration field to show...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.