Create temporary table and clear it after that in Form

[RowdyRoddy]

Hello everyone! I am new to access here!
I want to create a temporary table just for showing it in a subform.
I'm creating a form that when choosing a test(s), all data related to the test will be shown based on the test you have chosen. Let say I want to add 10 tests, that means the tests I've chosen will be shown in the list below. And also how do I link the add button so that I can add it to the table.

 

[Galaxiom]

This can be done on a bound form without a temporary table.

See the example on this thread.

In this structure all test will appear but only those which have a value entered in at least one field will be created in the table.

 

[RowdyRoddy]

Sorry, I don't really understand what you mean by bound form. I've checked the thread but it seems a little bit different. Can you explain a bit more of this? I only have 1 table created and there are only 5 fields which are testid, na2co3, hcl, ho3 and plain. There is 1 combobox which is the list of testid and an add button to add it to the subform table. The purpose is just to show the test(s) I've chosen so that all the rest of the field will be shown in the subform. When I'm finish the subform table will be cleared(maybe need a clear button) and choose new test(s).

 

[Galaxiom]

A bound form is the type of form the Access Form Wizard builds. Data is automatically saved when moving between records. What I am suggesting is that you can completely avoid the complexities of loading records through VBA, which is what you would have to do in the solution you are contemplating.

The main table stores the TestID and any common data for that test such as the time it was taken, TesterID who took it, the source location of the samle etc. The related table stored the results of the tests.

By outer joining the tables in the recordsource of the subform, the potential records will appear without actually existing. Entering a result on the subform of any of the displayed virtual records will automatically generate a record in the table.

It isn't a solution that is obvious to novice developers, especially when they are yet to fully comprehend relational database structure but it is worth you trying to understand. Unfortunately it is quite a learning curve.

However, getting your head around relational table structures is absolutely fundamental to effective database design. Although your proposed design could be implemented, it will lead you down the wrong path. Well-meaning but inexperienced helpers might even show you how.

 

[Galaxiom]

If you zip your database you can attach it to a post. I will try to implement the structure but it probably won't be tonight.

 

[RowdyRoddy]

There you go. Thanks in advance!